Repository searching code duplication

Question

A followup question to this: IQueryable Extensions working on expression for collection property

I am working on a project for a family member which involves the use of a database and a repository, and I have set up a fairly complex (possibly too complex, but I don't have a ton to go off of so I'm trying to make it very flexible for rapid expansion) system to allow for searching for items through the repository via the use of various SearchParameters objects, based off of the following base class to allow for pagination and direct item finding via GUID:

Notes:

Using Entity Framework Code First
EFCF model classes make use of navigation properties

public abstract class SearchParametersBase <TModel> : SearchBase
{
    public List<Guid> ItemGuids { get; set; }
    protected SearchParametersBase(int page, int size, params Guid[] itemGuids) : base(page, size)
    {
        if (itemGuids == null || !itemGuids.Any())
            ItemGuids = null;
        else ItemGuids = itemGuids.ToList();
    }
}

The biggest problem I see currently is that because I have search parameter classes such as these:

public class CompanySearchParameters : SearchParametersBase<Company>
{
    public string BillingAddressSearchParameter { get; set; }

    public ClientSearchParameters ClientSearchParameters { get; set; }

    public LocationSearchParameters LocationSearchParameters { get; set; }

    public string NameSearchParameter { get; set; }

    public CompanySearchParameters(int page, int size, params Guid[] itemGuids) : base(page, size, itemGuids)
    { }
}
public class LocationSearchParameters : SearchParametersBase<CompanyLocation>
{
    public string AddressSearchParameter { get; set; }

    public CompanySearchParameters CompanySearchParameters { get; set; }

    public string DescriptionSearchParameter { get; set; }

    public string LabelSearchParameter { get; set; }

    public LocationSearchParameters(int page, int size, params Guid[] itemGuids) : base(page, size, itemGuids)
    { }
}

I ended up with a helper class that has a fair amount of duplicated code based on a different type of IQueryable<T> for a model that has navigation properties:

internal static class RepositoryQueryFilterer
{
    internal static IQueryable<Account> FilterAccountQuery(IQueryable<Account> query, AccountSearchParameters searchParameters)
    {
        if (searchParameters == null)
            return query;

        if (!string.IsNullOrWhiteSpace(searchParameters.NotesSearchParameter))
            query = query.Where(x => x.Notes.Contains(searchParameters.NotesSearchParameter));

        query = FilterGuids(query, searchParameters);

        return query;
    }

    internal static IQueryable<Client> FilterClientQuery(IQueryable<Client> query, ClientSearchParameters searchParameters)
    {
        if (searchParameters == null)
            return query;

        query = FilterClientQueryByAccountSearch(query, searchParameters.AccountSearchParameters);

        query = FilterGuids(query, searchParameters);

        return query;
    }

    internal static IQueryable<Client> FilterClientQueryByAccountSearch(IQueryable<Client> query, AccountSearchParameters searchParameters)
    {
        if (searchParameters == null)
            return query;

        if (!string.IsNullOrWhiteSpace(searchParameters.NotesSearchParameter))
            query = query.Where(x => x.Account.Notes.Contains(searchParameters.NotesSearchParameter));

        if (searchParameters.ItemGuids != null && searchParameters.ItemGuids.Any())
            query = query.Where(x => searchParameters.ItemGuids.Contains(x.Id));

        return query;
    }

    internal static IQueryable<Company> FilterCompanyQuery(IQueryable<Company> query, CompanySearchParameters searchParameters)
    {
        if (searchParameters == null)
            return query;

        query = FilterCompanyQuery(query, searchParameters.ClientSearchParameters);
        query = FilterCompanyQuery(query, searchParameters.LocationSearchParameters);

        if (!string.IsNullOrWhiteSpace(searchParameters.NameSearchParameter))
            query = query.Where(x => x.Name.Contains(searchParameters.NameSearchParameter));

        query = FilterGuids(query, searchParameters);

        return query;
    }

    internal static IQueryable<Company> FilterCompanyQuery(IQueryable<Company> query, ClientSearchParameters searchParameters)
    {
        if (searchParameters == null) return query;

        query = FilterCompanyQuery(query, searchParameters.AccountSearchParameters);

        if (searchParameters.ItemGuids != null && searchParameters.ItemGuids.Any())
            query = query.Where(x => x.Clients.Any(y => searchParameters.ItemGuids.Contains(y.Id)));

        return query;
    }

    internal static IQueryable<Company> FilterCompanyQuery(IQueryable<Company> query, AccountSearchParameters searchParameters)
    {
        if (searchParameters == null) return query;

        if (!string.IsNullOrWhiteSpace(searchParameters.NotesSearchParameter))
            query = query.Where(x => x.Clients.Any(y => y.Account.Notes.Contains(searchParameters.NotesSearchParameter)));

        if (searchParameters.ItemGuids != null && searchParameters.ItemGuids.Any())
            query = query.Where(x => x.Clients.Any(y => searchParameters.ItemGuids.Contains(y.AccountId)));

        return query;
    }

    internal static IQueryable<Company> FilterCompanyQuery(IQueryable<Company> query, LocationSearchParameters searchParameters)
    {
        if (searchParameters == null) return query;

        if (!string.IsNullOrWhiteSpace(searchParameters.DescriptionSearchParameter))
            query = query.Where(x => x.Locations.Any(y => y.Description.Contains(searchParameters.DescriptionSearchParameter)));

        if (!string.IsNullOrWhiteSpace(searchParameters.LabelSearchParameter))
            query = query.Where(x => x.Locations.Any(y => y.Label.Contains(searchParameters.LabelSearchParameter)));

        if (searchParameters.ItemGuids != null && searchParameters.ItemGuids.Any())
            query = query.Where(x => x.Locations.Any(y => searchParameters.ItemGuids.Contains(y.Id)));

        return query;
    }

    internal static IQueryable<Contact> FilterContactQuery(IQueryable<Contact> query, ClientSearchParameters searchParameters)
    {
        if (searchParameters == null) return query;

        query = FilterContactQuery(query, searchParameters.AccountSearchParameters);

        if (searchParameters.ItemGuids != null && searchParameters.ItemGuids.Any())
            query = query.Where(x => searchParameters.ItemGuids.Contains(x.ClientId));

        return query;
    }

    internal static IQueryable<Contact> FilterContactQuery(IQueryable<Contact> query, CompanySearchParameters searchParameters)
    {
        if (searchParameters == null) return query;

        query = FilterContactQuery(query, searchParameters.LocationSearchParameters);
        query = FilterContactQuery(query, searchParameters.ClientSearchParameters);
        query = FilterContactQuery(query, searchParameters.LocationSearchParameters);

        if (!string.IsNullOrWhiteSpace(searchParameters.NameSearchParameter))
            query = query.Where(x => x.Name.Contains(searchParameters.NameSearchParameter));

        if (searchParameters.ItemGuids != null && searchParameters.ItemGuids.Any())
            query = query.Where(x => searchParameters.ItemGuids.Contains(x.Id));

        return query;
    }

    internal static IQueryable<Contact> FilterContactQuery(IQueryable<Contact> query, AccountSearchParameters searchParameters)
    {
        if (searchParameters == null) return query;

        if (!string.IsNullOrWhiteSpace(searchParameters.NotesSearchParameter))
            query = query.Where(x => x.Client.Account.Notes.Contains(searchParameters.NotesSearchParameter));

        if (searchParameters.ItemGuids != null && searchParameters.ItemGuids.Any())
            query = query.Where(x => searchParameters.ItemGuids.Contains(x.Client.AccountId));

        return query;
    }

    internal static IQueryable<Contact> FilterContactQuery(IQueryable<Contact> query, LocationSearchParameters searchParameters)
    {
        if (searchParameters == null) return query;

        if (!string.IsNullOrWhiteSpace(searchParameters.DescriptionSearchParameter))
            query = query.Where(x => x.Company.Locations.Any(y => y.Description.Contains(searchParameters.DescriptionSearchParameter)));

        if (!string.IsNullOrWhiteSpace(searchParameters.LabelSearchParameter))
            query = query.Where(x => x.Company.Locations.Any(y => y.Label.Contains(searchParameters.LabelSearchParameter)));

        if (searchParameters.ItemGuids != null && searchParameters.ItemGuids.Any())
            query = query.Where(x => x.Company.Locations.Any(y => searchParameters.ItemGuids.Contains(y.Id)));

        return query;
    }

    internal static IQueryable<Contact> FilterContactQuery(IQueryable<Contact> query, ContactSearchParameters searchParameters)
    {
        if (searchParameters == null)
            return query;

        query = FilterContactQuery(query, searchParameters.ClientSearchParameters);
        query = FilterContactQuery(query, searchParameters.CompanySearchParameters);

        if (!string.IsNullOrWhiteSpace(searchParameters.CellNumberSearchParameter))
            query = query.Where(x => x.CellNumber.Contains(searchParameters.CellNumberSearchParameter));

        if (!string.IsNullOrWhiteSpace(searchParameters.OfficeNumberSearchParameter))
            query = query.Where(x => x.OfficeNumber.Contains(searchParameters.OfficeNumberSearchParameter));

        if (!string.IsNullOrWhiteSpace(searchParameters.EmailSearchParameter))
            query = query.Where(x => x.Email.Contains(searchParameters.EmailSearchParameter));

        if (!string.IsNullOrWhiteSpace(searchParameters.NameSearchParameter))
            query = query.Where(x => x.Name.Contains(searchParameters.NameSearchParameter));

        query = FilterGuids(query, searchParameters);

        return query;
    }

    internal static IQueryable<Discount> FilterDiscountQuery(DiscountSearchParameters searchParameters, IQueryable<Discount> query)
    {
        if (searchParameters == null)
            return query;

        query = FilterDiscountQuery(searchParameters.ProductSearchParameters, query);

        if (searchParameters.IsPercentSearchParameter != null)
            query = query.Where(x => x.IsPercent == searchParameters.IsPercentSearchParameter.Value);

        query = FilterGuids(query, searchParameters);

        return query;
    }

    internal static IQueryable<Discount> FilterDiscountQuery(ProductSearchParameters searchParameters, IQueryable<Discount> query)
    {
        if (searchParameters == null) return query;

        if (!string.IsNullOrWhiteSpace(searchParameters.TitleSearchParameter))
            query = query.Where(x => x.Product.Title.Contains(searchParameters.TitleSearchParameter));

        if (searchParameters.ItemGuids != null && searchParameters.ItemGuids.Any())
            query = query.Where(x => searchParameters.ItemGuids.Contains(x.Id));

        return query;
    }

    internal static IQueryable<CompanyLocation> FilterLocationQuery(IQueryable<CompanyLocation> query, LocationSearchParameters searchParameters)
    {
        if (searchParameters == null)
            return query;

        query = FilterLocationQuery(query, searchParameters.CompanySearchParameters);

        if (!string.IsNullOrWhiteSpace(searchParameters.DescriptionSearchParameter))
            query = query.Where(x => x.Description.Contains(searchParameters.DescriptionSearchParameter));

        if (!string.IsNullOrWhiteSpace(searchParameters.LabelSearchParameter))
            query = query.Where(x => x.Label.Contains(searchParameters.LabelSearchParameter));

        query = FilterGuids(query, searchParameters);

        return query;
    }

    internal static IQueryable<CompanyLocation> FilterLocationQuery(IQueryable<CompanyLocation> query, CompanySearchParameters searchParameters)
    {
        if (searchParameters == null) return query;

        if (!string.IsNullOrWhiteSpace(searchParameters.NameSearchParameter))
            query = query.Where(x => x.Company.Name.Contains(searchParameters.NameSearchParameter));

        if (searchParameters.ItemGuids != null && searchParameters.ItemGuids.Any())
            query = query.Where(x => searchParameters.ItemGuids.Contains(x.Id));

        return query;
    }

    internal static IQueryable<Order> FilterOrderQuery(IQueryable<Order> query, OrderSearchParameters searchParameters)
    {
        if (searchParameters == null)
            return query;

        query = FilterOrderQuery(query, searchParameters.CompanySearchParameters);
        query = FilterOrderQuery(query, searchParameters.ProductSearchParameters);

        if (!string.IsNullOrWhiteSpace(searchParameters.NotesSearchParameter))
            query = query.Where(x => x.Notes.Contains(searchParameters.NotesSearchParameter));

        if (searchParameters.PositionsOfInterestSearchParameter != null && searchParameters.PositionsOfInterestSearchParameter.Any())
            query = query.Where(x => x.PositionsOfInterest.Any(y => searchParameters.PositionsOfInterestSearchParameter.Contains(y)));

        if (searchParameters.OrderStatusSearchParameter != null && searchParameters.OrderStatusSearchParameter.Any())
            query = query.Where(x => searchParameters.OrderStatusSearchParameter.Contains(x.Status));

        query = FilterGuids(query, searchParameters);

        return query;
    }

    internal static IQueryable<Order> FilterOrderQuery(IQueryable<Order> query, CompanySearchParameters searchParameters)
    {
        if (searchParameters == null) return query;

        if (!string.IsNullOrWhiteSpace(searchParameters.NameSearchParameter))
            query = query.Where(x => x.Company.Name.Contains(searchParameters.NameSearchParameter));

        if (searchParameters.ItemGuids != null && searchParameters.ItemGuids.Any())
            query = query.Where(x => searchParameters.ItemGuids.Contains(x.Id));

        return query;
    }

    internal static IQueryable<Order> FilterOrderQuery(IQueryable<Order> query, ProductSearchParameters searchParameters)
    {
        if (searchParameters == null) return query;

        if (!string.IsNullOrWhiteSpace(searchParameters.TitleSearchParameter))
            query = query.Where(x => x.Product.Title.Contains(searchParameters.TitleSearchParameter));

        if (searchParameters.ItemGuids != null && searchParameters.ItemGuids.Any())
            query = query.Where(x => searchParameters.ItemGuids.Contains(x.Id));

        return query;
    }

    internal static IQueryable<Product> FilterProductQuery(IQueryable<Product> query, ProductSearchParameters searchParameters)
    {
        if (searchParameters == null)
            return query;

        FilterProductQuery(query, searchParameters.DiscountSearchParameters);

        if (!string.IsNullOrWhiteSpace(searchParameters.TitleSearchParameter))
            query = query.Where(x => x.Title.Contains(searchParameters.TitleSearchParameter));

        query = FilterGuids(query, searchParameters);

        return query;
    }

    internal static IQueryable<Product> FilterProductQuery(IQueryable<Product> query, DiscountSearchParameters searchParameters)
    {
        if (searchParameters == null) return query;

        if (searchParameters.IsPercentSearchParameter != null)
            query = query.Where(x => x.Discounts.Any(y => y.IsPercent == searchParameters.IsPercentSearchParameter.Value));

        if (searchParameters.ItemGuids != null && searchParameters.ItemGuids.Any())
            query = query.Where(x => searchParameters.ItemGuids.Contains(x.Id));

        return query;
    }

    private static IQueryable<TModel> FilterGuids<TModel>(IQueryable<TModel> query, SearchParametersBase<TModel> searchParameters)
                                                                                                                                                                                                                                where TModel : PocoBase
    {
        if (searchParameters.ItemGuids != null && searchParameters.ItemGuids.Any())
            query = query.Where(x => searchParameters.ItemGuids.Contains(x.Id));

        return query;
    }
}

The reason that, so far, I haven't been able to combine those methods that look, and in fact are, almost identical, is that they are operating on different objects with the related navigation properties.

Is there a way of making these methods more single-minded and reusable?

Could you provide some examples how you use those filters? This seems to be really over engineered. I wrote a short review but without knowing how to use it it's hard to suggest better improvements. — t3chb0t, Nov 9, 2016 at 13:09

t3chb0t · Accepted Answer · 2016-11-09 13:07:41Z

 public List<Guid> ItemGuids { get; set; }

Start by initializing this to an empty collection. You'll save a lot of null checks.

Consider this old method:

private static IQueryable<TModel> FilterGuids<TModel>(IQueryable<TModel> query, SearchParametersBase<TModel> searchParameters) where TModel : PocoBase
{
    if (searchParameters.ItemGuids != null && searchParameters.ItemGuids.Any())
        query = query.Where(x => searchParameters.ItemGuids.Contains(x.Id));

    return query;
}

without all the null checks:

private static IQueryable<TModel> FilterGuids<TModel>(IQueryable<TModel> query, SearchParametersBase<TModel> searchParameters) where TModel : PocoBase
{
    return query.Where(x => !searchParameters.ItemGuids.Any() || searchParameters.ItemGuids.Contains(x.Id));
}

Yes, a one-liner.

   if (!string.IsNullOrWhiteSpace(searchParameters.NotesSearchParameter))
        query = query.Where(x => x.Notes.Contains(searchParameters.NotesSearchParameter));

You can simplify a few things by using the ?: ternary operator and get rid of the if's:

return
    searchParameters != null && !string.IsNullOrWhiteSpace(searchParameters.NotesSearchParameter)
    ? query.Where(x =>  x.Notes.Contains(searchParameters.NotesSearchParameter))
    : query;

Turn this class RepositoryQueryFilterer into a collection of extensions. Name each overload just FilterBy:

internal static class RepositoryQueryFilters
{
    internal static IQueryable<Account> FilterBy(this IQueryable<Account> query, AccountSearchParameters searchParameters)
    {
    return
        searchParameters != null && !string.IsNullOrWhiteSpace(searchParameters.NotesSearchParameter)
        ? query.Where(x =>  x.Notes.Contains(searchParameters.NotesSearchParameter))
        : query;
    }

    private static IQueryable<TModel> FilterBy<TModel>(this IQueryable<TModel> query, SearchParametersBase<TModel> searchParameters) where TModel : PocoBase
    {
        return searchParameters.ItemGuids.Any() ? query.Where(x => searchParameters.ItemGuids.Contains(x.Id)) : query;
    }

    // ...
}

Ok, it's shorter now but still, it's a lot for a filter that just uses a single property.

You shouldn't use big objects as parameters if you only need a single value. Consider this:

internal static class RepositoryQueryFilters
{
    internal static IQueryable<Account> FilterBy(this IQueryable<Account> query, string notesSearchParameter)
    {
        return
            !string.IsNullOrWhiteSpace(notesSearchParameter)
            ? query.Where(x => x.Notes.Contains(notesSearchParameter))
            : query;
    }

    private static IQueryable<TModel> FilterBy<TModel>(this IQueryable<TModel> query, IReadOnlyList<Guid> guids) where TModel : PocoBase
    {
        return guids.Any() ? query.Where(x => guids.Contains(x.Id)) : query;
    }
}

Now each extension uses only what it really requries. It's much easier to test because you don't have to create complex objects.

Try to keep things as simple as possible.

Anyways, I think creating a few queries that can perform the few different searches you requires would be easier to maintain then a all those filters an parameter objects.

I like this idea in theory, but in practice just attempting to convert one of my larger methods down I run into the problem of method signatures almost immediately as soon as I define one string search parameter for a given IQueryable<T>. Is there a way of passing a reference to the specific parameter? So that such an implementation would have a method per query type for strings, ints, etc? — Chris Thompson, Nov 9, 2016 at 22:44
Once I got home tonight I managed to get the code up to a point I'm happier with, but I'm going to continue this iteration process with a follow-up question: — Chris Thompson, Nov 10, 2016 at 3:57
Followup question link: codereview.stackexchange.com/questions/146625 — Chris Thompson, Nov 10, 2016 at 4:25

BCdotWEB · Accepted Answer · 2016-11-09 13:31:20Z

I'm not a fan of classes that are called a plural but aren't actually a collection of items: CompanySearchParameters, LocationSearchParameters, etc. This gets especially bad when they're properties:

public LocationSearchParameters LocationSearchParameters { get; set; }

To me, LocationSearchParameters suggests a collection, but instead it is a class.

Make it easy on yourself and call it... a Filter.

And why are all the properties in your XXXSearchParameters classes suffixed with SearchParameter(s)? This adds so much noise to your code -- e.g. companySearchParameters.BillingAddressSearchParameter -- whereas filter.BillingAddress is perfectly understandable.

Thanks for the input. Honestly the naming of the actual properties of each class was me just accepting the Resharper suggested names, but I like your method far better. — Chris Thompson, Nov 9, 2016 at 15:52

Stack Exchange Network

Repository searching code duplication

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged
c#
entity-framework
database
search
repository
or ask your own question.

Linked

Hot Network Questions

Repository searching code duplication

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged c#entity-frameworkdatabasesearchrepository or ask your own question.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
c#
entity-framework
database
search
repository
or ask your own question.