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