CODEWITHSUNDEEP
c#asp.netentity-frameworklinq-to-entities
Nisho
Nisho

Reputation: 365

Querying many times with Entity Framework mixes the results

I have an instance of the entities and I am trying to query the database 2 or more times with the same instance.

myAppDbEntities db = new myAppDbEntities();
List<user> usersList = db.users.Where(c => c.userid == usrID).ToList();

foreach (user u in usersList)
{
    u.items = db.items.Where(c => c.userid == u.id && c.date == today).ToList();
    item extraItem = db.items.Where(c => c.userid == u.id && c.date == yesterday).FirstOrDefault();
    //...
}

return usersList;

The problem is, when the usersList is returned, that extraItem is being included with the user's items list.

All data that I am fetching as if is being appeneded to one another.

Thanks

Upvotes: 1

Views: 63

Answers (2)

Nisho
Nisho

Reputation: 365

The solution is to use AsNoTracking() for the query that you want to be independent:

myAppDbEntities db = new myAppDbEntities();
List<user> usersList = db.users.Where(c => c.userid == usrID).ToList();

foreach (user u in usersList)
{
    u.items = db.items.Where(c => c.userid == u.id && c.date == today).ToList();
    item extraItem = db.items.Where(c => c.userid == u.id && c.date == yesterday).**AsNoTracking()**.FirstOrDefault();
    //...
}

return usersList;

Upvotes: 1

Eric Scherrer
Eric Scherrer

Reputation: 3388

If you have a foreign key defined between users and items, usersList is a pointer to myAppDbEntities.users, so no matter what you fetch from the db beforehand, if you go to a user and ask for it's items Entity Framework is going to go to the DB and get you all its items, unless you give it a filter at that time.

In order to stop this behavior and explicitly control what entities are loaded you must Detatch the entity from the context. Here is the code example I used to test this behavior using EF 6.0:

    private void Button_Click(object sender, RoutedEventArgs e)
    {
        var parents = GetParents();

        foreach (var parent in parents)
        {
            Console.WriteLine(@"Parent: " + parent.ParentId);

            foreach (var child in parent.Children)
            {
                Console.WriteLine(@"Child: " + child.ChildId);
            }
        }
    }

    private List<Parent> GetParents()
    {
        var context = new EFTestDBEntities();

        var parentList = context.Parents.Where(x => x.ParentId == 1).ToList();

        foreach (var parent in parentList)
        {
            // Commenting out the following line makes the above Console.WriteLines enumerate all children
            ((IObjectContextAdapter)context).ObjectContext.Detach(parent);
            parent.Children = context.Children.Where(x => x.ChildId == 1).ToList();
        }

        return parentList;
    }

Upvotes: 1

Related Questions