CODEWITHSUNDEEP
c#linqwhere-clauseternary-operator
fourbeatcoder
fourbeatcoder

Reputation: 1419

Ternary Operator in LINQ Where clause for nullable bool column

Can anyone see anything wrong with the ternary in the where of this linq statement:

var organizations = Context.Set<Domain.Content.Organisation>()
                    .Where(x => x.ShowCompanyPage == (showCompanyPagesOnly ? true : x.ShowCompanyPage))

if showCompanyPagesOnly is set to true, I get 4 results, this is correct only four companies have ShowCompanyPage = true.

However if I set it to false, I expect 1000+ results (all companies). But I STILL only get 4.

Is my logic not:

if showCompanyPagesOnly is true, then give me results where  x.ShowCompanyPage == true

else give me results where  x.ShowCompanyPage = whatever is in the column (ie ALL Organisations)

?

x.ShowCompanyPage is a nullable bool column.

Full code:

public Result<IList<Organisation>> GetAllOrganisations(bool showCompanyPagesOnly = false)
    {
        var result = new Result<IList<Organisation>>();

        try
        {
            var organizations = Context.Set<Domain.Content.Organisation>()
                .Where(x => x.ShowCompanyPage == (showCompanyPagesOnly == true ? true : x.ShowCompanyPage)) // show only company pages or show all
                .AsNoTracking()
                .Select(x => new DataContracts.Content.Organisation
                {
                    Id = x.Id,
                    Name = x.Name,
                    OrganisationTypeId = x.OrganisationTypeId,
                    IsCustomer = x.IsCustomer,
                    SeoName = x.SeoName,
                    Description = x.Description,
                    Website = x.Website
                }).OrderBy(x => x.Name).ToList();

            result.Data = organizations;
        }
        catch (Exception ex)
        {
            result.SetException(ex);
            HandleError(ex);
        }
        return result;

    }

Upvotes: 3

Views: 12508

Answers (3)

Robert McKee
Robert McKee

Reputation: 21477

This is a much better approach, as it will generate two distinct LINQ queries, and this will allow SQL Server to generate two distinct query plans, which can in most cases greatly affect the performance of the queries:

public Result<IList<Organisation>> GetAllOrganisations(bool showCompanyPagesOnly = false)
{
    var result = new Result<IList<Organisation>>();

    try
    {
        var organizations = Context.Set<Domain.Content.Organisation>()
            .AsNoTracking();

        if (showCompanyPagesOnly)
            organizations=organization
            .Where(x => x.ShowCompanyPage == true);

        result.Data = organizations
            .Select(x => new DataContracts.Content.Organisation
            {
                Id = x.Id,
                Name = x.Name,
                OrganisationTypeId = x.OrganisationTypeId,
                IsCustomer = x.IsCustomer,
                SeoName = x.SeoName,
                Description = x.Description,
                Website = x.Website
            }).OrderBy(x => x.Name).ToList();
    }
    catch (Exception ex)
    {
        result.SetException(ex);
        HandleError(ex);
    }
    return result;

}

Upvotes: 3

Racil Hilan
Racil Hilan

Reputation: 25351

Try this:

.Where(x => showCompanyPagesOnly ? x.ShowCompanyPage == true : true)

Here is a fiddle.

The Where() function returns records that satisfy the condition, so the condition must evaluate to a Boolean (i.e. either true or false). If you put the value true in the condition, then you're effectively asking the Where() function to return all records. It is similar to:

if(true){
    //do something.
}

As you know this will always execute the "do something".

Upvotes: 1

MikeT
MikeT

Reputation: 5500

Sometimes when logic is getting too complex the best answer is the turn the question upside down, currently you are asking

if showCompanyPagesOnly is true how do i get only the ones with with ShowCompanyPage = true

if you swap that with get everything unless showCompanyPagesOnly is true and your logic becomes a simple OR statement

either showCompanyPagesOnly is not true or ShowCompanyPage is true which is 

x => (!showCompanyPagesOnly) || x.ShowCompanyPage

you may need to make that 

   x => (!showCompanyPagesOnly) || (x.ShowCompanyPage ?? false)/*default value depends on if you want to treat null as true or false*/)

to take into account the nullability

Upvotes: 4

Related Questions