Linq query for Row_Number() over (partition by multiple column order by)

Question

I have a SQL Server table having data as shown below and I need a C# Linq query for SQL Server's Row_Number() over (partition by multiple column) windowing function. Any help would be appreciated

Table data:

locationid	ContractorID	ResourceID	ST	OT	DT	CostDate	AFEID
15	17570	37450	48.22	66.78	96.44	2022-07-20	1093
15	17570	37450	35.46	49.11	70.92	2022-07-21	1093
15	17570	37450	54.60	75.62	109.20	2022-07-19	1093
15	17570	37450	53.90	74.64	107.80	2022-07-20	1093
15	17571	37450	25.53	35.36	51.06	2022-07-20	1093
15	17571	37625	70.92	98.21	141.84	2022-07-20	1093
15	17571	37450	87.93	121.78	175.86	2022-07-20	1093
15	17571	37450	51.06	70.71	102.12	2022-07-19	1093
15	17570	37680	60.99	84.46	121.98	2022-07-20	1093
15	17570	37680	53.90	74.64	107.80	2022-07-19	1093
15	17570	37478	53.90	74.64	107.80	2022-07-19	1093

SQL query which needs to be converted to Linq:

SELECT  
    LocationID,
    AFEID,
    ContractorID,
    ResourceID,
    MAX(ST) AS MaxST,
    MAX(OT) AS MaxOT,
    MAX(DT) AS MaxDT,
    AVG(ST) AS AvgST,
    AVG(OT) AS AvgOT,
    AVG(DT) AS AvgDT,
    MIN(ST) AS MinST,
    MIN(OT) AS MinOT,
    MIN(DT) AS MinDT,
    CostDate,
    ROW_NUMBER() OVER (PARTITION BY afeid, contractorid, resourceid 
                       ORDER BY costdate DESC) AS rownum              
FROM 
    tbldata 
GROUP BY 
    LocationID, AFEID, ContractorID,
    ResourceID, CostDate

I tried this Linq query, but had no success with it:

tbldata.OrderByDescending(x => x.CostDate)
       .AsEnumerable()
       .GroupBy(x => new
       {
           x.LocationId,
           x.AfeId,
           x.ContractorId,
           x.ResourceId,
           x.CostDate,
       })
        .Select(grp => new
        {
            grp.Key.AfeId,
            grp.Key.ContractorId,
            grp.Key.ResourceId,
            grp.Key.LocationId,
            grp.Key.CostDate,
            MaxST = grp.Max(x => x.StandardTime),
            MaxOT = grp.Max(x => x.Overtime),
            MaxDT = grp.Max(x => x.DualTime),
            AvgST = grp.Average(x => x.StandardTime),
            AvgOT = grp.Average(x => x.Overtime),
            AvgDT = grp.Average(x => x.DualTime),
            MinST = grp.Min(x => x.StandardTime),
            MinOT = grp.Min(x => x.Overtime),
            MinDT = grp.Min(x => x.DualTime),
            count = grp.Count(),
            rownum = grp.Zip(Enumerable.Range(1, grp.Count()), (j, i) => new { rownum = i}).FirstOrDefault()
        });

Output Required using the Linq query:

rownum	LocationID	FEID	ContractorID	ResourceID	MaxST	MaxOT	MaxDT	AvgST	AvgOT	AvgDT	MinST	MinOT	MinDT	CostDate
1	15	1093	17570	37450	35.46	49.11	70.92	35.460000	49.11	70.920	35.46	49.11	70.92	2022-07-21
2	15	1093	17570	37450	53.90	74.64	107.80	51.060000	70.71	102.12	48.22	66.78	96.44	2022-07-20
3	15	1093	17570	37450	54.60	75.62	109.20	54.600000	75.62	109.20	54.60	75.62	109.20	2022-07-19
1	15	1093	17570	37478	53.90	74.64	107.80	53.900000	74.64	107.80	53.90	74.64	107.80	2022-07-19
1	15	1093	17570	37680	60.99	84.46	121.98	60.990000	84.46	121.98	60.99	84.46	121.98	2022-07-20
2	15	1093	17570	37680	53.90	74.64	107.80	53.900000	74.64	107.80	53.90	74.64	107.80	2022-07-19
1	15	1093	17571	37450	87.93	121.78	175.86	56.730000	78.57	113.46	25.53	35.36	51.06	2022-07-20
2	15	1093	17571	37450	51.06	70.71	102.12	51.060000	70.71	102.12	51.06	70.71	102.12	2022-07-19
1	15	1093	17571	37625	70.92	98.21	141.84	70.920000	98.21	141.84	70.92	98.21	141.84	2022-07-20

Please help me out.

Does this answer your question? [Row\_number over (Partition by yyy) in Entity Framework](https://stackoverflow.com/questions/33098361/row-number-over-partition-by-yyy-in-entity-framework) — gsharp, Jul 30 '22 at 06:54
I personally would do a view and then an entity for that view. — gsharp, Jul 30 '22 at 06:57
LinqToSQL was designed to translate an `IQueryable` to a reasonably generic SQL query, which is why `ROW_NUMBER` and numerous other server-specific methods were never implemented. It was never intended as anything other than a temporary fix until a more permanent solution (EF) was completed. That took longer than expected, I guess — Corey, Jul 30 '22 at 07:31
It is EF Core? There is [exension](https://stackoverflow.com/a/64413634/10646316) which nay help. — Svyatoslav Danyliv, Jul 30 '22 at 07:43

score 0 · Accepted Answer · answered Jul 30 '22 at 07:24

If I correctly understand what needs to be obtained, it has been simplified to illustrate the solution:

using System.Collections.Generic;
using Xunit;
using System.Linq;

namespace TestProject1
{
    public class TestGrouping
    {
        [Fact]
        public void Test()
        {
            var data = new List<Item>{
                new Item{Location = "A", Contractor = "John", SomeValue = 10},
                new Item{Location = "A", Contractor = "John", SomeValue = 12},
                new Item{Location = "B", Contractor = "John", SomeValue = 10},
                new Item{Location = "C", Contractor = "Tomm", SomeValue = 13},
            };

            var res =
                data
                    .GroupBy(
                        item => new { Location = item.Location },
                        (key, group) =>
                            group.Select((x, i) =>
                                new
                                {
                                    Row = i + 1,
                                    x.Location,
                                    x.Contractor,
                                    SumOfSomeValue = group.Sum(x => x.SomeValue)
                                }))
                    .SelectMany(reportItem => reportItem)
                    .ToList();
        }
    }

    public class Item
    {
        public string Location;
        public string Contractor;
        public int SomeValue;
    }
}

score 0 · Answer 2 · answered Jul 30 '22 at 08:51

The q in Linq stands for query. It is not meant modifying data. See my solution below

using System;
using System.Linq;
using System.Text;
using System.Collections;
using System.Collections.Generic;
using System.Data;

namespace ConsoleApp2
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable tbldata = new DataTable();
            tbldata.Columns.Add("locationid", typeof(int));
            tbldata.Columns.Add("ContractorID", typeof(int));
            tbldata.Columns.Add("ResourceID", typeof(int));
            tbldata.Columns.Add("ST", typeof(decimal));
            tbldata.Columns.Add("OT", typeof(decimal));
            tbldata.Columns.Add("DT", typeof(decimal));
            tbldata.Columns.Add("CostDate", typeof(DateTime));
            tbldata.Columns.Add("AFEID", typeof(int));

            tbldata.Rows.Add(15, 17570, 37450, 48.22, 66.78, 96.44, DateTime.Parse("2022 - 07 - 20"), 1093);
            tbldata.Rows.Add(15, 17570, 37450, 35.46, 49.11, 70.92, DateTime.Parse("2022 - 07 - 21"), 1093);
            tbldata.Rows.Add(15, 17570, 37450, 54.60, 75.62, 109.20, DateTime.Parse("2022 - 07 - 19"), 1093);
            tbldata.Rows.Add(15, 17570, 37450, 53.90, 74.64, 107.80, DateTime.Parse("2022 - 07 - 20"), 1093);
            tbldata.Rows.Add(15, 17571, 37450, 25.53, 35.36, 51.06, DateTime.Parse("2022 - 07 - 20"), 1093);
            tbldata.Rows.Add(15, 17571, 37625, 70.92, 98.21, 141.84, DateTime.Parse("2022 - 07 - 20"), 1093);
            tbldata.Rows.Add(15, 17571, 37450, 87.93, 121.78, 175.86, DateTime.Parse("2022 - 07 - 20"), 1093);
            tbldata.Rows.Add(15, 17571, 37450, 51.06, 70.71, 102.12, DateTime.Parse("2022 - 07 - 19"), 1093);
            tbldata.Rows.Add(15, 17570, 37680, 60.99, 84.46, 121.98, DateTime.Parse("2022 - 07 - 20"), 1093);
            tbldata.Rows.Add(15, 17570, 37680, 53.90, 74.64, 107.80, DateTime.Parse("2022 - 07 - 19"), 1093);
            tbldata.Rows.Add(15, 17570, 37478, 53.90, 74.64, 107.80, DateTime.Parse("2022 - 07 - 19"),  1093);

            var groupTable = tbldata.AsEnumerable()
                .OrderByDescending(x => x.Field<DateTime>("CostDate"))
                .GroupBy(x => new
                {
                    lid = x.Field<int>("LocationId"),
                    afeid = x.Field<int>("AFEID"),
                    cid = x.Field<int>("ContractorId"),
                    rid = x.Field<int>("ResourceId"),
                });


            DataTable output = tbldata.Clone();
            output.Columns.Add("MaxST", typeof(decimal));
            output.Columns.Add("MaxOT", typeof(decimal));
            output.Columns.Add("MaxDT", typeof(decimal));
            output.Columns.Add("AvgST", typeof(decimal));
            output.Columns.Add("AvgOT", typeof(decimal));
            output.Columns.Add("AvgDT", typeof(decimal));
            output.Columns.Add("MinST", typeof(decimal));
            output.Columns.Add("MinOT", typeof(decimal));
            output.Columns.Add("MinDT", typeof(decimal));
            output.Columns.Add("rownum");
            output.Columns["rownum"].SetOrdinal(0);
            output.Columns.Remove("ST");
            output.Columns.Remove("OT");
            output.Columns.Remove("DT");

            foreach (var row in groupTable)
            {
                var dates = row.GroupBy(x => x.Field<DateTime>("CostDate")).ToArray();

                for(int i = 0; i < dates.Count(); i++)
                {
                    DataRow newRow = output.Rows.Add(new object[]
                    {
                        i + 1,
                        row.Key.lid,
                        row.Key.cid,
                        row.Key.rid,
                        dates[i].Key,
                        row.Key.afeid,
                        dates[i].Max(x => x.Field<decimal>("ST")),
                        dates[i].Max(x => x.Field<decimal>("OT")),
                        dates[i].Max(x => x.Field<decimal>("DT")),
                        dates[i].Average(x => x.Field<decimal>("ST")),
                        dates[i].Average(x => x.Field<decimal>("OT")),
                        dates[i].Average(x => x.Field<decimal>("DT")),
                        dates[i].Min(x => x.Field<decimal>("ST")),
                        dates[i].Min(x => x.Field<decimal>("OT")),
                        dates[i].Min(x => x.Field<decimal>("DT"))
                    });
                }
            }

        }

    }
 
}

Linq query for Row_Number() over (partition by multiple column order by)

2 Answers2