Sql – How to do joins in linq to sql

linqlinq-to-sqlnet

Hi how do you do joins in linq to sql?

I see they have the key word join and group join but what kind of joins are they?

Left, Right, Cross, Inner?

Also how do I select certain columns once I am done?

Like

Select Column1, Column2
From Table A;

Either query or method syntax is fine. I still can't decide what I like better each seem to have their pluses.

Best Answer

As a first step, I would definitely recommend reading Scott Guthrie's excellent Using Linq-to-SQL multi-part tutorial. It covers a lot of those Linq-to-SQL basics.

Also check out the Hooked on LINQ 5 minute overview - as it mentioned there, often in Linq-to-SQL, you really don't even need to express JOINs yourself - LINQ-to-SQL handles a lot of those for you directly by exposing entity references and entity set references.

If you then conclude you really MUST use an explicit JOIN, Bilal Haidar shows nicely how to do an explicit LEFT OUTER JOIN in his blog post.

The "normal" case of an INNER JOIN is relatively simple - you just add a 

from c in customers
  join o in orders on o.customerid equals c.customerid

to your query.

As for selecting individual fields - see Scott Guthrie's intro article series - he explains it in great detail!

Basically, you can do one of three things:

  • select an entire entity (like a customer) and get back all of the information for it
  • select some fields into a new existing type (e.g. "CustomerOrder" type)
  • select some fields into a new anonymous type

This would select all customer who have an order:

   from c in customers
      join o in orders on o.customerid equals c.customerid
      select c;

This would select all customer and orders into a new "CustomerOrder" type that already exists in your project somewhere:

   from c in customers
      join o in orders on o.customerid equals c.customerid
      select new CustomerOrder { CustomerName = c.Name, 
                                 CustomerID = c.CustomerId,
                                 OrderDate = o.OrderDate, ...... } ;

This would select the customer ID and order date into a new, anonymous type for you to use:

   from c in customers
      join o in orders on o.customerid equals c.customerid
      select new { CustomerID = c.CustomerId, OrderDate = o.OrderDate } ;

It's a strong type - and there's a full-fledged .NET CLR type behind this - you just don't know its name :-) But you can use these elements in code, you'll get Intellisense and all - excellent for a quick temporary manipulation of some data.

Marc

Related Topic