Webapi: Odata functions with EnableQuery against a projection with EF Core 3.x Basically doesn't work

[JohnGalt1717]

Because of the changes to EF Core 3 making it significantly dumber even with queries that worked in the past, OData using Webapi endpoints with [EnableQuery] that work against a DTO response and not the actual Entity almost never work anymore.

ie if you have this:

public ActionResult<IQueryable<SomeDto>> GetList() {
    return Ok(from c in ctx.Contacts select new SomeDto {Id = c.Id, Name = c.Name});
}

It will work.

But essentially anything more complex will not. I.e. if you do a where on ctx.Contacts to test against a sub-collection before returning the Dto and there is an $orderby, $filter, $top, $skip or $select from odata it will fail.

If the select new SomeDto line has to get a value from FirstOrDefault on a child list, it will fail for everything.

oData appears to make things much worse than if you manually do this with ExpressionTrees as you'd expect that oData should be doing (i.e if you create a where using an expression tree of the failed $filter it works fine, but odata fails when odata applies the $filter clause) hence why this is both an EF Core bug (see https://github.com/dotnet/efcore/issues/20189 for details) and a OData bug.

And none of these are trying to execute client side. They're just failing because of issues in the EF Core parser and/or how oData is generating the expression trees.

If you go against raw entities (I hope no one is actually doing that) then it's mostly fine.

[NetTecture]

I am not sure there is anything that can be fixed here.

The problem runs down to EfCore basically not doing LINQ. Post 2.2 they decided to rewrite the whole LINQ processing stack from the baseline. Smart decision. Well, smart as in "you get fired for stupid" - because what came out for EfCore 3.0 was not finished and what then came out for 3.1 was basically unusable. Their supported use cases at the moment are EXTREMELY thin. Anything that is not in that line of programming - not supported. Fallbac kto client side evaluation - disabled.

• Projection with Automapper does not work. And you pretty much need Automapper. Problem is that optional projections (a _> b where b may be null) is a not supported construct.
• I find a tremendous amount of issues where basically you get some sort of error.
• Their release cycle is as broken as it seems the manager in charge is. They basically are afraid from publishing any bug that may introduce an error (in the broken stack) so all fixes are pushed to .NET 5.0 which is in november. Not that many things are fixed yet (particularly automapper projectto - not fixed). But it means you can forget any shorter than november timeframe unless you are iwlling to work on previews and nightly.

My workarounds so far are:

• Use .NET Core 3.1 but stay on EfCore 2.2.6.
• Because 2.2.6 is already broken, I basically load all viewable data into memory and then use projectto on that one. For that I wrote methods that extract all includes from the $filter/$expand structure.
• Because performance is "sucky" in some parts, I am now working on extracting primary key for first object conditions (i.e. if I use get by primary key) so I can forward that to EfCore outside of ProjectTo.

An alternative is to NOT use EfCore - Ef (not core, the classic one) got an update to version 6.4 and is usable in dotnet core applications now. You may remember this is the not so modern slower product - that in actual use is faster and WORKS. At least it has proper SQL generation for a LOT of cases where EfCore just says "ah, no client side evaluation, sorry".

After a year fighting with various ways and seriously getting answers from the efcore team that makes me consider this to be under the worst management seen in 30 years - I consider efcore broken for the moment with a fix coming possibly before EfCore V10 in maybe 2030 - earlier if someone decides to put in a manager that does his job, cut down the feature set on something the 6 people there can actually manage and put bug fixes and a faster release cycle in.

OData expression trees are fine - it is the now (particularly now in 3.1) EXTREMELY limited implementation in EfCore that is the problem. 2.2.6 was bad. 3.0 was - not sure. But then they decided to do MAJOR changes to get rid of bugs (by removing the possibly buggy functionality) in 3.1 which was done without a proper preview (remember, 3.1 was like 4 weeks after 3.0). While most people likely expected 3.1 to be "bug fixes and missing functionality" major parts where removed. The result is utter chaos and a ton of bad sql generated. Nightlies are a little better but - not good either.

And the manager in charge is demonstrating that Dilbert's pointy haired boss can look good compared to some people.

[JohnGalt1717]

So aside from laying off them at this point because the point is made and it isn't going to help the disaster going after their insanity I'd agree except I have tested that oData is making it worse when it doesn't need to. Thus the oData team needs to work with the EF Core team and try and optimize things.

The reason I say this is that the basic flow is this:

Select some data with a where into a projected Dto class as an IQueryable (i.e. not materialized)

Then!

Where ($filter) on selected dto
Orderby ($orderby) on selected dto
select ($select) on dto
take/skip ($top/$skip) on dtoed IQueryable.

Odata is working against the dto select. If instead, you simply return Ef Core objects, oData works fine most of the time (failure rate drop by about 90%.)

However, against the dtos it's awful. I'd blame Ef Core only except I did some experimenting:

If I manually go and write Expression Trees as oData should be for the same $filter that fails currently with oData against the Dtos, it works fine. (I can also write it so that it will fail with the same error as oData, but if you do it carefully you can generate the where clause as an expression tree from the parsed $filter and it works, whereas oData does not)

Same for $orderby, $select etc.

So whatever oData is doing is making the problem worse. Why the 2 teams haven't' been coordinating, I don't know and am too exhausted from weeks of dealing with this stuff to care anymore, but oData is definitely doing stuff it doesn't need to, and causing EF Core 3.1 to fail when it doesn't need to fail.

As for using EF 6.4 or even reverting back to EF Core 2.2, the problem is that most of the packages for identity etc. depend upon EF Core 3.1 and refuse to work if you force EF 6.4 in instead.

[NetTecture]

@JohnGalt1717 That is actually interesting, but I fear your point is badly made. Can you provide particualr examples where the generated expression trees could be more optimal? Especially given that you already made experiments, I think actionable issues per item where the trees can be better would be quite welcome ;)

I am also stuck with 2.2.6 AND a lot of workarounds because this not working. Updates to 3.x only make it a LOT worse.

[JohnGalt1717]

@NetTecture I did in the linked ticket.

[snappyfoo]

@NetTecture I did in the linked ticket.

In the linked ticket, they asked you do provide a reproducible solution. They closed it because you didn't. It sounds like you could offer some real help in fixing these issues, please consider revisiting!

[JohnGalt1717]

@snappyfoo There's no way to fix it. And there's no reason why a repro is needed. EF Core 3.1 cannot order the where queries intelligently based on the projection and cannot do a select => where in all but the most basic of cases because of design decisions when rewritting EF Core for the 3rd time.

This is known, and fundamentally breaks oData in a way that makes oData effectively useless in all but the most basic cases.

The EF Core team needs to invest time in being able to either reverse engineer the select so that the where in the $filter will work, OR allow far more complex cases for where queries on projected values which currently are not supported.

The EF Core team basically closes anything unless you've invested the hours it takes to create a repro case even for the most obvious things as a way to ignore all of the major issues and claim that they're on top of it and that there isn't a massive problem within the team creating a false sense of quality versus actual because these cases get closed and ignored by the team. And very likely that won't change because as they've said in other tickets, the EF Core team is vastly under staffed and thus there's no way for them to keep up and build a quality product.

Which is sad considering the focus on .net core itself being super fast, and the vast majority of time in most apps is spent querying data.