Error after applyed filter on column

[Dario]

I'm wrote this action

[Authorize]
        public IActionResult Item_ReadData([DataSourceRequest] DataSourceRequest request, string dateFiler)
        {
            CultureInfo culture = CultureInfo.CreateSpecificCulture("en-US");
            var selectedDate = DateTime.Parse(dateFiler, culture);

            System.Security.Claims.ClaimsPrincipal currentUser = this.User;
            var scope = currentUser.Claims.ToList().SingleOrDefault(c => c.Type == "Scope")?.Value;

            using (DbNavision ctx = new DbNavision())
            {
                var itemsFound = (from rec in ctx.UpSrlItem
                                  select new ItemModel(rec, selectedDate, scope));
                var dataResult = itemsFound.ToDataSourceResult(request);
                return Json(dataResult);
            }
        }

 

It works ok! But if I apply a filter on a column (attach what I'm doing), it shows me this error.

System.InvalidOperationException: 'The LINQ expression 'DbSet<UpSrlItem>
    .Where(u => new ItemModel(
        u,
        __selectedDate_0,
        __scope_1
    ).No.ToLower() == "fratverde")' 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().

 

Model output is a custom, because I need to update additional properties of model that aggregate several information.

I can't use ToList() or other, because result contains a lot of rows. Infact I added .Scrollable(scrollable => scrollable.Virtual(true)) property on Grid.

This is my model costructor

01.public ItemModel(UpSrlItem item, Nullable<DateTime> dateFilter, string locationFilter)
02.        {
03.            No = item.No;
04.            Description = item.Description;
05.            Brand = item.Brand;
06.
07.            using (DbNavision ctx = new DbNavision())
08.            {
09.                //Net Change
10.                List<UpSrlItemLedgerEntry> netChange;
11.
12.                if (locationFilter != "")
13.                    netChange = (from rec in ctx.UpSrlItemLedgerEntry
14.                                 where rec.ItemNo == No &&
15.                                 rec.LocationCode == locationFilter &&
16.                                 rec.PostingDate <= dateFilter
17.                                 select rec).ToList();
18.                else
19.                    netChange = (from rec in ctx.UpSrlItemLedgerEntry
20.                                 where rec.ItemNo == No &&
21.                                 rec.PostingDate <= dateFilter
22.                                 select rec).ToList();
23.
24.                NetChange = netChange.Sum(ile => ile.Quantity);
25.
26.                //Purchases (Qty.) + Positive Adjmt. (Qty.)
27.                List<UpSrlItemLedgerEntry> purchasesQty;
28.
29.                if (locationFilter != "")
30.                    purchasesQty = (from rec in ctx.UpSrlItemLedgerEntry
31.                                    where rec.ItemNo == No &&
32.                                    rec.LocationCode == locationFilter &&
33.                                    rec.PostingDate <= dateFilter &&
34.                                    (rec.EntryType == (int)UpSrlItemLedgerEntryEntryTypes.Purchase ||
35.                                    rec.EntryType == (int)UpSrlItemLedgerEntryEntryTypes.Positive_Adjmt)
36.                                    select rec).ToList();
37.                else
38.                    purchasesQty = (from rec in ctx.UpSrlItemLedgerEntry
39.                                    where rec.ItemNo == No &&
40.                                    rec.PostingDate <= dateFilter &&
41.                                    (rec.EntryType == (int)UpSrlItemLedgerEntryEntryTypes.Purchase ||
42.                                    rec.EntryType == (int)UpSrlItemLedgerEntryEntryTypes.Positive_Adjmt)
43.                                    select rec).ToList();
44.
45.                PurchasesQty = purchasesQty.Sum(ile => ile.Quantity);
46.
47.                UpSrlItemLedgerEntry lastEntry;
48.                if (locationFilter != "")
49.                    lastEntry = (from rec in ctx.UpSrlItemLedgerEntry
50.                                 where rec.ItemNo == No &&
51.                                 rec.LocationCode == locationFilter &&
52.                                 rec.PostingDate <= dateFilter
53.                                 select rec).ToList().LastOrDefault();
54.                else
55.                    lastEntry = (from rec in ctx.UpSrlItemLedgerEntry
56.                                 where rec.ItemNo == No &&
57.                                 rec.PostingDate <= dateFilter
58.                                 select rec).ToList().LastOrDefault();
59.
60.                LastEntryDate = lastEntry?.PostingDate;
61.
62.            }
63.        }

 

[Alex Hajigeorgieva]

Hello, Dario,

Are you using EF 3.1  as this might be related to the breaking change introduced by Microsoft with that version.

Take a look at this forum thread:

https://www.telerik.com/forums/todatasource-throws-exception-using-ef-core-3-0-with-groups#2U3aIDg07EiJPkqRslDHaQ

Kind Regards,
Alex Hajigeorgieva
Progress Telerik