Nested query in entity framework

c# entity-framework linq


I am getting the following exception:

The nested query is not supported. Operation1='Case' Operation2='Collect'

with this query

var Games = context.Games.Select(a => new GameModel
     Members = (a.Type == 1 ? (a.UsersInGames.Where(b => b.GameID == a.ID && b.StatusID == 1).Select(c => new Member
         ID = c.UserID,
         email = c.UserInfo.EmailAddress,
         screenName = c.UserInfo.ScreenName
     })) :   
    (a.Teams.Where(b => b.GameID == a.ID).SelectMany(b => b.UsersInTeams.Where(c => c.StatusID == 1)).Select(d => new Member
        ID = d.UserID,
        email = d.UserInfo.EmailAddress,
        screenName = d.UserInfo.ScreenName

when I don't include the condition in selecting Members, the query works fine. Is there a way I can do the conditional inside the query?

11/21/2016 3:12:42 PM

Accepted Answer

You're overestimating the power of LINQ translation to SQL. Not everything is translatable and there is no compiler warning for that due to the way LINQ works.

Nested collections are usually either a) not supported or b) end up in horrible SELECT N+1 queries. What you ask EF to do is to return an object tree. SQL does not support tree like results so you run into the object-relational impedance mismatch and it hurts.

I advise you to fetch the nested collection data as a second, completely separate query. That allows you more control and is guaranteed to work.

As a non-essential side-node, you will probably not be able to convince EF to use the ?: operator over sequences. That is very hard to translate. Think how you would write this as SQL - very hard and convoluted.

3/3/2013 11:53:36 AM

Popular Answer

It looks like Linq to EF doesn't support the following

context.Games.Select(g => new
    Field = g.IsX? queryable1 : queryable2

But, here's a hack you can use to get it to work:

context.Games.Select(g => new
    Field = queryable1.Where(q => g.IsX)
               .Concat(queryable2.Where(q => !g.IsX))

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow