Chapter 14

papadan · April 2nd, 2018, 03:49 PM

I am having trouble understanding a join query in LINQ versus an Include.
I read your explanation on the include method, but I am still unclear.

First of all, I ran the query on page 499 for anonymous types:

Code:

var authorizedReviews = from review in myEntities.Reviews
                        where review.Authorized == true
                        select new {review.id, review.Title, review.Genre.Name};

It worked fine. But I did not use myEntities.Reviews.Include("Genre"), nor did I use any join statements.

Why was review.Genre.Name even available?

Imar · April 3rd, 2018, 06:42 AM

Because you're not joining in the database here, you're project against the model. EF knows that a Review has a Genre which has a Name. So when you use review.Genre.Name, EF will create a SQL statement that uses a JOIN to get the data from the related tables.

Hope this helps,

Imar

papadan · April 3rd, 2018, 07:51 AM

I'm confused by your statement, sorry.

You say, "Because I'm NOT joining, the EF knows..."
Are you saying that if I HAD joined, the EF would NOT have known...?

If the EF knows (because I didn't join) - then why would I ever need to create a join?

Same question, but with the Include.etc - why would I ever need to use the Include("Genre") or Include("Reviews")

Imar · April 3rd, 2018, 09:08 AM

I am confused about your follow up.

I said you're not joining in the database here but using projection in the model. EF understands your model and database and is therefore able to convert your LINQ expression to SQL that does contain JOINs.

A lot of the joins are used for EF's Navigation properties. This allows you to eager load data (or lazy load). You can also use your own JOINs (even against ones that don't exist in the database).

Include is used to eager load data. So, say you get all Genres, no Review data is retrieved initially. Then depending on your settings, two things can happen when you iterate over your Genres and access the Reviews collection:

1. Lazy loading is off. In that case, Reviews will be empty.
2. Lazy loading is on. In that case, the Reviews are retrieved when you access the Reviews collection which means one additional query for each genre in your result.

Using Include, you can eager load that data so you can scenario 2 with hitting the database only once.

papadan · April 3rd, 2018, 09:49 AM

Thanks for the clarification!

I guess I just need to better understand the EF and the difference between when the DB is getting hit versus what's coming over into properties.

I have been primarily working with accessing the DB via ADO and need to better grasp the concept of Data Sets. I was under the impression that data came over into objects (from classes - data sets, for example) - and that any querying was done at this level - with no more hitting on the DB. Unless a refresh occurred, but maybe I'm not understanding the EF correctly.

Imar · April 3rd, 2018, 10:23 AM

It depends on how you manage your context. In a long-lived desktop application you might be right. The context is kept around, minimizing the need to go to the database.

For web apps, the DB Context is typically short-lived. That means it's usually created per HTTP request. You then fetch some data, or update existing data, and create a new context on the new request.

Are you familiar with the SQL Server Profiler (https://docs.microsoft.com/en-us/sql...ver-profiler)? it's an interesting tool to turn on an watch your database being accessed by EF.

papadan · April 3rd, 2018, 10:25 AM

I forgot about profiler - I used it all the time in my last job! I will check that out as I create these LINQ objects. Thanks very much!