Thursday, March 1, 2012

LINQ Query Vs SQL Query

While understanding LINQ it immensely helpful to compare LINQ Query
 that we like to learn with equivalent SQL query that we mastered over the years.

  Bellow are some LINQ queries and there equivalent SQL query. If anybody found it useful 
the tables used here are as bellow
   

A] Inner Join:

SQL Query
    SELECT
         B.TITLE,
         D.NAME
    FROM
         BOOKS  B
         INNER JOIN BOOKAUTHORS C  ON B.ID = C.BOOK
         INNER JOIN AUTHORS D ON C.AUTHOR = D.ID
    ORDER BY
          D.NAME
LINQ Query:
      var q = from b in db.Books
               join c in db.BookAuthors on b.Id equals c.Book
               join d in db.Authors on c.Author equals d.Id
               orderby d.Name
               select new { b.Title, d.Name };

B] Inner Query

SQL Query:
SELECT
      X.*  
FROM
      BOOKS X
WHERE
      X.CATEGORY IN (
SELECT
                              B.ID
FROM
                              BOOKCATEGORIES B
WHERE
                              B.ID % 2=0
  )
LINQ Query:
     var s = from b in db.BookCategories
                      where b.Id % 2==0
                      select b.Id;
    var t = from x in db.Books
                      where s.Contains((int)x.Category)
                      select x;
C] Like

SQL Query:

SELECT X.* FROM BOOKS X WHERE X.TITLE LIKE 'C#%'
LINQ Query:
      var t2 = from x in db.Books
              where x.Title.StartsWith("C#")
              select x;
D] Distinct:
     SQL Query:
                SELECT DISTINCT X.AUTHOR
            FROM  BOOKAUTHORS X
     LINQ Query:
          var t3 = (
                   from x in db.BookAuthors
                   select x.Author
                  ).Distinct();

E] NOT IN:
     SQL Query:
                SELECT Y.*
FROM AUTHORS Y
WHERE Y.ID NOT IN (
SELECT
DISTINCT X.AUTHOR
                        FROM
BOOKAUTHORS X
       )
     LINQ Query:
          var t5 = (from x in db.BookAuthors
                 select x.Author).Distinct();

       var t6 = from y in db.Authors
          where t5.Contains(y.Id) == false
           select y;











F] LEFT JOIN:
     SQL Query:
                SELECT
      X.NAME,
      P.TITLE
             FROM
                 BOOKCATEGORIES X
                 LEFT OUTER JOIN BOOKS P ON X.ID = P.CATEGORY
     LINQ Query:
          var t7 = from x in db.BookCategories
       join p in db.Books on x.Id equals p.Category into x_p
       from q1 in x_p.DefaultIfEmpty()
       select new {x.Name,q1.Title };

G] LEFT JOIN:
     SQL Query:
                SELECT
                  X.NAME,
P1.TITLE
FROM
                  BOOKCATEGORIES X
                  LEFT JOIN BOOKS P1 ON P1.CATEGORY = X.ID
ORDER BY
                  X.NAME,
                  P1.TITLE

     LINQ Query:
var t8 = from x in db.BookCategories
         from p1 in db.Books.Where(
p1 => p1.Category == x.Id
).DefaultIfEmpty()
         select new
                  {
                           x.Name,
                           p1.Title
                  };













  H] Group By & Count
       SQL Query:
                SELECT L.NAME,COUNT(*)
FROM
(
SELECT B.TITLE, D.NAME FROM BOOKS B
INNER JOIN  BOOKAUTHORS C ON B.ID = C.BOOK
INNER JOIN  AUTHORS D ON C.AUTHOR = D.ID
)L
GROUP  BY L.NAME

     LINQ Query:
from l in (
          from b in db.Books
          join c in db.BookAuthors on b.Id equals c.Book
          join d in db.Authors on c.Author equals d.Id
          orderby d.Name
          select new {b.Title, d.Name}
         )
group l by l.Name into g
select new { Author = g.Key, BookCount = g.Count() };







  I] Group By & HAVING
       SQL Query:
                SELECT L.NAME,COUNT(*)
FROM
(
SELECT
B.TITLE,
D.NAME
      FROM BOOKS B
INNER JOIN  BOOKAUTHORS C ON B.ID = C.BOOK
INNER JOIN  AUTHORS D ON C.AUTHOR = D.ID
)L
GROUP  BY
L.NAME
HAVING
COUNT(*) >2




     LINQ Query:
   var r1 = from l in
                          (from b in db.Books
                           join c in db.BookAuthors on b.Id equals c.Book
                           join d in db.Authors on c.Author equals d.Id
                           orderby d.Name
                           select new { b.Title, d.Name }
                           )
                 group l by l.Name into g
                 where g.Count() > 2
                 select new { Author = g.Key, BookCount = g.Count() };


No comments:

Post a Comment