Filtering Grid with UrlAdaptor on complex Objects generates LINQ Error

Question

Hello,I'm implementing a fully dynamic grid system on my project, with UrlAdaptor.Actually I encountered a problem with backEnd filtering IQueryable DataSource with complex Object data field on grid columns.(PS. The filtering works well with NON complex object/NON navigations columns.)Ex. Error (Complex Object / Navigation)From Method: public IQueryable PerformFilteringSyncfusion (IQueryable dataSource, DataManagerRequest dm) where T : class { DataOperations operation = new DataOperations(); if (dm.Where != null && dm.Where.Count > 0) //Filter dataSource = operation.PerformFiltering(dataSource, dm.Where, dm.Where[0].Operator); return dataSource; }Generates this error: The LINQ expression 'DbSet() .Where(r => r.IsDeleted == False && r.DateStart.Month == DateTime.Now.Month) .OrderBy(r => r.DateStart) .Join( inner: DbSet(), outerKeySelector: r => EF.Property(r, "IdTask"), innerKeySelector: t => EF.Property(t, "Id"), resultSelector: (o, i) => new TransparentIdentifier( Outer = o, Inner = i )) .Where(r => r.Inner == null ? null : new TaskDTO{ Title = r.Inner.Title, Description = r.Inner.Description, IsDefault = r.Inner.IsDefault, IsDisabled = r.Inner.IsDisabled, Reports = null, ProductionOrderTasks = null, Id = r.Inner.Id, DateCreated = (DateTime?)r.Inner.DateCreated, DateUpdated = r.Inner.DateUpdated, IsDeleted = r.Inner.IsDeleted } != null && (r.Inner.Title ?? "").ToLower().StartsWith("a"))' 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.Can you help me resolve this issue?Thanks in advice, best regards.

notMartino · Accepted Answer

Hi Joseph,

I've found the solution time ago, sorry for the late response.

As you're saying, LINQ was unable to translate the conversion between DTOs and EF objects.

Actually I do have to pass the DataManagerRequest to the Service and apply the filters/sortings etc. on the IQueryable, before performing the projection of the mapping from EF obj to DTOs.

Below an example:

public async Task<(IQueryable<BankDTO>, int)> GetAllQueryableForSyncfusion(DataManagerRequest dm, Expression<Func<Bank, bool>> filter = null, Func<IQueryable<Bank>, IOrderedQueryable<Bank>> orderBy = null, List<string> includes = null)

{
            try 
            {
                int count = 0;

                IQueryable<Bank> banks = await _unitOfWork.BankRepository.GetAllQueryable(filter, orderBy, includes);

                banks = PerformAllActionsSyncfusion(banks, dm, out count).AsQueryable();

                IQueryable<BankDTO> bankDTOs = banks.ProjectToType<BankDTO>(_mapper.Config);

                return (bankDTOs, count);
            }
            catch (Exception ex)
            {
                AddError(ex.Message);
                return (null, 0);
            }
}

Thanks for your help, best regards.

Joseph Christ Nithin Issack · Answer

Hi Martino,Greetings
from Syncfusion support.  Before proceeding with your solution, we
would like you to share the below details.
 Complete grid rendering
     code.
 Details of the model class
     code.
 Video demo of the issue
     you are facing.
 Simple sample to reproduce
     the issue you are facing.
 Syncfusion package
     version.
Regards,Joseph
I.

notMartino · Answer

Hello Joseph,Thanks for the response, below the requested details:Grid (tried both, with/without expand query command)

@* *@

@* BTNS *@Models (DTOs and EF Core entities have the same props./navigations, i won't write those to save some text space, they are mapped with Mapster as IQueryable with ProjectToType() method) public class ReportDTO : BaseDTO { public double HoursAmount { get; set; } public DateTime DateStart { get; set; } public DateTime DateEnd { get; set; } public string? Description { get; set; } #region Navigations public Guid IdUser { get; set; } public UserDTO User { get; set; } public Guid IdProductionOrder { get; set; } public ProductionOrderDTO ProductionOrder { get; set; } public Guid IdTask { get; set; } public TaskDTO Task { get; set; } #endregion }public class BaseDTO { public Guid Id { get; set; } public string? UserCreate { get; set; } public DateTime? DateCreated { get; set; } public string? UserUpdate { get; set; } public DateTime? DateUpdated { get; set; } public bool IsDeleted { get; set; } = false;}// The tested navigation prop.public class TaskDTO : BaseDTO { public string Title { get; set; } public string? Description { get; set; } public bool IsDefault { get; set; } = false; public bool IsDisabled { get; set; } = false; #region Navigations public List Reports { get; set; } = new List(); public List ProductionOrderTasks { get; set; } = []; #endregion}Video demo -> https://streamable.com/j95tvn?src=player-page-share Sample:View: {grid} // reported up in this reply Controller/Action (used to load, filter, sort etc.): public async Task Load([FromBody] DataManagerRequest dm){ try { IQueryable reports = await _reportService.GetAllQueryable( filter: x => x.IsDeleted == false && x.DateStart.Month == DateTime.Now.Month, orderBy: x => x.OrderBy(y => y.DateStart) includes: new() { "Task", "ProductionOrder" } //(tried with/without including the navigation prop) ); // The GetAllQueryable return the mapped IQueryable from EF Core DBContext, for the entity, and works fine (all the DB records list in queryable form) int count = 0; var result = PerformAllActionsSyncfusion(reports, dm, out count); return dm.RequiresCounts ? Json(new { result, count = count }) : Json(result); } catch (Exception ex) { return StatusCode(500, ClientConstants.ERROR_INTERNAL_DEFAULT); } } // In the "PerformAllActionsSyncfusion" method i perform all the sorting, filtering, paging action as you report in the documentation, with the same order and count, everything works fine excluding the filtering of complex objects, specifically the LINQ error reported appear here: public IQueryable PerformFilteringSyncfusion (IQueryable dataSource, DataManagerRequest dm) where T : class { DataOperations operation = new DataOperations(); if (dm.Where != null && dm.Where.Count > 0) //Filter dataSource = operation.PerformFiltering(dataSource, dm.Where, dm.Where[0].Operator); return dataSource; }Package version (NUGET) -> Syncfusion.EJ2.AspNet.Core 24.2.7Thanks in advance, best regards.

Joseph Christ Nithin Issack · Answer

Hi Martino,   On inspecting the
details and the code example provided, we suspect that you are facing issue,
when you are filtering the datasource with complex Object data field on grid
columns and get and error with the LINQ query in the EF core. The issue you have
mentioned above is related to EF Core which has already been discussed in the
below stack overflow thread.Stack overflow: https://stackoverflow.com/questions/68737681/the-linq-expression-could-not-be-translated-either-rewrite-the-query-in-a-formIf you still face the issue, kindly share an issue reproducing
sample, which will be helpful for us to validate the issue.

Johnson Soundararajan S · Answer

Hi notMartino,We are happy to hear that you resolved the reported
query.Please get back to us if you need any other assistance.
Regards,Johnson Soundararajan S