Question 86424 in ASP.NET Question posted in
The official documentation can be found here.

How can I use multiple where on keys in LINQ with certain condition – Asp.net

I am working on a query where I want to retrieve data including multiple int? keys.
My output Dto:

 public class FilterParamsDto {
    public int? StudentId { get; set; }
    public int? NationalityId { get; set; }
    public int? CountryId { get; set; }
    public int? SchoolCountryId { get; set; }
    public int? SchoolStateId { get; set; }
    public int? SchoolCityId { get; set; }

    .... More keys
}

I used following queries

var value = from y in data
            where dto.CountryId == null
                ? y.CountryId != null
                : y.IntakeId == dto.CountryId && dto.StudentId == null
                    ? y.StudentId != null
                    : y.StudentId == dto.StudentId && dto.SchoolCityId == null
                        ? y.SchoolCityId != null
                        : y.SchoolCityId == dto.SchoolCityId
            select y;

What I want to Achieve:

I want to make a method where if any property have some value I want to filter data based on that particular property and if there is not any value I want to filter data based on another properties who do have some value.
if any property have 0 value I want to skip filter because if any property have 0 value so the data wont’ match and i am not going to receive any data using || the data is not getting filtered as per required condition.

EDIT 1 There are three possibilities either all properties have some values, some properties caring values, all the properties caring values.
the required logic should be like if first where executed then another where should be executed on updated values and so on…

Tags:

3

Answers


  1. 0

    To give you just one example, do it like this

    .Where(y => (dto.CountryId == null || y.CountryId == dto.CountryId))

    Add as many conditions as you want.

    Login or Signup to reply.
  2. 0

    Well, if you have many properties like the above and have multiple versions of this kind of filter operation I would suggest building a dynamic expression to filter your collections. This approach has one assumption: the entity that is queried and the dto has the same property names and similar types.

     public static void Main()
    {
        var dto = new FilterParamsDto { CountryId = 3, StudentId = 5 };
        var data = new List<Dummy> { new() { CountryId = 3, StudentId = 1 }, new() { CountryId = 4, StudentId = 5 }, new() { CountryId = 1, StudentId = 2 }, new() { CountryId = 3, StudentId = 5 } };
        var expAnd = GenerateFilterExpression<Dummy, FilterParamsDto>(dto);
        var expOr = GenerateFilterExpression<Dummy, FilterParamsDto>(dto, false);

        var filteredWithAnd = data.AsQueryable().Where(expAnd).ToArray();
        var filteredWithOr = data.AsQueryable().Where(expOr).ToArray();
    }

    public static PropertyInfo[] GetNonNullProperties<T>(T item) where T : class
    {
        var properties = typeof(T)
            .GetProperties(BindingFlags.Instance | BindingFlags.Public)
            .Where(r => r.GetValue(item) != null)
            .Select(r => r).ToArray();

        return properties;
    }

    public static Expression<Func<T, bool>> GenerateFilterExpression<T, R>(R dto, bool and = true) where T : class where R : class
    {
        var p = Expression.Parameter(typeof(T), "p");
        var nonnullProps = GetNonNullProperties(dto);
        var constExp = Expression.Constant(dto);

        // here we decide how to join conditions
        Func<Expression, Expression, BinaryExpression> operatorExp = and ? Expression.AndAlso : Expression.OrElse;
        Expression? exp = null;

        var sourceType = typeof(T);

        foreach (var item in nonnullProps)
        {
            var sourceProp = sourceType.GetProperty(item.Name);
            var prop = Expression.Property(p, sourceProp!);
            Expression dtoProp = Expression.Property(constExp, item);

            // we need this trick otherwise we will have runtime error that says you can not have an expression like : int? == int
            if (sourceProp!.PropertyType != item.PropertyType)
            {
                var underlyingType = Nullable.GetUnderlyingType(item.PropertyType);
                dtoProp = Expression.Convert(dtoProp, underlyingType!);
            }

            if (exp == null)
            {
                exp = Expression.Equal(prop, dtoProp);
            }
            else
            {
                exp = operatorExp(exp, Expression.Equal(prop, dtoProp));
            }
        }

        var result = Expression.Lambda<Func<T, bool>>(exp!, p);

        return result;
    }

    Fiddle

    Login or Signup to reply.
  3. 0

    To filter by the first available filter property, you can write (I replaced dto by filter and y by d to make it clearer):

    var value = from d in data
   where
      filter.CountryId != null && (d.CountryId ?? d.IntakeId) == filter.CountryId ||
      filter.StudentId != null && d.StudentId == filter.StudentId ||
      filter.SchoolCityId != null && d.SchoolCityId == filter.SchoolCityId
   select d;

    To filter by all available filters:

    var value = from d in data
   where
     (filter.CountryId == null || (d.CountryId ?? d.IntakeId) == filter.CountryId) &&
     (filter.StudentId == null || d.StudentId == filter.StudentId) &&
     (filter.SchoolCityId == null || d.SchoolCityId == filter.SchoolCityId)
   select d;

    The test (d.CountryId ?? d.IntakeId) == filter.CountryId compares d.CountryId if it is not null and otherwise d.IntakeId with filter.CountryId.

    According to the documentation of ?? and ??= operators (C# reference):

    The null-coalescing operator ?? returns the value of its left-hand operand if it isn’t null; otherwise, it evaluates the right-hand operand and returns its result.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search