How to create complex dynamic queries in EF Core 3.1 after breaking changes?

.net-core-3.1 c# ef-core-3.0 entity-framework entity-framework-core

Question

I have this function that returns an IQueryable:

private IQueryable<string> GetActiveCellPhoneNumbersUpToDate(long serviceToken, DateTime date, bool? isPrepaid = null)
{
    var to = date.AddDays(1).Date;
    var query = ViewRepository
        .All
        .Where(i => i.ServiceToken == serviceToken)
        .Where(i => i.Date < to);
    if (isPrepaid.HasValue)
    {
        query = query.Where(i => i.IsPrepaid == isPrepaid);
    }
    query = query.OrderByDescending(i => i.Date);
    var result = query
        .GroupBy(i => i.CellPhoneNumber)
        .Where(i => i.First().ActionId == (int)SubscriptionAction.Subscription)
        .SelectMany(i => i.ToList())
        .Select(i => i.CellPhoneNumber)
        .Distinct();
    return result;
}

and this function would be called another function for only counting:

var prepaidsCount = GetActiveCellPhoneNumbersUpToDate(serviceToken, DateTime.Date, true);
var postPaidsCount = GetActiveCellPhoneNumbersUpToDate(serviceToken, DateTime.Date, false);

And when I execute it I see a breaking change of EF 3.0, that says:

Processing of the LINQ expression 'i => i .ToList()' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.

As stated in the breaking change notes, I need to user AsEnumerable or ToList before complex Where clauses to execute that portion of LINQ and bring data into RAM, then continue my query.

But for large amounts of data with the need of dynamic queries, this is absolutely insane and unimaginably inefficient.

What is the substitute for this? How can we create dynamic complex queries to be translated at runtime and only return a singular scalar value?

Update: Real-world requirements are not hello-world examples. They need complex filtering and sorting and grouping and other functions, mixed together, to extract data from a relational structure. In the past we would use Stored Procedures for those purposes. Passing a couple of parameters to database, and writing ugly, hard-to-test, far-from-maintainable, weekly-typed, refactor-resistant SQL code to fetch data.

Now the only option that comes to my mind is downgrading back to those ugly Stored Procedures. Is this nightmare a reality in EF 3.1?

Update 2: This is my scenario. I have a table in which I store cell phone number subscription/cancellations in specific services. The simplified version of that table would be:

create table Subscriptions
(
    Id,
    CellPhoneNumber,
    ServiceId,
    Date,
    ActionId
)

And these can be records:

John,+1-541-754-3010,15,2019-10-13 12:10:06.153,1
John,+1-541-754-3010,15,2019-10-18 12:10:06.153,2

Here we can see that John has subscribed to service 15, and has remained in it for 5 days, and then he has cancelled. If we want to report how many subscriber did we have at 2019-10-14, John would be counted. Because in that time, his last action was to enroll. But if we want to report how many subscribers did we have at 2910-11-03 then John's last action was to leave the service and he should not be counted.

1
6
12/13/2019 8:43:52 AM

Popular Answer

Queries that depend on intervals, or the current state of a record, can be tricky. Normally, we'd have to search for subscriptions that have one status in the specified period but not the other. This would require at least one subquery or CTE. This can be expensive even with indexing, because it requires two seeks or scans over the target table.

Any trick that avoids this is welcome. In this particular case, where the action IDs are 1 and 2, a simple way to get active subscribers would be to get those whose MAX(ActionID) isn't 2, or is less than 2, eg :

SELECT COUNT(Distinct cellnumber)
FROM Subscriptions 
WHERE Date <=@reportDate ....
GROUP by CellNumber
HAVING MAX(ActionID)<2

The equivalent in LINQ would be

var actives= ctx.Subscriptions
                .Where(sub=>sub.Date <= reportDate )
                .GroupBy(sub=>sub.CellNumber)
                .Where(grp=>grp.Max(sub=>sub.ActionId)<2)  // Results in a HAVING clause
                .Distinct()
                .Count();

Adding the rest of the criteria :

var query = ctx.Subscriptions
                .Where(sub=>sub.Date <= reportDate && sub.ServiceToken == serviceToken);
if(isPrepaid.HasValue)
{
    query = query.Where(sub => sub.IsPrePaid==isPrepaid);
}

var actives= query.GroupBy(sub=>sub.CellNumber)
                  .Where(grp=>grp.Max(sub=>sub.ActionId)<2)
                  .Distinct()
                  .Count();

SQL Server 2016 temporal tables

If we are lucky enough to use SQL Server 2016 or later, we can convert Subscriptions into a temporal table and simply count the subscriptions with a specific state at a certain point in time. We could just use :

SELECT COUNT(DISTINCT CellPhoneNumber)
FROM Subscriptions  FOR SYSTEM_TIME AS OF @someTime
WHERE ActionID<2

EF Core doesn't support temporal tables directly, so we need to use FromSqlRaw for that part of the query :

var query = ctx.Subscriptions
                .FromSqlRaw("select * from Subscriptions FOR SYSTEM_TIME AS OF {0}",
                            reportDate)
                .Where(sub=>sub.Date <= reportDate && sub.ServiceToken == serviceToken);
if(isPrepaid.HasValue)
{
    query = query.Where(sub => sub.IsPrePaid==isPrepaid);
}

var actives= query.Distinct()
                  .Count();

There's no grouping involved in that query. It doesn't depends on the actual number or order of the Action values, nor is it confused by multiple records per subscription.

2
12/13/2019 9:23:38 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow