7.4 release count is not fowarded to Entity Framework

[NetTecture]

I remember this working in an earlier release.

Certain of our operations have turned VERY slow.

Queries of that form in Simple.OData.CLient:

        await c.For<OdataApi.Equipment>()
            .Filter(x => x.KpiMarker.HasInspectionDutyUnterminated)
            .Count()
            .FindScalarAsync<Int32>();

are generating a simple url in the form:

{/Equipment/$count}

as per ODataQueryOptions.

Full ODataQUeryOptions here:

	Name	Value	Type
◢	options	{Microsoft.AspNet.OData.Query.ODataQueryOptions}	Microsoft.AspNet.OData.Query.ODataQueryOptions
	Apply	null	Microsoft.AspNet.OData.Query.ApplyQueryOption
	▶ Context	{Microsoft.AspNet.OData.ODataQueryContext}	Microsoft.AspNet.OData.ODataQueryContext
	▶ Count	{Microsoft.AspNet.OData.Query.CountQueryOption}	Microsoft.AspNet.OData.Query.CountQueryOption
	▶ Filter	{Microsoft.AspNet.OData.Query.FilterQueryOption}	Microsoft.AspNet.OData.Query.FilterQueryOption
	IfMatch (Microsoft.AspNet.OData.Query.ODataQueryOptions)	null	Microsoft.AspNet.OData.Formatter.ETag
	IfMatch	null	Microsoft.AspNet.OData.Formatter.ETag
	IfNoneMatch (Microsoft.AspNet.OData.Query.ODataQueryOptions)	null	Microsoft.AspNet.OData.Formatter.ETag
	IfNoneMatch	null	Microsoft.AspNet.OData.Formatter.ETag
	OrderBy	null	Microsoft.AspNet.OData.Query.OrderByQueryOption
	▶ RawValues	{Microsoft.AspNet.OData.Query.ODataRawQueryOptions}	Microsoft.AspNet.OData.Query.ODataRawQueryOptions
	▶ Request	{Microsoft.AspNetCore.Http.DefaultHttpRequest}	Microsoft.AspNetCore.Http.HttpRequest {Microsoft.AspNetCore.Http.DefaultHttpRequest}
	SelectExpand	null	Microsoft.AspNet.OData.Query.SelectExpandQueryOption
	Skip	null	Microsoft.AspNet.OData.Query.SkipQueryOption
	SkipToken	null	Microsoft.AspNet.OData.Query.SkipTokenQueryOption
	Top	null	Microsoft.AspNet.OData.Query.TopQueryOption
	Validator	{Microsoft.AspNet.OData.Query.Validators.ODataQueryValidator}	Microsoft.AspNet.OData.Query.Validators.ODataQueryValidator

As you can see - some filtering, no selectexpand. The return is one scalar value - the count.

Now, I know EfCore is not really working to spec- which is why I have a ton of code in the backend trying to determine whether or not to execute on the server or on the client. THIS QUERY is something EfCore CAN handle, so I forward it to execution:

        var setQuery = Repository.Set<T>()
            .AsNoTracking();
        var query = setQuery
            // No tracking. 
            .ProjectTo<P>(MapperConfiguration, EmptyParams, includes)

Query is shown in intellisense as IQueryable

(IQueryable of the projected generic type - we use a generic controller on this level).

The generated SQL is using a full enumeration of the results, resulting in an abysmal execution time.

The following code: if (options?.Count?.Value == true) { repositoryCanHandle = true;

            var count = query.Count();
        }

(it is after the code above and before the execution) and the count here executes in milliseconds (instead of nearly half a minute). I remember it working some days ago. It also generates efficient SQL (i.e. only asking for the count). I assume this changed some point the last days when I upgraded to the 7.4 release.

Now, the generated SQL in EfCore is a full materialization. It seems some change done between nightly builds and release makes the framework not recognize IQueryable and falling back to the IEnumerable behavior.

And yes, we do return the IQueryable:

        if (repositoryCanHandle) {
            return query;
        }

the alternative is:

        var data = query.ToList().AsQueryable();
        return data;

and I have validated that this one is NOT called for count ;) This is how some of us work around EfCore issuing bad SQL.

To reproduce I would assume all you ahve to do is check whether a count acutally executes backend wise as a count or is falling back to the IEnumerable implementation.

[NetTecture]

As more info: My count (and alternative LongCount) as tests are forwarded to EfCore and result in code like this:

SELECT COUNT_BIG(*) FROM [dbo].[OB_TInstallEquipment] AS [f] INNER JOIN [dbo].[OB_TEquipment] AS [dtoOBTInstallEquipment.OBTEquipment] ON [f].

Perfect.

When returning the query for Odata, what happens now is that I Get code hat shows a projected SELECT. It is the same query. This bug did pop up on my desk from our test team as performance regression on Friday, after I did an upgrade of the OData library from 7.4 nightly to the 7.4 release the days before.

[NetTecture]

@xuzhg I acutally tried to roll back today and could not get the old performance. It may be something else broke it - which would s***. We are in the final stages of rolling out a project and this is a breaking change (because with query times hitting 22 seconds, the UI is unusable). Unless there is a way to get this working in some way, It MAY be related to something else - totally not sure by now. I will add Ef (not core) as seond ORM and use that for all queries, using EfCore only for the updates.