Multiple condition checks in the query?

Question

I have the below query where I pass in 4 parameters from the UI. BU and Region have radio button options of (All, Option1, Option2, Option3 etc) in the UI. Options 1 - 3 are values in the data whereas 'All' is not.

Is there a way to write the below more efficiently? For example, if BU or Region values are 'All' "from the UI", (which are not values in the dataset), is there a character I can assign to bu or region if all is selected so that the query knows to select all the values?

I don't know if there is such a thing but I want to avoid having to do something like the below.

     public string GetAvgSeatPrice(string bu, string region, DateTime? startDate, DateTime? endDate)
    {

        if (bu.Equals("All"))
        {
            var averageSeatPrice = (from r in db.Registrations
                                    where
                                         //r.BusinessUnit.Equals(bu) &&
                                         r.Region.Equals(region) &&
                                         r.StartDate >= startDate &&
                                         r.EndDate <= endDate &&
                                         r.ActualPrice > 0
                                    select r.ActualPrice).Average();

            var AvgSeatPrice = "$" + string.Format("{0:0.00}", averageSeatPrice);

            return AvgSeatPrice;
        }
        else if (region.Equals("All"))
        {
            var averageSeatPrice = (from r in db.Registrations
                                    where
                                         r.BusinessUnit.Equals(bu) &&
                                        // r.Region.Equals(region) &&
                                         r.StartDate >= startDate &&
                                         r.EndDate <= endDate &&
                                         r.ActualPrice > 0
                                    select r.ActualPrice).Average();

            var AvgSeatPrice = "$" + string.Format("{0:0.00}", averageSeatPrice);

            return AvgSeatPrice;
        }
        else if (bu.Equals("All") && region.Equals("All"))
        {
            var averageSeatPrice = (from r in db.Registrations
                                    where
                                         //r.BusinessUnit.Equals(bu) &&
                                         // r.Region.Equals(region) &&
                                         r.StartDate >= startDate &&
                                         r.EndDate <= endDate &&
                                         r.ActualPrice > 0
                                    select r.ActualPrice).Average();

            var AvgSeatPrice = "$" + string.Format("{0:0.00}", averageSeatPrice);

            return AvgSeatPrice;
        }
        else
        {
            var averageSeatPrice = (from r in db.Registrations
                                    where
                                         r.BusinessUnit.Equals(bu) &&
                                         r.Region.Equals(region) &&
                                         r.StartDate >= startDate &&
                                         r.EndDate <= endDate &&
                                         r.ActualPrice > 0
                                    select r.ActualPrice).Average();

            var AvgSeatPrice = "$" + string.Format("{0:0.00}", averageSeatPrice);

            return AvgSeatPrice;
        }

    }

}

}

Answer 1

I hope this isn't exactly what you're asking to not have provided (I could interpret multiple if/else case/etc two separate ways from your question). But you could do something like this:

var averageSeatPrice = 
    from r in db.Registrations
    where r.StartDate >= startDate &&
          r.EndDate <= endDate &&
          r.ActualPrice > 0

// note I'm assuming your "bu" "all" option is represented as the string "All"
if (bu.ToUpper() != "ALL") 
    averageSeatPrice = averageSeatPrice
        .Where(w => w.BusinessUnit == bu);

if (region.ToUpper() !+ "ALL")
    averageSeatPrice = averageSeatPrice
        .Where(w => w.Region == region);

averageSeatPrice = averageSeatPrice select r.ActualPrice).Average();

Sorry I'm less clear with the linq syntax you're using (or I would have done my answer with that), hopefully this helps though.

Basically the if statement states if the option is not all, then tack on that portion of the where clause otherwise it's not tacked on, and never becomes a part of the query.

Kritner - can you elaborate a bit on what you're doing here? seems like an interesting approach too

Given BusinessUnit can have the following values:

option1
option2
option3

if bu is provided as option1 then we need our where clause to contain where BusinessUnit = 'option1'

so:

if (bu.ToUpper() != "ALL")  // bu is option1, evaluates true
    averageSeatPrice = averageSeatPrice // add the where clause to the IQueryable
        .Where(w => w.BusinessUnit == bu); // deferred execution, so have not yet executed query, and can continue to modify and only do a single trip to db

Now on the other hand, if bu is "ALL" or string.empty, or whatever you want it to be that isn't a valid value, then we don't want BusinessUnit to be a part of the where clause at all... the posted code accomplishes this scenario as well:

if (bu.ToUpper() != "ALL") // this evaluates to false (bu is "ALL"), don't add the where clause information at all.
    averageSeatPrice = averageSeatPrice
        .Where(w => w.BusinessUnit == bu);

Each averageSeatPrice = averageSeatPrice.Where(...) is simply tacked on to the query until a point at which you enumerate the results.

Answer 2

Simply do this:

public string GetAvgSeatPrice(string bu, string region, DateTime? startDate, DateTime? endDate)
    {
        var averageSeatPrice = (
            from r in db.Registrations
            where (bu == "ALL" || r.BusinessUnit.Equals(bu))
            && (region == "ALL" || r.Region.Equals(region))
            && r.StartDate >= startDate
            && r.EndDate <= endDate
            && r.ActualPrice > 0
            select r.ActualPrice).Average();

        var AvgSeatPrice = "$" + string.Format("{0:0.00}", averageSeatPrice);

        return AvgSeatPrice;
    }

Because of the OR (||), if the selection is "ALL" the second condition is never checked and do not narrow the selection down by the given property. It is checked only if the selection is NOT "ALL" and in such case the selection is narrowed down.