Data in memory is re-used instead of executing a new sql query

Question

I have a WCF service FooService. My service implements a method LoginAsync which takes a User object as parameters.

public async Task<Token> LoginAsync(User user)
{
    var result = await _userManager.GetModelAsync(user.Uname, user.Pword);
    if (result != null && result.Id > 0)
        return await _tokenManager.GetModelAsync(result);                          
    return null;
}

Inside this method we call _userManager.GetModelAsync(string, string) which is implemented as follows:

public async Task<User> GetModelAsync(string username, string password)
{
    var result =
        (from m in await _userRepo.GetModelsAsync()
            where m.Uname.Equals(username, StringComparison.InvariantCulture)
                && m.Pword.Equals(password, StringComparison.InvariantCulture)
        select m).ToList();

    if (result.Any() && result.Count == 1)
    {
        var user = result.First();
        user.Pword = null;
        return user;
    }
    return null;
}

To mention it again: this is all server-side code. I never want my service to send back the Pword field, even though it is not clear text. I just don't want that information to be on my client-side code.

This is why I'm setting this property to NULL when I found a User by comparing username and password.

Here's how _userRepo.GetModelAsync() is implemented (don't get confused with _userManager and _userRepo):

public async Task<IList<User>> GetModelsAsync()
{
    return await MediaPlayer.GetModelsAsync<User>(_getQuery);
}

private readonly string _getQuery = "SELECT ID, Uname, DateCreated, Pword FROM dbo.[User] WITH(READUNCOMMITTED)";

And here MediaPlayer.GetModelsAsync<T>(string, params IDbParameter[])

public static async Task<IList<T>> GetModelsAsync<T>(string query, params DbParameter[] parameters)
{
    IList<T> models;
    using (SqlConnection con = new SqlConnection(Builder.ConnectionString))
    using (SqlCommand command = Db.GetCommand(query, CommandType.Text, parameters))
    {
        await con.OpenAsync();
        command.Connection = con;          
        using (SqlDataReader dr = await command.ExecuteReaderAsync(CommandBehavior.SequentialAccess))
            models = ReadModels<T>(dr);
    }
    return models;
}

This code works fine the first time executing it after publishing or restarting this service (the service is consumed by a WPF application). But calling FooService.LoginAsync(User) a second time, without publishing or restarting the service again, it will throw a NullReferenceException in my _userManager.GetModelAsync LINQ because Pword is NULL.

Which is really strange to me, because as you can see there is no logic implemented where my data is explicit stored in memory. Normally my code should execute a sql query everytime calling this method, but somehow it doesn't. It seems like WCF does not get its data from my database, instead re-uses it from memory.

Can somehow explain this behavior to me and what I can do against it?

Edit 26.09.2018

To add some more details:

The method _userManager.GetModelAsync(string, string) always gets called, same for _userRepo.GetModelsAsync. I did some file logging at different points in my server-side code. What I also did, is to take the result of _userRepo.GetModelsAsync, iterated through every object in it and logged Uname and Pword. Only Pword was NULL (did this logging before doing my LINQ). I also logged the parameters _userManager.GetModelAsync(user.Uname, user.Pword) receives. user.Uname and user.Pword are not NULL.

Answer 1

I just noticed that this question was reposed. My diagnosis is the same:

What I am thinking right now, is that my service keeps my IList with the cleared Pword in memory and uses it the next time without performing a new sql query.

LINQ to SQL (and EF) reuse the same entity objects keyed on primary key. This is a very important feature.

Translate will give you preexisting objects if you use it to query an entity type. You can avoid that by querying with a DTO type (e.g. class UserDTO { public string UserName; }).

It is best practice to treat entity objects as a synchronized mirror of the database. Do not make temporary edits to them.

Make sure that your DataContext has the right scope. Normally, you want one context per HTTP request. All code inside one request should share one context and no context should ever be shared across requests.

So maybe there are two issues: You modifying entities, and reusing a DataContext across requests.