CODEWITHSUNDEEP
c#.netlinqlinq-to-sql
Mike Caron
Mike Caron

Reputation: 5784

Linq to Sql selection of items with count of nested

I have the following table structure:

enter image description here

Using Linq To SQL I want to get a list like this:

new {
    E = e,      // the E object
    Ds = e.Ds,  // list of D objects referenced by E
    NumAs = ??? // COUNT OF A objects that are eventually associated with this E
}

BUT, I want these things sorted by the number of A's associated with each E.

I've been trying to use combination of joins/orderby and nested queries, but I'm not sure how to get it.

If I make a reference from table E to A, then it seems trivial; I.e. this:

enter image description here

from e in ctx.E
orderby e.As.Count()
select new {
   E     = e,
   Ds    = e.Ds,
   numAs = e.As.Count()
}

But is making that reference breaking some kind of DB normalization rule?

Upvotes: 2

Views: 668

Answers (2)

Cyril Gandon
Cyril Gandon

Reputation: 17058

Well, just follow your entities relations all along:

ctx.E.Select(e => new {
                 E = e
               , Ds = e.Ds
               , numAs = e.Ds.SelectMany(d => d.Cs).SelectMany(c => c.As).Count()
                 }
             )
     .OrderBy(e => e.numAs);

e.Ds.SelectMany(d=>d.Cs) give you all the Cs associated to your e via the Ds.
And again, SelectMany(c => c.As) give you all the As associated to your e via the Cs and the Ds.

Upvotes: 1

p.s.w.g
p.s.w.g

Reputation: 149020

You can just do this

from e in ctx.E
let ds = e.Ds
let cs = ds.SelectMany(d => d.Cs)
let as = cs.SelectMany(c => c.As)
orderby as.Count()
select new {
   E     = e,
   Ds    = e.Ds,
   numAs = as.Count()
}

Or this, for brevity

from e in ctx.E
let as = e.Ds.SelectMany(d => d.Cs).SelectMany(c => c.As)
orderby as.Count()
select new {
   E     = e,
   Ds    = e.Ds,
   numAs = as.Count()
}

Another way to do it might be to start with As and group them like this:

from a in ctx.A
group a by a.C.D.E into g
orderby g.Count()
select new {
    E = g.Key,
    Ds = g.Key.Ds,
    numAs = g.Count()
}

Upvotes: 1

Related Questions