CODEWITHSUNDEEP
c#sqllinq
user1155911
user1155911

Reputation: 41

How to write linq query for this sql statement

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!

Upvotes: 0

Views: 77

Answers (3)

granadaCoder
granadaCoder

Reputation: 27842

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.

Upvotes: 0

user1155911
user1155911

Reputation: 41

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;

Upvotes: 0

MatR
MatR

Reputation: 305

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

Upvotes: 1

Related Questions