Nested query in entity framework

Question

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?

Answer 1

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-note, 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.