How to write linq query for this sql statement

Question

How would you write a linq query with the following SQL statement. I've tried several methods referenced on stackoverflow but they either don't work with the EF version I'm using (EF core 3.5.1) or the DBMS (SQL Server).

select a.ProductID, a.DateTimeStamp, a.LastPrice
from Products a
where a.DateTimeStamp = (select max(DateTimeStamp) from Products where a.ProductID = ProductID)

For reference, a couple that I've tried (both get run-time errors).

var results = _context.Products
                .GroupBy(s => s.ProductID)
                .Select(s => s.OrderByDescending(x => x.DateTimeStamp).FirstOrDefault());


var results = _context.Products
            .GroupBy(x => new { x.ProductID, x.DateTimeStamp })
            .SelectMany(y => y.OrderByDescending(z => z.DateTimeStamp).Take(1))

Thanks!

could you share what doesn't work means? Any error or wrong results? — madoxdev, Jul 20 '20 at 11:47
Don't you need two equal signs in the where? : where a.ProductID == ProductID — jdweng, Jul 20 '20 at 11:56
Thanks for the feedback @jdweng but the top statement is SQL not linq — user1155911, Jul 20 '20 at 12:02
you could simply split it in 2 calls like in [this very similar post](https://stackoverflow.com/questions/9114863/linq-using-max-to-select-a-single-row) — Mong Zhu, Jul 20 '20 at 12:04

score 1 · Answer 1 · answered Jul 20 '20 at 12:07

I understand you would like to have a list of the latest prices of each products? First of all I prefer to use group by option even over 1st query

select a.ProductID, a.DateTimeStamp, a.LastPrice
from Products a
where a.DateTimeStamp IN (select max(DateTimeStamp) from Products group by ProductID)

Later Linq:

var maxDateTimeStamps = _context.Products
                .GroupBy(s => s.ProductID)
                .Select(s => s.Max(x => x.DateTimeStamp)).ToArray();
var results = _context.Products.Where(s=>maxDateTimeStamps.Contains(s.DateTimeStamp));

-- all assuming that max datetime stamps are unique

Thanks for the answer but unfortunately the assumption that DateTimeStamp is unique isn't true in my case — user1155911, Jul 20 '20 at 12:56

score 0 · Answer 2 · answered Jul 20 '20 at 12:59

I've managed to do it with the following which replicates the correlated sub query in the original post (other than using TOP and order by instead of the Max aggregate), though I feel like there must be a more elegant way to do this.

var results = from x
              in _context.Products
                  where x.DateTimeStamp == (from y
                                            in _context.Products
                                            where y.ProductID == x.ProductID
                                            orderby y.DateTimeStamp descending
                                            select y.DateTimeStamp
                                            ).FirstOrDefault()
                  select x;

granadaCoder · Answer 3 · 2020-07-20T13:18:03.083

I prefer to break up these queries into IQueryable parts, do you can debug each "step".

Something like this:

        IQueryable<ProductOrmEntity> pocoPerParentMaxUpdateDates =
            entityDbContext.Products
            //.Where(itm => itm.x == 1)/*if you need where */
            .GroupBy(i => i.ProductID)
            .Select(g => new ProductOrmEntity
            {
                ProductID = g.Key,
                DateTimeStamp = g.Max(row => row.DateTimeStamp)
            });

        //// next line for debugging..do not leave in for production code
        var temppocoPerParentMaxUpdateDates = pocoPerParentMaxUpdateDates.ToListAsync(CancellationToken.None);

        IQueryable<ProductOrmEntity> filteredChildren =
                    from itm
                    in entityDbContext.Products
                    join pocoMaxUpdateDatePerParent in pocoPerParentMaxUpdateDates
                        on new { a = itm.DateTimeStamp, b = itm.ProductID }
                        equals
                        new { a = pocoMaxUpdateDatePerParent.DateTimeStamp, b = pocoMaxUpdateDatePerParent.ProductID }
//                        where
;



IEnumerable<ProductOrmEntity> hereIsWhatIWantItems = filteredChildren.ToListAsync(CancellationToken.None);

That last step, I am putting in an anonymous object. You can put the data in a "new ProductOrmEntity() { ProductID = pocoMaxUpdateDatePerParent.ProductID }...or you can get the FULL ProductOrmEntity object. Your original code, I don't know if getting all columns of the Product object is what you want, or only some of the columns of the object.

How to write linq query for this sql statement

3 Answers3