yqit yqit - 5 months ago 98
C# Question

Nested query in entity framework

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?

usr usr

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.