does using "from" more than once is equivalent to have join?

c#, linq

Solution

It is something like a join. When you say:

from customer in customers
join order in orders on customer.Id equals order.CustomerId
select whatever

that is essentially a more efficient way of writing:

from customer in customers
from order in orders
where customer.Id == order.CustomerId
select whatever

Do you see why? The first one says "hey query processor, customers and orders have a special relationship that is defined by the equality of the customer's ID and the customer ID stored in an order". The second one just says "give me the Cartesian product -- all the possible combinations of customers and orders -- and then filter out the ones that don't make any sense". They have the same effect, but the former is more efficient.

However, you can use multiple "from" clauses to do things that are more fancy than just Cartesian products. Suppose a customer can have more than one address:

from customer in customers
from address in customer.Addresses
select address

Multiple `from` clauses are actually a "select many". That is, they take a sequence, and a way of making sequences from every item of the first sequence, and the merge all the resulting sequences together.

The "select many" is simple but extremely powerful; we've already seen that you can use a "select many" to make a (slow, but correct) join operation. In fact, you can use select many to make every possible query if you are sufficiently clever and don't mind wasting a lot of time and memory. For example:

from customer in customers
where customer.City == "London"
select customer

could be written without a "where" like this:

from customer in customers
from c in (customer.City == "London" ? 
               new Customer[] {customer} : 
               new Customer[] { } )
select c;

You would be crazy to do so, but `where` and `join` are actually unnecessary -- they are just faster, shorter and more efficient ways to write a select many.

Problem

Original source