none
from sql to Linq RRS feed

  • Question

  • Is this sql query properly translated to Linq query ?

    In first case it was direct database query, but i second case it's a query to Entity Data Model.

    SQL:

    string where = " INNER JOIN kontrahenci ON (faktury.id_kontrachenta = kontrahenci.id) " +
    
    
                   "LEFT JOIN faktury_pozycje ON (faktury_pozycje.id_faktury = faktury.id) " +
                     "LEFT JOIN produkty ON (faktury_pozycje.id_produktu = produkty.id)

    Linq:

    var searchQuery = (from fak in EntityProvider .Instance.BazaContext.faktury
    join
    kont in EntityProvider .Instance.BazaContext.kontrahenci on fak.id_kontrachenta equals kont.id

    join fak_poz in EntityProvider .Instance.BazaContext.faktury_pozycje on fak.id equals fak_poz.id_faktury into fak_pozGroup
    from fak_pozG in fak_pozGroup.DefaultIfEmpty()
                      join prod in EntityProvider .Instance.BazaContext.produkty on fak_pozG.id_produktu equals prod.id into prod_Group
    from prod_G in prod_Group.DefaultIfEmpty()
                      select new { fak, kont, fak_pozG, prod_G });

    Thanks.
    Monday, January 10, 2011 7:24 PM

Answers

  • Hello tutis,

    Welcome to the MSDN Forum and thank you for posting here.

    Based on you description, I think it is difficult to judge whether your traslation is correct or not without knowing the relationships among tables and the entire SQL query. However, it is easy for you to check, you are able to check the result when you run these code. If you can get the collection you want, then it is correct.

    And more, generallr speaking, you tralated the keyword "INNER JOIN" to "join" and "LEFT OUTER JOIN" to "join...into..."  are totally correct. The following is some knowledge about INNER JOIN and LEFT OUTER JOIN in LINQ to SQL FAQ:

    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 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.JOIN

     

    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 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 and extension methods.   DefaultIfEmptySelectManyGroupJoin

     

    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

                         }

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

    I hope it can help you.

    Have a nice day,

    Thursday, January 13, 2011 2:14 AM
    Moderator