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

Question

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

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

Answer 1

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);

Answer 2

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.