public static IQueryable <T> DistinctByField <T>(this IQueryable <T> queryable, string propertyName) { var type = typeof(T); var property = type.GetPropertyByName(propertyName); if (property == null) { return(queryable); } var parameter = Expression.Parameter(type); var propertyAccess = ExpressionUtils.CreateMemberAccess(parameter, propertyName); var groupByExp = Expression.Lambda(propertyAccess, parameter); var groupByQueryable = queryable.Provider.CreateQuery(Expression.Call(typeof(Queryable), "GroupBy", new[] { type, property.PropertyType }, queryable.Expression, Expression.Quote(groupByExp))); var groupByArgument = typeof(IGrouping <,>).MakeGenericType(property.PropertyType, type); var parameterExpression = Expression.Parameter(groupByArgument, "x"); var firstOrDefaultMethod = typeof(Enumerable).GetMethods().FirstOrDefault(x => x.Name == "FirstOrDefault"); var firstOrDefaultMethodExp = Expression.Lambda(Expression.Call(null, firstOrDefaultMethod.MakeGenericMethod(type), parameterExpression), parameterExpression); var selectMethod = typeof(Queryable).GetMethods().FirstOrDefault(x => x.Name == "Select"); var selectQueryable = groupByQueryable.Provider.CreateQuery <T>(Expression.Call(null, selectMethod.MakeGenericMethod(groupByArgument, type), groupByQueryable.Expression, firstOrDefaultMethodExp)); return(selectQueryable); }
public static IOrderedQueryable <T> ThenByDescendingWithNullLowPriority <T>(this IOrderedQueryable <T> queryable, string propertyName = null) { var type = typeof(T); PropertyInfo property = null; if (!string.IsNullOrEmpty(propertyName)) { property = type.GetPropertyByName(propertyName); } if (property == null) { property = type.GetProperties().FirstOrDefault(x => x.GetCustomAttributes(typeof(DefaultSortPropertyAttribute), true).Any()); propertyName = property?.Name; } if (property == null) { return(queryable.ThenByDescending(x => true)); } var propertyPropertyType = property.PropertyType; if (propertyPropertyType == typeof(string)) { var strExpr = ExpressionUtils.ToLambda <T, string>(propertyName); return(queryable.ThenByDescending(x => string.IsNullOrEmpty(strExpr.Invoke(x))).ThenByDescending(strExpr)); } if (!propertyPropertyType.IsNullable()) { var parameter = Expression.Parameter(type); var propertyAccess = ExpressionUtils.CreateMemberAccess(parameter, propertyName); var orderByExp = Expression.Lambda(propertyAccess, parameter); var resultExp = Expression.Call(typeof(Queryable), "ThenByDescending", new[] { type, propertyPropertyType }, queryable.Expression, Expression.Quote(orderByExp)); return((IOrderedQueryable <T>)queryable.Provider.CreateQuery <T>(resultExp)); } else { var parameter = Expression.Parameter(type); var propertyAccess = ExpressionUtils.CreateMemberAccess(parameter, propertyName); var orderByDescendingExp = Expression.Lambda <Func <T, bool> >(Expression.Equal(propertyAccess, Expression.Constant(null, propertyPropertyType)), parameter); var orderByDescending = Expression.Call(typeof(Queryable), "ThenByDescending", new[] { type, orderByDescendingExp.Body.Type }, queryable.Expression, Expression.Quote(orderByDescendingExp)); var orderedQueryable = (IOrderedQueryable <T>)queryable.Provider.CreateQuery <T>(orderByDescending); var thenByDescendingExp = Expression.Lambda(propertyAccess, parameter); var thenByDescending = Expression.Call(typeof(Queryable), "ThenByDescending", new[] { type, propertyPropertyType }, orderedQueryable.Expression, Expression.Quote(thenByDescendingExp)); return((IOrderedQueryable <T>)orderedQueryable.Provider.CreateQuery <T>(thenByDescending)); } }
public static IOrderedQueryable <T> OrderByWithNullLowPriority <T>(this IQueryable <T> queryable, string propertyName = null, bool isDesc = false) { if (isDesc) { return(queryable.OrderByDescendingWithNullLowPriority(propertyName)); } var type = typeof(T); PropertyInfo property = null; if (!string.IsNullOrEmpty(propertyName)) { property = type.GetPropertyByName(propertyName); } if (property == null) { property = type.GetProperties().FirstOrDefault(x => x.GetCustomAttributes(typeof(DefaultSortPropertyAttribute), true).Any()); propertyName = property?.Name; } if (property == null) { return(queryable.OrderBy(x => true)); } var propertyPropertyType = property.PropertyType; if (propertyPropertyType == typeof(string)) { var strExpr = ExpressionUtils.ToLambda <T, string>(propertyName); return(queryable.OrderBy(x => string.IsNullOrEmpty(strExpr.Invoke(x))).ThenBy(strExpr)); } if (propertyPropertyType.IsEnum) { var strExpr = ExpressionUtils.ToLambda <T, int>(propertyName); var enumValues = Enum.GetValues(propertyPropertyType); var objs = enumValues.Cast <object>().Select((t, i) => enumValues.GetValue(i)).ToList(); var body = objs .OrderBy(value => value.GetDisplayName()) .Select((value, ordinal) => new { value = (int)value, ordinal }) .Reverse() .Aggregate((Expression)null, (next, item) => next == null ? (Expression) Expression.Constant(item.ordinal) : Expression.Condition(Expression.Equal(strExpr.Body, Expression.Constant(item.value)), Expression.Constant(item.ordinal), next)); var expr = Expression.Lambda <Func <T, int> >(body, strExpr.Parameters[0]); return(queryable.OrderBy(expr)); } if (propertyPropertyType.IsGenericType && propertyPropertyType.GenericTypeArguments[0].IsEnum) { var enumType = propertyPropertyType.GenericTypeArguments[0]; var strExpr = ExpressionUtils.ToLambda <T, int>(propertyName); var enumValues = Enum.GetValues(enumType); var objs = enumValues.Cast <object>().Select((t, i) => enumValues.GetValue(i)).ToList(); var body = objs .OrderBy(value => value.GetDisplayName()) .Select((value, ordinal) => new { value = (int)value, ordinal }) .Reverse() .Aggregate((Expression)null, (next, item) => next == null ? (Expression) Expression.Constant(item.ordinal) : Expression.Condition(Expression.Equal(strExpr.Body, Expression.Constant(item.value)), Expression.Constant(item.ordinal), next)); var expr = Expression.Lambda <Func <T, int> >(body, strExpr.Parameters[0]); return(queryable.OrderBy(x => strExpr.Invoke(x) == null).ThenBy(expr)); } if (!propertyPropertyType.IsNullable()) { var parameter = Expression.Parameter(type); var propertyAccess = ExpressionUtils.CreateMemberAccess(parameter, propertyName); var orderByExp = Expression.Lambda(propertyAccess, parameter); var resultExp = Expression.Call(typeof(Queryable), "OrderBy", new[] { type, propertyPropertyType }, queryable.Expression, Expression.Quote(orderByExp)); return((IOrderedQueryable <T>)queryable.Provider.CreateQuery <T>(resultExp)); } else { var parameter = Expression.Parameter(type); var propertyAccess = ExpressionUtils.CreateMemberAccess(parameter, propertyName); var orderByExp = Expression.Lambda <Func <T, bool> >(Expression.Equal(propertyAccess, Expression.Constant(null, propertyPropertyType)), parameter); var orderBy = Expression.Call(typeof(Queryable), "OrderBy", new[] { type, orderByExp.Body.Type }, queryable.Expression, Expression.Quote(orderByExp)); var orderedQueryable = (IOrderedQueryable <T>)queryable.Provider.CreateQuery <T>(orderBy); var thenByExp = Expression.Lambda(propertyAccess, parameter); var thenBy = Expression.Call(typeof(Queryable), "ThenBy", new[] { type, propertyPropertyType }, orderedQueryable.Expression, Expression.Quote(thenByExp)); return((IOrderedQueryable <T>)orderedQueryable.Provider.CreateQuery <T>(thenBy)); } }
public static Expression <Func <T, bool> > FiltersToLambda <T>(List <string> conditions) { var type = typeof(T); var parameter = Expression.Parameter(type, "x"); var conditionKeyPairs = ToPropertyValuePairs(conditions, type); if (!conditionKeyPairs.Any()) { return(Expression.Lambda <Func <T, bool> >(Expression.Constant(true), parameter)); } Expression GetExpression(PropertyFilterItem item) { var property = item.Property; var value = item.Value; var operation = item.Operation; var expProp = ExpressionUtils.CreateMemberAccess(parameter, item.PropertyName); if (string.IsNullOrEmpty(value) && property.PropertyType.IsNullable()) { if (property.PropertyType == typeof(string)) { return(Expression.Call(typeof(string), nameof(string.IsNullOrEmpty), null, expProp)); } if (property.PropertyType.GetInterfaces().Any(x => x == typeof(IEnumerable)) && property.PropertyType.GetGenericArguments().FirstOrDefault() == typeof(ItemDto)) { var countMethod = typeof(Enumerable).GetMethods(BindingFlags.Static | BindingFlags.Public).ToList().FirstOrDefault(m => m.Name == "Count" && m.GetParameters().Count() == 1)?.MakeGenericMethod(typeof(ItemDto)); if (countMethod == null) { return(Expression.Constant(true)); } return(Expression.Equal(Expression.Call(null, countMethod, expProp), Expression.Constant(0))); } return(Expression.Equal(expProp, Expression.Constant(null, property.PropertyType))); } if (property.PropertyType == typeof(string)) { if (operation == OperationFilter.FullEqual) { return(Expression.Equal(expProp, Expression.Constant(value, typeof(string)))); } if (operation == OperationFilter.Equal) { return(Expression.Call(expProp, typeof(string).GetMethod(nameof(string.Contains), new[] { typeof(string) }), Expression.Constant(value, typeof(string)))); } } if (property.PropertyType == typeof(DateTime)) { if (!DateTimeOffset.TryParse(value, CultureInfo.InvariantCulture, DateTimeStyles.AssumeUniversal, out DateTimeOffset intValueOffset)) { return(Expression.Constant(true)); } var intValue = intValueOffset.DateTime; var dateDiff = typeof(SqlFunctions).GetMethod(nameof(SqlFunctions.DateDiff), new[] { typeof(string), typeof(DateTime?), typeof(DateTime?) }); var day = intValue == intValue.Date ? "DAY" : "MINUTE"; var callDateDiff = Expression.Call(null, dateDiff, Expression.Constant(day), Expression.Convert(expProp, typeof(DateTime?)), Expression.Convert(Expression.Constant(intValue), typeof(DateTime?))); return(Expression.Equal(callDateDiff, Expression.Convert(Expression.Constant(0), typeof(int?)))); } if (property.PropertyType == typeof(DateTime?)) { if (!DateTimeOffset.TryParse(value, CultureInfo.InvariantCulture, DateTimeStyles.AssumeUniversal, out DateTimeOffset intValueOffset)) { return(Expression.Constant(true)); } var intValue = intValueOffset.DateTime; var hasValue = Expression.Property(expProp, nameof(Nullable <DateTime> .HasValue)); var dateDiff = typeof(SqlFunctions).GetMethod(nameof(SqlFunctions.DateDiff), new[] { typeof(string), typeof(DateTime?), typeof(DateTime?) }); var day = intValue == intValue.Date ? "DAY" : "MINUTE"; var callDateDiff = Expression.Call(null, dateDiff, Expression.Constant(day), Expression.Convert(expProp, typeof(DateTime?)), Expression.Convert(Expression.Constant(intValue), typeof(DateTime?))); return(Expression.And(hasValue, Expression.Equal(callDateDiff, Expression.Convert(Expression.Constant(0), typeof(int?))))); } if (property.PropertyType == typeof(int)) { if (!value.TryConvertToType(out int intValue)) { return(Expression.Constant(true)); } return(Expression.Equal(expProp, Expression.Constant(intValue))); } if (property.PropertyType == typeof(int?)) { if (!value.TryConvertToType(out int intValue)) { return(Expression.Constant(true)); } var memberExpression = expProp; return(Expression.And(Expression.Property(memberExpression, nameof(Nullable <double> .HasValue)), Expression.Equal(Expression.Property(memberExpression, nameof(Nullable <double> .Value)), Expression.Constant(intValue)))); } if (property.PropertyType == typeof(double)) { if (!value.TryConvertToType(out double intValue)) { return(Expression.Constant(true)); } var methodInfo = typeof(Math).GetMethod(nameof(Math.Abs), new[] { typeof(double) }); var memberExpression = expProp; return(Expression.LessThanOrEqual(Expression.Call(null, methodInfo, Expression.Subtract(memberExpression, Expression.Constant(intValue))), Expression.Constant((double)0.1))); } if (property.PropertyType == typeof(double?)) { if (!value.TryConvertToType(out double intValue)) { return(Expression.Constant(true)); } var methodInfo = typeof(Math).GetMethod(nameof(Math.Abs), new[] { typeof(double) }); var memberExpression = expProp; return(Expression.And(Expression.Property(memberExpression, nameof(Nullable <double> .HasValue)), Expression.LessThanOrEqual(Expression.Call(null, methodInfo, Expression.Subtract(Expression.Property(memberExpression, nameof(Nullable <double> .Value)), Expression.Constant(intValue))), Expression.Constant((double)0.1)))); } if (property.PropertyType == typeof(decimal)) { if (!value.TryConvertToType(out decimal intValue)) { return(Expression.Constant(true)); } var methodInfo = typeof(Math).GetMethod(nameof(Math.Abs), new[] { typeof(decimal) }); var memberExpression = expProp; return(Expression.LessThanOrEqual(Expression.Call(null, methodInfo, Expression.Subtract(memberExpression, Expression.Constant(intValue))), Expression.Constant((decimal)0.1))); } if (property.PropertyType == typeof(decimal?)) { if (!value.TryConvertToType(out decimal intValue)) { return(Expression.Constant(true)); } var methodInfo = typeof(Math).GetMethod(nameof(Math.Abs), new[] { typeof(decimal) }); var memberExpression = expProp; return(Expression.And(Expression.Property(memberExpression, nameof(Nullable <decimal> .HasValue)), Expression.LessThanOrEqual(Expression.Call(null, methodInfo, Expression.Subtract(Expression.Property(memberExpression, nameof(Nullable <decimal> .Value)), Expression.Constant(intValue))), Expression.Constant((decimal)0.1)))); } if (property.PropertyType == typeof(float)) { if (!value.TryConvertToType(out float intValue)) { return(Expression.Constant(true)); } var methodInfo = typeof(Math).GetMethod(nameof(Math.Abs), new[] { typeof(float) }); var memberExpression = expProp; return(Expression.LessThanOrEqual(Expression.Call(null, methodInfo, Expression.Subtract(memberExpression, Expression.Constant(intValue))), Expression.Constant((float)0.1))); } if (property.PropertyType == typeof(float?)) { if (!value.TryConvertToType(out float intValue)) { return(Expression.Constant(true)); } var methodInfo = typeof(Math).GetMethod(nameof(Math.Abs), new[] { typeof(float) }); var memberExpression = expProp; return(Expression.And(Expression.Property(memberExpression, nameof(Nullable <float> .HasValue)), Expression.LessThanOrEqual(Expression.Call(null, methodInfo, Expression.Subtract(Expression.Property(memberExpression, nameof(Nullable <float> .Value)), Expression.Constant(intValue))), Expression.Constant((float)0.1)))); } if (property.PropertyType == typeof(bool)) { if (!value.TryConvertToType(out bool intValue)) { return(Expression.Constant(true)); } return(Expression.Equal(expProp, Expression.Constant(intValue))); } if (property.PropertyType == typeof(bool?)) { if (!value.TryConvertToType(out bool intValue)) { return(Expression.Constant(true)); } return(Expression.And(Expression.Property(expProp, nameof(Nullable <bool> .HasValue)), Expression.Equal(Expression.Property(expProp, nameof(Nullable <bool> .Value)), Expression.Constant(intValue)))); } if (property.PropertyType.GetInterfaces().Any(x => x == typeof(IEnumerable)) && property.PropertyType.GetGenericArguments().FirstOrDefault() == typeof(ItemDto)) { var parameterExpression = Expression.Parameter(typeof(ItemDto), "i"); var containsMethod = Expression.Call(Expression.Property(parameterExpression, nameof(ItemDto.Name)), typeof(string).GetMethod(nameof(string.Contains), new[] { typeof(string) }), Expression.Constant(value, typeof(string))); var anyExpr = Expression.Lambda <Func <ItemDto, bool> >(containsMethod, parameterExpression); var anyMethod = typeof(Enumerable).GetMethods(BindingFlags.Static | BindingFlags.Public).ToList().FirstOrDefault(m => m.Name == "Any" && m.GetParameters().Count() == 2)?.MakeGenericMethod(typeof(ItemDto)); if (anyMethod == null) { return(Expression.Constant(true)); } return(Expression.Call(null, anyMethod, expProp, anyExpr)); } if (property.PropertyType.IsGenericType && (property.PropertyType.GetGenericArguments().FirstOrDefault()?.IsEnum ?? false)) { if (value.TryConvertToType(property.PropertyType.GetGenericArguments().FirstOrDefault(), out var enumValue)) { return(Expression.And(Expression.Property(expProp, nameof(Nullable <bool> .HasValue)), Expression.Equal(Expression.Property(expProp, nameof(Nullable <bool> .Value)), Expression.Constant(enumValue)))); } return(Expression.Constant(true)); } if (property.PropertyType.IsEnum) { if (value.TryConvertToType(property.PropertyType, out var enumValue)) { return(Expression.Equal(expProp, Expression.Constant(enumValue))); } return(Expression.Constant(true)); } return(Expression.Constant(true)); } var expressions = conditionKeyPairs.Select(x => x.Select(GetExpression)); var result = expressions.Select(x => x.Aggregate(Expression.Or)).Aggregate(Expression.And); return(Expression.Lambda <Func <T, bool> >(result, parameter)); }