解析OData $过滤器到SQL Where子句

( (startswith(Name,'Bill'))  and  
(substringof('sunset',Address))  and  
(substringof('7421',Phone)) )

SELECT CustName, Address1, Address2, ... 
FROM Customers
WHERE CustName like 'Bill%' AND 
  Address1 like '%sunset% AND 
  Phone like '%7421%'

您将需要对原始值应用一些Regex，获得匹配并应用一些逻辑来进行转换。基本上，搜索带有参数的函数，删除函数文本，获取参数并将其转换为类似子句。像这样:

string str = @"( (startswith(Name,'Bill'))  and  
(substringof('sunset',Address))  and  
(substringof('7421',Phone)) )";
System.Text.RegularExpressions.Regex regex = new  System.Text.RegularExpressions.Regex(@"startswith'(([^')]+)')");
System.Text.RegularExpressions.Match match = regex.Match(str);
if (match.Success)
{
  string tmp = match.Value;
  string destination = "@field LIKE '@val%'";
  tmp = tmp.Replace( "startswith(","");
  tmp = tmp.Replace( ")","");
  string[] keyvalue = tmp.Split(',');
  string field = keyvalue[0];
  string val = keyvalue[1];
  destination = destination.Replace("@field", field);
  destination = destination.Replace("@val", val.Replace("'",""));
  Console.WriteLine( destination );
}

这个输出:

Name LIKE 'Bill%'

虽然没有直接帮助OP，但多年来我一直在讨论这个问题，并且开发了另一个技巧，如果您当前的模式接近遗留数据库，您可以使用。

这将只适用于你可以创建一个类似或相同的查询EF上下文，我们将利用Linq到实体SQL表别名约定，因此它可能会受到未来更新的影响。

1. 定义一个EF查询，它与输出的表结构非常接近。
2. 使用FilterQueryOption.ApplyTo()将仅$filter 应用于近似查询
3. 从查询
捕获SQL字符串
4. 从查询
中提取WHERE子句
5. 在自定义查询中注入WHERE子句

除了绑定到EF注入的表别名约束之外，它比单独使用REGEX提供了更多的安全性和灵活性。您可能会发现可以使用regex进一步增强此输出，但是OData解析器已经将URL表达式验证并净化为有效的SQL语法，包括将表达式转换为SQL函数调用。

下面是基于EF6和OData v4的，所以URL语法有点不同，但是相同的概念也应该适用于以前版本的ODataLib。

• CustomDTO是一个自定义类，不在EF DbContext模型中定义。
• Customer 是在EF DbContext中定义的，它与遗留数据库
具有相似的字段。

/// <summary>Return a list of customer summaries for a given Account</summary>
[EnableQuery, HttpGet]
public IQueryable<CustomDTO> Customers([FromODataUri] int key, ODataQueryOptions<CustomDTO> _queryOptions)
{
    // The custom query we want to apply to the legacy database.
    // NOTE: If the fields are identical to the current DbContext, then we don't have to go this far.
    // We MUST alias the table to match the generated SQL
    string sql = "SELECT CustName, IsNull(Address1,'') + IsNull(Address2,'') as Address, Phone " + 
                 "FROM Customers AS [Extent1]" + 
                 "WHERE AccountId = @AccountId";
    if (!String.IsNullOrWhiteSpace(_queryOptions.Filter?.RawValue))
    {
        var criteriaQuery = from x in db.Customers
                            select new CustomDTO
                            {
                                Name = CustName,
                                Address = Address1 + Address2
                                Phone = Phone
                            };
        var modifiedQuery = _queryOptions.Filter.ApplyTo(criteriaQuery, new ODataQuerySettings({ EnableConstantParameterization = false });
        string modifiedSql = modifiedQuery.ToString();
        modifiedSql = modifiedSql.Substring(modifiedSql.LastIndexOf("WHERE ") + 5);
        sql += $" AND ({modifiedSql})";
    }
    var customers = aDifferentContext.Database.SqlQuery<CustomDTO>(sql, new SqlParameter("@AccountId", key)).ToList();
    return customers.AsQueryable();
}

• 在我们的自定义查询中使用别名[Extent1]的另一种选择是使用字符串替换，但这工作得很好。
• EnableConstantParameterization被故意禁用，以内联过滤器值，而不是为每个过滤器参数跟踪和注入SqlParameter。它简化了代码，并在一定程度上进行了净化。如果这不能满足您的安全考虑，则取决于您是否要付出额外的努力。
• 您将注意到我在查询中过滤到LAST WHERE子句，这是因为如果此查询涉及投影并且调用者试图将过滤器应用于次要范围之一(连接的结果集)，那么EF将通过过滤子查询来优化查询，而不是在最后全部应用过滤器。有很多方法可以解决这个问题，现在让我们坚持一个简单的例子。

modifiedQuery生成的SQL:

URL: ~/OData/Accounts(1102)/Customers?$filter=startswith(Name, 'Bill') and contains(Address, 'sunset') and contains(Phone, '7421')

过滤器。RawValue: startswith(Name, 'Bill') and contains(Address, 'sunset') and contains(Phone, '7421')

SELECT 
    [Extent1].[CustName] AS [Name], 
    CASE WHEN ([Extent1].[Address1] IS NULL) THEN N'' ELSE [Extent1].[Address1] END + CASE WHEN ([Extent1].[Address2] IS NULL) THEN N'' ELSE [Extent1].[Address2] END AS [C1], 
    [Extent1].[Phone] AS [Phone]
    FROM  [dbo].[Customer] AS [Extent1]
    WHERE ([Extent1].[CustName] LIKE 'Bill%') 
      AND (CASE WHEN ([Extent1].[Address1] IS NULL) THEN N'' ELSE [Extent1].[Address1] END 
              + CASE WHEN ([Extent1].[Address2] IS NULL) THEN N'' ELSE [Extent1].[Address2] END 
           LIKE N'%sunset%') 
      AND ([Extent1].[Phone] LIKE '%7421%')

最后执行的SQL:

SELECT CustName as Name, IsNull(Address1,'') + IsNull(Address2,'') as Address, Phone 
  FROM  [dbo].[Customer] AS [Extent1]
 WHERE AccountId = @AccountId AND (([Extent1].[CustName] LIKE 'Bill%') 
      AND (CASE WHEN ([Extent1].[Address1] IS NULL) THEN N'' ELSE [Extent1].[Address1] END 
              + CASE WHEN ([Extent1].[Address2] IS NULL) THEN N'' ELSE [Extent1].[Address2] END 
           LIKE N'%sunset%') 
      AND ([Extent1].[Phone] LIKE '%7421%'))

类定义

public class CustomDTO
{
    public string Name { get;set; }
    public string Address { get;set; }
    public string Phone { get;set; }
} 
public class Customer
{
    public int AccountId { get;set; }
    public string CustName { get;set; }
    public string Address1 { get;set; }
    public string Address2 { get;set; }
    public string Phone { get;set; }
}

---

我主要在优化返回DTO结构的复杂Linq表达式时使用这个技巧，这些结构可以用比EF生成的SQL简单得多的SQL实现。传统的EF查询被DbContext.Database.SqlQuery<T>(sql, parameters)

形式的原始SQL查询所取代。

在这个例子中，我使用了一个不同的EF DbContext，但是一旦你有了SQL脚本，你应该能够运行它，无论你需要。