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:

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:

Please help me out.

CodePudding user response：

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;
    }
}

CodePudding user response：

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"))
                    });
                }
            }

        }

    }
 
}