Open Closed

Howto implement a generic Filter for IQuerable for all Entities ? #2528


User avatar
0
hakan.uskaner created

If you're creating a bug/problem report, please include followings:

  • ABP Framework version: v5.1.3
  • UI type: Blazor Server
  • DB provider: EF Core
  • Tiered (MVC) or Identity Server Separated (Angular): yes

We generated our base code with abp suite. It does generate in every entity repository a IQueryable<T> ApplyFilter Function. Asume that you have over 100.000 Entries for an entity.

a) We would like to have the ability to get only a few selected items (as List<Guid>) from the database. So similar to filterText we want to add a parameter filterIds to be able to get only these Ids as result.

Normally you would provide for every Entity like Car something like this:

   public static class QueryableExtensions
   {
        public static IQueryable<Car> IncludeFilterIds(
          this IQueryable<Car> queryable,
          List<Guid> filterIds = null)
        {
            if (!filterIds.IsNullOrEmpty())
            {
                return queryable
                    .WhereIf(!filterIds.IsNullOrEmpty(), e => filterIds.Contains(e.Id));
            }
            return queryable;
        }
   }

I would like to have this ability for every entity, so i tried;

    public static class QueryableExtensions
    {
        public static IQueryable<IEntity<Guid>> IncludeFilterIds(
          this IQueryable<IEntity<Guid>> queryable,
         List<Guid> filterIds = null)
        {
            if (!filterIds.IsNullOrEmpty())
            {
                return queryable
                    .WhereIf(!filterIds.IsNullOrEmpty(), e => filterIds.Contains(e.Id));
            }
            return queryable;
        }

I did call it with:

  var query = ApplyFilter((await GetQueryableAsync()), filterText, filterIds, name, description);
       query = (IQueryable<Car>) query.IncludeFilterIds(filterIds);

But this wont work as expected.causing a casting exception. Of course i can us instead of "IEntity<Guid>" "dynamic" as type, but then i wont have the Id Field, and will need to get it per reflection.

What is here the best approach ? In your examples at https://docs.abp.io/en/abp/latest/Repositories you implement the IQueryable Logic at Application Level. Is this suggested instead of Entity Framework Level ?

b) I further would like to limit the properties in a generic way. Sometimes you need only one field like Id instead the whole table. ( so instead select * from db , i would use select id from db) For a known data type i could expand above solution with:

return queryable
 .WhereIf(!filterIds.IsNullOrEmpty(), e => filterIds.Contains(e.Id));
 .Select(p => new { p.Id });

But again if i want to do it an an generic way , i will first need to implement a parameter like List<string> proplist and then add it to the query without knowing the datatype. Do you got an idea howto achieve that ?

I think both functionaltities can be used everywhere..

Hope, you could help.,


6 Answer(s)
  • User Avatar
    0
    enisn created
    Support Team .NET Developer

    In your scenario, you can use add a where clause to specify the type, so you can access the Id property. The following extension method should work:

    public static class QueryExtensions
    {
        public static IQueryable<T> IncludeFilterIds<T>(this IQueryable<T> queryable, List<Guid> ids)
            where T : Entity<Guid>
        {
            return queryable.WhereIf(!ids.IsNullOrEmpty(), t => ids.Contains(t.Id));
        }
    }
    

    Then you can call normally:

      query = query.IncludeFilterIds(filterIds);
    
  • User Avatar
    0
    hakan.uskaner created

    Hi Ensin,

    i thank you for your quick response. This works lika charm. Do you also got an idea for the second part on howto limit the properties with an paramter List<string> propertyList to generate a dynamic select statement which then can be added to the query like describes above.

  • User Avatar
    0
    enisn created
    Support Team .NET Developer

    I think you need something like that: https://stackoverflow.com/a/16517768/7200126

  • User Avatar
    0
    hakan.uskaner created

    I thank you. I will take a look at it

  • User Avatar
    0
    hakan.uskaner created

    For thos who also searched around for this infos:

    My solution for filtering properties and id now looks like this.:

       public static class QueryableExtensions
        {
            public static Expression<Func<T, T>> DynamicSelectGenerator<T>(string filterProperties)
            {
                string[] entityProperties;
                if (filterProperties.IsNullOrEmpty())
    
                    // get Properties of the T
                    entityProperties = typeof(T).GetProperties().Select(propertyInfo => propertyInfo.Name).ToArray();
                else
                {
                    // at least Id must be include besides a property (Case sensitive !)
                    if (!filterProperties.Contains("Id"))
                        filterProperties +=",Id";
                        
                    entityProperties = filterProperties.Split(',');
                }
    
                // input parameter "o"
                var xParameter = Expression.Parameter(typeof(T), "o");
    
                // new statement like "new Data()"
                var xNew = Expression.New(typeof(T));
    
                // create initializers
                var bindings = entityProperties.Select(o => o.Trim())
                    .Select(o =>
                    {
    
                    // property "Field1"
                    var mi = typeof(T).GetProperty(o);
    
                    // original value "o.Field1"
                    var xOriginal = Expression.Property(xParameter, mi);
    
                    // set value "Field1 = o.Field1"
                    return Expression.Bind(mi, xOriginal);
                    }
                );
    
                // initialization "new Data { Field1 = o.Field1, Field2 = o.Field2 }"
                var xInit = Expression.MemberInit(xNew, bindings);
    
                // expression "o => new Data { Field1 = o.Field1, Field2 = o.Field2 }"
                var lambda = Expression.Lambda<Func<T, T>>(xInit, xParameter);
    
                // return expression
                return lambda;
            }
    
    
            // Extend IQuerable to use DynanimcSelector for Select
            public static IQueryable<T> Select<T>(this IQueryable<T> source, string parameters)
            {
                return source.Select(DynamicSelectGenerator<T>(parameters));
            }
    
            // Extend IQuerable to be able to use IncludeFilter
            public static IQueryable<T> IncludeFilter<T>(this IQueryable<T> queryable, List<Guid> filterIds, string filterProperties)
                    where T : Entity<Guid>
            {
                return queryable
                    .WhereIf(!filterIds.IsNullOrEmpty(), t => filterIds.Contains(t.Id))
                    .Select(filterProperties);
            }
    
        }
    

    Within an Repository i then call it like this, and also set Sorting (because DefaultSorting can fail if you omit the default parameter used for sorting) :

    var query = ApplyFilter((await GetQueryableAsync()), filterText, name, description);
     // Add IncludeFilter to Query and adjust sorting
    query = query.IncludeFilter(filterIds, filterProperties);
    if (!filterProperties.IsNullOrEmpty())
      sorting = filterProperties.Split(",").First();
    
    query = query.OrderBy(string.IsNullOrWhiteSpace(sorting) ? MyEntityConsts.GetDefaultSorting(false) : sorting);
    return await query.PageBy(skipCount, maxResultCount).ToListAsync(cancellationToken);
    

    To be able to use these both fields for my InputDto i used this:

     public class GlobalInputDto : PagedAndSortedResultRequestDto
        {
            /// Filter for specific Ids.
            public List<Guid> FilterIds { get; set; }
    
            /// Filter Properties (case-sensitive) p.E. "Name,Description,Id"
            public string FilterProperties { get; set; }
    
        }
    

    To use it in for an Entity you only need to replacein your InputDtos PagedAndSortedResultRequestDto inheritance through GlobalInputDto

    Hope this helps..

  • User Avatar
    0
    enisn created
    Support Team .NET Developer

    Thank you for your well-implemented sharing @hakan.uskaner.

    I'm closing this issue now

Made with ❤️ on ABP v9.1.0-preview. Updated on December 12, 2024, 07:15