Followup to Repository searching code duplication
My goal currently is to make the current implementation of my repository's IQueryable filtering less duplicative. First, I looked into passing expressions around to target properties generically so that I can create reusable methods in a new extensions class I created:
internal static class IQueryableExtensions
{
internal static IQueryable<TModel> FilterByAtLeast<TModel, TPropertyType>(this IQueryable<TModel> query, TPropertyType value, Expression<Func<TModel, TPropertyType>> property)
where TPropertyType : IComparable
{
return query.Where(x => value == null || property.Compile().Invoke(x).CompareTo(value) >= 0);
}
internal static IQueryable<TModel> FilterByAtMost<TModel, TPropertyType>(this IQueryable<TModel> query, TPropertyType value, Expression<Func<TModel, TPropertyType>> property)
where TPropertyType : IComparable
{
return query.Where(x => value == null || property.Compile().Invoke(x).CompareTo(value) <= 0);
}
internal static IQueryable<TModel> FilterByContains<TModel>(this IQueryable<TModel> query, string value, Expression<Func<TModel, string>> property)
{
return query.Where(x => value == null || property.Compile().Invoke(x).Contains(value));
}
internal static IQueryable<TModel> FilterByExact<TModel, TPropertyType>(this IQueryable<TModel> query, TPropertyType value, Expression<Func<TModel, TPropertyType>> property)
where TPropertyType : IComparable
{
return query.Where(x => property.Compile().Invoke(x).Equals(value));
}
internal static IQueryable<TModel> FilterByGreaterThan<TModel, TPropertyType>(this IQueryable<TModel> query, TPropertyType value, Expression<Func<TModel, TPropertyType>> property)
where TPropertyType : IComparable
{
return query.Where(x => value == null || property.Compile().Invoke(x).CompareTo(value) > 0);
}
internal static IQueryable<TModel> FilterByGuids<TModel>(this IQueryable<TModel> query, Filter<TModel> filter)
where TModel : PocoBase
{
return query.Where(x => !filter.ItemGuids.Any() || filter.ItemGuids.Contains(x.Id));
}
internal static IQueryable<TModel> FilterByLessThan<TModel, TPropertyType>(this IQueryable<TModel> query, TPropertyType value, Expression<Func<TModel, TPropertyType>> property)
where TPropertyType : IComparable
{
return query.Where(x => value == null || property.Compile().Invoke(x).CompareTo(value) < 0);
}
internal static IQueryable<TModel> FilterByNumericComparator<TModel, TPropertyType>(this IQueryable<TModel> query, NumericSearchComparators comparator, TPropertyType value, Expression<Func<TModel, TPropertyType>> property)
where TPropertyType : IComparable
{
switch (comparator)
{
case NumericSearchComparators.GreaterThan:
return query.Where(x => property.Compile().Invoke(x).CompareTo(value) > 0);
case NumericSearchComparators.GreaterThanOrEqualTo:
return query.Where(x => property.Compile().Invoke(x).CompareTo(value) >= 0);
case NumericSearchComparators.EqualTo:
return query.Where(x => property.Compile().Invoke(x).CompareTo(value) == 0);
case NumericSearchComparators.LessThan:
return query.Where(x => property.Compile().Invoke(x).CompareTo(value) < 0);
case NumericSearchComparators.LessThanOrEqualTo:
return query.Where(x => property.Compile().Invoke(x).CompareTo(value) <= 0);
default:
return query;
}
}
//internal static IQueryable<TModel> FilterByAnyContaining<TModel, TPropertyType>(this IQueryable<TModel> query, TPropertyType value, Expression<Func<ICollection<TModel>, ICollection<TPropertyType>, TPropertyType>> property)
//{
// return query.Where(x => value == null || property.Compile().Invoke(x).Contains(value));
//}
}
The commented out method in that class is one that I'm currently stuck on. I would like to extract out code like this:
internal static IQueryable<Company> FilterCompanyQueryByAdmin(IQueryable<Company> query, ContactFilter filter)
{
query = query.Where(x => !string.IsNullOrWhiteSpace(filter.CellNumber) || x.Clients.Any(y => y.Admin.CellNumber.Contains(filter.CellNumber)));
query = query.Where(x => !filter.ItemGuids.Any() || x.Clients.Any(y => filter.ItemGuids.Contains(y.AdminContactId)));
return query;
}
internal static IQueryable<Product> FilterProductQuery(IQueryable<Product> query, DiscountFilter filter)
{
if (filter.Amount != null && filter.AmountComparator != null)
{
switch (filter.AmountComparator.Value)
{
case NumericSearchComparators.GreaterThan:
query = query.Where(x => x.Discounts.Any(y => y.Amount > filter.Amount));
break;
case NumericSearchComparators.GreaterThanOrEqualTo:
query = query.Where(x => x.Discounts.Any(y => y.Amount >= filter.Amount));
break;
case NumericSearchComparators.EqualTo:
query = query.Where(x => x.Discounts.Any(y => y.Amount == filter.Amount));
break;
case NumericSearchComparators.LessThan:
query = query.Where(x => x.Discounts.Any(y => y.Amount < filter.Amount));
break;
case NumericSearchComparators.LessThanOrEqualTo:
query = query.Where(x => x.Discounts.Any(y => y.Amount <= filter.Amount));
break;
default:
break;
}
}
if (filter.Threshold != null && filter.ThresholdComparator != null)
{
switch (filter.ThresholdComparator.Value)
{
case NumericSearchComparators.GreaterThan:
query = query.Where(x => x.Discounts.Any(y => y.Threshold > filter.Threshold.Value));
break;
case NumericSearchComparators.GreaterThanOrEqualTo:
query = query.Where(x => x.Discounts.Any(y => y.Threshold >= filter.Threshold.Value));
break;
case NumericSearchComparators.EqualTo:
query = query.Where(x => x.Discounts.Any(y => y.Threshold == filter.Threshold.Value));
break;
case NumericSearchComparators.LessThan:
query = query.Where(x => x.Discounts.Any(y => y.Threshold < filter.Threshold.Value));
break;
case NumericSearchComparators.LessThanOrEqualTo:
query = query.Where(x => x.Discounts.Any(y => y.Threshold <= filter.Threshold.Value));
break;
default:
break;
}
}
query = query.Where(x => filter.IsPercent == null || x.Discounts.Any(y => y.IsPercent == filter.IsPercent.Value));
return query;
}
...into additional extension methods that are as generic as possible.
The part where I'm struggling to wrap my head around is how to set up these Expressions
and/or Expression<Func<>>
s when I get to sending in a collection of a second model type and I want to target a property on that secondary model.
I'm thinking of the above examples in this sense - "I need a query which will return all Products whose collections of Discounts contains at least one match for this value on this property"
The full set of code I'm currently using in the Filterer
class is:
internal static class RepositoryQueryFilterer
{
internal static IQueryable<Account> FilterAccountQuery(IQueryable<Account> query, AccountFilter filter)
{
return !string.IsNullOrWhiteSpace(filter.Notes) ? query.FilterByContains(filter.Notes, x => x.Notes) : query;
}
internal static IQueryable<Client> FilterClientQuery(IQueryable<Client> query, ClientFilter filter)
{
return filter != null ? FilterClientQueryFilters(query, filter) : query;
}
internal static IQueryable<Client> FilterClientQueryByAccountSearch(IQueryable<Client> query, AccountFilter filter)
{
if (filter == null) return query;
query = query.FilterByContains(filter.Notes, x => x.Account.Notes);
query = query.Where(x => !filter.ItemGuids.Any() || filter.ItemGuids.Contains(x.Id));
return query;
}
internal static IQueryable<Client> FilterClientQueryByAdminSeach(IQueryable<Client> query, ContactFilter filter)
{
if (filter == null) return query;
query = query.FilterByContains(filter.CellNumber, x => x.Admin.CellNumber);
query = query.FilterByContains(filter.Email, x => x.Admin.Email);
query = query.FilterByContains(filter.OfficeNumber, x => x.Admin.OfficeNumber);
query = query.Where(x => !filter.ItemGuids.Any() || filter.ItemGuids.Contains(x.AdminContactId));
return query;
}
internal static IQueryable<Client> FilterClientQueryByPrimaryContactSeach(IQueryable<Client> query, ContactFilter filter)
{
if (filter == null) return query;
query = query.FilterByContains(filter.Email, x => x.PrimaryContact.Email);
query = query.FilterByContains(filter.CellNumber, x => x.PrimaryContact.CellNumber);
query = query.FilterByContains(filter.OfficeNumber, x => x.PrimaryContact.OfficeNumber);
query = query.Where(x => filter.ItemGuids != null && filter.ItemGuids.Any() || filter.ItemGuids.Contains(x.PrimaryContactId));
return query;
}
internal static IQueryable<Company> FilterCompanyQuery(IQueryable<Company> query, CompanyFilter filter)
{
if (filter == null) return query;
query = FilterCompanyQuery(query, filter.ClientFilter);
query = FilterCompanyQuery(query, filter.LocationFilter);
query = query.Where(x => !string.IsNullOrWhiteSpace(filter.Name) || x.Name.Contains(filter.Name));
query = query.FilterByGuids(filter);
return query;
}
internal static IQueryable<Company> FilterCompanyQuery(IQueryable<Company> query, ClientFilter filter)
{
if (filter == null) return query;
query = FilterCompanyQuery(query, filter.AccountFilter);
query = FilterCompanyQueryByAdmin(query, filter.AdminFilter);
query = FilterCompanyQueryByPrimaryContact(query, filter.PrimaryContactFilter);
query = query.Where(x => !filter.ItemGuids.Any() || x.Clients.Any(y => filter.ItemGuids.Contains(y.Id)));
return query;
}
internal static IQueryable<Company> FilterCompanyQuery(IQueryable<Company> query, AccountFilter filter)
{
if (filter == null) return query;
query = query.Where(x => !string.IsNullOrWhiteSpace(filter.Notes) || x.Clients.Any(y => y.Account.Notes.Contains(filter.Notes)));
query = query.Where(x => filter.ItemGuids != null && filter.ItemGuids.Any() || x.Clients.Any(y => filter.ItemGuids.Contains(y.AccountId)));
return query;
}
internal static IQueryable<Company> FilterCompanyQuery(IQueryable<Company> query, LocationFilter filter)
{
if (filter == null) return query;
query = query.Where(x => !string.IsNullOrWhiteSpace(filter.Description) || x.Locations.Any(y => y.Description.Contains(filter.Description)));
query = //query.FilterByAnyContaining(filter.Label, x => x.Locations.Any(y => y.Label))
query.Where(x => !string.IsNullOrWhiteSpace(filter.Label) || x.Locations.Any(y => y.Label.Contains(filter.Label)));
query = query.Where(x => !filter.ItemGuids.Any() || x.Locations.Any(y => filter.ItemGuids.Contains(y.Id)));
return query;
}
internal static IQueryable<Company> FilterCompanyQueryByAdmin(IQueryable<Company> query, ContactFilter filter)
{
if (filter == null) return query;
query = query.Where(x => !string.IsNullOrWhiteSpace(filter.CellNumber) || x.Clients.Any(y => y.Admin.CellNumber.Contains(filter.CellNumber)));
query = query.Where(x => !string.IsNullOrWhiteSpace(filter.OfficeNumber) || x.Clients.Any(y => y.Admin.OfficeNumber.Contains(filter.OfficeNumber)));
query = query.Where(x => !string.IsNullOrWhiteSpace(filter.Email) || x.Clients.Any(y => y.Admin.Email.Contains(filter.Email)));
query = query.Where(x => !string.IsNullOrWhiteSpace(filter.Name) || x.Clients.Any(y => y.Admin.Name.Contains(filter.Name)));
query = query.Where(x => !filter.ItemGuids.Any() || x.Clients.Any(y => filter.ItemGuids.Contains(y.AdminContactId)));
return query;
}
internal static IQueryable<Company> FilterCompanyQueryByPrimaryContact(IQueryable<Company> query, ContactFilter filter)
{
if (filter == null) return query;
query = query.Where(x => !string.IsNullOrWhiteSpace(filter.CellNumber) || x.Clients.Any(y => y.PrimaryContact.CellNumber.Contains(filter.CellNumber)));
query = query.Where(x => !string.IsNullOrWhiteSpace(filter.OfficeNumber) || x.Clients.Any(y => y.PrimaryContact.OfficeNumber.Contains(filter.OfficeNumber)));
query = query.Where(x => !string.IsNullOrWhiteSpace(filter.Email) || x.Clients.Any(y => y.PrimaryContact.Email.Contains(filter.Email)));
query = query.Where(x => !string.IsNullOrWhiteSpace(filter.Name) || x.Clients.Any(y => y.PrimaryContact.Name.Contains(filter.Name)));
query = query.Where(x => !filter.ItemGuids.Any() || x.Clients.Any(y => filter.ItemGuids.Contains(y.PrimaryContactId)));
return query;
}
internal static IQueryable<Contact> FilterContactQuery(IQueryable<Contact> query, ClientFilter filter)
{
if (filter == null) return query;
query = FilterContactQuery(query, filter.AccountFilter);
query = FilterContactQueryByAdmin(query, filter.AdminFilter);
query = FilterContactQueryByPrimaryContact(query, filter.PrimaryContactFilter);
query = query.Where(x => !filter.ItemGuids.Any() || filter.ItemGuids.Contains(x.ClientId));
return query;
}
internal static IQueryable<Contact> FilterContactQuery(IQueryable<Contact> query, CompanyFilter filter)
{
if (filter == null) return query;
query = FilterContactQuery(query, filter.LocationFilter);
query = FilterContactQuery(query, filter.ClientFilter);
query = FilterContactQuery(query, filter.LocationFilter);
query = query.FilterByContains(filter.Name, x => x.Name);
query = query.Where(x => !filter.ItemGuids.Any() || filter.ItemGuids.Contains(x.Id));
return query;
}
internal static IQueryable<Contact> FilterContactQuery(IQueryable<Contact> query, AccountFilter filter)
{
if (filter == null) return query;
query = query.FilterByContains(filter.Notes, x => x.Client.Account.Notes);
query = query.Where(x => !filter.ItemGuids.Any() || filter.ItemGuids.Contains(x.Client.AccountId));
return query;
}
internal static IQueryable<Contact> FilterContactQuery(IQueryable<Contact> query, LocationFilter filter)
{
if (filter == null) return query;
query = query.Where(x => !string.IsNullOrWhiteSpace(filter.Description) || x.Company.Locations.Any(y => y.Description.Contains(filter.Description)));
query = query.Where(x => !string.IsNullOrWhiteSpace(filter.Label) || x.Company.Locations.Any(y => y.Label.Contains(filter.Label)));
query = query.Where(x => !filter.ItemGuids.Any() || x.Company.Locations.Any(y => filter.ItemGuids.Contains(y.Id)));
return query;
}
internal static IQueryable<Contact> FilterContactQuery(IQueryable<Contact> query, ContactFilter filter)
{
if (filter == null) return query;
query = FilterContactQuery(query, filter.ClientFilter);
query = FilterContactQuery(query, filter.CompanyFilter);
query = query.FilterByContains(filter.CellNumber, x => x.CellNumber);
query = query.FilterByContains(filter.OfficeNumber, x => x.OfficeNumber);
query = query.FilterByContains(filter.Email, x => x.Email);
query = query.FilterByContains(filter.Name, x => x.Name);
query = query.FilterByGuids(filter);
return query;
}
internal static IQueryable<Contact> FilterContactQueryByAdmin(IQueryable<Contact> query, ContactFilter filter)
{
if (filter == null) return query;
query = query.FilterByContains(filter.CellNumber, x => x.Client.Admin.CellNumber);
query = query.FilterByContains(filter.OfficeNumber, x => x.Client.Admin.OfficeNumber);
query = query.FilterByContains(filter.Email, x => x.Client.Admin.Email);
query = query.FilterByContains(filter.Name, x => x.Client.Admin.Name);
query = query.Where(x => !filter.ItemGuids.Any() || filter.ItemGuids.Contains(x.Client.AdminContactId));
return query;
}
internal static IQueryable<Contact> FilterContactQueryByPrimaryContact(IQueryable<Contact> query, ContactFilter filter)
{
if (filter == null) return query;
query = query.FilterByContains(filter.CellNumber, x => x.Client.PrimaryContact.CellNumber);
query = query.FilterByContains(filter.OfficeNumber, x => x.Client.PrimaryContact.OfficeNumber);
query = query.FilterByContains(filter.Email, x => x.Client.PrimaryContact.Email);
query = query.FilterByContains(filter.Name, x => x.Client.PrimaryContact.Name);
query = query.Where(x => !filter.ItemGuids.Any() || filter.ItemGuids.Contains(x.Client.PrimaryContactId));
return query;
}
internal static IQueryable<Discount> FilterDiscountQuery(DiscountFilter filter, IQueryable<Discount> query)
{
if (filter == null) return query;
query = FilterDiscountQuery(filter.ProductFilter, query);
query = query.Where(x => filter.IsPercent == null || x.IsPercent == filter.IsPercent.Value);
query = filter.Amount != null && filter.AmountComparator != null
? query.FilterByNumericComparator(filter.AmountComparator.Value, filter.Amount.Value, x => x.Amount)
: query;
query = filter.Threshold != null && filter.ThresholdComparator != null
? query.FilterByNumericComparator(filter.ThresholdComparator.Value, filter.Threshold.Value, x => x.Threshold)
: query;
query = query.FilterByGuids(filter);
return query;
}
internal static IQueryable<Discount> FilterDiscountQuery(ProductFilter filter, IQueryable<Discount> query)
{
if (filter == null) return query;
query = query.FilterByContains(filter.Title, x => x.Product.Title);
query = filter.Price != null && filter.PriceComparator != null
? query.FilterByNumericComparator(filter.PriceComparator.Value, filter.Price.Value, x => x.Product.Price)
: query;
query = query.Where(x => !filter.ItemGuids.Any() || filter.ItemGuids.Contains(x.Id));
return query;
}
internal static IQueryable<CompanyLocation> FilterLocationQuery(IQueryable<CompanyLocation> query, LocationFilter filter)
{
if (filter == null) return query;
query = FilterLocationQuery(query, filter.CompanyFilter);
query = query.FilterByContains(filter.Description, x => x.Description);
query = query.FilterByContains(filter.Label, x => x.Label);
query = query.FilterByGuids(filter);
return query;
}
internal static IQueryable<CompanyLocation> FilterLocationQuery(IQueryable<CompanyLocation> query, CompanyFilter filter)
{
if (filter == null) return query;
query = query.FilterByContains(filter.Name, x => x.Company.Name);
query = query.Where(x => !filter.ItemGuids.Any() || filter.ItemGuids.Contains(x.Id));
return query;
}
internal static IQueryable<Order> FilterOrderQuery(IQueryable<Order> query, OrderFilter filter)
{
if (filter == null) return query;
query = FilterOrderQuery(query, filter.CompanyFilter);
query = FilterOrderQuery(query, filter.ProductFilter);
query = query.FilterByContains(filter.Notes, x => x.Notes);
query = query.Where(x => !filter.PositionsOfInterest.Any() || x.PositionsOfInterest.Any(y => filter.PositionsOfInterest.Contains(y)));
query = filter.OrderStatus != null && filter.OrderStatus.Any()
? query.Where(x => filter.OrderStatus.Contains(x.Status))
: query;
query = filter.Quantity != null && filter.QuantityComparator != null
? query.FilterByNumericComparator(filter.QuantityComparator.Value, filter.Quantity.Value, x => x.Quantity)
: query;
query = query.FilterByGuids(filter);
if (filter.Completed != null && filter.CompletedComparator != null)
{
query = query.Where(x => x.Status == OrderStatus.Completed);
switch (filter.CompletedComparator.Value)
{
case DateTimeSearchComparators.After:
query = query.Where(x => x.Completed > filter.Completed.Value);
break;
case DateTimeSearchComparators.Before:
query = query.Where(x => x.Completed < filter.Completed.Value);
break;
case DateTimeSearchComparators.DayOf:
query = query.Where(x => x.Completed.Value.DayOfYear == filter.Completed.Value.DayOfYear);
break;
case DateTimeSearchComparators.HourOf:
query = query.Where(x => x.Completed.Value.Hour == filter.Completed.Value.Hour);
break;
case DateTimeSearchComparators.Exactly:
query = query.Where(x => x.Completed.Value == filter.Completed.Value);
break;
default:
break;
}
}
return query;
}
internal static IQueryable<Order> FilterOrderQuery(IQueryable<Order> query, CompanyFilter filter)
{
if (filter == null) return query;
query = query.FilterByContains(filter.Name, x => x.Company.Name);
query = query.Where(x => !filter.ItemGuids.Any() || filter.ItemGuids.Contains(x.Id));
return query;
}
internal static IQueryable<Order> FilterOrderQuery(IQueryable<Order> query, ProductFilter filter)
{
if (filter == null) return query;
query = query.FilterByContains(filter.Title, x => x.Product.Title);
query = query.Where(x => !filter.ItemGuids.Any() || filter.ItemGuids.Contains(x.Id));
query = filter.Price != null && filter.PriceComparator != null
? query.FilterByNumericComparator(filter.PriceComparator.Value, filter.Price.Value, x => x.Product.Price)
: query;
return query;
}
internal static IQueryable<Product> FilterProductQuery(IQueryable<Product> query, ProductFilter filter)
{
if (filter == null) return query;
FilterProductQuery(query, filter.DiscountFilter);
query = query.FilterByContains(filter.Title, x => x.Title);
query = query.FilterByGuids(filter);
query = filter.Price != null && filter.PriceComparator != null
? query.FilterByNumericComparator(filter.PriceComparator.Value, filter.Price.Value, x => x.Price)
: query;
return query;
}
internal static IQueryable<Product> FilterProductQuery(IQueryable<Product> query, DiscountFilter filter)
{
if (filter == null) return query;
if (filter.Amount != null && filter.AmountComparator != null)
{
switch (filter.AmountComparator.Value)
{
case NumericSearchComparators.GreaterThan:
query = query.Where(x => x.Discounts.Any(y => y.Amount > filter.Amount));
break;
case NumericSearchComparators.GreaterThanOrEqualTo:
query = query.Where(x => x.Discounts.Any(y => y.Amount >= filter.Amount));
break;
case NumericSearchComparators.EqualTo:
query = query.Where(x => x.Discounts.Any(y => y.Amount == filter.Amount));
break;
case NumericSearchComparators.LessThan:
query = query.Where(x => x.Discounts.Any(y => y.Amount < filter.Amount));
break;
case NumericSearchComparators.LessThanOrEqualTo:
query = query.Where(x => x.Discounts.Any(y => y.Amount <= filter.Amount));
break;
default:
break;
}
}
if (filter.Threshold != null && filter.ThresholdComparator != null)
{
switch (filter.ThresholdComparator.Value)
{
case NumericSearchComparators.GreaterThan:
query = query.Where(x => x.Discounts.Any(y => y.Threshold > filter.Threshold.Value));
break;
case NumericSearchComparators.GreaterThanOrEqualTo:
query = query.Where(x => x.Discounts.Any(y => y.Threshold >= filter.Threshold.Value));
break;
case NumericSearchComparators.EqualTo:
query = query.Where(x => x.Discounts.Any(y => y.Threshold == filter.Threshold.Value));
break;
case NumericSearchComparators.LessThan:
query = query.Where(x => x.Discounts.Any(y => y.Threshold < filter.Threshold.Value));
break;
case NumericSearchComparators.LessThanOrEqualTo:
query = query.Where(x => x.Discounts.Any(y => y.Threshold <= filter.Threshold.Value));
break;
default:
break;
}
}
query = query.Where(x => filter.IsPercent == null || x.Discounts.Any(y => y.IsPercent == filter.IsPercent.Value));
return query;
}
private static IQueryable<Client> FilterClientQueryFilters(IQueryable<Client> query, ClientFilter filter)
{
query = FilterClientQueryByAccountSearch(query, filter.AccountFilter);
query = FilterClientQueryByAdminSeach(query, filter.AdminFilter);
query = FilterClientQueryByPrimaryContactSeach(query, filter.PrimaryContactFilter);
query = query.FilterByGuids(filter);
return query;
}
}