How to make a linq-query with multiple Contains()/Any() on possibly empty lists?-CodePudding

I am trying to make a query to a database view based on earlier user-choices. The choices are stored in lists of objects.

What I want to achieve is for a record to be added to the reportViewList if the stated value exists in one of the lists, but if for example the clientList is empty the query should overlook this statement and add all clients in the selected date-range. The user-choices are stored in temporary lists of objects.

The first condition is a time-range, this works fine. I understand why my current solution does not work, but I can not seem to wrap my head around how to fix it. This example works when both a client and a product is chosen. When the lists are empty the reportViewList is obviously also empty.

I have played with the idea of adding all the records in the date-range and then removing the ones that does not fit, but this would be a bad solution and not efficient.

Any help or feedback is much appreciated.

List<ReportView> reportViews = new List<ReportView>();

using(var dbc = new localContext())
{
    reportViewList = dbc.ReportViews.AsEnumerable()
    .Where(x => x.OrderDateTime >= from && x.OrderDateTime <= to)
    .Where(y => clientList.Any(x2 => x2.Id == y.ClientId)
    .Where(z => productList.Any(x3 => x3.Id == z.ProductId)).ToList();
}

CodePudding user response：

You should not call AsEnumerable() before you have added eeverything to your query. Calling AsEnumerable() here will cause your complete data to be loaded in memory and then be filtered in your application. Without AsEnumerable() and before calling calling ToList() (Better call ToListAsync()), you are working with an IQueryable<ReportView>. You can easily compose it and just call ToList() on your final query. Entity Framework will then examinate your IQueryable<ReportView> and generate an SQL expression out of it.

For your problem, you just need to check if the user has selected any filters and only add them to the query if they are present.

using var dbc = new localContext();

var reportViewQuery = dbc.ReportViews.AsQueryable(); // You could also write IQuryable<ReportView> reportViewQuery = dbc.ReportViews; but I prefer it this way as it is a little more save when you are refactoring.

// Assuming from and to are nullable and are null if the user has not selected them.
if (from.HasValue)
    reportViewQuery = reportViewQuery.Where(r => r.OrderDateTime >= from);

if (to.HasValue)
    reportViewQuery = reportViewQuery.Where(r => r.OrderDateTime <= to);

if(clientList is not null && clientList.Any())
{
    var clientIds = clientList.Select(c => c.Id).ToHashSet();
    reportViewQuery = reportViewQuery.Where(r => clientIds.Contains(y.ClientId));
}

if(productList is not null && productList.Any())
{
    var productIds = productList.Select(p => p.Id).ToHashSet();
    reportViewQuery = reportViewQuery.Where(r => productIds .Contains(r.ProductId));
}

var reportViews = await reportViewQuery.ToListAsync(); // You can also use ToList(), if you absolutely must, but I would not recommend it as it will block your current thread.