LINQ Include vs Join. Are they equivalent?

Question

I have used join in linq to join 2 tables. What is the difference between a join and Include. From what I see, they both behave the same.

    Include vs. Join

*From what I see* Please elaborate. I think using both statements in LINQ queries will clearly show how different they are. — Gert Arnold, Jul 11 '21 at 12:21

score 56 · Accepted Answer · edited May 07 '20 at 20:58

56

An Included is intended to retain the original object structures and graphs. A Join is needed to project a flattened representation of the object graph or to join types which are not naturally related through the graph (ie. join the customer's city with a shipping facility's city).

Compare the following: db.Customers.Include("Orders") Generates an IEnumerable each of which may contain their corresponding list of Orders in an object graph like this:

Customer 1
   Order
   Order
   Order
Customer 2
   Order
   Order

In contrast, if you do the same with a join projecting into an anonymous type you could get the following:

    from c in db.Customers 
    join o in db.Orders on c.CustomerId equals o.CustomerId 
    select new {c, o}

This produces a new IEnumerable<Anonymous<Customer, Order>> where the customer is repeated for each order.

{ Customer1, orderA }
{ Customer1, orderB }
{ Customer1, orderC }
{ Customer2, orderD }
{ Customer2, orderE }
{ Customer2, orderF }

While both may issue the same request to the database, the resulting type may be quite different.

edited May 07 '20 at 20:58

Community

1
1

answered Sep 05 '12 at 18:22

Jim Wooley

10,169
1
25
43

1

but is the result the same and is it when is it advisable to use the .Include vs a join – Nate Pet Sep 05 '12 at 19:32
4

can someone add the actual T-sql queries behind linqtosql vs Include? would like to see t-sql queries in database, thanks! – May 20 '18 at 08:35
3

I am doing Entity framework for couple months, can someone make the answer easier to understand also? Thanks, – May 20 '18 at 08:39
1

i think @Jim Wooley meant that using Join or Include is depending of the result type you want to get. – Terai May 06 '20 at 17:19
@user8280126, Unfortunately, the actual TSQL queries depend on the provider and version of EF. It changed between each version of EF core for example until 3.x with 1.x actually doing lazy evaluation even though lazy loading wasn't enabled until 2.x, so it's not simple to show you the SQL here and the point of my comment was more on the resulting object shapes than the generated SQL statements. – Jim Wooley Apr 15 '21 at 15:44

score 18 · Answer 2 · answered Sep 05 '12 at 15:27

18

In a sense, yes. Include is implemented as a join. Depending on the nullability of the included link it is an inner or left join.

You can always build an include yourself by using a join, like this:

db.Users.Select(u => new { u, u.City })

This is an "include" for the user's city. It manifests itself as a SQL join.

answered Sep 05 '12 at 15:27

usr

168,620
35
240
369

Hi can you please look at http://stackoverflow.com/q/18809817/859154 ? it's about something you said here in your answer. – Royi Namir Sep 15 '13 at 08:15

score 5 · Answer 3 · answered Aug 31 '20 at 04:45

If you simply need all Orders for some Customers. Good example here for blog application is displaying all Comments below Articles always. Then Include is your way of work.

Join in opposition is more helpful if you need some Customers and filters out them using some data contained in Orders entity. For example you want to sort out Articles to send to the Police Articles with Comments containing vulgar words.

Also if your Orders entity contains a lot of data (many columns) taking a lot of memory and you don't need all of them then join can be much more efficient but here always is a question what "lot of data" or "many columns" means so test first will be the best choice.

LINQ Include vs Join. Are they equivalent?

3 Answers3

Linked