c# - 如果参数为空，则查询返回所有记录

Question

我的数据库表是:

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

我必须按 6 个参数搜索 Ad，即按品牌、型号、标签、标题、minPrice 和 maxPrice。现在，如果 brand 为空，那么它应该选择所有行，否则只选择那些 brand 等于 userDesiredBrand 的行。

我的职能是:

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?
}

Answer 1

在普通的 SQL 语句中，我将使用以下约束来实现您的目标:

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

@variables 是参数。将其反向转换为 LINQ 可能如下所示:

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

另一种方式，仅用于教育目的(因为出于性能原因我不建议在生产代码中使用它)，将一点一点地构建您的查询:

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);
....