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?

Laevorotatory

Is it imperative that you do the query in the initialisation? – Succory

@NathanWhite No not imperative. I would love to know how to use a conditional inside a query though. – Laevorotatory

I'm pretty sure EF-core 8 supports this query shape now (although I obviously couldn't reproduce it 100%). – Pridemore

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.

Anneliese

I think your last paragraph about the ternary ?: operator is more essential than you think -- it was precisely the reason I was getting the same exception as the asker. Thank you for your answer, by the way. – Shaffer

These are common 'problems' moving from Linq2Sql (which allows you to do all kinds of crazy translations that EF doesn't) but hey I'm guessing you're moving in part for performance reasons so you have to say goodbye to some of the awful SQL that Linq2Sql would generate. – Seibel

@Seibel I'm still stuck with L2S on a big code base because EF cannot, after 8 years time advantage, translate things like DateTime.Date. L2S was a really nice system and I think they picked the wrong one to deprecate. L2S has a provider model, too. They just marked the members private (presumably for time and budget reasons. Was planned for v2). So far I have few complaints about L2S SQL generation that would not apply to EF. Although EF now generates quite good SQL. I'd be interested in hearing your thoughts. – Anneliese

@Anneliese L2S just allowed me to add more stuff everytime I needed it and it got more and more inefficient over time. This was fine when we had 100 orders at a time (to print) but now it can be 1000-2000 some mornings so it was just getting out of control. I've optimized L2S over the years but I just had to start over and clean stuff up. Turning into a bit of a rabbit hole of refactoring but thanks for the DateTime heads up. That one is going to hit me soon :-( – Seibel

I feel like <cond> ? <collect> : <collect> could naively be implemented as a UNION where the first subquery does WHERE <cond> and the second one does WHERE NOT <cond>. It probably wouldn't be the fastest thing in the world, but probably still better than N+1 queries or nothing at all. In any case, this is why I still prefer to just write a damn sproc for anything expensive. I find the solutions are generally less convoluted and more flexible, and much faster to run. Objects are overrated. – Anticlockwise

@Anticlockwise that technique would work, thanks for contributing. L2S does not support it although it could. I would not share your recommendation to write a proc for anything expensive. I don't think expensive is a good criterion to make that choice. Many other things factor in such as the structure of the query as well as refactoring and IDE support. T-SQL sucks big time in all possible ways. Sometimes it's the best solution though. – Anneliese

Answer 2

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

Atcliffe

I wouldn't call this a "hack". Depending on what one is after, it could be the nicest way of "saying things" :-) – Tarrsus

I needed to do a resultset sort in EF by a specific object in a navigation property list. It wanted to get the current month, but if that didn't find results, we wanted to return the first month. It was naively something like... expr = x => (x.subset.Where(y => currentmonth).Count() > 0 ? x.subset.Where(y => currentmonth) : x.subset.OrderBy(y => month)).FirstOrDefault().property I used this code to convert to the following and everything worked perfectly and it converted to SQL. x => (x.subset.Where(y => currentmonth).Concat(x.subset.OrderBy(y => month))).FirstOrDefault().property; – Beatabeaten

Answer 3

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.

Glans

Answer 4

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

Loxodromics

Answer 5

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 .... )

Packton

Please try to answer the question, i.e. use the entities in the question and try to achieve what is asked there. The question is not "show me any nested query that works". – Pridemore

BTW: first check if the LINQ query shape in the question is still not supported. – Pridemore