Sql – LINQ2SQL – Cross join emitted when I want inner join

linqlinq-to-sqlsql-server-2008

This emits inner joins, which is what I want and works:

var q =
    from itm in esdc.items
    join itmImg in esdc.itemImages on itm.itemId equals itmImg.itemId
    join itmIdent in esdc.itemIdentities on itm.imgIdentityId equals itmIdent.itemIdentityId
    join startImgs in esdc.vStartPgImgs on itmImg.imgId equals startImgs.imgId
    select ...

This works too, but cross joins were emitted. How do I get inner joins? I double checked the entities and the relationships are correct.

var q =
    from itmIdent in esdc.itemIdentities
    from itm in itmIdent.items
    from itmImg in itm.itemImages
    join startImgs in esdc.vStartPgImgs 
        on itmImg.imgId equals startImgs.imgId
    select ...

If you need me to post the emitted SQL and the entity setup, let me know.

Best Answer

To put it simply: you get inner joins if you use the first form. Multiple from clauses represent cross joins (sort of, anyway ¹) in LINQ.

Basically, if you want inner joins, use multiple join clauses.

¹ (I say sort of because the subsequent sequences can depend on the earlier ones... but for LINQ to SQL they're almost always cross joins.)

Related Solutions

C# – the syntax for an inner join in LINQ to SQL

It goes something like:

from t1 in db.Table1
join t2 in db.Table2 on t1.field equals t2.field
select new { t1.field2, t2.field3}

It would be nice to have sensible names and fields for your tables for a better example. :)

Update

I think for your query this might be more appropriate:

var dealercontacts = from contact in DealerContact
                     join dealer in Dealer on contact.DealerId equals dealer.ID
                     select contact;

Since you are looking for the contacts, not the dealers.

Sql – How to do joins in linq to sql

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

Best Answer

Related Solutions

C# – the syntax for an inner join in LINQ to SQL

Sql – How to do joins in linq to sql

Related Topic