How to select records with MAX Id that group by multiple columns in LINQ to SQL

c# entity-framework-6 linq-to-sql

Question

I need to select the last record of particular columns. I have the following records

WarehouseId | ItemId | SubItemId | DeliveryGroupId | Other Columns
     1      |   1    |     1     |        1        |      ...
     1      |   1    |     1     |        2        |      ...
     1      |   1    |     1     |        3        |      ...
     1      |   1    |     2     |        1        |      ...
     1      |   1    |     2     |        2        |      ...
     1      |   2    |     1     |        1        |      ...

Then I only want to select the MAX(DeliveryGroupId) for each WarehouseId | ItemId | SubItemId. The result should be:

WarehouseId | ItemId | SubItemId | DeliveryGroupId | Other Columns
     1      |   1    |     1     |        3        |      ...
     1      |   1    |     2     |        2        |      ...
     1      |   2    |     1     |        1        |      ...

In SQL, it is very simple to do:

SELECT *
FROM [dbo].[tblOrderDeliveryGroup] t1
WHERE [DeliveryGroupId] IN
(
    SELECT MAX([DeliveryGroupId])
    FROM [dbo].[tblOrderDeliveryGroup] t2
    WHERE (t1.[WarehouseId] = t2.[WarehouseId]) AND (t1.[ItemId] = t2.[ItemId]) AND (t1.[SubItemId] = t2.[SubItemId])
    GROUP BY [WarehouseId], [ItemId], [SubItemId]
);

The question is, how do I translate that SQL statement into LINQ-to-SQL?

Thanks

UPDATE

So far, this is my solution. It is very ugly and surely not efficient.

var vLastRecs = (from rec in tblOrderDeliveryGroups.AsNoTracking()
                 group rec by new { rec.WarehouseId, rec.ItemId, rec.SubItemId } into grec
                 select new
                 {
                     grec.Key.WarehouseId,
                     grec.Key.ItemId,
                     grec.Key.SubItemId,
                     DeliveryGroupId = grec.Max(rec => rec.DeliveryGroupId)
                 });

return (from rec in tblOrderDeliveryGroups.AsNoTracking()
        where vLastRecs.Any(lrec => (rec.WarehouseId == lrec.WarehouseId) && (rec.ItemId == lrec.ItemId) && (rec.SubItemId == lrec.SubItemId) && (rec.DeliveryGroupId == lrec.DeliveryGroupId))
        select rec).ToList();

Is it possible to improve it?

1
0
3/5/2020 10:20:15 AM

Popular Answer

Try following :

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

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("WarehouseId", typeof(int));
            dt.Columns.Add("ItemId", typeof(int));
            dt.Columns.Add("SubItemId", typeof(int));
            dt.Columns.Add("DeliveryGroupId", typeof(int));

            dt.Rows.Add(new object[] {1,1,1,1});
            dt.Rows.Add(new object[] {1,1,1,2});
            dt.Rows.Add(new object[] {1,1,1,3});
            dt.Rows.Add(new object[] {1,1,2,1});
            dt.Rows.Add(new object[] {1,1,2,2});
            dt.Rows.Add(new object[] {1,2,1,1});

            DataTable dt2 = dt.AsEnumerable()
                .OrderByDescending(x => x.Field<int>("DeliveryGroupId"))
                .GroupBy(x => new { warehouse = x.Field<int>("WarehouseId"), item = x.Field<int>("ItemId"), subitem = x.Field<int>("SubItemId")})
                .Select(x => x.FirstOrDefault())
                .CopyToDataTable();

        }
    }
}

Here is a solution using classes

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

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            OrderDeliveryGroups tblOrderDeliverGroups = new OrderDeliveryGroups();


            List<AsNoTracking> vLastRecs = tblOrderDeliverGroups.AsNoTracking() 
                .OrderByDescending(x => x.DeliverGroupId)
                .GroupBy(x => new { x.WarehouseId, x.ItemId, x.SubItemId})
                .Select(x => x.FirstOrDefault())
                .ToList();
        }
    }
    public class OrderDeliveryGroups
    {
        public List<AsNoTracking> AsNoTracking()
        {
            return new List<AsNoTracking>() {
                new AsNoTracking() { WarehouseId = 1, ItemId = 1, SubItemId = 1, DeliverGroupId = 1 },
                new AsNoTracking() { WarehouseId = 1, ItemId = 1, SubItemId = 1, DeliverGroupId = 2 },
                new AsNoTracking() { WarehouseId = 1, ItemId = 1, SubItemId = 1, DeliverGroupId = 3 },
                new AsNoTracking() { WarehouseId = 1, ItemId = 1, SubItemId = 2, DeliverGroupId = 1 },
                new AsNoTracking() { WarehouseId = 1, ItemId = 1, SubItemId = 2, DeliverGroupId = 2 },
                new AsNoTracking() { WarehouseId = 1, ItemId = 2, SubItemId = 1, DeliverGroupId = 1 }
            };
        }
    }
    public class AsNoTracking
    {
        public int WarehouseId { get; set; }
        public int ItemId { get; set; }
        public int SubItemId { get; set; }
        public int DeliverGroupId { get; set; }
    }
}
0
3/6/2020 7:55:56 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