How to determine whether to use join in linq for sql?

Question

I'm just wondering how we can determine whether to use join or not in linq to sql .

Eg. let's say if we have two tables like this

 Table 1 Customer id name Table 2 addresstype id address1 customerid 

and

 var address = from cu in Customer from ad in addresstype where cu.id == ad.customerid select ad; 

or

 var address = from cu in Customer join ad in addresstype on cu.id equals ad.customerid select de; 

Both methods are the same. Is there a difference in performance?

And the second method, will it appear with an error if there is no match?

Answer 1

Basically, these two LINQ queries are equivalent to the following SQL queries:

  select ad.* from Customer cu, AddressType ad where cu.ID == ad.CustomerID -- I assume this was meant by the OP 

and

 select ad.* from Customer cu inner join AddressType ad on cu.id = ad.CustomerID; 

The difference between the two queries is mostly semantic, as the database will do the same in both cases and return the same result set for both queries.

I would prefer join syntax in SQL and LINQ, as it defines an explicit relationship between two tables / entities, which is implied only in the version without connection.

Answer 2

Are you using linq for entities or linq for SQL? If its the first, then you can avoid both of them by defining your relationships in the model and using the navigation properties. That would be the best way to do things.

Answer 3

This is similar to the same query, they return the same result, but I don’t know which one can be faster, it should be marked with a bench.

But, in the case of linq2sql , I prefer the correlated subquery over the join, because currently, if you want to check the equation of two elements, you should use the syntax:

 new {X,Y} equals new {X',Y'} 

in a connection, and if you have more than this equation, you should convert it to a subquery. Therefore, I prefer to have more readable code that uses minimal differences in difference actions.

Answer 4

To add a third and more preferred method to the mix with LINQ to SQL, use the associations between the tables (even if you have not configured them in your database). In this case, you can navigate through the graph of objects, and not using connections:

 var query = from cu in Customer from ad in cu.Addresses select ad; 

Note: when querying object graphs, LINQ to SQL translates the join into the left outer join, where - since the join / where syntax is by default the inner join.

Connections in LINQ should be used when there is no natural connection between objects. For example, use the connection if you want to see a list of stores located in the same city as your customers. (Join the .Address.City customer with Store.Address.City).

Answer 5

There should be no difference between the two queries. I really wondered this question a few months ago. I checked this through LINQPad. This is a free tool that you can download and actually view the generated SQL of any LINQ query (this is a query that is sent to the database).

The generated SQL must be the same for these two queries.

If you do this through Visual Studio, you can also see the generated SQL.