$select doesn't always translate to efficient T-SQL with EF Core

[bkwdesign]

I am using the latest OData v9 + EF Core nugets and am noticing some interesting T-SQL depending on how I formulate my controller. The thing I'm currently scrutinizing is efficient data querying of the fields specified by the $select query-string parameter.

This works:

     [EnableQuery]
     public IQueryable<ProfileComposition> GetProfileCompositions()
     {
         return db.ProfileCompositions;
     }

But the async version will generate T-SQL for all the fields

     [EnableQuery]
     public async Task<ActionResult<IQueryable<ProfileComposition>>> GetProfileCompositions()
     {
         var comps = await db.ProfileCompositions.ToListAsync();
         return Ok(comps);
     }

In both approaches, the returned JSON payload is trimmed to the desired fields.

In the 2nd approach, I'm obviously calling .ToListAsync() directly instead of letting the framework handle it- therein lies the problem, but I have yet to discover a way around this. So it is doing a full load of the table's rows and columns into memory before trimming it down

But I don't want to solely focus on the async pattern, as I believe I have other scenarios that cause the expected ODataQueryOptions to fail at translating the desired LINQ logic into the EF queryable

===

FWIW - this is the relevant portions of my Program.cs you might want to see

            builder.Services.AddDbContext<My.EntityCore.DBModel>(opt =>
              opt.UseSqlServer(builder.Configuration.GetConnectionString("MyDBModel"), options => options.EnableRetryOnFailure())
               .EnableSensitiveDataLogging(true)
               .UseQueryTrackingBehavior(QueryTrackingBehavior.TrackAll));

These are my Web API packages:

          <PackageReference Include="Microsoft.Identity.Web" Version="2.19.1" />
	  <PackageReference Include="Microsoft.AspNetCore.OData" Version="9.0.0" />
	  <PackageReference Include="Microsoft.EntityFrameworkCore" Version="8.0.10" />
	  <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="8.0.10" />
	  <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="8.0.10" />
	  <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite" Version="8.0.10" />
	  <PackageReference Include="Microsoft.OData.Core" Version="8.0.2" />
	  <PackageReference Include="Microsoft.OpenApi.OData" Version="1.6.8" />

===

Here's an example API call that features several query string parameters, and I want to notice that only the $select parameter is ignored in the generated T-SQL
/odata/Profiles?$select=ProfileId,WasteName,GeneratorCode&$filter=CustomerId gt 15089&$top=5

EF Log Data

SELECT TOP(@__TypedProperty_2) [w].[WMWPS#], [w].[WMAVAS], [w].[WMFRAS], [w].[WMTOAS], [w].[WMAVBT], [w].[WMFRBT], [w].[WMTOBT], [w].[WMARCL], [w].[WMCACL], [w].[WMCHCL], [w].[WMLECL], [w].[WMMECL], [w].[WMNICL], [w].[WMPHC], [w].[WMSECL], [w].[WMTHCL], [w].[WMCVCR], [w].[WMSOIL], [w].[WMKWST], [w].[WMCLR1], [w].[WMCLR2], [w].[WMTOHC], [w].[WMGENC], [w].[WMCUST], [w].[WMDAT5], [w].[WMDAT2], [w].[WMTOXD], [w].[WMDSAV], [w].[WMDSTO], [w].[WMDSFR], [w].[WMDSNA], [w].[WMEPNM], [w].[WMEXDT], [w].[WMTOXE], <<truncated 50+ more fields for brevity>>, [w].[WasteCardCode], [w].[WMPCBC], [w].[WMWSNM]
FROM [WASTEPR] AS [w]
WHERE [w].[WMCUST] > @__TypedProperty_0
ORDER BY [w].[WMWPS#]

I have a controller that is formulated correctly as described above, but, is producing this result. And I don't know exactly how to go about deubgging this issue yet

[julealgon]

In the 2nd approach, I'm obviously calling .ToListAsync() directly instead of letting the framework handle it- therein lies the problem, but I have yet to discover a way around this. So it is doing a full load of the table's rows and columns into memory before trimming it down

I'm particularly confused about this statement. What exactly are you trying to "find a way around of" over there?

[bkwdesign]

I am under the presumption that when I directly call .ToListAsync() it causes the LINQ to trigger the EF query building routine at the cost of having the query options from being understood by EF's query building.. and thus $select query option is applied later rather than sooner.

[bkwdesign]

If I didn't say it before, most of my simpler POCO / entities are working correctly, in that the $select query options are passed in from the ODataController and are generating properly slimmed-down T-SQL.

So, I am baffled at how to debug the scenario that isn't working as expected. The particular entity in question is at the center of my schema (the primary business object of interest) and was reverse-engineered from a table with over 230 columns, many of which I then gave a makeover of the column name using the ColumnAttribute.

I've tried removing navigation properties, valueconverters and other EF goodies just to see if simplifying the POCO might help, but so far I cannot figure out what's causing my issue.

[julealgon]

@bkwdesign if this works:

 [EnableQuery]
 public IQueryable<ProfileComposition> GetProfileCompositions()
 {
     return db.ProfileCompositions;
 }

Why not go with that? Can you elaborate why you want to have the async there in the first place?

I am under the presumption that when I directly call .ToListAsync() it causes the LINQ to trigger the EF query building routine at the cost of having the query options from being understood by EF's query building.. and thus $select query option is applied later rather than sooner.

Correct. The EnableQuery attribute is an action filter that will run after your controlled method executes, on top of its result. If you materialize the query, or even call something like AsEnumerable or ToAsyncEnumerable on the query before returning, you are "leaving the queryable land" and any operation appended after that point will execute in-memory and not in the database.

If you want EnableQuery to apply at the DB level, return IQueryable<T> from the method.

If you want to perform anything custom and want to handle the materialization yourself, remove EnableQuery and pass a ODataQueryOptions<T> object to your action and then apply it manually. This is a more advanced scenario but gives you full control of when OData operations are applied.

[bkwdesign]

Apologize for tackling two subject matters at the start of this thread. The secondary concern is that I believe async controller methods in general allow the Web API to be more performant. Putting async aside, even when I use the correct pattern, just as you've suggested I should do in your reply above, I still have an entity that isn't playing like it ought to.

I've been discussing this a little more over here on this discussion

[julealgon]

The secondary concern is that I believe async controller methods in general allow the Web API to be more performant.

More performant in terms of scalability, true, and also due to HTTP streaming support.

Keep in mind that that should be handled by EnableQuery itself though if it is present, since the async part is the iteration/materialization, which you shouldn't be doing yourself when using the attribute.

Check these:

• Asynchronous calls to database with EnableQuery attribute [workaround solution] #1326
• Need to support async queries #775