0

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:

enter image description here

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.

  • Do you have an int column in Aoplications that you aren't using for this particular query? I think I'd get the db to do the ranking with a raw, into that column, and compose over it – Caius Jard May 10 '22 at 04:44
  • I have an int column, but I am using it for this query. It is AppNumber. –  May 10 '22 at 14:07
  • Can you post the definition of class Application? – Caius Jard May 10 '22 at 19:07
  • I added the class for Application generated by POCO. I hope that is what you meant. –  May 10 '22 at 19:24
  • I'm also struggling to work out what you're trying to give a ranking for. What does your data look like? What is used to rank it? – Caius Jard May 10 '22 at 19:42
  • The following is a Rank performed in a stored procedure: `CASE WHEN AP.WorkFlowStep = 1 THEN RANK() OVER(PARTITION BY A.ApplicationTypeID, AAP.ActivityPhaseID ORDER BY AAP.ActivityPhaseDate, A.AppNumber) ELSE NULL END AS WaitNumber` I want to add this Rank in my jQuery datatable. If an Application has a WorkflowStep = 1, then it should be ranked. WorkflowStep is an attribute in the ActivityPhases table. The Applications and ActivityPhases tables are linked via the ApplicationActivityPhases many-to-many table. –  May 10 '22 at 19:47

2 Answers2

0

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_
Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • 1. My context is `_db`. I tried `_db.Applications.FromSql...` but `FromSql` is not recognized. I am using .NET 4.8. Is it included in a newer version or a part of a nuget package? 2. I call Address singular because there can only be 1 address of type "Physical". I call Client singular because there can only be 1 Head of Household client. –  May 11 '22 at 16:12
  • FromSql is an EF Core thing, sorry.. I didn't realize you were using EF6.. The equivalent there is `_db.Applications.SqlQuery("SELECT...")` but I don't think you can compose on top of it - I never used EF6; I'm just referring to https://learn.microsoft.com/en-us/ef/ef6/querying/raw-sql - that said, it looks like you can have a type with all the properties here, and do a query for all of it with `_db.Database.SqlQuery("SELECT ...")` but it's not using EF as anything more than a mapper.. I'll delete this answer soon as it's not relevant to your query – Caius Jard May 11 '22 at 17:18
0

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#.