LINQ query with variable that may or maynot be null does not return results when null-CodePudding

I have a LINQ query with a WHERE clause that has a variable that sometimes will be NULL. When this variable is NULL I can not get it to pull any results even though there are results to be taken. I found Linq where column == (null reference) not the same as column == null and tried the solutions provided there with no success. What am I doing wrong?

public async Task<List<SectionNavigationMenuDTO>> GetSectionNavigationMenu(int? SectionID, bool IsAdmin = false)
{
    return await _siteDbContext.SectionNavigationMenuItems
        //.Where(snmi => snmi.SectionID == SectionID && snmi.IsAdminOnly == IsAdmin) //No results at all
        //.Where(snmi => object.Equals(snmi.SectionID, SectionID)) //No results at all
        //.Where(snmi => (snmi.SectionID == SectionID ||  (SectionID == null &&  snmi.SectionID == null))) //No results at all
        //.Where(snmi => snmi.IsAdminOnly == IsAdmin) //Returns 3 results
       .OrderBy(snmi => snmi.Name)
       .Select(sni => new SectionNavigationMenuDTO()
       {
           Name = sni.Name,
           URL = sni.URL
       })
       .ToListAsync();
}

Edit:

The SectionID should be either filled with a int or null and be valid in both cases. If the SectionID variable is NULL then it should pass NULL as the argument in for the LINQ query. The database does contain entries with NULL for the SectionID and the query SELECT * FROM dbo.SectionNavigationMenuItems WHERE SectionID IS NULL does return 3 results as expected.

The top commented WHERE clause is what I want to happen (with the two comparisons) the other 3 are what I have tried to get this to work and the results.

CodePudding user response：

Try this:

public async Task<List<SectionNavigationMenuDTO>> GetSectionNavigationMenu(int? SectionID, bool IsAdmin = false)
    {
        return await _siteDbContext.SectionNavigationMenuItems
                                 
                                   .Where(snmi => snmi.IsAdminOnly == IsAdmin && (SectionID==null || SectionID==smi.SectionID ) ) 
                                   .OrderBy(snmi => snmi.Name)
                                   .Select(sni => new SectionNavigationMenuDTO()
                                   {
                                       Name = sni.Name,
                                       URL = sni.URL
                                   })
                                   .ToListAsync();
    }