public List <Test> Lookup(TestSearchParamsDto searchParams, int skip, int take, string orderBy, string thenBy, IAttributeRepository attributeRepo)
{
using (var ctx = _factory.Create() as DbAgrotool)
{
//Predicate and conditions
var query = ApplyConditions(ctx.Tests, searchParams, attributeRepo);
//Includes
query = query.Include(t => t.Campaign)
.Include(t => t.Place)
.Include(t => t.Product);
//Group and Sorting
query = OrderByThenBy(query, orderBy, thenBy);
//Paging
query = query.Skip(skip)
.Take(take);
//Execute
var tests = query.ToList();
return(tests);
}
}
public int LookupCount(TestSearchParamsDto searchParams, IAttributeRepository attributeRepo)
{
using (var ctx = _factory.Create() as DbAgrotool)
{
//Predicate and conditions
var query = ApplyConditions(ctx.Tests, searchParams, attributeRepo);
return(query.Count());
}
}
private IQueryable <Test> ApplyConditions(IQueryable <Test> tests, TestSearchParamsDto searchParams, IAttributeRepository attributeRepo)
{
//attribute filters
var attributesPredicate = PredicateBuilder.True <Test>();
foreach (var attr in searchParams.AttributeFilters)
{
var mappingIds = attributeRepo.Single(a => a.Id == attr.Key).AttributeMappings.Where(m => m.MappedValue == attr.Value).Select(m => m.AttributeMappingId).ToList();
attributesPredicate = attributesPredicate.And(t => t.Product.AttributeMappings.Any(m => mappingIds.Contains(m.AttributeMappingId)));
}
//free text search
var term = searchParams.SearchTerm;
var searchTermPredicate = PredicateBuilder.False <Test>();
searchTermPredicate = searchTermPredicate.Or(t => t.Product.Category.Name.Contains(term));
searchTermPredicate = searchTermPredicate.Or(t => t.Product.Name.Contains(term));
searchTermPredicate = searchTermPredicate.Or(t => t.Source.Contains(term));
searchTermPredicate = searchTermPredicate.Or(t => t.Product.Company.Name.Contains(term));
searchTermPredicate = searchTermPredicate.Or(t => t.Place.Province.Contains(term));
searchTermPredicate = searchTermPredicate.Or(t => t.Place.Locality.Contains(term));
searchTermPredicate = searchTermPredicate.Or(t => t.Place.Department.Contains(term));
searchTermPredicate = searchTermPredicate.Or(t => t.Place.Header.Contains(term));
searchTermPredicate = searchTermPredicate.Or(t => t.Place.Region.Contains(term));
searchTermPredicate = searchTermPredicate.Or(t => t.Product.AttributeMappings.Select(a => a.Attribute.Name).Any(attName => attName.Contains(term)));
searchTermPredicate = searchTermPredicate.Or(t => t.Product.AttributeMappings.Select(a => a.Attribute.Tags).Any(attTag => attTag.Contains(term)));
//final query predicate
var query = tests.Where(test => test.Product.CategoryId == searchParams.CategoryId &&
(!searchParams.Companies.Any() || searchParams.Companies.Contains(test.Product.CompanyId)) &&
(!searchParams.Sources.Any() || searchParams.Sources.Contains(test.Source)) &&
(!searchParams.Provinces.Any() || searchParams.Provinces.Contains(test.Place.Province)) &&
(!searchParams.Localities.Any() || searchParams.Localities.Contains(test.Place.Locality)) &&
(!searchParams.Campaigns.Any() || (searchParams.Campaigns.Contains(test.CampaignId)) && test.Campaign.CategoryId == searchParams.CategoryId))
.Where(attributesPredicate)
.Where(searchTermPredicate);
return(query);
}