SQL to Linq Where Case Statement

本文关键字：Case Statement Where Linq to SQL | 更新日期: 2023-09-27 18:17:44

如何在Linq中编写以下SQL:

SELECT clientID, businessName 
  FROM clients 
  WHERE (regionID = CASE WHEN '{0}' = -1 THEN regionID ELSE '{0}' END)
  ORDER BY businessName

这是我到目前为止所拥有的，但我不确定如何翻译where case语句:

var result = context.clients
    .OrderBy(x => x.businessName)
    .Select(x => new
        {
            x.clientID, x.businessName, x.active, x.regionID
        })
    .OrderBy(x=> x.businessName);

SQL to Linq Where Case Statement

尝试在查询中使用三元语句。我不确定它是否转换为SQL中的case语句，但您可以使用LINQPad来查找。

假设'val'包含要插入{0}的值…

var result = context.clients
    .OrderBy(x => x.businessName)
    .Select(x => new
        {
            x.clientID, 
            x.businessName, 
            x.active, 
            x.regionID == -1 ? val : x.regionID
        })
    .OrderBy(x=> x.businessName);

假设您的变量名为parameter，您可以添加像

这样的部分
 .Where(a=>a.RegionID == ((parameter == -1) ? a.RegionID : parameter));


还有一种方法可以避免使用case语句，方法是像

这样构建查询
var query = context.clients.AsQueryable();
if (parameter != -1)
    query = query.Where(a=>a.regionID == parameter);

var result = query
    .OrderBy(x => x.Name )
    .Select(x => new
    {
       x.clientID, x.businessName
    });

如果parameter为-1，这将生成类似

的内容
SELECT clientID, businessName 
FROM clients 
ORDER BY businessName

否则会生成类似

的内容
SELECT clientID, businessName 
FROM clients 
WHERE (regionID = parameter)
ORDER BY businessName
相关文章：