如何根据参数动态更改位置的条件

本文关键字：位置 条件 动态 何根 参数 | 更新日期: 2023-09-27 18:36:25

我需要用一个查询来编写它。根据参数，如果参数大于零，则 regionid 必须持有其中一个条件，如果此参数涉及请求（如果未涉及）。

 var myRegionId = 0;
                if (!string.IsNullOrEmpty(regionId))
                {
                    myRegionId = int.Parse(regionId);
                }
                IOrderedQueryable price;
                if (myRegionId>0)
                {
                     price = (from p in _db.PRICEs
                                  join good in _db.GOODs on p.good_id equals good.id
                                  join gname in _db.spr_goods_names on good.goods_name_id equals gname.id
                                  where ******p.region_id == myRegionId &&** gname.name.ToLower().Contains(filterText.ToLower())****
                                  group p by new{ p.good_id} into g
                                  select new
                                  {
                                      GoodId = g.Key.good_id,
                                      Promotion = g.Count(x => x.promotion != ""),
                                      MinPrice = g.Min(x => x.good_price),
                                      DistributorCount = g.Count(x => x.distributor_id != null)
                                  }
                            ).OrderByDescending(x => x.DistributorCount).Take(100).OrderBy(x => x.MinPrice);
                }else
                {
                    price = (from p in _db.PRICEs
                             join good in _db.GOODs on p.good_id equals good.id
                             join gname in _db.spr_goods_names on good.goods_name_id equals gname.id
                             **where gname.name.ToLower().Contains(filterText.ToLower())**
                             group p by new { p.good_id } into g
                             select new
                             {
                                 GoodId = g.Key.good_id,
                                 Promotion = g.Count(x => x.promotion != ""),
                                 MinPrice = g.Min(x => x.good_price),
                                 DistributorCount = g.Count(x => x.distributor_id != null)
                             }
                           ).OrderByDescending(x => x.DistributorCount).Take(100).OrderBy(x => x.MinPrice);
                }

如何根据参数动态更改位置的条件

这个怎么样？

 price = (from p in _db.PRICEs
          join good in _db.GOODs on p.good_id equals good.id
          join gname in _db.spr_goods_names on good.goods_name_id equals gname.id
          where (myRegionId <= 0 || p.region_id == myRegionId) && gname.name.ToLower().Contains(filterText.ToLower())
          group p by new{ p.good_id} into g
          select new
          {
              GoodId = g.Key.good_id,
              Promotion = g.Count(x => x.promotion != ""),
              MinPrice = g.Min(x => x.good_price),
              DistributorCount = g.Count(x => x.distributor_id != null)
          }).OrderByDescending(x => x.DistributorCount).Take(100).OrderBy(x => x.MinPrice);

如果myRegionId <= 0，则它的计算结果为 true，并且不计算p.region_id == myRegionId，因此被忽略。

使用延迟加载功能，只要不需要数据，就可以操作查询构建。

var a = from p in _db.PRICEs
...
if (condition) a = a.where...
else a=a.where...
if (condition2) a = a.where...
var b = from a ....

一旦你迭代或调用 ToList 数据将被下载，并将查询弹出到数据库。因此，在Prom之前，您可以做您需要做的事情。

PS：使用分析器检查发送到BD的查询以优化性能。有时ORM可以构建一些非常大的东西并损害性能。

您也可以将其分成几部分，如下所示：

var query = from p in _db.PRICEs        
            join good in _db.GOODs on p.good_id equals good.id                                    
            join gname in _db.spr_goods_names on good.goods_name_id equals gname.id
            where gname.name.ToLower().Contains(filterText.ToLower())
            select p;
if (myRegionId>0)
{
   query = query.Where(p => p.region_id == myRegionId);
}
var price = from p in query
            group p by new{ p.good_id} into g
            select new
            {
               GoodId = g.Key.good_id,
               Promotion = g.Count(x => x.promotion != ""),
               MinPrice = g.Min(x => x.good_price),
               DistributorCount = g.Count(x => x.distributor_id != null)
            }).OrderByDescending(x => x.DistributorCount).Take(100).OrderBy(x => x.MinPrice);
相关文章：