I am trying to convert some result that I read into DataTable. The SQL code will be something like this that I need to convert to C# Linq.

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<string>("MinValue") != null && row.Field<string>("MaxValue") != null
     group row by row.Field<string>(Name) into grp
     orderby grp.Key
     select new
     {
          Name = grp.Key,
          ServiceDate = grp.Max(r => r.Field<DateTime>(ServiceData)),
     };

Any help will be highly appreciated. TIA

Just to be clear, You don't want to fetch the data because you already fetched it and it is in a DataTable, then you want to query the DataTable and produce a join to the subquery (something like your approximate sql code you posted). Right? If that's true, it's more a Linq question, not Linq2LLBL.

That is correct; i agree its more of a linq question