CODEWITHSUNDEEP
c#linqentity-framework
yqit
yqit

Reputation: 672

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?

Upvotes: 32

Views: 31438

Answers (5)

M Mohammed Adil
M Mohammed Adil

Reputation: 91

We want to get the custumers with commands added on 2021, using nested queries will be like :

     List<Client> clients = this.db.Clients.Where(c => db.Commandes.Where(cmd => cmd.Datecommande.Year == 2021).Select(cmd => cmd.ClientId).Contains(c.Id)).ToList();

This is the equivalent of :

Select * from clients where id any (select id from commandes where .... )

Upvotes: -1

usr
usr

Reputation: 171178

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.

Upvotes: 40

GuRAm
GuRAm

Reputation: 777

I had this error too. I had code like this:

var Games = context.Games.Select(a => new GameModel
{
    Members = (!filters.GetDatailedDataToo ? null : new List<MemberModel>())
};

This error occurs when null is used in ? : operation.

This is not that case, written up here, but I've wasted lot of time, I think anyone uses this case, who searches this error text..

Upvotes: 4

N73k
N73k

Reputation: 593

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))
});

Upvotes: 21

Shadi Alnamrouti
Shadi Alnamrouti

Reputation: 13248

I faced the same problem. The solution was to load both results and determine what to use after the query (I know it has performance downside), but at least you can do it temporarily if deadline attacks you:

At the LINQ side

  var Games = context.Games.Select(a => new GameModel
        {
            // carries type1 results
            Members = 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
            })),

             //You need to create this temporary carrier to carry type 2 results
             MembersOfType2 = 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
                })))
            })
        }

After that you may loop Gamesand make the assignment Members = MembersOfType2 if Type == 1 for a certain game.

Upvotes: 3

Related Questions