private static IQueryable <T> Filter <T>(IQueryable <T> queryable, Filter filter)
where T : class
{
var t = typeof(T);
if (filter != null && filter.Logic != null)
{
// Collect a flat list of all filters
var filters = filter.All()
.Select(s =>
{
s.Field = Char.ToUpperInvariant(s.Field[0]) + s.Field.Substring(1);
return(s);
}).ToList();
// Get all filter values as array (needed by the Where method of Dynamic Linq)
var values = filters.Select(f => f.Value).ToArray();
// Create a predicate expression e.g. Field1 = @0 And Field2 > @1
string predicate = filter.ToExpression(filters);
// Use the Where method of Dynamic Linq to filter the data
queryable = queryable.Where(predicate, values);
}
return(queryable);
}
private static IQueryable <T> Filter <T>(IQueryable <T> queryable, Filter filter, List <object> errors)
{
if ((filter != null) && (filter.Logic != null))
{
// Pretreatment some work
filter = PreliminaryWork(filter);
// Collect a flat list of all filters
var filters = filter.All().Distinct().ToList();
// Get all filter values as array (needed by the Where method of Dynamic Linq)
var values = filters.Select(f => f.Value).ToArray();
// Create a predicate expression e.g. Field1 = @0 And Field2 > @1
string predicate;
try
{
// Create a predicate expression e.g. Field1 = @0 And Field2 > @1
predicate = filter.ToExpression(typeof(T), filters);
}
catch (Exception ex)
{
errors.Add(ex.Message);
return(queryable);
}
// Use the Where method of Dynamic Linq to filter the data
queryable = queryable.Where(predicate, values);
}
return(queryable);
}
private static IQueryable <T> Filters <T>(IQueryable <T> queryable, Filter filter, List <object> errors)
{
if (filter?.Logic != null)
{
// Pretreatment some work
filter = PreliminaryWork(typeof(T), filter);
// Collect a flat list of all filters
var filters = filter.All();
/* Method.1 Use the combined expression string */
// Step.1 Create a predicate expression e.g. Field1 = @0 And Field2 > @1
string predicate;
try
{
predicate = filter.ToExpression(typeof(T), filters);
}
catch (Exception ex)
{
errors.Add(ex.Message);
return(queryable);
}
// Step.2 Get all filter values as array (needed by the Where method of Dynamic Linq)
var values = filters.Select(f => f.Value).ToArray();
// Step.3 Use the Where method of Dynamic Linq to filter the data
queryable = queryable.Where(predicate, values);
/* Method.2 Use the combined lambda expression */
// Step.1 Create a parameter "p"
//var parameter = Expression.Parameter(typeof(T), "p");
// Step.2 Make up expression e.g. (p.Number >= 3) AndAlso (p.Company.Name.Contains("M"))
//Expression expression;
//try
//{
// expression = filter.ToLambdaExpression<T>(parameter, filters);
//}
//catch(Exception ex)
//{
// errors.Add(ex.Message);
// return queryable;
//}
// Step.3 The result is e.g. p => (p.Number >= 3) AndAlso (p.Company.Name.Contains("M"))
//var predicateExpression = Expression.Lambda<Func<T, bool>>(expression, parameter);
//queryable = queryable.Where(predicateExpression);
}
return(queryable);
}
private static IQueryable <T> Filter <T>(IQueryable <T> queryable, Filter filter)
{
if (filter != null && filter.Logic != null)
{
// Collect a flat list of all filters
var filters = filter.All();
// Get all filter values as array (needed by the Where method of Dynamic Linq)
var values = filters.Select(f => f.value).ToArray();
// Create a predicate expression e.g. Field1 = @0 And Field2 > @1
string predicate = filter.ToExpression(filters);
// Use the Where method of Dynamic Linq to filter the data
queryable = queryable.Where(predicate, values);
}
return(queryable);
}
private static IQueryable <T> Filter <T>(IQueryable <T> queryable, Filter filter)
{
if ((filter != null) && (filter.Logic != null))
{
// Collect a flat list of all filters
var filters = filter.All();
// Get all filter values as array (needed by the Where method of Dynamic Linq)
var values = filters.Select(f => f.Value is string?f.Value.ToString().ToLower() : f.Value).ToArray();
////Add toLower() for all filter Fields with type of string in the values
for (var i = 0; i < values.Length; i++)
{
if (values[i] is string)
{
filters[i].Field = string.Format("{0}.ToString().ToLower()", filters[i].Field);
}
// when we have a decimal value it gets converted to double and the query will break
if (values[i] is double)
{
values[i] = Convert.ToDecimal(values[i]);
}
if (values[i] is DateTime)
{
var dateTimeFilterValue = (DateTime)values[i];
values[i] = new DateTime(dateTimeFilterValue.Year, dateTimeFilterValue.Month,
dateTimeFilterValue.Day, 0, 0, 0);
}
}
var valuesList = values.ToList();
//Remove duplicate filters
//NOTE: we loop, and don't use .distinct for a reason!
//There is a miniscule chance different columns will filter by the same value, in which case using distinct will remove too many filters
for (int i = filters.Count - 1; i >= 0; i--)
{
var previousFilter = filters.ElementAtOrDefault(i - 1);
if (previousFilter != null && filters[i].Equals(previousFilter))
{
filters.RemoveAt(i);
valuesList.RemoveAt(i);
}
}
var filtersList = filters.ToList();
for (int i = 0; i < filters.Count; i++)
{
if (filters[i].Value is DateTime && filters[i].Operator == "eq")
{
var filterToEdit = filtersList[i];
//Copy the date from the filter
var baseDate = ((DateTime)filters[i].Value).Date;
//Instead of comparing for exact equality, we compare as greater than the start of the day...
filterToEdit.Value = new DateTime(baseDate.Year, baseDate.Month, baseDate.Day, 0, 0, 0);
filterToEdit.Operator = "gte";
valuesList[i] = filterToEdit.Value;
//...and less than the end of that same day (we're making an additional filter here)
var newFilter = new Filter()
{
Value = new DateTime(baseDate.Year, baseDate.Month, baseDate.Day, 23, 59, 59),
Field = filters[i].Field,
Filters = filters[i].Filters,
Operator = "lte",
Logic = "and"
};
//Add that additional filter to the list of filters
filtersList.Add(newFilter);
valuesList.Add(newFilter.Value);
}
}
values = valuesList.ToArray();
filters = filtersList;
//Set the filters, since we may have editted them
filter.Filters = filtersList;
// Create a predicate expression e.g. Field1 = @0 And Field2 > @1
var predicate = filter.ToExpression(filters);
// Use the Where method of Dynamic Linq to filter the data
queryable = queryable.Where(predicate, values);
}
return(queryable);
}