DAL Layer With Dynamic Query

Question

I've develop a n-tier e-commerce app with ASP.NET MVC 5 and entity framework. Mybe Of course i can change my DAL in the future. But i can figure out how should imlement my DAL layer for dynamic queries. Imagine that, users list products on the site, and they can be filter prods by product type or category or etc.. May be they are sort products by price or alphabetical order etc.. i mean that i need fully dynamic query. At this point i use ef and if i create my dynamic query with ef it doesn't appropriate for other dal technique (nhibername, ado.net etc..) What should i do?

//in my controller
var db = ApplicationDbContext.Create();
IQueryable<Product> query = from q in db.Products
    where
        q.ProdType == ProdType.StorPerde && q.UserId == LoggedUserId &&
        !selectedStorPerdeId.Contains(q.ProdId)
    select q;
List<Product> prods = _productService.GetAll(query);

//service layer
public List<Product> GetAll(IQueryable<Product> query)
{
    return _productDal.GetAll(query);
}

//dal
public List<Product> GetAll(IQueryable<Product> query)
{
    return query.ToList();
}

Answer 1

Well, first, you're not really even using a separate data access layer with this current approach. Your controller accesses the context directly getting an IQueryable, which you then pass into your service layer, merely calling .ToList() on it. That defeats the entire point of having a separate layer. Move your logic into your service layer entirely.

Next, the point of having a service layer is to abstract the data source and how you work with it. Therefore, your service layer methods should be created around specific information you need to retrieve. If for example, you need to get a specific user's products, you should have some method like GetProductsForUser(int userId).

Finally, for more complex scenarios where you need to be able to submit an arbitrary query, you can allow your service layer methods to accept an actual filter: Get(Expression<Func<TEntity, bool>> filter). A parameter of that type will allow you to pass the standard m => m.Foo == "Bar" style where clauses. However, this is less portable at this point, as the filter is more implementation-specific. Most ORMs would probably allow you to use this type of parameter intrinsically, but you might have to jump through a few hoops to convert it to something useful for something like a Web Api.

You might also want to consider offloading a task such as this to a real search appliance like Elasticsearch.