.Net Core 6.0 : Entity Framework - Either Or / Neither Nor / Both in Where clause

Question

I am building a .Net Core 6.0 application that uses Entity Framework.

I have situation where I need to apply the filter(Where clause) based on two properties (eg: Guid? skillType, string skillName). These properties may or may not have values, means that

1. Neither of the property will have value
2. Either of the property will have value
3. Both properties will have value

How do I construct the Where clause? I tried something like this

        var paginationListResult = await this.DbContext.Skills
            .Where(st => skillType == null || st.SkillTypeId == skillType)
            .Where(st => string.IsNullOrEmpty(skillName) || st.Name == skillName)
             .Include(sk => sk.SkillType)
             .Include(sk => sk.Regroupment)
             .Include(sk => sk.SkillRoleRequirements)
            .OrderBy(s => s.Name)
            .ToPaginateListAsync(pageIndex, itemsPerPage, default(CancellationToken)).ConfigureAwait(false);

Is there a better way to handle the situation?

Answer 1

Since you're using Entity Framework, you can use null-coalescing operator to get the property instead of the given value. This would be translated into COALESCE function, which is a standard SQL function that is supported in most of database providers.

Example :

.Where(st => skillType == null || st.SkillTypeId == skillType)
.Where(st => string.IsNullOrEmpty(skillName) || st.Name == skillName)

would be modified to this :

.Where(st => st.SkillTypeId == (skillType ?? st.SkillTypeId))
.Where(st => st.Name == (skillName ?? st.Name))

In SQL, it would be translated into something similar to this query:

SELECT *
FROM table 
WHERE 
    SkillTypeId = COALESCE(@skillType, SkillTypeId)
AND Name = COALESCE(@skillName, Name)

So, the above query will check first @skillType if null, then it will get current value of SkillTypeId and compare it to itself. This would be also applied to the Name as well. which is the same effect as 1 == 1.

This is only applicable on null-coalescing operator, and because of that, you will need to handle the value to reassign null to it to avoid default values or empty or whitespace for strings before you pass it to the query.

Something like this would be applicable:

skillName = string.IsNullOrWhiteSpace(skillName) ? null : skillName;        

var paginationListResult = await this.DbContext.Skills
    .Where(st => st.SkillTypeId == (skillType ?? st.SkillTypeId))
    .Where(st => st.Name == (skillName ?? st.Name))
     .Include(sk => sk.SkillType)
     .Include(sk => sk.Regroupment)
     .Include(sk => sk.SkillRoleRequirements)
    .OrderBy(s => s.Name)
    .ToPaginateListAsync(pageIndex, itemsPerPage, default(CancellationToken)).ConfigureAwait(false);