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.
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:
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
Check out the MSDN article on LINQ-To-SQL joins: http://msdn.microsoft.com/en-us/library/bb425822.aspx#linqtosql%5Ftopic12
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.
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.