none
Compare between two query RRS feed

  • Question

  • Hello ,

    - I Have a northwind data base and I write two queries :

     

    var

    revenueByYear =

     

    from detail in nwindDataContext.Order_Details

    group detail by detail.Order.OrderDate.Value.Year

     

    into groupedOrders

     

    orderby groupedOrders.Key descending
     

     

    select new
     

    {

    year = groupedOrders.Key,

    revenue = groupedOrders.Sum(

    o => o.UnitPrice * o.Quantity)

    };

    I check The transaltion of this query :

    SELECT

     

    [t4].[value2] AS [year], [t4].[value] AS

    [revenue]

    FROM

     

    (

     

    SELECT SUM([t3].[value]) AS [value], [t3].

    [value2]

     

    FROM

    (

     

    SELECT [t2].[UnitPrice] * (CONVERT(Decimal(29,4),[t2].[Quantity])) AS [value], [t2].[value] AS

    [value2]

     

    FROM

    (

     

    SELECT DATEPART(Year, [t1].[OrderDate]) AS [value], [t0].[UnitPrice], [t0].

    [Quantity]

     

    FROM [dbo].[Order Details] AS

    [t0]

     

    INNER JOIN [dbo].[Orders] AS [t1] ON [t1].[OrderID] = [t0].

    [OrderID]

     

    ) AS

    [t2]

     

    ) AS

    [t3]

     

    GROUP BY [t3].

    [value2]

     

    ) AS

    [t4]

    ORDER

     

    BY [t4].[value2]

    DESC

    I want to Ask why  used inner join betwwen Orders and Order Details  instead of left outer join ? I ask this question because there are another queries  when is transalted , it used left outer join not inner join .

    Thanks,

    Hema

     
    ASk
    Saturday, April 2, 2011 9:50 PM

Answers

  • Hello Hema,

     

    Welcome to the LINQ to SQL Forum!

    As I know, although LINQ does not define any keywords for inner or outer joins, we can use the existing LINQ join grammar to implement them.    Suppose we have a sequence of departments and a sequence of courses, then we can perform the inner and outer joins as following:

     

    INNER JOIN:

    Generally, the default behavior of the JOIN operator is an “inner join”.    When joining a sequence of departments with a sequence of courses, we only get the departments which have one or more related courses.

     

    Using JOIN operator:

    ===================================================================
    var query = from d in db.Departments

                         join c in db.Courses

                         on d.DepartmentID equals c.DepartmentID

                         select new

                         {

                             DepartmentName = d.Name,

                             CourseName = c.Title

                         };
    ===================================================================

     

    Using JOIN extension method:

    ===================================================================

    var query = db.Departments.Join(db.Courses,

                                                                  d => d.DepartmentID,

                                                                  c => d.DepartmentID,

                                                                  (d, c) => new

                                                                                 {

                                                                                     DepartmentName = d.Name,

                                                                                     CourseName = c.Title

                                                                                 });

    ===================================================================

     

    OUTER JOIN:

    We can use the GroupJoin operator as the SQL left outer join.   The compiler differentiate the GroupJoin and the Join operator by the into keyword in the next clause of the join clause.    If we need to enumerate each element of the inner sequence project a null value when no course exists for a given department, the job is left for the SelectMany and DefaultIfEmpty extension methods.  

     

    Using GroupJoin operator:

    ===================================================================

    var query = from d in db.Departments

                         join c in db.Courses

                         on d.DepartmetnID equals c.DepartmentID into courseList

                         select new

                         {

                             DepartmentName = d.Name,

                             Courses = courseList

                         };

    ===================================================================

     

    Using GroupJoin extension method:

    ===================================================================

    var query = db.Departments.GroupJoin(db.Courses,

                                                                             d => d.DepartmentID,

                                                                             c => c.DepartmentID,

                                                                             (d, courseList) => new

                                                                                                            {

                                                                                                                 DepartmentName = d.Name,

                                                                                                                 Courses = courseList

                                                                                                            });

    ===================================================================

     

    Using SelectMany and DefaultIfEmpty:

    ===================================================================

    var query = from d in db.Departments

                         join c in db.Courses

                         on d.DepartmentID equals c.DepartmentID into courseList

                         from cl in courseList.DefaultIfEmpty()

                         select new

                         {

                              DepartmentName = d.Name,

                              CourseName = cl == null ? string.Empty : cl.Title

                         }

    ===================================================================

     

    Related threads:

    http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/fb6a48ea-cd98-4304-95a9-bd4534523fec

    http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/b6f2beff-ccdb-419d-9464-08a977d1bce5

    http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/541706be-8d21-4618-a0c0-72fa2ffdd047

     

    Related references:

    http://codeexperiment.com/post/LINQ-To-SQL-Outer-Joins.aspx

    http://odetocode.com/blogs/scott/archive/2008/03/24/inner-outer-lets-all-join-together-with-linq.aspx


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, April 4, 2011 8:32 AM
    Moderator