CODEWITHSUNDEEP
c#sql-serverasp.net-mvcado.net
Irfan Y
Irfan Y

Reputation: 1300

query to return all records if parameter is null

My database table is:

----------
|   Ad   |
----------
|Id
|title
|price
|tags
|brand
|model

I have to search Ad by 6 parameters i.e, by brand, model , tags, title, minPrice and maxPrice. Now if brand is null then it should pick all rows else pick only those rows where brand is equal to userDesiredBrand.

My function is:

public async Task<IHttpActionResult> SearchAds(string brand, string model,string tags,string title, int minPrice, int maxPrice){
     if(brand != null && model != null && tags != null && title != null && minPrice != null && maxPrice != null){
           var ret = from ad in db.Ads
                     where ad.brand.Equals(brand) && ad.model.Equals(model) && ad.tags.Equals(tags) && ad.title.Equals(title) && ad.price > minPrice && ad.price < maxPrice
                     select new{
                           id = ad.Id,
                           title = ad.title,
                           //retrieve other attributes.
                     }
            return OK(ret);
      }
      if(brand != null && model == null && tags != null && title != null && minPrice != null && maxPrice != null){
           var ret = from ad in db.Ads
                     where ad.brand.Equals(brand) && ad.tags.Equals(tags) && ad.title.Equals(title) && ad.price > minPrice && ad.price < maxPrice
                     select new{
                           id = ad.Id,
                           title = ad.title,
                           //retrieve other attributes.
                     }
            return OK(ret);
      }
      if(brand != null && model != null && tags == null && title != null && minPrice != null && maxPrice != null){
           var ret = from ad in db.Ads
                     where ad.brand.Equals(brand) && ad.model.Equals(model)  && ad.title.Equals(title) && ad.price > minPrice && ad.price < maxPrice
                     select new{
                           id = ad.Id,
                           title = ad.title,
                           //retrieve other attributes.
                     }
            return OK(ret);
      }
      //Do I have to write 6 * 6 if statements or is this achievable in one query?
}

Upvotes: 2

Views: 5457

Answers (4)

IITC
IITC

Reputation: 81

If you opt for T-SQL, you can write everything in one query (and embed it, if you want)

To start with DECLARE @brand VARCHAR(max); SET @brand = 'WhatsUse'

SELECT * FROM Ad WHERE brand IS NULL

SELECT * FROM Ad WHERE brand = @brand

Now - since you'll be checking for MIN and MAX values of price, you have to use the temp tables for sorting and then using SELECT ..... WHERE ..... for extracting the rows.

Cheers

Upvotes: 0

David
David

Reputation: 218798

Do I have to write 6 * 6 if statements

Absolutely not. If this logic doesn't work as intended for null values:

where ad.brand.Equals(brand)

Then simply add a check for null to that logic:

where (brand == null || ad.brand.Equals(brand))

Another approach could be to build the query in stages. Something like this:

var ads = db.Ads;
if (!string.IsNullOrEmpty(brand))
    ads = ads.Where(ad => ad.brand.Equals(brand));
if (!string.IsNullOrEmpty(tags))
    ads = ads.Where(ad => ad.tags.Equals(tags));
// etc.

ads = ads.Select(ad => new {
                         id = ad.Id,
                         title = ad.title,
                         //retrieve other attributes.
                       });
return OK(ads);

That is, you can chain as many clauses as you like and the actual query won't materialize against the database until later anyway. (Until it's actuall enumerated by something. Which in this case is likely the WebAPI framework itself when preparing the response.)

Upvotes: 3

Cᴏʀʏ
Cᴏʀʏ

Reputation: 107498

In a plain SQL statement I would use the following constraints to achieve your goal:

SELECT
    ...
WHERE
    (@brand IS NULL OR brand = @brand)
    AND
    (@model IS NULL OR model = @model)
    ...

where the @variables are the parameters. Translating this backwards to LINQ might look like:

where (brand == null || ad.brand == brand) &&
      (model == null || ad.model == model) && ...

Another way, for education purposes only (because I wouldn't recommend using this in production code for performance reasons), would be to construct your query bit by bit:

var query = (from ad in db.Ads);
if (brand != null)
    query = query.Where(ad => ad.brand == brand);
if (model != null)
    query = query.Where(ad => ad.model == model);
....

Upvotes: 5

M B
M B

Reputation: 2326

check for null values in the query

where ((brand == null || ad.model.Eqauls(model)

Upvotes: 0

Related Questions