C# Entity Framework - What happens in the database when you fill a list with a sub query?

Question

I have a few models that I fill in using entity framework with a MS SQL Server back end. The model consist of a record and many records that associate with that one record. I use a list to hold these records. In a LINQ Entity framework query, I write a sub query to get the list, but I don't know if I'm making a trip to the database at that moment. I may want to add on a where clause later etc.

This is the way I currently do it:

public class TestOwner
{
    public Owner OwnerRecord { get; set; }
    public List<OwnerUser> OwnerUsers { get; set; }

    public TestOwner()
    {

    }
}

public static class DataTools
{
    public static TestOwner testQuery()
    {
        //Create the holder variable for the database context.
        MyEntities db = (MyEntities)CommonDataTools.GetDbContext();

        //Places the database context in a using statement to have it disposed of when we are done.
        using (db)
        {
            //Get the test owner.
            return (from o in db.Owner
                    where o.owner_id == 44
                    select new TestOwner()
                    {
                        OwnerRecord = o,
                        OwnerUsers = (from ou in db.OwnerUser
                                      where ou.owner_id == o.owner_id
                                      select ou).ToList() //This is the part in question, what does this do in the database? Is this the proper way?
                    }).FirstOrDefault();
        }
    }
}

Note the query where I turn the sub query into a list.

I've been wondering if this is "better":

public class TestOwner
{
    public Owner OwnerRecord { get; set; }
    public IEnumerable<OwnerUser> OwnerUsers { get; set; }

    public TestOwner()
    {

    }
}

By making in IEnumerable, I don't need the .ToList but I'm not sure if that will hurt me later on.

So for the list version, what does that do in the database? Does the query partially get run? Is this the proper way to handle this situation? If not, how is the correct way to fill a list inside of the object we are filling from the database?

A good method of seeing what's really happening this is to use [Entity Framework logging](https://msdn.microsoft.com/en-us/library/dn469464(v=vs.113).aspx), if you're using EF6 or later. This will log the SQL generated by the LINQ queries. It's quite an eye-opener. — markpsmith, Jul 13 '17 at 16:15

score 1 · Accepted Answer · answered Jul 13 '17 at 16:20

1

First you should run the SQL profiler and see with the current code how many queries are getting generated and being sent to the database.

The Entity framework generates sql queries based on your LINQ statements. Those queries would be sent to database. Now let's consider your current code:

Part I:

  return (from o in db.Owner
            where o.owner_id == 44

This will return a IQueryable<> object which generated and trigger query in the DB when enumerated in loop or a aggregate function like 'count(), sum()' etc. or conversion functions like .ToList() is called.

Same will happen to your inner LINQ statement. But the difference is every time you enumerate the outer statement result the sub query will be triggered to database because you have a .ToList(). Part II:

(from ou in db.OwnerUser
   where ou.owner_id == o.owner_id
   select ou).ToList()

What if you remove `ToList()'? Your sql will be generated as single statement including your sub query. So enumeration will only trigger query in the DB only once. So it would reduce your round trips to DB.

Further, IEnumerable<> would be a better choice as it supports deferred execution like IQueryable<>.

For Further reading Returning IEnumerable<T> vs. IQueryable<T>

answered Jul 13 '17 at 16:20

vendettamit

14,315
2
32
54

Interesting. So it would seem using ToList is not what I want as it will run too many queries. I read your link for further reading and see that IQueryable is better than IEnumerable as you can add to it and have it combine in one SQL statement. How am I able to handle that here? If I change the model to use IQueryable for the list part, will it be executed when I run the FirstOrDefault line? I basically am wondering how I can get this to work with the proper one database call. – SolidSnake4444 Jul 13 '17 at 16:59
To expand, I just tried to change the IEnumerable line to IQueryable and once I return from the function and the database context is gone, I try to use the IQueryable values and get: "This method is not supported against a materialized query result" exception. – SolidSnake4444 Jul 13 '17 at 17:03
I did and it worked, however in your further reading link it states that if I wanted to add a where clause to the IEnumerable after the fact, it would execute the query and then run in memory while an IQueryable would be just one sql statement with the where added. I'm hoping for a method that works in all cases. – SolidSnake4444 Jul 13 '17 at 18:12
That's true but you would need a DbContext associate with that IQueryable which in your case doesn't apply as you Entity is not associated with any context object. IQueryable produces queries which get executed when deferred execution occurs. So holding on to IEnumerable<> object would also prevent the execution unless the Enumerator is generated. – vendettamit Jul 13 '17 at 18:16
The link I have provided was only for FYI not an add on to the explanation. Just in case if you may wonder about IQueryable instead of IEnumerable. May be this link was not the best resource at this moment. – vendettamit Jul 13 '17 at 18:17

C# Entity Framework - What happens in the database when you fill a list with a sub query?

1 Answers1