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

hakan.uskaner created 3 years ago

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

enisn created 3 years ago

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);

hakan.uskaner created 3 years ago

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.

enisn created 3 years ago

Support Team .NET Developer

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

hakan.uskaner created 3 years ago

I thank you. I will take a look at it

hakan.uskaner created 3 years ago

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