none
DefaultIfEmpty with join RRS feed

  • Question

  • Hello ,

     I want to know what is the difference between two queries :

       var spainCustomers =
            from cust in nwindDataContext.Customers
            where cust.Country == "Spain"
            join ord in nwindDataContext.Orders on cust equals ord.Customer
            into groupedOrders
            select new
            {
              companyName = cust.CompanyName,
              orders = groupedOrders
            };

     

    var spainCustomers =
            from cust in nwindDataContext.Customers
            where cust.Country == "Spain"
            join ord in nwindDataContext.Orders on cust equals ord.Customer
            into groupedOrders
            from t in groupedOrders.DefaultIfEmpty()
            select new
            {
              companyName = cust.CompanyName,
              orders = groupedOrders
            };

     

    is the first one "left outer join" and second one is "cross Join" ?  or what?


    ASk
    Thursday, May 26, 2011 2:57 PM

Answers

  • Hi Himo,

     

    As I can see in the first one you will get a list IEnumerable<'a> where 'a is an anonymous type containing a customer name and a list of the orders.

    This one is a left outer join, so the result will contain all of the customers (from Spain because of the where statement) whether they have orders or not.

     

    The second query does not makes too much sense for me as you select all of the orders again and create the anonymous class for each customer as many times as many orders it has.

    e.g.: we have Customer1 with two orders Order1 and Order2

    then the result will contain two elements:

    { CompanyName = Customer1.CompanyName, orders = {Order1, Order2}}

    { CompanyName = Customer1.CompanyName, orders = {Order1, Order2}}

     

    In this case the DefaultIfEmpty does not makes any difference.

    http://msdn.microsoft.com/en-us/library/bb360179.aspx

    The DefaultEmpty returns the elements of the specified sequence or the type parameter's default value in a singleton collection if the sequence is empty.

    e.g.:

     

       List<int> numbers = new List<int>();
    
       foreach (int number in numbers.DefaultIfEmpty())
       {
        Console.WriteLine(number);
       }
    
       /*
        This code produces the following output:
    
        0
       */
    

     

    I hope it helps for you :)

     

     


    Regards, Peter
    • Proposed as answer by Crick3t Tuesday, May 31, 2011 8:28 AM
    • Marked as answer by Jackie-SunModerator Thursday, June 2, 2011 11:03 AM
    Thursday, May 26, 2011 4:59 PM
  • Hi himo;

    Below are the two queries you posted with what Linq translated them into T-SQL. The first query I have no issues with the second query by just adding this line to the query, from t in groupedOrders.DefaultIfEmpty(), and not even using it gave a strange translation as you can see, it produced two Left Outer Joins as can be seen below. I have no idea why thy translate it like this and is the reason for duplicates in the results set.

    I also included a third query which uses the relationship in the database to create the same T-SQL as your first query bu a little less verbose in the Linq query.

    var spainCustomers =
         from cust in nwindDataContext.Customers
         where cust.Country == "Spain"
         join ord in nwindDataContext.Orders on cust equals ord.Customer
         into groupedOrders
         select new
         {
          companyName = cust.CompanyName,
          orders = groupedOrders
         };
    
    T-SQL produced by Linq for this query
     
    -- Region Parameters
    DECLARE @p0 NVarChar(1000) = 'Spain'
    -- EndRegion
    SELECT [t0].[CompanyName] AS [companyName], 
        [t1].[OrderID], [t1].[CustomerID], 
        [t1].[EmployeeID], [t1].[OrderDate], 
        [t1].[RequiredDate], [t1].[ShippedDate], 
        [t1].[ShipVia], [t1].[Freight], 
        [t1].[ShipName], [t1].[ShipAddress], 
        [t1].[ShipCity], [t1].[ShipRegion], 
        [t1].[ShipPostalCode], [t1].[ShipCountry], (
      SELECT COUNT(*)
      FROM [Orders] AS [t2]
      WHERE [t0].[CustomerID] = [t2].[CustomerID]
      ) AS [value]
    FROM [Customers] AS [t0]
    LEFT OUTER JOIN [Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
    WHERE [t0].[Country] = @p0
    ORDER BY [t0].[CustomerID], [t1].[OrderID]
    
    =======================================================================================
     var spainCustomers2 =
         from cust in nwindDataContext.Customers
         where cust.Country == "Spain"
         join ord in nwindDataContext.Orders on cust equals ord.Customer
         into groupedOrders
         from t in groupedOrders.DefaultIfEmpty()
         select new
         {
          companyName = cust.CompanyName,
          orders = groupedOrders
         };
    
    T-SQL produced by Linq for this query
      
    -- Region Parameters
    DECLARE @p0 NVarChar(1000) = 'Spain'
    -- EndRegion
    SELECT [t0].[CompanyName] AS [companyName], 
        [t2].[OrderID], [t2].[CustomerID], 
        [t2].[EmployeeID], [t2].[OrderDate], 
        [t2].[RequiredDate], [t2].[ShippedDate], 
        [t2].[ShipVia], [t2].[Freight], 
        [t2].[ShipName], [t2].[ShipAddress], 
        [t2].[ShipCity], [t2].[ShipRegion], 
        [t2].[ShipPostalCode], [t2].[ShipCountry], (
      SELECT COUNT(*)
      FROM [Orders] AS [t3]
      WHERE [t0].[CustomerID] = [t3].[CustomerID]
      ) AS [value]
    FROM [Customers] AS [t0]
    LEFT OUTER JOIN [Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
    LEFT OUTER JOIN [Orders] AS [t2] ON [t0].[CustomerID] = [t2].[CustomerID]
    WHERE [t0].[Country] = @p0
    ORDER BY [t0].[CustomerID], [t1].[OrderID], [t2].[OrderID]
    
    ===========================================================================================
    var spainCustomers3 =
         from cust in nwindDataContext.Customers
         where cust.Country == "Spain"
         select new 
         {
          companyName = cust.CompanyName,
          orders = cust.Orders
         };
    
    T-SQL produced by Linq for this query
         
    -- Region Parameters
    DECLARE @p0 NVarChar(1000) = 'Spain'
    -- EndRegion
    SELECT [t0].[CompanyName] AS [companyName], 
        [t1].[OrderID], [t1].[CustomerID], 
        [t1].[EmployeeID], [t1].[OrderDate], 
        [t1].[RequiredDate], [t1].[ShippedDate], 
        [t1].[ShipVia], [t1].[Freight], 
        [t1].[ShipName], [t1].[ShipAddress], 
        [t1].[ShipCity], [t1].[ShipRegion], 
        [t1].[ShipPostalCode], [t1].[ShipCountry], (
      SELECT COUNT(*)
      FROM [Orders] AS [t2]
      WHERE [t2].[CustomerID] = [t0].[CustomerID]
      ) AS [value]
    FROM [Customers] AS [t0]
    LEFT OUTER JOIN [Orders] AS [t1] ON [t1].[CustomerID] = [t0].[CustomerID]
    WHERE [t0].[Country] = @p0
    ORDER BY [t0].[CustomerID], [t1].[OrderID]
    
    

     


    Fernando

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Thursday, May 26, 2011 5:59 PM

All replies

  • Hi Himo,

     

    As I can see in the first one you will get a list IEnumerable<'a> where 'a is an anonymous type containing a customer name and a list of the orders.

    This one is a left outer join, so the result will contain all of the customers (from Spain because of the where statement) whether they have orders or not.

     

    The second query does not makes too much sense for me as you select all of the orders again and create the anonymous class for each customer as many times as many orders it has.

    e.g.: we have Customer1 with two orders Order1 and Order2

    then the result will contain two elements:

    { CompanyName = Customer1.CompanyName, orders = {Order1, Order2}}

    { CompanyName = Customer1.CompanyName, orders = {Order1, Order2}}

     

    In this case the DefaultIfEmpty does not makes any difference.

    http://msdn.microsoft.com/en-us/library/bb360179.aspx

    The DefaultEmpty returns the elements of the specified sequence or the type parameter's default value in a singleton collection if the sequence is empty.

    e.g.:

     

       List<int> numbers = new List<int>();
    
       foreach (int number in numbers.DefaultIfEmpty())
       {
        Console.WriteLine(number);
       }
    
       /*
        This code produces the following output:
    
        0
       */
    

     

    I hope it helps for you :)

     

     


    Regards, Peter
    • Proposed as answer by Crick3t Tuesday, May 31, 2011 8:28 AM
    • Marked as answer by Jackie-SunModerator Thursday, June 2, 2011 11:03 AM
    Thursday, May 26, 2011 4:59 PM
  • Hi himo;

    Below are the two queries you posted with what Linq translated them into T-SQL. The first query I have no issues with the second query by just adding this line to the query, from t in groupedOrders.DefaultIfEmpty(), and not even using it gave a strange translation as you can see, it produced two Left Outer Joins as can be seen below. I have no idea why thy translate it like this and is the reason for duplicates in the results set.

    I also included a third query which uses the relationship in the database to create the same T-SQL as your first query bu a little less verbose in the Linq query.

    var spainCustomers =
         from cust in nwindDataContext.Customers
         where cust.Country == "Spain"
         join ord in nwindDataContext.Orders on cust equals ord.Customer
         into groupedOrders
         select new
         {
          companyName = cust.CompanyName,
          orders = groupedOrders
         };
    
    T-SQL produced by Linq for this query
     
    -- Region Parameters
    DECLARE @p0 NVarChar(1000) = 'Spain'
    -- EndRegion
    SELECT [t0].[CompanyName] AS [companyName], 
        [t1].[OrderID], [t1].[CustomerID], 
        [t1].[EmployeeID], [t1].[OrderDate], 
        [t1].[RequiredDate], [t1].[ShippedDate], 
        [t1].[ShipVia], [t1].[Freight], 
        [t1].[ShipName], [t1].[ShipAddress], 
        [t1].[ShipCity], [t1].[ShipRegion], 
        [t1].[ShipPostalCode], [t1].[ShipCountry], (
      SELECT COUNT(*)
      FROM [Orders] AS [t2]
      WHERE [t0].[CustomerID] = [t2].[CustomerID]
      ) AS [value]
    FROM [Customers] AS [t0]
    LEFT OUTER JOIN [Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
    WHERE [t0].[Country] = @p0
    ORDER BY [t0].[CustomerID], [t1].[OrderID]
    
    =======================================================================================
     var spainCustomers2 =
         from cust in nwindDataContext.Customers
         where cust.Country == "Spain"
         join ord in nwindDataContext.Orders on cust equals ord.Customer
         into groupedOrders
         from t in groupedOrders.DefaultIfEmpty()
         select new
         {
          companyName = cust.CompanyName,
          orders = groupedOrders
         };
    
    T-SQL produced by Linq for this query
      
    -- Region Parameters
    DECLARE @p0 NVarChar(1000) = 'Spain'
    -- EndRegion
    SELECT [t0].[CompanyName] AS [companyName], 
        [t2].[OrderID], [t2].[CustomerID], 
        [t2].[EmployeeID], [t2].[OrderDate], 
        [t2].[RequiredDate], [t2].[ShippedDate], 
        [t2].[ShipVia], [t2].[Freight], 
        [t2].[ShipName], [t2].[ShipAddress], 
        [t2].[ShipCity], [t2].[ShipRegion], 
        [t2].[ShipPostalCode], [t2].[ShipCountry], (
      SELECT COUNT(*)
      FROM [Orders] AS [t3]
      WHERE [t0].[CustomerID] = [t3].[CustomerID]
      ) AS [value]
    FROM [Customers] AS [t0]
    LEFT OUTER JOIN [Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
    LEFT OUTER JOIN [Orders] AS [t2] ON [t0].[CustomerID] = [t2].[CustomerID]
    WHERE [t0].[Country] = @p0
    ORDER BY [t0].[CustomerID], [t1].[OrderID], [t2].[OrderID]
    
    ===========================================================================================
    var spainCustomers3 =
         from cust in nwindDataContext.Customers
         where cust.Country == "Spain"
         select new 
         {
          companyName = cust.CompanyName,
          orders = cust.Orders
         };
    
    T-SQL produced by Linq for this query
         
    -- Region Parameters
    DECLARE @p0 NVarChar(1000) = 'Spain'
    -- EndRegion
    SELECT [t0].[CompanyName] AS [companyName], 
        [t1].[OrderID], [t1].[CustomerID], 
        [t1].[EmployeeID], [t1].[OrderDate], 
        [t1].[RequiredDate], [t1].[ShippedDate], 
        [t1].[ShipVia], [t1].[Freight], 
        [t1].[ShipName], [t1].[ShipAddress], 
        [t1].[ShipCity], [t1].[ShipRegion], 
        [t1].[ShipPostalCode], [t1].[ShipCountry], (
      SELECT COUNT(*)
      FROM [Orders] AS [t2]
      WHERE [t2].[CustomerID] = [t0].[CustomerID]
      ) AS [value]
    FROM [Customers] AS [t0]
    LEFT OUTER JOIN [Orders] AS [t1] ON [t1].[CustomerID] = [t0].[CustomerID]
    WHERE [t0].[Country] = @p0
    ORDER BY [t0].[CustomerID], [t1].[OrderID]
    
    

     


    Fernando

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Thursday, May 26, 2011 5:59 PM