CODEWITHSUNDEEP
c#.netlinq
Eyad
Eyad

Reputation: 14229

LINQ - mandatory field and dont care if null

I am building a search feature in an asp.net application and I am using LINQ to SQL to retrieve data based on the selected search criteria. The search criteria are

  1. Country
  2. City
  3. District
  4. number of rooms
  5. Rent Cycle

Only the first search criterion, Country, is the mandatory field. However, if the user entered values for criteria 2, 3, 4 and/or 5 then the entered values should be taken into account and only retrieve results that matched all entered search criterion. Notice that if one of the criterion 2, 3, 4 and/or 5 is left empty (null) then LINQ should act as 'DONT CARE' and return that row.

So for example, if the criteria entered are:

  1. Country = USA
  2. City = null
  3. District = null
  4. number of rooms = null
  5. Rent Cycle = null

Then all rows with Country == USA should be returned.

Another example:

  1. Country = UK
  2. City = null
  3. District = null
  4. number of rooms = 5
  5. Rent Cycle = null

Then all rows with Country == UK and NumberOfRooms == 5 should be returned.

How do I achieve this in LINQ to SQL?

Here is what i have so far:

var data = from x in db.Units where x.Country == coutnryID && /*PLEASE HELP!*/ select x;

Upvotes: 4

Views: 669

Answers (3)

Joe
Joe

Reputation: 82584

Using GetValueOrDefault, and supply the default to the current value if null:

var data = from x in db.Units
    where x.Country == countryId
        && (x.City == cityId.GetValueOrDefault(x.City))
        && (x.District == districtId.GetValueOrDefault(x.District))
        && (x.Rooms == rooms.GetValueOrDefault(x.Rooms))
        && (x.RentCycle == rentCycle.GetValueOrDefault(x.RentCycle))
    select x;

Upvotes: 1

Malcolm
Malcolm

Reputation: 1259

You can build up the query in stages:

var query = from x in db.Units  where x.Country == countryId;
if (cityId != null)        query = query.Where(x.City == cityId);
if (districtId != null)    query = query.Where(x.City == districtId);
if (rooms != null)         query = query.Where(x.Rooms == rooms);
if (rentCycle != null)     query = query.Where(x.RentCycle == rentCycle);
var data = query.Select();

That'll give you slightly more efficient SQL if slightly messier C#

Upvotes: 1

Mathew Thompson
Mathew Thompson

Reputation: 56429

Try this (assuming cityId, districtId, rooms and rentCycle are the variables that you're wanting to search on:

var data = from x in db.Units
    where x.Country == countryId
        && (cityId == null || x.City == cityId)
        && (districtId == null || x.District == districtId)
        && (rooms == null || x.Rooms == rooms)
        && (rentCycle == null || x.RentCycle == rentCycle)
    select x;

I'm basically saying, if your variables you want to search on are null, then disregard them, otherwise match them to the corresponding field in the Unit.

Upvotes: 4

Related Questions