.Net下验证MongoDB 的 Linq 模式联合查询是否可用

MongoDB.Driver 类库提供了 Linq 查询的支持。然而，在使用 Linq 进行联合查询时，是否能够正确转换为 MongoDB 底层的查询语句还有待验证。今天，我将进行实验来验证一下。

输出查询语句

首先，通过订阅 MongoClientSettings 的功能，将查询语句输出。

<span style="color:#000000"><span style="background-color:#ffffff"><code class="language-cs language-csharp">            <span style="color:#0000ff">var</span> settings = MongoClientSettings.FromConnectionString(<span style="color:#a31515">"mongodb://192.168.11.137:27017"</span>);

            settings.ClusterConfigurator = cb => {
                cb.Subscribe<CommandStartedEvent>(e =>
                {
                    Debug.WriteLine( e.Command.ToString());
                });
            };
</code></span></span>

接下来，实例化 MongoClient 对象。由于我准备测试三个集合的联合查询，所以初始化了三个集合对象，并将它们转换为 Queryable 类型，以便使用 Linq 语句进行查询。

<span style="color:#000000"><span style="background-color:#ffffff"><code class="language-cs language-csharp">            <span style="color:#0000ff">var</span> client = <span style="color:#0000ff">new</span> MongoClient(settings);
            <span style="color:#0000ff">var</span> database = client.GetDatabase(<span style="color:#a31515">"MyTestDB"</span>);

            <span style="color:#0000ff">var</span> userinfos = database.GetCollection<UserInfo>(<span style="color:#a31515">"UserInfo"</span>).AsQueryable();
            <span style="color:#0000ff">var</span> ages = database.GetCollection<UserAge>(<span style="color:#a31515">"UserAges"</span>).AsQueryable();
            <span style="color:#0000ff">var</span> ageinfos = database.GetCollection<AgeInfo>(<span style="color:#a31515">"AgeInfos"</span>).AsQueryable();
</code></span></span>

简洁版联合查询

先尝试直接使用 SelectMany 查询，看是否支持联合查询。
记得先使用 MongoDB.Driver.Linq 命名空间，否则会报错。

<span style="color:#000000"><span style="background-color:#ffffff"><code class="language-cs language-csharp">            <span style="color:#0000ff">var</span> data = (<span style="color:#0000ff">from</span> u <span style="color:#0000ff">in</span> userinfos
                         <span style="color:#0000ff">from</span> a <span style="color:#0000ff">in</span> ages
                         <span style="color:#0000ff">where</span> u.Id == a.UserId
                         <span style="color:#0000ff">select</span> u).FirstOrDefault();
</code></span></span>

运行代码后，data 对象是有值的。实际输出的查询语句如下：

<span style="color:#000000"><span style="background-color:#ffffff"><code class="language-cs language-csharp">{ <span style="color:#a31515">"aggregate"</span> : <span style="color:#a31515">"UserAges"</span>, <span style="color:#a31515">"pipeline"</span> : [], <span style="color:#a31515">"cursor"</span> : { }, <span style="color:#a31515">"$db"</span> : <span style="color:#a31515">"MyTestDB"</span>, <span style="color:#a31515">"lsid"</span> : { <span style="color:#a31515">"id"</span> : CSUUID(<span style="color:#a31515">"f8e45203-f268-4fe1-9adf-b1071b3baa1f"</span>) } }

{ <span style="color:#a31515">"aggregate"</span> : <span style="color:#a31515">"UserInfo"</span>, <span style="color:#a31515">"pipeline"</span> : [{ <span style="color:#a31515">"$project"</span> : { <span style="color:#a31515">"_v"</span> : { <span style="color:#a31515">"$map"</span> : { <span style="color:#a31515">"input"</span> : [{ <span style="color:#a31515">"_id"</span> : ObjectId(<span style="color:#a31515">"64a264055a5c1963f4f330a0"</span>), <span style="color:#a31515">"UserId"</span> : ObjectId(<span style="color:#a31515">"6470620ab45534bbc84d41ec"</span>), <span style="color:#a31515">"Name"</span> : <span style="color:#a31515">"Jack"</span>, <span style="color:#a31515">"Age"</span> : <span style="color:#880000">0</span> }], <span style="color:#a31515">"as"</span> : <span style="color:#a31515">"a"</span>, <span style="color:#a31515">"in"</span> : { <span style="color:#a31515">"u"</span> : <span style="color:#a31515">"$$ROOT"</span>, <span style="color:#a31515">"a"</span> : <span style="color:#a31515">"$$a"</span> } } }, <span style="color:#a31515">"_id"</span> : <span style="color:#880000">0</span> } }, { <span style="color:#a31515">"$unwind"</span> : <span style="color:#a31515">"$_v"</span> }, { <span style="color:#a31515">"$match"</span> : { <span style="color:#a31515">"$expr"</span> : { <span style="color:#a31515">"$eq"</span> : [<span style="color:#a31515">"$_v.u._id"</span>, <span style="color:#a31515">"$_v.a.UserId"</span>] } } }, { <span style="color:#a31515">"$project"</span> : { <span style="color:#a31515">"_v"</span> : <span style="color:#a31515">"$_v.u"</span>, <span style="color:#a31515">"_id"</span> : <span style="color:#880000">0</span> } }], <span style="color:#a31515">"cursor"</span> : { }, <span style="color:#a31515">"$db"</span> : <span style="color:#a31515">"MyTestDB"</span>, <span style="color:#a31515">"lsid"</span> : { <span style="color:#a31515">"id"</span> : CSUUID(<span style="color:#a31515">"f8e45203-f268-4fe1-9adf-b1071b3baa1f"</span>) }, <span style="color:#a31515">"$clusterTime"</span> : { <span style="color:#a31515">"clusterTime"</span> : Timestamp(<span style="color:#880000">1688436977</span>, <span style="color:#880000">1</span>), <span style="color:#a31515">"signature"</span> : { <span style="color:#a31515">"hash"</span> : <span style="color:#0000ff">new</span> BinData(<span style="color:#880000">0</span>, <span style="color:#a31515">"AAAAAAAAAAAAAAAAAAAAAAAAAAA="</span>), <span style="color:#a31515">"keyId"</span> : NumberLong(<span style="color:#880000">0</span>) } } }


</code></span></span>

对于了解 MongoDB 的人来说，可以看出这并不是 MongoDB 的联合查询语句。它实际上是首先将一个表的数据取出，然后与另一个表进行比较。因此，这种方法不能用于联合查询。

Join查询

接下来，我们来看看 Join 查询的语句是什么样的。

<span style="color:#000000"><span style="background-color:#ffffff"><code class="language-cs language-csharp">            <span style="color:#0000ff">var</span> datas = (<span style="color:#0000ff">from</span> u <span style="color:#0000ff">in</span> userinfos
                         <span style="color:#0000ff">join</span> a <span style="color:#0000ff">in</span> ages <span style="color:#0000ff">on</span> u.Id <span style="color:#0000ff">equals</span> a.UserId <span style="color:#0000ff">into</span> aGroup
                         <span style="color:#0000ff">from</span> a2 <span style="color:#0000ff">in</span> aGroup.DefaultIfEmpty()
                         <span style="color:#0000ff">select</span> <span style="color:#0000ff">new</span> { 
                            User = u,
                            Age = a2
                         }).FirstOrDefault();
</code></span></span>

输出的查询语句如下：

<span style="color:#000000"><span style="background-color:#ffffff"><code class="language-cs language-csharp">{ <span style="color:#a31515">"aggregate"</span> : <span style="color:#a31515">"UserInfo"</span>, <span style="color:#a31515">"pipeline"</span> : [{ <span style="color:#a31515">"$project"</span> : { <span style="color:#a31515">"_outer"</span> : <span style="color:#a31515">"$$ROOT"</span>, <span style="color:#a31515">"_id"</span> : <span style="color:#880000">0</span> } }, { <span style="color:#a31515">"$lookup"</span> : { <span style="color:#a31515">"from"</span> : <span style="color:#a31515">"UserAges"</span>, <span style="color:#a31515">"localField"</span> : <span style="color:#a31515">"_outer._id"</span>, <span style="color:#a31515">"foreignField"</span> : <span style="color:#a31515">"UserId"</span>, <span style="color:#a31515">"as"</span> : <span style="color:#a31515">"_inner"</span> } }, { <span style="color:#a31515">"$project"</span> : { <span style="color:#a31515">"u"</span> : <span style="color:#a31515">"$_outer"</span>, <span style="color:#a31515">"aGroup"</span> : <span style="color:#a31515">"$_inner"</span>, <span style="color:#a31515">"_id"</span> : <span style="color:#880000">0</span> } }, { <span style="color:#a31515">"$project"</span> : { <span style="color:#a31515">"_v"</span> : { <span style="color:#a31515">"$map"</span> : { <span style="color:#a31515">"input"</span> : { <span style="color:#a31515">"$let"</span> : { <span style="color:#a31515">"vars"</span> : { <span style="color:#a31515">"source"</span> : <span style="color:#a31515">"$aGroup"</span> }, <span style="color:#a31515">"in"</span> : { <span style="color:#a31515">"$cond"</span> : { <span style="color:#a31515">"if"</span> : { <span style="color:#a31515">"$eq"</span> : [{ <span style="color:#a31515">"$size"</span> : <span style="color:#a31515">"$$source"</span> }, <span style="color:#880000">0</span>] }, <span style="color:#a31515">"then"</span> : [{ <span style="color:#a31515">"_id"</span> : ObjectId(<span style="color:#a31515">"000000000000000000000000"</span>), <span style="color:#a31515">"UserId"</span> : ObjectId(<span style="color:#a31515">"000000000000000000000000"</span>), <span style="color:#a31515">"Name"</span> : <span style="color:#a31515">null</span>, <span style="color:#a31515">"Age"</span> : <span style="color:#880000">0</span> }], <span style="color:#a31515">"else"</span> : <span style="color:#a31515">"$$source"</span> } } } }, <span style="color:#a31515">"as"</span> : <span style="color:#a31515">"a2"</span>, <span style="color:#a31515">"in"</span> : { <span style="color:#a31515">"User"</span> : <span style="color:#a31515">"$u"</span>, <span style="color:#a31515">"Age"</span> : <span style="color:#a31515">"$$a2"</span> } } }, <span style="color:#a31515">"_id"</span> : <span style="color:#880000">0</span> } }, { <span style="color:#a31515">"$unwind"</span> : <span style="color:#a31515">"$_v"</span> }, { <span style="color:#a31515">"$limit"</span> : NumberLong(<span style="color:#880000">1</span>) }], <span style="color:#a31515">"cursor"</span> : { }, <span style="color:#a31515">"$db"</span> : <span style="color:#a31515">"MyTestDB"</span>, <span style="color:#a31515">"lsid"</span> : { <span style="color:#a31515">"id"</span> : CSUUID(<span style="color:#a31515">"7eb2b612-b037-430e-b86c-4f349112ba56"</span>) } }

</code></span></span>

这个查询语句看起来是比较标准的 MongoDB 联合查询了。再多加一个表进行 Join 查询，看看输出的语句。

<span style="color:#000000"><span style="background-color:#ffffff"><code class="language-cs language-csharp">            <span style="color:#0000ff">var</span> datas = (<span style="color:#0000ff">from</span> u <span style="color:#0000ff">in</span> userinfos
                         <span style="color:#0000ff">join</span> a <span style="color:#0000ff">in</span> ages <span style="color:#0000ff">on</span> u.Id <span style="color:#0000ff">equals</span> a.UserId <span style="color:#0000ff">into</span> aGroup
                         <span style="color:#0000ff">from</span> a2 <span style="color:#0000ff">in</span> aGroup.DefaultIfEmpty()
                         <span style="color:#0000ff">join</span> info <span style="color:#0000ff">in</span> ageinfos <span style="color:#0000ff">on</span> a2.Id <span style="color:#0000ff">equals</span> info.AgeId <span style="color:#0000ff">into</span> bGroup
                         <span style="color:#0000ff">from</span> info2 <span style="color:#0000ff">in</span> bGroup.DefaultIfEmpty()
                         <span style="color:#0000ff">select</span> <span style="color:#0000ff">new</span> { 
                            User = u,
                            Age = a2,
                            Info = info2
                         }).FirstOrDefault();
</code></span></span>

输出查询语句：

<span style="color:#000000"><span style="background-color:#ffffff"><code class="language-cs language-csharp">{ <span style="color:#a31515">"aggregate"</span> : <span style="color:#a31515">"UserInfo"</span>, <span style="color:#a31515">"pipeline"</span> : [{ <span style="color:#a31515">"$project"</span> : { <span style="color:#a31515">"_outer"</span> : <span style="color:#a31515">"$$ROOT"</span>, <span style="color:#a31515">"_id"</span> : <span style="color:#880000">0</span> } }, { <span style="color:#a31515">"$lookup"</span> : { <span style="color:#a31515">"from"</span> : <span style="color:#a31515">"UserAges"</span>, <span style="color:#a31515">"localField"</span> : <span style="color:#a31515">"_outer._id"</span>, <span style="color:#a31515">"foreignField"</span> : <span style="color:#a31515">"UserId"</span>, <span style="color:#a31515">"as"</span> : <span style="color:#a31515">"_inner"</span> } }, { <span style="color:#a31515">"$project"</span> : { <span style="color:#a31515">"u"</span> : <span style="color:#a31515">"$_outer"</span>, <span style="color:#a31515">"aGroup"</span> : <span style="color:#a31515">"$_inner"</span>, <span style="color:#a31515">"_id"</span> : <span style="color:#880000">0</span> } }, { <span style="color:#a31515">"$project"</span> : { <span style="color:#a31515">"_v"</span> : { <span style="color:#a31515">"$map"</span> : { <span style="color:#a31515">"input"</span> : { <span style="color:#a31515">"$let"</span> : { <span style="color:#a31515">"vars"</span> : { <span style="color:#a31515">"source"</span> : <span style="color:#a31515">"$aGroup"</span> }, <span style="color:#a31515">"in"</span> : { <span style="color:#a31515">"$cond"</span> : { <span style="color:#a31515">"if"</span> : { <span style="color:#a31515">"$eq"</span> : [{ <span style="color:#a31515">"$size"</span> : <span style="color:#a31515">"$$source"</span> }, <span style="color:#880000">0</span>] }, <span style="color:#a31515">"then"</span> : [{ <span style="color:#a31515">"_id"</span> : ObjectId(<span style="color:#a31515">"000000000000000000000000"</span>), <span style="color:#a31515">"UserId"</span> : ObjectId(<span style="color:#a31515">"000000000000000000000000"</span>), <span style="color:#a31515">"Name"</span> : <span style="color:#a31515">null</span>, <span style="color:#a31515">"Age"</span> : <span style="color:#880000">0</span> }], <span style="color:#a31515">"else"</span> : <span style="color:#a31515">"$$source"</span> } } } }, <span style="color:#a31515">"as"</span> : <span style="color:#a31515">"a2"</span>, <span style="color:#a31515">"in"</span> : { <span style="color:#a31515">"<>h__TransparentIdentifier0"</span> : <span style="color:#a31515">"$$ROOT"</span>, <span style="color:#a31515">"a2"</span> : <span style="color:#a31515">"$$a2"</span> } } }, <span style="color:#a31515">"_id"</span> : <span style="color:#880000">0</span> } }, { <span style="color:#a31515">"$unwind"</span> : <span style="color:#a31515">"$_v"</span> }, { <span style="color:#a31515">"$project"</span> : { <span style="color:#a31515">"_outer"</span> : <span style="color:#a31515">"$_v"</span>, <span style="color:#a31515">"_id"</span> : <span style="color:#880000">0</span> } }, { <span style="color:#a31515">"$lookup"</span> : { <span style="color:#a31515">"from"</span> : <span style="color:#a31515">"AgeInfos"</span>, <span style="color:#a31515">"localField"</span> : <span style="color:#a31515">"_outer.a2._id"</span>, <span style="color:#a31515">"foreignField"</span> : <span style="color:#a31515">"AgeId"</span>, <span style="color:#a31515">"as"</span> : <span style="color:#a31515">"_inner"</span> } }, { <span style="color:#a31515">"$project"</span> : { <span style="color:#a31515">"<>h__TransparentIdentifier1"</span> : <span style="color:#a31515">"$_outer"</span>, <span style="color:#a31515">"bGroup"</span> : <span style="color:#a31515">"$_inner"</span>, <span style="color:#a31515">"_id"</span> : <span style="color:#880000">0</span> } }, { <span style="color:#a31515">"$project"</span> : { <span style="color:#a31515">"_v"</span> : { <span style="color:#a31515">"$map"</span> : { <span style="color:#a31515">"input"</span> : { <span style="color:#a31515">"$let"</span> : { <span style="color:#a31515">"vars"</span> : { <span style="color:#a31515">"source"</span> : <span style="color:#a31515">"$bGroup"</span> }, <span style="color:#a31515">"in"</span> : { <span style="color:#a31515">"$cond"</span> : { <span style="color:#a31515">"if"</span> : { <span style="color:#a31515">"$eq"</span> : [{ <span style="color:#a31515">"$size"</span> : <span style="color:#a31515">"$$source"</span> }, <span style="color:#880000">0</span>] }, <span style="color:#a31515">"then"</span> : [{ <span style="color:#a31515">"_id"</span> : ObjectId(<span style="color:#a31515">"000000000000000000000000"</span>), <span style="color:#a31515">"AgeId"</span> : ObjectId(<span style="color:#a31515">"000000000000000000000000"</span>), <span style="color:#a31515">"CreateTime"</span> : ISODate(<span style="color:#a31515">"0001-01-01T00:00:00Z"</span>) }], <span style="color:#a31515">"else"</span> : <span style="color:#a31515">"$$source"</span> } } } }, <span style="color:#a31515">"as"</span> : <span style="color:#a31515">"info2"</span>, <span style="color:#a31515">"in"</span> : { <span style="color:#a31515">"User"</span> : <span style="color:#a31515">"$<>h__TransparentIdentifier1.<>h__TransparentIdentifier0.u"</span>, <span style="color:#a31515">"Age"</span> : <span style="color:#a31515">"$<>h__TransparentIdentifier1.a2"</span>, <span style="color:#a31515">"Info"</span> : <span style="color:#a31515">"$$info2"</span> } } }, <span style="color:#a31515">"_id"</span> : <span style="color:#880000">0</span> } }, { <span style="color:#a31515">"$unwind"</span> : <span style="color:#a31515">"$_v"</span> }, { <span style="color:#a31515">"$limit"</span> : NumberLong(<span style="color:#880000">1</span>) }], <span style="color:#a31515">"cursor"</span> : { }, <span style="color:#a31515">"$db"</span> : <span style="color:#a31515">"MyTestDB"</span>, <span style="color:#a31515">"lsid"</span> : { <span style="color:#a31515">"id"</span> : CSUUID(<span style="color:#a31515">"bb4e2da5-bedb-4a8e-b1f0-92e5889bc71d"</span>) } }

</code></span></span>

通过三表联合查询，lookup 了两次，应该是正确的。不过里面是否有一些无用并且会影响性能的语法，熟悉 MongoDB 语法的朋友可以来发表一下意见。