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