What is the best practice for querying related tables using LINQ given the following example?

Question

Let's say I am generating a View Model from several related tables. What are the advantages/differences/disadvantages of querying either in this way:

var enrollment = db.enrollment
.Include(d => d.cohort.OldCourses.OldCourseSections.Select(f => f.OldCoursePages))
            .Include(d => d.OldProgress)
            .FirstOrDefault(b => b.UserID == currentuser && b.cohort.OldCourses.Titleabbrev == courseabbrev);
var viewModel = new OldSectionViewModel();

        viewModel.OldCourseTitle = enrollment.cohort.OldCourses.OldCourseTitle;
        viewModel.OldCourseSec_title = enrollment.cohort.OldCourses.OldCourseSections.FirstOrDefault(f => f.OldCourseSec_abbrev == secabbrev).OldCourseSec_title;
        viewModel.Meta = enrollment.cohort.OldCourses.Meta;
        viewModel.Titleabbrev = enrollment.cohort.OldCourses.Titleabbrev;
        viewModel.OldCourseSec_abbrev = enrollment.cohort.OldCourses.OldCourseSections.FirstOrDefault(f => f.OldCourseSec_abbrev == secabbrev).OldCourseSec_abbrev;
        viewModel.progress = currentprogress;
        viewModel.pages = enrollment.cohort.OldCourses.OldCourseSections.FirstOrDefault(f => f.OldCourseSec_abbrev == secabbrev).OldCourse_Page_Total;
        viewModel.EnrollmentID = enrollment.EnrollmentID;
        viewModel.OldCourseSectionID = enrollment.cohort.OldCourses.OldCourseSections.FirstOrDefault(f => f.OldCourseSec_abbrev == secabbrev).OldCourseSectionID;

        viewModel.OldCoursePage_title = enrollment.cohort.OldCourses.OldCourseSections.FirstOrDefault(f => f.OldCourseSec_abbrev == secabbrev).OldCoursePages.FirstOrDefault(g => g.OldCoursePage_Order ==
        currentprogress).OldCoursePage_title;

        viewModel.OldCoursePage_HTML = enrollment.cohort.OldCourses.OldCourseSections.FirstOrDefault(f => f.OldCourseSec_abbrev == secabbrev).OldCoursePages.FirstOrDefault(g => g.OldCoursePage_Order ==
        currentprogress).OldCoursePage_HTML;

        viewModel.OldCoursePage_Order = enrollment.cohort.OldCourses.OldCourseSections.FirstOrDefault(f => f.OldCourseSec_abbrev == secabbrev).OldCoursePages.FirstOrDefault(g => g.OldCoursePage_Order ==
        currentprogress).OldCoursePage_Order;

Or alternatively this way:

var viewModel = db.enrollment
        .Where(b => b.UserID == currentuser && b.cohort.OldCourses.Titleabbrev == courseabbrev)

        .Select(x => new OldSectionViewModel
        {
            OldCourseTitle = x.cohort.OldCourses.OldCourseTitle,
            OldCourseSec_title = x.cohort.OldCourses.OldCourseSections.FirstOrDefault(f => f.OldCourseSec_abbrev == secabbrev).OldCourseSec_title,
            Meta = x.cohort.OldCourses.Meta,
            Titleabbrev = x.cohort.OldCourses.Titleabbrev,
            OldCourseSec_abbrev = x.cohort.OldCourses.OldCourseSections.FirstOrDefault(f => f.OldCourseSec_abbrev == secabbrev).OldCourseSec_abbrev,
            progress = currentprogress,
            pages = x.cohort.OldCourses.OldCourseSections.FirstOrDefault(f => f.OldCourseSec_abbrev == secabbrev).OldCourse_Page_Total,
            EnrollmentID = x.EnrollmentID,
            OldCourseSectionID = x.cohort.OldCourses.OldCourseSections.FirstOrDefault(f => f.OldCourseSec_abbrev == secabbrev).OldCourseSectionID,

            OldCoursePage_title = x.cohort.OldCourses.OldCourseSections.FirstOrDefault(f => f.OldCourseSec_abbrev == secabbrev).OldCoursePages.FirstOrDefault(g => g.OldCoursePage_Order ==
            currentprogress).OldCoursePage_title,

            OldCoursePage_HTML = x.cohort.OldCourses.OldCourseSections.FirstOrDefault(f => f.OldCourseSec_abbrev == secabbrev).OldCoursePages.FirstOrDefault(g => g.OldCoursePage_Order ==
            currentprogress).OldCoursePage_HTML,

            OldCoursePage_Order = x.cohort.OldCourses.OldCourseSections.FirstOrDefault(f => f.OldCourseSec_abbrev == secabbrev).OldCoursePages.FirstOrDefault(g => g.OldCoursePage_Order ==
            currentprogress).OldCoursePage_Order,

        }).Single();

Assuming 'currentprogress' is an integer set elsewhere. It appears to me that the second query would be slightly more effective as I am loading on the specific fields I require, whereas with the first query I am loading the entire tables. However my understanding of these things is extremely basic and I would be interested to hear exactly which of these is more efficient and why from someone who genuinely knows what they are talking about?

Do both of these queries qualify as eager loading?

These links might be useful for you. http://stackoverflow.com/questions/36961249/using-include-doesnt-change-the-behavior or this one http://stackoverflow.com/questions/34627865/eager-lazy-and-explicit-loading-in-ef6 to decide when and why you should use **Eager** or **Lazy** Loading. — Salah Akbari, Oct 27 '16 at 13:38
this amount of repear of `x.cohort.OldCourses.OldCourseSections.FirstOrDefault(f => f.OldCourseSec_abbrev == secabbrev)` hurts my eyes. You should store the value into a variable and reuse it instead of searching your list multiple times — Franck, Oct 27 '16 at 13:47
@S.Akbari - Thanks, I will have a read of those. They seem to suggest the queries will do the same thing, although I would still like to understand the underlying mechanics of what is going on better. — Rob, Oct 27 '16 at 13:49
@Franck - That's an interesting response, by that you are inferring in the second query that each time I reference that address I do a separate search, whereas if I stored that table as a variable I can just reference the fields without looking up the table again? I did not know that was the case. — Rob, Oct 27 '16 at 13:58
@Franck - I can see how you would do that for the first query but how would I do that for the second query? Given it's form I'm not sure how that makes sense. Could you demonstrate? Thanks. — Rob, Oct 27 '16 at 14:02
@Rob i added and answer as a sample of what i mean. i makes clearer code — Franck, Oct 27 '16 at 14:50

score 1 · Accepted Answer · answered Oct 27 '16 at 14:17

Simply, in the first method, you're including all the related tables in a single query. In the second method, you're generating N+1 queries.

While generally speaking you should avoid N+1 query situations, it also depends a lot on what's happening with the data. Querying this many related tables all at once is going to bring down a ton of data, and the nature of doing these joins means that you're going to be bring down all related items. If you actually only need like the first related item from each, for example, then by doing it one big query, you're returning a lot of data that you will never utilize.

Doing N+1 queries could mean sending a lot of queries to your server, but the definition of "a lot" is variable. If it ends up being like 10-20 relatively small queries and you've got a good DB server instance with plenty of resources, it will likely not matter much, and you're better off, then, selecting just the data you actually need.

Additionally, you can possibly employ memory caching so that you may only need to run these queries just once an hour or something at the maximum. You just need to study what's happening with either option and determine which is ultimately the most efficient / makes the most sense.

- Hello Chris, sorry could I ask you a quick single question if you have a moment. When I remove the includes from the 1st query and have a look at the variable in VS that it produces, the variable seems to contain all the relevant data from the child tables already. Do I need the includes in the first query? Does all the related data get called by the query anyhow because of FirstOrDefault somehow? — Rob, Oct 27 '16 at 16:08
It depends. Entity Framework will automatically include some of the items in the `Select` call, assuming that it determines that it needs that data and can create an appropriate join based on what's there. It's not really something you should count on, though. — Chris Pratt, Oct 27 '16 at 16:41

Franck · Answer 2 · 2016-10-27T15:25:40.477

1

var viewModel = db.enrollment
    .Where(b => b.UserID == currentuser && b.cohort.OldCourses.Titleabbrev == courseabbrev).ToList()
    .Select(x => 
        {
           var oldCourseSection = x.cohort.OldCourses.OldCourseSections.FirstOrDefault(f => f.OldCourseSec_abbrev == secabbrev);
           var oldCourseSectionPage = oldCourseSection.OldCoursePages.FirstOrDefault(g => g.OldCoursePage_Order == currentprogress);

           return new OldSectionViewModel()
            {
                OldCourseTitle = x.cohort.OldCourses.OldCourseTitle,
                OldCourseSec_title = oldCourseSection.OldCourseSec_title,
                Meta = x.cohort.OldCourses.Meta,
                Titleabbrev = x.cohort.OldCourses.Titleabbrev,
                OldCourseSec_abbrev = oldCourseSection.OldCourseSec_abbrev,
                progress = currentprogress,
                pages = oldCourseSection.OldCourse_Page_Total,
                EnrollmentID = x.EnrollmentID,
                OldCourseSectionID = oldCourseSection.OldCourseSectionID,
                OldCoursePage_title = oldCourseSectionPage.OldCoursePage_title,
                OldCoursePage_HTML = oldCourseSectionPage.OldCoursePage_HTML,
                OldCoursePage_Order = oldCourseSectionPage.OldCoursePage_Order,

            };}).Single();

the whole point is to load variable within the select to do that you need to use the brackets PLUS the return keyword. here's a smaller example on how to do this. it allow you to run code before the return of the anonymous or class object (both works)

 public class Item { public int Price { get; set; } = 0; public string Name { get; set; } = ""; }

 static void Main(string[] args)
    {           
        var Collection = new List<Item>();

        var itemPrices = Collection.Select(item =>
        {
            var x = 10;
            var y = item.Price;
            return new { ItemName = item.Name, ItemPrice = x * y };
        }).ToList();

        itemPrices.ForEach(itemData =>
        {
            Console.WriteLine(itemData.ItemName + " " + itemData.ItemPrice.ToString());
        });
    }

edited Oct 27 '16 at 15:25

answered Oct 27 '16 at 14:50

Franck

4,438
1
28
55

Thanks, this is useful to know. – Rob Oct 27 '16 at 14:54
Having given this a go, unfortunately it does not appear to work. Intellisense does not like the variables being declared within the select statement. – Rob Oct 27 '16 at 15:07
you sure you have put the semi colon near the `.Single()` – Franck Oct 27 '16 at 15:08
Yes, after modifying what was there myself I also attempted to copy and paste the code in your answer when that didn't work. I also tried adding an additional } to your code as I thought one might be needed, either way I get an error. 'A lambda expression with a statement body cannot be converted to an expression tree'. – Rob Oct 27 '16 at 15:14
I was missing a bracket. just updated the sample and it works fine on my computer – Franck Oct 27 '16 at 15:14
The second example works fine, but it doesn't seem to like using that syntax with my query as per the modification of my code you have provided. I did try with adding that bracket previously, same error unfortunately. I appreciate the help either way. – Rob Oct 27 '16 at 15:19
I believe the initial line should be ToList() in order for this syntax to work i.e. .Where(b => b.UserID == currentuser && b.cohort.OldCourses.Titleabbrev == courseabbrev).ToList() I'm not entirely sure why though. – Rob Oct 27 '16 at 15:23
`.Select()` doesn't work on `IEnumerables` on `List<>` i does. that explained why it didn't work. I cannot compile your code to test so difficult to find :). Does it work well now ? – Franck Oct 27 '16 at 15:25
Yes works with that, thanks for your help Franck, I appreciate it. – Rob Oct 27 '16 at 15:27

What is the best practice for querying related tables using LINQ given the following example?

2 Answers2