CODEWITHSUNDEEP
.netlinqlinq-to-sql
chobo2
chobo2

Reputation: 85865

How to do joins in linq to sql?

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.

Upvotes: 2

Views: 1149

Answers (3)

dnatoli
dnatoli

Reputation: 7012

Check out the MSDN article on LINQ-To-SQL joins: http://msdn.microsoft.com/en-us/library/bb425822.aspx#linqtosql_topic12

A join in LINQ is an inner join.

A group join in LINQ is a join that has an into clause. The parent information is joined to groups of the child information. That is, the child information is coalesced into a collection and the child collection's parent information occurs only once.

Upvotes: 0

marc_s
marc_s

Reputation: 755371

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

Upvotes: 4

Lance Fisher
Lance Fisher

Reputation: 25823

The join keyword in LINQ will give you an inner join. You can "project" your results by creating a new object in your select clause. That object can be typed or anonymous. Here's an example:

var ordersAndProducts = from o in orders
    join p in products on o.ProductId equals p.ProductId
    select new { OrderDate = o.OrderDate, ProductName = p.Name };

This query does an inner join on the orders and products collections on the ProductId property. Each item in the resulting collection is projected into an anonymous object (new {..list of properties/values...} creates an object on the fly.

If you are using LINQ to SQL (and it looks like you are), you can see the SELECT statement that is sent to your database by either debugging your application or running the profiler on your database.

Upvotes: 0

Related Questions