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 語法的朋友可以來發表一下意見。