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

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

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

In Linq to Entities / Entity SQL with type conversion, choose Max.

I'm trying to create some CRUD functionality for a legacy database and using the Entity Framework as an ORM tool. One table, 'Sites', has a Guid type 'Id' column which is never seen by the users and an integer type 'SiteId' which the users use to identitfy a Site....For god knows what reason the numeric SiteId column is of type varchar(10) in the d...
entity-framework linq max select type-conversion
asked by andrej351

Select * from Entities where Id = (select max(Id) from Entities) in Entity Framework

I have an entity set called ...Entities... which has a field ...Name... and a field ...Version.... I wish to return the object having the highest version for the selected ...Name.......SQL wise I'd go ...Select * from table where name = 'name' and version = (select max(version) from table ...
entity-framework
asked by Ian

Linq to SQL Group by and Sum in Select

You are getting this error because the Grouping doesn't return ...IEnumerable<OrderedArticle>... but ...IEnumerable<IGrouping<string, OrderedArticle>>...You need to change your code to use ...a.Key...:...var sells = orderedArt .GroupBy(a => a.ArticleName) .Select(a => new { Amount = a.Sum(b => b.ArticleAmount), Name = a.Key}) .OrderByDe...
asp.net-mvc entity-framework linq sql
asked by Wouter de Kort

Entity Framework - select group by, select max date

I have a NoteBrief ...public int Id { get; set; } public string Title { get; set; } public DateTime Created { get; set; } public int ParentNoteId { get; set; } ...Data looks something like...1 Title1 03/31/1987 1 2 Title1 03/31/1988 1 3 Title3 01/01/2000 3 4 Title4 01/01/2001 4 5 Title4 01/01/2005 4 ...I want to do:...SELECT t1.* FROM Notes AS...
entity-framework
asked by user2827377

how to group by multiple columns using linq

You can group by a new anon class as follows:...// I created a Foo class to show this working var fooList = new List<Foo> { new Foo { ItemId = 62224, Code = "NC0860000", StatusId = 8 }, new Foo { ItemId = 62225, Code = "NC0860000", StatusId = 8 }, new Foo { ItemId = 62226, Code = "NC0860000", StatusId = 8 }, new Foo { ItemId = 62227...
entity-framework linq linq-to-sql
asked by Timothy Walters

Linq: Select Most Recent Record of Each Group

var query = from p in db.Payments where p.Status == false group p by p.CompanyID into op select new { CompanyID = op.Key, NextPaymentDate = op.Max(x => x.NextPaymentDate), Status = false }; ...The reason your query is not being ordered correctl...
entity-framework-6 linq
asked by Gilang

Select all columns but group by only one in linq

This will give you one item per group:...List<dynamic> data = new List<dynamic> { new {ID = 1, Message = "Hello", GroupId = 1, Date = DateTime.Now}, new {ID = 2, Message = "Hello", GroupId = 1, Date = DateTime.Now}, new {ID = 3, Message = "Hey", GroupId = 2, Date = DateTime.Now}, new {ID = 4, Message = "Dude", GroupId = 3, Da...
c# entity-framework linq sql-server
asked by Gilad Green

Max of 2 columns with multiple group by lambda expression entity framework

I believe what you are looking for is the ...GroupBy...clause....var list = dbConnection.Form_Datas .Join(dbConnection.Forms, fd => fd.form_ID, f => f.form_ID, (fd, f) => new { form_data = fd, form = f }) .Where(a => a.form_data.External_ID == personID) .GroupBy(s => new { FormID = s.form.form_ID, FormName =...
c#-4.0 entity-framework-6 lambda linq
asked by David Lee

Select multiple columns from table based on different set of multiple columns

I'm trying to get query like this from LINQ/EF6 in C#...SELECT "ID_column" FROM "Entity" WHERE ("ColumnA","ColumnB") IN (('Value_0_0','Value_0_1'),('Value_1_0','Value_1_1'), ...); ...But I'm getting the following error for the code bellow...Unable to create a constant value of type 'Anonymous type'. Only primitive types or enumeration types are s...
.net c# entity-framework-6 linq postgresql
asked by user1833414

LINQ to SQL - order by, group by and order by each group with skip and take

This is an extension of already answered question by Jon Skeet that you can ...find here....The desired result is following:...A 100 A 80 B 80 B 50 B 40 C 70 C 30 ...considering you have following class:...public class Student { public string Name { get; set; } public int Grade { get; set; } } ...to get to the result (in ideal scenario) ca...
c# entity-framework-6 linq linq-to-sql
asked by Jakub Holovsky

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

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