Linq - how to write code for this query

imak

Hi I am a new bie to Linq and trying to convert having code in C# for following.

CREATE TABLE #test
(
Name VARCHAR(100) NOT NULL,
ServiceDate DATETIME2 NOT NULL,
MinValue INT,
MaxValue INT
)

INSERT INTO #test (Name, ServiceDate, MinValue, MaxValue) VALUES ( 'Foo1', '2009-05-01', 11, 12)
INSERT INTO #test (Name, ServiceDate, MinValue, MaxValue) VALUES ( 'Foo1', '2008-04-02', 10, 13)
INSERT INTO #test (Name, ServiceDate, MinValue, MaxValue) VALUES ( 'Foo1', '2010-01-05', NULL, NULL)
INSERT INTO #test (Name, ServiceDate, MinValue, MaxValue) VALUES ( 'Foo2', '2001-11-03', 23, 87)

SELECT A.*
FROM #test A
JOIN
(
SELECT Name, MAX(ServiceDate) ServiceDate
FROM #test
WHERE MinValue IS NOT NULL AND MaxValue IS NOT NULL
GROUP BY Name
) B
ON A.Name = B.Name AND A.ServiceDate = B.ServiceDate

The contents of #test table are available in DataTable dt. This is what I got so far, which basically give me the inner table with grouping, not sure what need to be done to join it to the outer table.

var query = from row in dt.AsEnumerable()
where row.Field("MinValue") != null && row.Field("MaxValue") != null
group row by row.Field(Name) into grp
orderby grp.Key
select new
{
Name = grp.Key,
ServiceDate = grp.Max(r => r.Field(ServiceData)),
};

Any help will be highly appreciated. TIA

Syed Mehroz Alam

Hi, In fact you need to get the latest record group by Name. Here's a post that describes such scenario: LINQ: How to get the latest/last record with a group by clause[^]. Regards,

Syed Mehroz Alam My Blog | My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein

imak

Thanks Mehroz for your reply. I know that's what I need to do in the inner query. If you see my code, I am able to extract latest record group, however after that this inner query need to do a join with #test table. That's exactly where I am having difficult of how to join result of grouping to this #test table. That's where I need help.

Syed Mehroz Alam

This should do the trick:

var query = from row in dt.AsEnumerable()
where row.Field<string>("MinValue") != null && row.Field<string>("MaxValue") != null
group row by row.Field<string>("Name") into grp
let maxServiceDate = grp.Max(r => r.Field<DateTime>("ServiceData"))

 from row in grp
 where row.Field<string;>("ServiceData") == maxServiceDate 
 select row

Syed Mehroz Alam My Blog | My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein