SqlLinq：将 LINQ to SQL 反向进行
4.96/5 (48投票s)
解析 SQL 语句以创建 LINQ 表达式。
引言
我一直想编写一个应用程序，允许我查询我的音乐收藏。多年来，我一直在将 CD 刻录成 MP3 和 WMA 格式，我拥有成千上万的歌曲，并且我一直在寻找比大多数音乐播放器（如 Windows Media Player）提供的更强大的功能。随着 .NET 3.5 中 LINQ 的引入，这似乎是一个很好的机会来创建我一直思考的应用程序，同时也能深入研究一项新技术。这套代码就是结果。
我的最终目标是创建一个库集，用于允许针对任何可以表示为关系型、半关系型和表格格式的数据源执行 SQL 查询。
更新
2009/11/11 - 查询评估代码的一个演进版本可在更新版本中找到：动态评估的 SQL LINQ 查询。更新版本操作于任何 IEnumerable<T>，而不仅仅是 IDictionary<string, object>。
自从这篇博文首次发布以来，我一直在不断地对这套代码进行实验，并进行了一些修改和补充。
- 用户界面
- 语法高亮（感谢 Uri Guy）
- 添加了一些来自 FAMFAMFAM 的精美图标
- 跟踪输出窗口
DataGridView打印和打印预览（感谢 Salan Al-Ani）- 添加了模式视图对话框，显示可用的文件属性读取器命名空间以及每个读取器公开的属性
- 其他杂项更改
- SQL 语法支持
- 增加了对聚合中表达式的支持（例如，
SELECT MAX(duration / 1000)） - 增加了
DELETE支持（在链接的代码中禁用，但可以通过删除throws语句轻松启用） - 增加了在子句中按别名或名称引用字段的能力
- 文件属性读取器重构
- 将文件属性读取抽象为可插入的实现
- 将
System.IO.File属性与媒体属性分离 - 添加了一个（实验性的）程序集文件属性读取器
- 将 SQL 评估（SQLLinq）与文件属性读取分离
- 重组了
SyntaxTree命名空间 - 杂项评估错误修复
背景
在 LINQ 和大多数用法示例中，我首先遇到的问题是它们都假设对底层数据结构和所需结果结构编译时知识的隐式了解。我相信这适用于“业务线”应用程序的大部分情况，LINQ 在其中将真正发挥其作用。创建 LINQ 查询以聚合和操作销售报告或产品目录页面，需要对数据以及所需聚合的详细知识。查看 LINQ to SQL，它会生成 SQL 语句来匹配运行时 LINQ 表达式执行，并提供了一个非常好的对象关系映射工具，过去该工具需要大量基础设施编码或第三方工具。
编译后的代码不会知道底层数据的结构（除了它由名称值对组成），也不会知道需要哪些字段或聚合，因为确切的数据操作和结果结构是在运行时定义的，而不是在编译时。构建一个 LINQ 语句，如
EnumerableRowCollection<DataRow> query =
from order in orders.AsEnumerable()
where order.Field<bool>("OnlineOrderFlag") == true
orderby order.Field<decimal>("TotalDue")
select order;
至少需要知道 where 语句和 order by 语句的子句顺序和数量。因此，在进行了一些初步的 LINQ 阅读和研究后，我得出了结论，我需要走另一条路：SQL 到 LINQ，而不是 LINQ 到 SQL。这导致了一些其他有趣的领域，比如需要一个 SQL 解析器和动态构造复杂表达式，但它确实让我对 LINQ 有了一个很好的速成学习。
Using the Code
代码的结构由多个层组成
FileDbProvider- 此层包含一个IDataAdapter和相关的类型，如派生的DbConnection和DbCommand。这是示例用户界面与之交互的层。File.PropertyProvider- 此层包含System.IO.File属性的读取器以及其他文件属性读取器的基类型。Media.PropertyProvider- 此层知道如何访问媒体文件的标签，并使用SqlLinq层来检索和评估从数据提供程序层传入的 SQL 语句。SqlLinq- 此层包含 SQL 解析器，并公开解析后的树以及一个 API，允许评估任意数据集合。
UI 与数据提供程序层的交互方式与其他任何提供程序的交互方式类似
DataSet data = new DataSet();
MusDbConnection connection = new MusDbConnection(
"root='C:\\Music';recurse=true;FileExtensions=wma,mp3");
connection.Open();
IDataAdapter adapter = new MusDataAdapter("SELECT * FROM media", connection);
adapter.Fill(data);
然后，DataSet 就可以像应用程序一样使用，就像从 SQL Server、Oracle、Jet 或其他数据源检索到的数据集一样。
关于单元测试的说明
当您从提供的代码运行大多数单元测试时，它们都会失败，因为它们是针对一组媒体文件编写的，由于明显的原因，我没有在下载中包含这些文件。它们确实提供了 SQL 语法正常工作的基本示例，并且应该很容易指向另一组媒体并使其再次通过。
FileDbProvider 层
此程序集将执行与文件属性读取器以及 SQL 解析和执行绑定在一起。它的操作方式与大多数其他 SQL 数据提供程序类似。
连接字符串的格式为 name=value，使用分号分隔，与大多数其他数据库连接格式类似。
支持的连接字符串参数为
- root - 查询将从中搜索的根路径。此参数是必需的。查询将相对于此路径进行评估。在上面的
SELECT语句中，查询将搜索“C:\Music\soundtrack\”。 - recurse -
true|false- 指示在查询评估期间是否递归搜索目录结构。如果未提供，则默认为true。 - fileExtensions - 要包含在搜索中的文件扩展名列表。如果未提供，则默认为 *。
除了使用 SqlLinq 层处理 SQL 语句之外，在此程序集中还可以实现 IDataAdapter 和 DbDataReader，以便将结果作为 System.DataSet 返回给应用程序。这都很简单，并且有很多示例，所以我不会深入细节。
File.PropertyProvider
此程序集包含读取文件属性的基本功能。IPropertyReader 接口是任何组件都需要实现的基类型，才能包含在文件属性查询中。
public interface IFilePropertyReader
{
string Namespace { get; }
bool Match(string filePath);
IEnumerable<KeyValuePair<string, object>> Read(string path,
IEnumerable<string> selectProperties);
IEnumerable<KeyValuePair<string, object>> Read(string path);
IEnumerable<KeyValuePair<string, Type>> GetFields();
}
除了实现 IFilePropertyReader 之外，还有一些属性用于定义读取器支持的文件类型以及定义读取器类型将在 SQL 语句中绑定的“表名”。
举例来说，用于基本文件属性的类的声明如下：
[FilePropertyReader("*")]
[PropertyTableName("File")]
[TypePropertyReader(typeof(FileReflectedPropertyReader))]
class FilePropertyReader : PropertyReader
FilePropertyReader 属性将导致查询引擎使用 MediaFileProperyReader 来检索任何文件扩展名的属性。上面的 PropertyTableName 属性将此读取器绑定到一个名为 file 的逻辑表；例如，SQL 语句 SELECT * FROM file。此类读取 System.IO.File 类可访问的任何属性，例如名称、扩展名、大小等。
最后，此程序集在运行时动态加载其他文件属性读取器。它通过反射一个以 .PropertyProvider.dll 结尾的程序集来完成此操作，查找实现 IFilePropertyReader 的类。它使用一个动态的 TypeLoader 库来完成此操作。
Media.PropertyProvider
此程序集包含 IFilePropertyReader 的一个实现，它可以使用 Windows Media Format SDK 从元数据标签返回媒体属性。
[FilePropertyReader("mp3")]
[FilePropertyReader("wma")]
[FilePropertyReader("asf")]
[FilePropertyReader("wmv")]
[PropertyTableName("Media")]
class MediaFilePropertyReader : IFilePropertyReader
我从一些可以读取 ID3 v1.1 标签的简单代码开始，但想要一些更强大的功能（ASF 容器格式，其他 ID3 标签版本等）。经过一番搜索，我找到了 Windows Media Format SDK，现在实际标签值的检索都使用 WMVCore.dll 和接口 IWMMetadataEditor、IWMMetadataEditor2 以及 IWMHeaderInfo3 来完成。执行此检索的代码最初是 SDK 示例的一部分。现在它已根据此应用程序的需求和我的特定编码风格进行了修改。因此，我认为它可能比原始示例更具通用性，因为它在反序列化标签时（除了 byte 数组，它们仅作为表示其长度的字符串返回）不会进行相同的标签格式化。如果您正在寻找一些媒体标签访问代码，请查看附件代码中的 FileMediaTags 类。它应该很容易提取并根据您的需求进行调整。
检索实际标签的核心部分具有基本的 Win32 风格；调用两次方法以获取缓冲区大小，然后将它们传回以填充，以 null 结尾的字符串等。如果您曾与 Windows API 合作过，这部分不会有太多令人惊讶之处。
private static IWMHeaderInfo3 GetHeaderInfo(string path)
{
IWMMetadataEditor editor;
WMFSDKFunctions.WMCreateEditor(out editor);
IWMMetadataEditor2 editor2 = (IWMMetadataEditor2)editor;
editor2.OpenEx(path, FILE_ACCESS.GENERIC_READ, FILE_SHARE.FILE_SHARE_READ);
return (IWMHeaderInfo3)editor2;
}
private static void GetAllMediaTags(IDictionary<string, object> tags, string path)
{
IWMHeaderInfo3 headerInfo3 = GetHeaderInfo(path);
try
{
ushort wAttributeCount;
headerInfo3.GetAttributeCountEx(0, out wAttributeCount);
for (ushort wIndex = 0; wIndex < wAttributeCount; wIndex++)
{
WMT_ATTR_DATATYPE wAttribType;
string pwszName = null;
ushort wAttribNameLen = 0;
ushort pwLangIndex = 0;
uint pdwDataLength = 0;
// get the length of this attribute name
// and value in order to alloc the buffers
headerInfo3.GetAttributeByIndexEx(0,
wIndex,
pwszName,
ref wAttribNameLen,
out wAttribType,
out pwLangIndex,
null,
ref pdwDataLength);
pwszName = new String('\0', wAttribNameLen);
ReadAndAddAttribue(tags, headerInfo3, wIndex, pwszName, pdwDataLength);
}
}
finally
{
((IWMMetadataEditor)headerInfo3).Close();
Marshal.FinalReleaseComObject(headerInfo3);
headerInfo3 = null;
}
}
从每个媒体文件中检索到的标签将以 IDictionary<string, object> 的形式从加载器返回。字典键是大小写不敏感的，以匹配 SQL 解析的大小写不敏感性。当代码迭代指定目录结构中的所有文件时，它会构建一个 IList<IDictionary<string, object>>。此 IList 代表查询的结果集，其中每个 IDictionary 都类似于单行。行列表就是数据返回到提供程序层的方式。
SqlLinq 层
这就是所有精彩的 LINQ 功能所在！（好吧，实际上，这里比其他任何地方都有更多的 Linq.Expression 内容。）
SQL 解析
SQL 解析最初采用了一种非常简单的方法，几乎只是一个基础的标记器。这让我可以解析简单的 SELECT field FROM source 部分，但仅此而已。经过一两天的搜索，一无所获，直到我偶然发现了 Grammar Oriented Language Developer 或 GOLD 应用程序。这个东西最初是 Devin Cook 的硕士论文，确实是一个了不起的工具。
它所做的是接收任意语言的规则，以 BNF 格式，然后生成一套解析表，供稍后由特定语言的状态机解析引擎使用。甚至有多种语言（包括 C#）的引擎可用。此代码使用了 Vladimir Morozova 的 C# 引擎，该引擎包含在下载文件中。甚至还有一个简单的 SQL 语法规范（稍作扩展以添加列别名、布尔文字，并允许 HAVING 子句包含聚合表达式）。这里还有一篇关于 CodeProject 的很棒的 文章，更深入地解释了 GOLD 应用程序及其用法。基本上，这完美地解决了我的问题，并归功于 GOLD 应用程序的作者。
解析从叶子到树干进行，同时构建解析树。当解析器遍历标记和规则时，它会输出一个 Reduction 规则，该规则对应于语言的各个部分。一个自定义属性用于将 C# 类映射到 SQL 规则，并在解析器工作时实例化它们。
[SyntaxNode(RuleConstants.RULE_WHERECLAUSE_WHERE)]
public class WhereClause : NonTerminalNode
{
public WhereClause()
{
}
public IEnumerable<IDictionary<string, T>> Evaluate<T>(
IEnumerable<IDictionary<string, T>> source)
{
return source.Where(CreateEvaluationFunction<T>());
}
private Func<IDictionary<string, T>, bool> CreateEvaluationFunction<T>()
{
PredicateNode predicate = FindChild<PredicateNode>();
if (predicate != null)
return predicate.CreateEvaluationFunction<T>();
LiteralNode node = FindChild<LiteralNode>();
if (node != null)
return node.CreateEvaluationFunction<T>();
Debug.Assert(false);
return null;
}
}
static class SyntaxRuleFactory
{
private static TypeLoader<NonTerminalNode, int> _nodeImplTypeMap = LoadImplTypes();
public static NonTerminalNode CreateNode(Rule rule)
{
Debug.Assert(rule != null);
NonTerminalNode node = null;
if (_nodeImplTypeMap.Contains(rule.Index))
node = _nodeImplTypeMap.CreateInstance(rule.Index);
else
node = new NonTerminalNode();// if no type is bound to the rule then
// just create a base non-terminal node
node.Rule = rule;
return node;
}
private static IEnumerable<int> GetRuleIds(Type t)
{
return t.GetCustomAttributes(typeof(SyntaxNodeAttribute),
false).Select(attr => (int)((SyntaxNodeAttribute)attr).RuleConstant);
}
private static TypeLoader<NonTerminalNode, int> LoadImplTypes()
{
TypeLoader<NonTerminalNode, int> loader = new TypeLoader<NonTerminalNode, int>();
loader.SearchDirectories = false;
loader.LoadMany(GetRuleIds);
return loader;
}
}
一个看起来像 SELECT * FROM soundtrack WHERE bitrate = 128000 的 SQL 语句会生成一个解析树，如下所示：
<Select Stm> [Rule Id=RULE_SELECTSTM_SELECT Class=SelectStatement]
SELECT
<Columns> [Rule Id=RULE_COLUMNS_TIMES Class=Columns]
<Restriction> [Rule Id=RULE_RESTRICTION Class=NonTerminalNode]
*
<Into Clause> [Rule Id=RULE_INTOCLAUSE Class=NonTerminalNode]
<From Clause> [Rule Id=RULE_FROMCLAUSE_FROM Class=FromClause]
FROM
<Id Member> [Rule Id=RULE_IDMEMBER_ID Class=NodeWithId]
soundtrack
<Join Chain> [Rule Id=RULE_JOINCHAIN2 Class=NonTerminalNode]
<Where Clause> [Rule Id=RULE_WHERECLAUSE_WHERE Class=WhereClause]
WHERE
<Pred Exp> [Rule Id=RULE_PREDEXP_EQ Class=EqualityNode]
<Value> [Rule Id=RULE_VALUE_ID Class=NodeWithId]
bitrate
=
<Value> [Rule Id=RULE_VALUE_INTEGERLITERAL Class=IntegerLiteral]
128000
<Group Clause> [Rule Id=RULE_GROUPCLAUSE Class=NonTerminalNode]
<Having Clause> [Rule Id=RULE_HAVINGCLAUSE Class=NonTerminalNode]
<Order Clause> [Rule Id=RULE_ORDERCLAUSE Class=NonTerminalNode]
一旦解析树构建完成，剩下的就是遍历它，以生成适当的 LINQ 构造或适用于每个节点的表达式。例如，在 SelectStatement（SELECT 语句的根节点）中，评估函数使用各个子子句和每个子节点，根据其 SQL 语义和解析树的分支进一步修改传入的数据。
public override IEnumerable<IDictionary<string, object>> Evaluate(
IEnumerable<IDictionary<string, object>> data)
{
// constrain results by where clause conditions
if (WhereClause != null)
data = WhereClause.Evaluate(data);
// calculate any aggregated values
data = EvaluateAggregates(data);
// constrain aggrated values by having conditions
if (HavingClause != null)
data = HavingClause.Evaluate(data);
// order the results
if (OrderByClause != null)
data = OrderByClause.Evaluate(data);
// and post process (remove any intermediate columns not specified in the
// select clause)
return PostProcessResults(data);
}
OrderByClause 使用 OrderBy 和 ThenBy 扩展来执行排序。同样，GroupByClause 使用 LINQ 的分组构造来汇总源数据。
public IEnumerable<IDictionary<string, object>>
Evaluate(IEnumerable<IDictionary<string, object>> source,
IEnumerable<AggregateNode> aggregates)
{
IList<IDictionary<string, object>> list =
new List<IDictionary<string, object>>();
foreach (NodeWithId item in GroupByItems)
{
var groupBy = from d in source
group d by d.ContainsKey(item.LookupId) ?
d[item.LookupId] : DBNull.Value
into g
select g;
foreach (var g in groupBy)
{
IDictionary<string, object> dict =
new Dictionary<string, object>(StringComparer.InvariantCultureIgnoreCase);
dict.Add(item.LookupId, g.Key.ToString());
foreach (AggregateNode aggregate in aggregates)
dict.Add(aggregate.LookupId,
aggregate.Evaluate<object>(g.AsEnumerable()));
list.Add(dict);
}
}
return list;
}
public IEnumerable<NodeWithId> GroupByItems
{
get
{
return FindDescendants<NodeWithId>();
}
}
构建表达式
为了实现 WHERE 和 HAVING 子句，大量使用了 System.Linq.Expressions。这使得实现该逻辑相当直接，因为大多数 SQL 运算符在 Expression 命名空间中都有直接的对应项。唯一明显的例外是 LIKE，我使用 RegEx 实现它。表达式评估的主要任务是生成一个 Func<IDictionary<string, object>, bool>，然后可以使用它与 LINQ Where<T> 扩展方法。所有这些工作都是通过一个从 PredicateNode 开始的类层次结构完成的。这个抽象基类实现了基本功能，如构建表达式以索引输入 IDictionary、类型转换以及导航到子谓词和操作数。
public Func<IDictionary<string, T>, bool> CreateEvaluationFunction<T>()
{
ParameterExpression param = Expression.Parameter(typeof(IDictionary<string, object>),
"arg");
//traverse the tree to generate a lambda expression and then compile into a function
return Expression.Lambda<Func<IDictionary<string, object>, bool>>
(CreateOperatorExpression(param, GetLeftExpression(param)), param).Compile();
}
protected abstract Expression CreateOperatorExpression(ParameterExpression param,
Expression left);
类型转换
由于输入数据中每个字段的数据类型在构建表达式时是未知的，因此类型转换有点宽松。例如，如果一个表达式正在与一个整数字面量进行比较，则将使用 Expression.Convert 将输入转换为整数（最终使用 System.Convert）。如果无法从字面量确定类型，则有一个简单的回退系统。算术表达式将默认为实数域；例如，布尔运算默认为 bool，而大多数其他谓词默认为 string。
private LiteralNode FindCoercionType(int index)
{
if (index != 0 && index != 2)
return null;
LiteralNode node = FindChild<LiteralNode>(OppositeSide(index));
// look at what the child operand is being compared to
if (node == null && (this.Index == 0 || this.Index == 2))
node = Parent.FindChild<LiteralNode>(OppositeSide(this.Index));
// look at what the whole expression is being compared to
// if we don't find any literals in the area, look for a predicate
// expression that can drive the type coercion
if (node == null)
{
PredicateNode predicate = FindChild<PredicateNode>(OppositeSide(index));
// look at what the child operand is being compared to
if (predicate == null &&
(this.Index == 0 || this.Index == 2))
predicate = Parent.FindChild<PredicateNode>(OppositeSide(this.Index));
// look at what the whole expression is being compared to
if (predicate != null)
node = predicate.GetExpressionType();
}
return node;
}
索引字典
在此动态表达式构建示例（媒体文件标签集合）中，并非输入数据中的每个行都具有相同的字段集。这是因为并非每个媒体文件都包含相同的标签集。因此，在索引每个字典时会进行额外的步骤，首先检查 ContainsKey，如果返回 false，则返回 DbNull，这基本上允许对每一行评估相同的字段集，并允许每一行在概念上是 NULLABLE 的。这种首先检查 ContainsKey 的模式在代码的许多地方都有出现。
private ConditionalExpression CreateDereferenceExpression(ParameterExpression param,
int index)
{
NodeWithId idNode = FindChild<NodeWithId>(index);
if (idNode != null)
{
// in order to avoid KeyNotFoundExceptions this will create an expression
// of the general form:
// if(dictionary.ContainsKey(key))
// return dictionary[key];
// else
// return NULL;
MethodInfo indexerMethod = typeof(IDictionary<string, object>).GetMethod(
"get_Item");
MethodInfo containsKeyMethod = typeof(IDictionary<string, object>).GetMethod(
"ContainsKey");
LiteralNode node = GetTypeCoercionNode(index); // this is used to coerce
// the value in the dictionary to
// the correct type for comparison
Expression key = Expression.Constant(idNode.EvaluationId);
Expression containsKey = Expression.Call(param, containsKeyMethod, key);
Expression returnValue = Expression.Convert(Expression.Call(param,
indexerMethod, key), node.ValueType, node.CoercionDelegate.Method);
Expression returnNull = Expression.Constant(node.NullRepresentation,
node.ValueType);
return Expression.Condition(containsKey, returnValue, returnNull);
}
return null;
}
将它们组合在一起
所有繁重的工作都在 PredicateNode 基类中完成，每个派生类只需要在其 CreateOperatorExpression 重写中提供正确的 Expression。
[SyntaxNode(RuleConstants.RULE_PREDEXP_EQ)]
public class EqualityNode : PredicateNode
{
public EqualityNode()
{
}
protected override Expression CreateOperatorExpression(
ParameterExpression param, Expression left)
{
return Expression.Equal(left, GetRightExpression(param));
}
}
检索和评估数据
在当前实现中，评估数据集是一个两步过程。首先，客户端代码必须检索要评估的更大数据集。它可以使用解析树来指导检索，但实际的反序列化出源数据存储完全存在于 SqlLinq 程序集之外。其次，它使用 SqlLinq 代码来评估数据，该代码应用除 FROM 子句之外的所有逻辑。最终，我希望探索 IQueryable<T> 以消除两步序列。当前两步实现的好处是，SqlLinq 代码对数据的性质一无所知，除了它可以表示为 IList<IDictionary<string, object>>。这一点被稍微妥协的地方是，由于此测试用例搜索文件夹层次结构，因此 FromClause 没有像真正关系型数据源那样暴露 JOIN 链。FROM 子句包含要评估的“表”的逗号分隔列表：SELECT * FROM media, file，这将返回所有媒体和文件属性。
关注点
我在这篇文章的写作中学到了很多东西，从如何从媒体文件中提取标签，到解析和解析树的细节，再到 LINQ 和 LINQ 表达式。对于像我这样的过程式/面向对象程序员来说，LINQ 花费了一些时间才理解。我希望在时间允许的情况下继续扩展它，并看看还有哪些其他类型的数据存储可以使用文本 SQL 语句进行评估……
另一个有趣的点是，可插入的架构使得添加其他类型的文件查询变得简单。例如，可下载的代码包含一个程序集文件属性读取器，可用于查找符合自定义搜索条件的程序集。
再花一些工夫，这就可以变成一个非常通用的可重用文件搜索和分析应用程序。也许，随着我继续调整它，它就会变成这样。
待办事项
- 添加对
IQueryable<T>的支持，并提供一个可以直接访问数据的实现，而不是要求客户端代码检索数据然后评估结果。 - 实现
UPDATE语句。
历史
- 2008 年 7 月 27 日 - 首次发布。
- 2008 年 7 月 28 日 - 添加了
LIKE、NOT LIKE、IN和NOT IN运算符。 - 2008 年 10 月 20 日 - 重构了大部分底层，以支持可插入的文件属性读取器。
- 2008 年 11 月 2 日 - 进行了一些小的代码清理和文章更新。还用支持撤销/重做的实现替换了语法高亮。