CODEWITHSUNDEEP
c#linq
TrevorGoodchild
TrevorGoodchild

Reputation: 1058

LINQ statement using one of two parameters

I'm working on a LINQ statement. I have a table of cities where the records have either a countryId or a stateId. I'd like to just write the one statement and have the where clause check to see which of the two parameters is null and then select on the one that is not.

Here's what I'm working with:

public List<City> Cities(int? countryTypeId, int? stateTypeId)
{
    if (countryTypeId == null && stateTypeId == null) 
        return null;

    return _db.City
        .Where(x => x.StateTypeId == stateTypeId
                    && x.CountryTypeId == countryTypeId)
        .OrderBy(x => x.Description)
        .ToDTOs();
}

I'm pretty new to LINQ, and I know this code isn't right, just adding it for context.

Upvotes: 0

Views: 88

Answers (3)

Anthony Pegram
Anthony Pegram

Reputation: 126804

My DBA has sufficiently beaten it into my head that ignoring parameters in a query (ex: WHERE Field = @PARAM or @PARAM IS NULL) can result in very bad things. As a result, I would encourage you to conditionally add only the parameters that you absolutely need. Fortunately, given that you are working with just two possible parameters, this is trivial.

Start with the base of your query, and then add to it. 

var queryBase = _db.City.OrderBy(x => x.Description);

if (countryTypeId.HasValue)
{
    queryBase = queryBase.Where(x => x.CountryTypeId == countryTypeId);
}

if (stateTypeId.HasValue)
{
    queryBase = queryBase.Where(x => x.StateTypeId == stateTypeId);
}

return queryBase.ToDTOs(); // or .ToList() for a more universal outcome

Add whatever logic you may need if parameters are mutually exclusive, one supercedes the other, etc.

Upvotes: 0

TVOHM
TVOHM

Reputation: 2742

_db.City.Where(c => c.CountryTypeId?.Equals(countryTypeId) ?? false 
    | c.StateTypeId?.Equals(stateTypeId) ?? false);

Using null conditional operators - when a type Id is null use the null coalescing operator to return false and fail the match - otherwise check for equality and return matching.

Note you cannot short circuit the OR operator here!

I'm not sure if this is the case, but if one of the input parameters was always null and the entries were guaranteed to always have one property null, the following would be a cool solution:

_db.City.Where(c => (c.CountryTypeId ?? c.StateTypeId) == (countryTypeId ?? stateTypeId))

Upvotes: 0

AD.Net
AD.Net

Reputation: 13399

If the State and Country ids are all >0 you simply can do this, no need to check for null

 .Where(x => x.StateTypeId == stateTypeId.GetValueOrDefault()
                    && x.CountryTypeId == countryTypeId.GetValueOrDefault())

Else you need to add the condition if those nullable inputs have value or not, as mentioned in the comment

Edit: after seeing some comments, if you are looking for list of cities based on either of the parameters, then you should be using || not && in your where condition

Where(x => (stateTypeId.HasValue && stateTypeId.Value == x.StateTypeId) 
     || (countryTypeId.HasValue && countryTypeId.Value == x.CountryTypeId))

Note the order matters, this code will first check if stateTypeId has value and if it has it'll match only the cities with that stateTypeId

Upvotes: 1

Related Questions