Recommended way to do outer joins in LINQ to SQL

Question

First option:

    var query =
        from c in Data.Customers
        from p in c.Purchases.DefaultIfEmpty()
        select new
        {
            c.Name,
            p.Price
        };

This produces the SQL you'd expect, i.e.

    SELECT [t0].[Name], [t1].[Price]
    FROM [Customer] AS [t0]
    LEFT OUTER JOIN [Purchase] AS [t1] ON [t1].[CustomerID] = [t0].[ID]

Second option:

    var query =
        from c in Data.Customers
        from p in c.Purchases.DefaultIfEmpty()
        select new
        {
            c.Name,
            Price = (p == null ? null : p.Price)
        };

This type of query works over both IEnumerable and IQueryable, but the SQL it produces seems less efficient:

SELECT [t3].[Name], [t3].[value] AS [Price]
FROM (
    SELECT
        (CASE
            WHEN [t2].[test] IS NULL THEN NULL
             ELSE [t2].[Price]
         END) AS [value], [t0].[Name]
    FROM [Customer] AS [t0]
    LEFT OUTER JOIN (
        SELECT 1 AS [test], [t1].[CustomerID], [t1].[Price]
        FROM [Purchase] AS [t1]
        ) AS [t2] ON [t2].[CustomerID] = [t0].[ID]
    ) AS [t3]

Which is the recommended way to do an outer join? Should the query be written differently depending on whether it's going to run over LINQ to SQL or a local collection?

Joe

Answer 1

Wow, that is the best example I found on the internet! Appreciate it!

---

S. Weera

Answer 2

Hi Joe Albahari

I have following Five Entities

1. Contract
     ID         Guid
     Name     string
     Navigation Properties
      ContractDefinions

2. ContractDefinition
     ID         Guid
     Name     string
     Portal     bit
     Device    bit
     Navigation Properties
     Contract
     Definition

3. Definition
    ID
    Modified
   Navigation Properties
   ContractDefinitions
   Categorys
   Versions

4. Category
    ID
    Name
    Navigation Properties
    Definitions

5. Version
    ID
    Name
    Number
    State
    Navigation Properties
     Definition

Now I want to get Version.ID, Version.Name, Version.Number, Version.State, ContractDefiniton.Portal, ContractDefiniton.Device Where Version.State=2  Category.ID=1 and Contract.ID=1

I want to get all those records those are in version table as well as in contractdefinition table. In addition all those record those are present in version table but not in ContractDefinition table . I think it is an outer join. How I can query it please
 
   

Answer 3

Third option:

var query =
  from t in (
    from c in Data.Customers
    from p in c.Purchase.DefaultIfEmpty()
    select new { c.Name, p.Price }
  )
  select new { c.Name, Price = ( p.Price == null ? null : p.Price ) }

This is be translated into

SELECT [t2].[Name],
  (CASE WHEN [t2].[Price] IS NULL THEN NULL
             ELSE [t2].[Price]
   END)
FROM (
   SELECT [t0].[Name], [t1].[Price]
    FROM [Customer] AS [t0]
    LEFT OUTER JOIN [Purchase] AS [t1] ON [t1].[CustomerID] = [t0].[ID]
) AS [t2]

 

 

Answer 4

I'm not sure how this works for anyone else, but it gives me a cross join...

---

PWC