Error after applyed filter on column

2 posts, 1 answers
  1. Dario
    Dario avatar
    40 posts
    Member since:
    Jun 2018

    Posted 16 Apr 2020 Link to this post

    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.        }

     

  2. Answer
    Alex Hajigeorgieva
    Admin
    Alex Hajigeorgieva avatar
    1082 posts

    Posted 21 Apr 2020 Link to this post

    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

    Progress is here for your business, like always. Read more about the measures we are taking to ensure business continuity and help fight the COVID-19 pandemic.
    Our thoughts here at Progress are with those affected by the outbreak.
Back to Top