CODEWITHSUNDEEP
c#entity-frameworklinqhotchocolatedynamic-linq-core
fouks
fouks

Reputation: 67

Dynamic Linq / EF Where query not working

I'm working with a GraphQL, HotChocolate, API with EF where we fetch certain information. To authorize the users access on this information we first retrieve an entity from the database that holds the users permissions, eg:

public class Permissions 
{
   public int InformationId { get; set; }
   public bool View { get; set; }
   public bool ViewSecret{ get; set; }
}

At first we joined the permissions query on the base-query to filter what information you fetch depending on your permissions, this however turned out to be very bad in terms of performance, that's why we decided to first do the permission query, then in the second query we filter the result depending on the permissions, eg:

var result = _dbContext.Information
    .Where(workingQueryFilter)
    .Select(x => new Information
    {
        Name = x.Name
        ChildInformation = x.ChildInformation
        .AsQueryable()
        .Where(notWorkingQueryFilter).ToList()
    });

For the first .Where(QueryFilter), we use the dynamic linq package where we build the filter as a string, eg:

var workingQueryFilter =  Filter(ListOfPermissions);

public static string Filter(List<Permissions> permission)
{
   var expression = "i => i.IsDeleted == false"

   for (var i = 0; i < permission.Count; i++)
   {
      expression += $"i.InformationId == {permission[i].InformationId} && {information[i].View}"
   }
 
   return expression;
}

This works fine.

However, when we want to filter the ChildInformation, I get this error message:

Expression of type 'System.Collections.Generic.List1[Models.ChildInformation]' cannot be used for parameter of type 'System.Linq.IQueryable1[Models.ChildInformation]' of method 'System.Linq.IQueryable1[Models.ChildInformation] Where[ChildInformation](System.Linq.IQueryable1[Models.ChildInformation], System.String, System.Object[])' (Parameter 'arg0')

Update

I decided to use PredicateBuilder instead, which works!

Upvotes: 0

Views: 108

Answers (1)

jakub podhaisky
jakub podhaisky

Reputation: 978

It looks like the issue you are facing as the error msg suggests you are trying to use Liqn on a List<T>

Try to use .ToList after the query:

var result = _dbContext.Information
               .Where(workingQueryFilter) // This is fine since it's operating on IQueryable
               .Select(x => new Information
                {
                   Name = x.Name,
                   ChildInformation = x.ChildInformation
                                       .AsQueryable() // Ensure this is IQueryable
                                       .Where(dynamicChildFilter) // Apply dynamic filter here
                                       // Do not call .ToList() here
                })
               .ToList(); // Convert to list after query is finished

Also make sure in your queries you are not converting to List<T> until all of your queries on the object are done or save the information to a temp object that you can make AsQueryable() and do not convert to the list when you might want to use query later on

EDIT: workaround for

Cannot implicitly convert type 'System.Linq.IQueryable<Models.ChildInformation>' to 'System.Collections.Generic.ICollection<Models.ChildInformation>'. An explicit conversion exists (are you missing a cast?)

var result = _dbContext.Information
    .Where(workingQueryFilter) // Apply initial filter dynamically
    .Select(x => new 
    {
        // Project your main entity including any necessary properties
        Information = x,
        // Apply dynamic filtering on the child collection and then convert to List
        FilteredChildInformation = x.ChildInformation
            .AsQueryable()
            .Where(dynamicChildFilter) // This needs to be an IQueryable operation
            .ToList() // Convert here, within the projection
    })
    .ToList() // This final ToList is for the main query
    .Select(x => new Information 
    { 
        // Map the projected anonymous type back to your domain model
        // Assuming 'Information' has properties Name, ChildInformation, etc.
        Name = x.Information.Name,
        ChildInformation = x.FilteredChildInformation // This is already a List from the projection
    })
    .ToList(); // Ensure everything is materialized to List at the end

Upvotes: -1

Related Questions