Entity Framework Select several levels of childs

[desanti]

I'm using entity Framework 6. On my database I have these tables :

MasterTable ( Id , name)
Child1 ( ID , name , vl1 , Master_ID)
Child2 ( ID , name , vl2 , MasterID )
Child3 (ID , name , vl3 , Master_ID )
Child3Itm ( ID , name , Child3_ID)

For a given `MasterTable` item, I want to load with a single Query from database:

• All `Child1` where `vl1 > 5`
• All `Child2` where `vl2 > 6`
• All `Child3` where `vl3 > 7`

And in each `Child3` to load all of the `Child3Itm` content.

I'm using this query:

Dim lst = (From t In context.MasterTable.Where(Function(t1) t1.id = 7)
Select New With {
t,
.chld1 = t.child1s.Where(Function(t21) t21.vl1 >5),
.chld2 = t.child2s.Where(Function(t31) t31.vl2>6 ),
.chld3 = t.child3s.Where(Function(t41) t41.vl3>7).Select(Function(t411) t411.Child3Itms)
}).ToList

The problem is that no `Child3` are selected. All others are OK. What can i do? Thanks in advance!

[Richard Deeming]

How about:

.chld3 = t.child3s.AsQueryable().Include(Function(t411) t411.Child3Itms).Where(Function(t41) t41.vl3 > 7)

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[desanti]

Now i'm getting an error :

System.NotSupportedException: 'LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1[Myprog.Child3] Include[child3,ICollection`1](System.Linq.IQueryable`1[Myprog.Child3], System.Linq.Expressions.Expression`1[System.Func`2[Myprog.Child3,System.Collections.Generic.ICollection`1[Myprog.Child3itm]]])' method, and this method cannot be translated into a store expression.'

[desanti]

I've found 2 solutions that are working. Can someone tell me which is better ? Solution 1

Dim lst = (From t In context.MasterTable.Where(Function(t1) t1.id = 7)
Select New With {
t,
.chld1 = t.child1s.Where(Function(t21) t21.vl1 >5),
.chld2 = t.child2s.Where(Function(t31) t31.vl2>6 ),
.chld3 = t.child3s.Where(Function(t41) t41.vl3>7),
.chld3itms = t.child3s.Where(Function(t51) t51.vl3>7).Select(Function(t511) t511.Child3Itms)
}).ToList

Solution 2

Dim lst = (From t In context.MasterTable.Where(Function(t1) t1.id = 7)
Select New With {
t,
.chld1 = t.child1s.Where(Function(t21) t21.vl1 >5),
.chld2 = t.child2s.Where(Function(t31) t31.vl2>6 ),
.chld3 = (From t2 in t.child3s.Where(Function(t41) t41.vl3>7)
Select New With {
t2,
.chld3it=t2.Child3Itms
})
}).ToList

[Dave Kreskowiak]

Neither, they do the exact same thing. It's up to your preference.

Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak

[desanti]

I mean better for performance and the complexity of sql query that is generated in each case..

[Dave Kreskowiak]

Like I said, they both do the exact same thing and generate the exact same SQL.

Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak