Is it true that writing LINQ queries is not easy for complex queries?

Question

select a.sNavID,
       a.sNavText,
       a.sNavText as EName,
       ' '+a.sNavText as NameDisplay 
from ContentPageNav as a 
where a.navID=0 
union 
select b.sNavID,
       a.sNavText + ' >> ' + b.sNavText as Name,
       b.sNavText as EName,
       ' ' + b.sNavText as NameDisplay 
from ContentPageNav as a 
inner join ContentPageNav as b on a.sNavID=b.navID and b.catNo=1
union 
select c.sNavID,a.sNavText + ' >> ' + b.sNavText + ' >> ' + c.sNavText as Name, 
       c.sNavText as EName,
       ' ' + c.sNavText as NameDisplay 
from ContentPageNav as a 
inner join ContentPageNav as b on a.sNavID=b.navID and b.catNo=1 
inner join ContentPageNav as c on b.sNavID=c.navID and c.catNo=2

Can you provide an example of what you mean with a complex query? — Fredrik Mörk, Oct 31 '10 at 11:32
Generally, writing complex code should not be considered easy. :) — Vlad, Oct 31 '10 at 11:33
Linq is fantastic but you have to get familiar with it, explain complex please. — A_Nabelsi, Oct 31 '10 at 11:34
select a.sNavID,a.sNavText,a.sNavText as EName,' '+a.sNavText as NameDisplay from ContentPageNav as a where a.navID=0 union select b.sNavID,a.sNavText + ' >> ' + b.sNavText as Name,b.sNavText as EName,' ' + b.sNavText as NameDisplay from ContentPageNav as a inner join ContentPageNav as b on a.sNavID=b.navID and b.catNo=1 — sly_Chandan, Oct 31 '10 at 11:36
union select c.sNavID,a.sNavText + ' >> ' + b.sNavText + ' >> ' + c.sNavText as Name, c.sNavText as EName,' ' + c.sNavText as NameDisplay from ContentPageNav as a inner join ContentPageNav as b on a.sNavID=b.navID and b.catNo=1 inner join ContentPageNav as c on b.sNavID=c.navID and c.catNo=2 — sly_Chandan, Oct 31 '10 at 11:36
As far as I know that in LINQ when we write the similar query, it will take the result set of each query and then do a union on the result set. — sly_Chandan, Oct 31 '10 at 11:37

score 3 · Accepted Answer · answered Oct 31 '10 at 12:03

LINQ can pretty much handle and query you want to toss at it.

... Fully Composed Query ...

var query = (
    from a in ContentPageNav
    where a.navID == 0
    select new
    {
        a.sNavID,
        Name = a.sNavText,
        EName = a.sNavText,
        NameDisplay = " " + a.sNavText,
    }).Concat(
    from a in ContentPageNav
    join b in ContentPageNav on a.navID equals b.navID
    where b.catNo == 1
    select new
    {
        b.sNavID,
        Name = a.sNavText + " >> " + b.sNavText,
        EName = b.sNavText,
        NameDisplay = " " + b.sNavText,
    }).Concat(
    from a in ContentPageNav
    join b in ContentPageNav on a.navID equals b.navID
    where b.catNo == 1
    join c in ContentPageNav on b.navID equals c.navID
    where b.catNo == 1
    select new
    {
        c.sNavID,
        Name = a.sNavText + " >> " + b.sNavText + " >> " + c.sNavText,
        EName = c.sNavText,
        NameDisplay = " " + c.sNavText,
    });

... Here is a version that has been decomposed into smaller parts ...

var rootRecords = ContentPageNav.Where(r => r.navID == 0);
var cat1Records = ContentPageNav.Where(r => r.catNo == 1);
var cat2Records = ContentPageNav.Where(r => r.catNo == 2);

var rootComposed =
    from a in rootRecords
    select new
    {
        a.sNavID,
        Name = a.sNavText,
        EName = a.sNavText,
        NameDisplay = " " + a.sNavText,
    };
var cat1Composed =
    from a in rootRecords
    join b in cat1Records on a.navID equals b.navID
    select new
    {
        b.sNavID,
        Name = a.sNavText + " >> " + b.sNavText,
        EName = b.sNavText,
        NameDisplay = " " + b.sNavText,
    };
var cat2Composed =
    from a in rootRecords
    join b in cat1Records on a.navID equals b.navID
    join c in cat2Records on b.navID equals c.navID
    select new
    {
        c.sNavID,
        Name = a.sNavText + " >> " + b.sNavText + " >> " + c.sNavText,
        EName = c.sNavText,
        NameDisplay = " " + c.sNavText,
    };

var composedQuery = rootComposed.Concat(cat1Composed).Concat(cat2Composed);

`.Union(...)` will remove duplicates, `'Concat(...)` won't – Matthew Whited Oct 31 '10 at 15:15 — Matthew Whited, Oct 31 '10 at 15:15

score 1 · Answer 2 · edited May 23 '17 at 11:45

1

You just used SQL anti-pattern #1.

Is it true that writing LINQ queries is not easy for complex queries?

When one uses Linq for data access, one tends to write simple queries that get the job done. It is a mistake to write complex queries in the first place. In that sense, it is true. It was also true for SQL.

edited May 23 '17 at 11:45

Community

1
1

answered Oct 31 '10 at 15:03

Amy B

108,202
21
135
185

Is it true that writing LINQ queries is not easy for complex queries?

2 Answers2