locked
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

    Saturday, October 21, 2006 1:50 AM

All replies

  • Wow, that is the best example I found on the internet! Appreciate it!
    S. Weera
    Tuesday, February 24, 2009 3:53 PM
  • 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
     
       

    Tuesday, July 21, 2009 10:49 AM
  • 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]

     

     

    Thursday, August 13, 2009 8:24 PM
  • I'm not sure how this works for anyone else, but it gives me a cross join...
    PWC
    Friday, November 20, 2009 11:23 PM
  • Thanks for your post.

    But when i bind data to gridview, it throw exception

    Exception Details: System.NotSupportedException: The 'select' and 'orderBy' calls may only reference a single common entity type.


    Any idea how to handle it, i did search on internet but so far i have no clue.

    Thanks a lot.

    Tuesday, August 31, 2010 3:37 AM
  • In your first option when you specify the LINQ, you don't seem to specify which fields you want to join on which shows up in the SQL that this LINQ produces (ON [t1].[CustomerID] = [t0].[ID])

    Is this SQL "JOIN ON" part (that specifies the PK/FK fields) automatically generated because Primary/Foreign keys (i.e. t0's PK is "ID" and t1's FK is "CustomerID") are defined in the tables you are joining and the link is included when you use the Object Relational Designer to generate the dataclasses?  Did you use the Object Relational Designer and drag and drop DB tables into it to generate DataClasses?

    It would be helpful if you attached a screenshot of your Object Relational Designer showing tables and their field names with their attributes including any PK/FK relationship arrows that show Parent/Child relationship :-)

    Tuesday, August 07, 2012 1:19 AM
  • To LachlanP:

    The first option you refer to is
     var query =
            from c in Data.Customers
            from p in c.Purchases.DefaultIfEmpty()
            select new
            {
                c.Name,
                p.Price
            };

    It's the c.Purchases reference in the line "from p in c.Purchases.DefaultIfEmpty()" that causes the join

    So the fetch is from the Purchases that are children of Customer c. You probably did the same thing I did, which was to mis-read the second line as 'from p in Data.Purchases.DefaultIfEmpty()'. Which gives you the cross join someone complained of a few years ago :)

    Thursday, February 28, 2013 3:11 AM
  • I've done my research about LINQ joins too, I'd recommend to read my blog post

    http://msguy.net/post/2013/01/04/LINQ-Join-Operations.aspx


    Please Mark as Reply and Vote as Helpful if I helped.

    Also please visit my blog http://msguy.net/

    Thursday, February 28, 2013 8:33 AM