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

Anabolite

From what I see Please elaborate. I think using both statements in LINQ queries will clearly show how different they are. – Aeneous

Answer 1

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.

Oconnell

but is the result the same and is it when is it advisable to use the .Include vs a join – Anabolite

can someone add the actual T-sql queries behind linqtosql vs Include? would like to see t-sql queries in database, thanks! – Vere

I am doing Entity framework for couple months, can someone make the answer easier to understand also? Thanks, – Vere

i think @Jim Wooley meant that using Join or Include is depending of the result type you want to get. – Ventral

@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. – Oconnell

Answer 2

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.

Marotta

Hi can you please look at https://mcmap.net/q/340771/-entity-framework-include-command-left-or-inner-join/859154 ? it's about something you said here in your answer. – Aile

Answer 3

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.

Yasmeen