How to add this Rank Over Partition By in C#?

Question

I am trying to add a Rank Over Partition By as a table column. I am first starting with a simple example. However, I receive the following error message:

I first created the rank variable that should hold the rank values.

I then added the rank variable within the body of my modifiedData variable (4th line from the bottom).

Code:

    var rank = data.GroupBy(a => new
    {
        a.AppNumber
    }).SelectMany(a => a.OrderBy(x => x.AppNumber)
        .Select((x, i) => new { a.Key, Item = x, Rank = i + 1 }));

    var modifiedData = data.Select(a =>
        new
        {
            a.AppNumber,
            a.Hrdbid,
            ApplicationType = a.ApplicationType.Label,
            a.ApplicationTypeId,
            ActivityPhase = a.ApplicationActivityPhas
                .Where(aap => aap.ActivityPhas.WorkFlowStep == a.ApplicationActivityPhas.Max(x => x.ActivityPhas.WorkFlowStep))
                .Select(aap => aap.ActivityPhas.ActivityPhase),
            ActivityPhaseDate = a.ApplicationActivityPhas
                .Where(aap => aap.ActivityPhas.WorkFlowStep == a.ApplicationActivityPhas.Max(x => x.ActivityPhas.WorkFlowStep))
                .Select(aap => aap.ActivityPhaseDate),
            Address = a.Addresses.Where(x => x.AddressType == "Physical").Select(x => x.Address_),
            rank,
            Client = a.ApplicationClients.Where(ac => ac.HeadOfHousehold).Select(ac => ac.Client.LastName + ", " + ac.Client.FirstName),
            a.Id
        }).Where(a => a.ActivityPhase.Count() == 1 && a.ActivityPhase.Contains("Waiting"));

Then in my jQuery datatable, I call/reference the column as { data: "Rank" }

Note that data is equal to var data = _db.Applications.AsQueryable();

I am not sure if I am missing a NuGet package or something else.

Answer 1

One of the things I thought was quite neat about recent EFCs is that they can compose over raw queries

Doing something like:

context.Applications.FromSqlRaw(@"
  SELECT 
    Blah, 
    Blah, 
    CASE WHEN AP.WorkFlowStep = 1 THEN RANK() OVER(PARTITION BY A.ApplicationTypeID, AAP.ActivityPhaseID ORDER BY AAP.ActivityPhaseDate, A.AppNumber) ELSE NULL END AS HouseholdSize,
    Blah,
    Blah
FROM Applications")
  .Where(...)
  .Select(...)

The SQL gets put down as a subquery that takes the place of the Applications table, so it can calculate things like this rank and with a bit of a rename, you can stuff the rank result into a column you're not using, even one of a different data type if you CAST. EF runs the query and collects the results into an Application so here you'd just need to have your C# treat the HouseholdSize as the ranking

Address = ...,
Rank = a.HouseholdSize,
Client = ...

Side note you've got a lot of props in your anonymous type that are named in the singular, like Address, Client, but they are assigned from code that results in an enumerable.. I'm not sure you'll get what you expected out of those - are you missing a call to First/Single?

Address = a.Addresses.First(x => x.AddressType == "Physical").Address_
        

Answer 2

I ended up creating a view with a ranking column using RANK() OVER (PARTITION BY foo) AS WaitList. I still used the jQuery datatables plugin and LINQ with C#.