CODEWITHSUNDEEP
c#oracle-databaseentity-framework
Baldor
Baldor

Reputation: 33

Entity Framework - Use condition statements in query

Good Day Guys,

I have three lists (ICollection of strings). My intention is to fetch results from the db based on the values stored in these lists using linq to entities. Below is my snippet

int entityCriteriaCount = entityCriteria == null ? 0 : entityCriteria.Count();
int operationCriteriaCount = operationCriteria == null ? 0 : operationCriteria.Count();
int roleCriteriaCount = roleCriteria == null ? 0 : roleCriteria.Count();
// Where entityCriteria,operationCriteria and roleCriteria are the above mentioned lists

Data Query:

var auditItems = db.AuditTrails.Where(a => (entityCriteriaCount > 0 ? reportCriteria.EntityTypes.Contains(a.EntityType) : a.EntityType.Contains(""))
&& (roleCriteriaCount > 0 ? reportCriteria.Roles.Contains(a.UserRole) : a.UserRole.Contains(""))
&& (operationCriteriaCount > 0 ? reportCriteria.Operations.Contains(a.UserAction) : a.UserAction.Contains(""))
&& EntityFunctions.TruncateTime(a.TimeStamp) >= startDate
&& EntityFunctions.TruncateTime(a.TimeStamp) <= endDate).OrderByDescending(a => a.TimeStamp).ToList();

The goal of the above query is to check if the list is empty before attempting to fetch records based on its content. The above works perfectly if all the lists have entries. It however fails if any or all of them are empty. How can I make this work i.e Query the database using the lists if there are entries and fetch all entries for the criteria if the list is empty. Any help will be appreciated. Thanks guys

Upvotes: 0

Views: 63

Answers (1)

musefan
musefan

Reputation: 48415

You don't have to do the whole query in one go, break it down and only filter when you need to:

var auditItems = db.AuditTrails.Where(EntityFunctions.TruncateTime(a.TimeStamp) >= startDate && EntityFunctions.TruncateTime(a.TimeStamp) <= endDate);

if(entityCriteriaCount > 0)
    auditItems = auditItems.Where(a => reportCriteria.EntityTypes.Contains(a.EntityType));
if(roleCriteriaCount > 0)
    auditItems = auditItems.Where(a => reportCriteria.Roles.Contains(a.UserRole));
if(operationCriteriaCount > 0)
    auditItems = auditItems.Where(a => reportCriteria.Operations.Contains(a.UserAction));

var results = auditItems.OrderByDescending(a => a.TimeStamp).ToList();

Upvotes: 1

Related Questions