2

Using the below code, I'm Attempting to get a list of items which is in price range by Linq

public Task<List<Item>> GetFilteredItems(List<Tuple<decimal, decimal>> priceList)
{
    var itemList = from i in _dbTable
                   where (priceList.Count() == 0 || (priceList.All(x => i.MRP >= x.Item1) && priceList.All(x => i.MRP <= x.Item2)))
                   select i;

    return Task.FromResult(itemList.Cast<Item>().ToList());
}

But Getting an error

Error creating query string: The LINQ expression 'x => EntityShaperExpression: 
    GiftCartBO.Entities.ItemTBL
    ValueBufferExpression: 
        ProjectionBindingExpression: EmptyProjectionMember
    IsNullable: False
.MRP >= x.Item1' 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 
'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. 
See https://go.microsoft.com/fwlink/?linkid=2101038 for more information..
Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
Prakash
  • 23
  • 3
  • 4
    If MRP has to be greater than all of x.Item1 (and less than all of item2), I think you can get a bit creative and simply do two single comparisons: one between `i.MRP` and the **max** of item1 and the other between `i.MRP` and the **min** of item2. You will also get a faster query as a bonus. – Robert Harvey Jan 03 '23 at 12:49
  • 1
    @RobertHarvey i think you should write the code and post the answer – theemee Jan 03 '23 at 13:04
  • Get this extension [FilterByItems](https://stackoverflow.com/a/67666993/10646316) and you can do the following: `_dbTable.FilterByItems(priceList, (i, p) => i.MRP >= p.Item1 && i.MRP <= p.Item2, true)`. Function will generate needed predicate. – Svyatoslav Danyliv Jan 03 '23 at 13:28
  • Pricelist Can be 0-500 and 1000-2000. This case i no need 501-999 mrp items. So this won't helpful – Prakash Feb 11 '23 at 17:29

1 Answers1

1

EF internally convert your LINQ query to SQL query. And sometimes all part of your LINQ query can not be converted to SQL so it will raise runtime error. Same happen with you as well. EF will not be able to convert List<Tuple<decimal, decimal>> object for SQL query. And same thing is mentioned in error message and link provided in error message https://go.microsoft.com/fwlink/?linkid=2101038.

You can fetch all data at once and they you apply your LINQ query like below.

var itemList = _dbTable.ToList()
                .Where(i => priceList.Count() == 0 || (priceList.All(x => i.MRP >= x.Item1) && priceList.All(x => i.MRP <= x.Item2)));

return Task.FromResult(itemList.Cast<Item>().ToList());

If you find above query returns correct result then you should improve your code as suggested by @Robert Harvey in comment. You can fetch max value of item1 & min value of item2 and use it instead. Complete code cab be like below.

if (priceList.Count() == 0)
{
    var itemList = _dbTable.ToList();
    
    return Task.FromResult(itemList.Cast<Item>().ToList());
}
else
{
    var maxItem1 = priceList.Max(x => x.item1);
    var minItem2 = priceList.Min(x => x.item2);
    var itemList = _dbTable.ToList()
                .Where(i => i.MRP >= maxItem1 && i.MRP <= minItem2);
    
    return Task.FromResult(itemList.Cast<Item>().ToList());
}

I believe you want to fetch all records whose price fall between any one of the value from priceList. Then you need to update your query like below.

var itemList = _dbTable.ToList()
                .Where(i => priceList.Count() == 0 || priceList.Any(x => i.MRP >= x.Item1 && i.MRP <= x.Item2));

return Task.FromResult(itemList.Cast<Item>().ToList());
Karan
  • 12,059
  • 3
  • 24
  • 40