locked
difference between compound from clause and inner join RRS feed

  • Question

  • I was reading Linq MSDN samples. I came accross SelectMany - Compound from 2. What is the difference between using 2 from clause and making inner join? Which one do I use? And When?
    Tuesday, March 22, 2011 9:05 PM

Answers

  • Hi sp_412000;

    The differences in the two ways of using SelectMany - Compound from 2 and Inner Join. The run time is almost the same if not the same, so one is not more efficient then the other. the Compound uses the association, PK - FK, assigned in the database where the Inner Join does not. When to use between the two if you have a relationship defined use Compound when you don't have a relationship defined use Inner Join. The below code snipper is using the Microsoft sample database AdventureWorks. Below each query I placed the T-SQL which is sent to the server for that query.

    DataClasses1DataContext ctx = new DataClasses1DataContext( );
    
    var selectMany = ( from c in ctx.Customers
              from o in c.SalesOrderHeaders
              where o.TotalDue < 500.00M
              select new { c.CustomerID, o.SalesOrderID, o.TotalDue } ).ToList( );
    
    SELECT [t0].[CustomerID], [t1].[SalesOrderID], [t1].[TotalDue]
    FROM [Sales].[Customer] AS [t0], [Sales].[SalesOrderHeader] AS [t1]
    WHERE ([t1].[TotalDue] < @p0) AND ([t1].[CustomerID] = [t0].[CustomerID])
    -- @p0: Input Decimal (Size = -1; Prec = 31; Scale = 4) [500.00]
    
    var innerJoin = ( from c in ctx.Customers
             join o in ctx.SalesOrderHeaders on c.CustomerID equals o.CustomerID
             where o.TotalDue < 500.00M
             select new { c.CustomerID, o.SalesOrderID, o.TotalDue } ).ToList( );
    
    SELECT [t0].[CustomerID], [t1].[SalesOrderID], [t1].[TotalDue]
    FROM [Sales].[Customer] AS [t0]
    INNER JOIN [Sales].[SalesOrderHeader] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
    WHERE [t1].[TotalDue] < @p0
    -- @p0: Input Decimal (Size = -1; Prec = 31; Scale = 4) [500.00]
    
    


    Fernando


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Friday, March 25, 2011 1:07 AM