Linq-to-sql using GroupBy and return record satisfying condition-CodePudding

I have a query for which I'll provide the relevant part of it

IQueryable<a> query;

query.Where(dbEntry => dbEntry.ConditionId == id)
    .Where(dbEntry => dbEntry.PharmacyStockBBD.Value > NO_BEFORE_THAN)
    .GroupBy(dbEntry => dbEntry.ProductId, pair => pair, (_e1, _e2) => new
    {
        ProductId = _e1,
        Entry = _e2.OrderBy(pair => pair.PharmacyStockBBD).First()
    })
    .ToListAsync();

I'm getting

System.InvalidOperationException: The LINQ expression '(GroupByShaperExpression:
KeySelector: (p.Id), 
ElementSelector:new { 
    PharmacyStockId = (ProjectionBindingExpression: PharmacyStockId), 
    PharmacyStockBBD = (ProjectionBindingExpression: PharmacyStockBBD), 
    ProductId = (ProjectionBindingExpression: ProductId), 
    ProductName = (ProjectionBindingExpression: ProductName), 
    ChildProductId = (ProjectionBindingExpression: ChildProductId), 
    IcnId = (ProjectionBindingExpression: IcnId), 
    IcnName = (ProjectionBindingExpression: IcnName), 
    PharmacyStock_ProductBoxes = (ProjectionBindingExpression: PharmacyStock_ProductBoxes), 
    .....
 }
)
    .OrderBy(pair => pair.PharmacyStockBBD)' could not be translated. Either rewrite the query in a form that can be translated,

or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

What I'm trying to do is group by ProductId, but get only the record having the PharmacyStockBBD as Min.

I've treid

Entry = _e2.Min(pair => pair.PharmacyStockBBD)

which works, but I need the whole _e2.

Also I know that GroupBy will get executed on the sql engine (MSSSQL) and I also knwo that it needs some aggregate function, but I need the entire record set for which PharmacyStockBBD is min.

CodePudding user response：

I think the most efficient way to do this would be with raw sql;

    context.YourTable.FromSqlInterpolated(@"
SELECT * 
FROM
(
  SELECT *, ROW_NUMBER() OVER(PARTITION BY ProductId ORDER BY PharmacyStockBBD) rn
  FROM YourTable
  WHERE 
    PharmacyStockId > {NO_BEFORE_THAN}
) x
WHERE rn = 1")

You can compose on top of this (include more tables etc

I'm not sure where you want to do the ConditionId check; it does make a difference. If you do it in the inner query, then you'll find all products of that condition, then number them from 1 and find the min product on that condition. If you do it in the outer query, you'll find all products regardless of condition, number them and then discard all those not in the condition. Where you put it depends on whether the first product will be in the condition you want

In essence, do you want to "filter for condition, then take the first" -> put it in the inner where, or do you want "take all the firsts, then filter them for condition" -> put it in the outer where