MongoDB.Driver 类库提供了 Linq 查询的支持。然而，在使用 Linq 进行联合查询时，是否能够正确转换为 MongoDB 底层的查询语句还有待验证。今天，我将进行实验来验证一下。
输出查询语句
首先，通过订阅 MongoClientSettings 的功能，将查询语句输出。
var settings = MongoClientSettings.FromConnectionString("mongodb://192.168.11.137:27017");
settings.ClusterConfigurator = cb => {
cb.Subscribe(e =>
{
Debug.WriteLine( e.Command.ToString());
});
};
接下来，实例化 MongoClient 对象。由于我准备测试三个集合的联合查询，所以初始化了三个集合对象，并将它们转换为 Queryable 类型，以便使用 Linq 语句进行查询。
var client = new MongoClient(settings);
var database = client.GetDatabase("MyTestDB");
var userinfos = database.GetCollection("UserInfo").AsQueryable();
var ages = database.GetCollection("UserAges").AsQueryable();
var ageinfos = database.GetCollection("AgeInfos").AsQueryable();
简洁版联合查询
先尝试直接使用 SelectMany 查询，看是否支持联合查询。
记得先使用 MongoDB.Driver.Linq 命名空间，否则会报错。
var data = (from u in userinfos
from a in ages
where u.Id == a.UserId
select u).FirstOrDefault();
运行代码后，data 对象是有值的。实际输出的查询语句如下：
{ "aggregate" : "UserAges", "pipeline" : [], "cursor" : { }, "$db" : "MyTestDB", "lsid" : { "id" : CSUUID("f8e45203-f268-4fe1-9adf-b1071b3baa1f") } }
{ "aggregate" : "UserInfo", "pipeline" : [{ "$project" : { "_v" : { "$map" : { "input" : [{ "_id" : ObjectId("64a264055a5c1963f4f330a0"), "UserId" : ObjectId("6470620ab45534bbc84d41ec"), "Name" : "Jack", "Age" : 0 }], "as" : "a", "in" : { "u" : "$$ROOT", "a" : "$$a" } } }, "_id" : 0 } }, { "$unwind" : "$_v" }, { "$match" : { "$expr" : { "$eq" : ["$_v.u._id", "$_v.a.UserId"] } } }, { "$project" : { "_v" : "$_v.u", "_id" : 0 } }], "cursor" : { }, "$db" : "MyTestDB", "lsid" : { "id" : CSUUID("f8e45203-f268-4fe1-9adf-b1071b3baa1f") }, "$clusterTime" : { "clusterTime" : Timestamp(1688436977, 1), "signature" : { "hash" : new BinData(0, "AAAAAAAAAAAAAAAAAAAAAAAAAAA="), "keyId" : NumberLong(0) } } }
对于了解 MongoDB 的人来说，可以看出这并不是 MongoDB 的联合查询语句。它实际上是首先将一个表的数据取出，然后与另一个表进行比较。因此，这种方法不能用于联合查询。
Join查询
接下来，我们来看看 Join 查询的语句是什么样的。
var datas = (from u in userinfos
join a in ages on u.Id equals a.UserId into aGroup
from a2 in aGroup.DefaultIfEmpty()
select new {
User = u,
Age = a2
}).FirstOrDefault();
输出的查询语句如下：
{ "aggregate" : "UserInfo", "pipeline" : [{ "$project" : { "_outer" : "$$ROOT", "_id" : 0 } }, { "$lookup" : { "from" : "UserAges", "localField" : "_outer._id", "foreignField" : "UserId", "as" : "_inner" } }, { "$project" : { "u" : "$_outer", "aGroup" : "$_inner", "_id" : 0 } }, { "$project" : { "_v" : { "$map" : { "input" : { "$let" : { "vars" : { "source" : "$aGroup" }, "in" : { "$cond" : { "if" : { "$eq" : [{ "$size" : "$$source" }, 0] }, "then" : [{ "_id" : ObjectId("000000000000000000000000"), "UserId" : ObjectId("000000000000000000000000"), "Name" : null, "Age" : 0 }], "else" : "$$source" } } } }, "as" : "a2", "in" : { "User" : "$u", "Age" : "$$a2" } } }, "_id" : 0 } }, { "$unwind" : "$_v" }, { "$limit" : NumberLong(1) }], "cursor" : { }, "$db" : "MyTestDB", "lsid" : { "id" : CSUUID("7eb2b612-b037-430e-b86c-4f349112ba56") } }
这个查询语句看起来是比较标准的 MongoDB 联合查询了。再多加一个表进行 Join 查询，看看输出的语句。
var datas = (from u in userinfos
join a in ages on u.Id equals a.UserId into aGroup
from a2 in aGroup.DefaultIfEmpty()
join info in ageinfos on a2.Id equals info.AgeId into bGroup
from info2 in bGroup.DefaultIfEmpty()
select new {
User = u,
Age = a2,
Info = info2
}).FirstOrDefault();
输出查询语句：
{ "aggregate" : "UserInfo", "pipeline" : [{ "$project" : { "_outer" : "$$ROOT", "_id" : 0 } }, { "$lookup" : { "from" : "UserAges", "localField" : "_outer._id", "foreignField" : "UserId", "as" : "_inner" } }, { "$project" : { "u" : "$_outer", "aGroup" : "$_inner", "_id" : 0 } }, { "$project" : { "_v" : { "$map" : { "input" : { "$let" : { "vars" : { "source" : "$aGroup" }, "in" : { "$cond" : { "if" : { "$eq" : [{ "$size" : "$$source" }, 0] }, "then" : [{ "_id" : ObjectId("000000000000000000000000"), "UserId" : ObjectId("000000000000000000000000"), "Name" : null, "Age" : 0 }], "else" : "$$source" } } } }, "as" : "a2", "in" : { "h__TransparentIdentifier0" : "$$ROOT", "a2" : "$$a2" } } }, "_id" : 0 } }, { "$unwind" : "$_v" }, { "$project" : { "_outer" : "$_v", "_id" : 0 } }, { "$lookup" : { "from" : "AgeInfos", "localField" : "_outer.a2._id", "foreignField" : "AgeId", "as" : "_inner" } }, { "$project" : { "h__TransparentIdentifier1" : "$_outer", "bGroup" : "$_inner", "_id" : 0 } }, { "$project" : { "_v" : { "$map" : { "input" : { "$let" : { "vars" : { "source" : "$bGroup" }, "in" : { "$cond" : { "if" : { "$eq" : [{ "$size" : "$$source" }, 0] }, "then" : [{ "_id" : ObjectId("000000000000000000000000"), "AgeId" : ObjectId("000000000000000000000000"), "CreateTime" : ISODate("0001-01-01T00:00:00Z") }], "else" : "$$source" } } } }, "as" : "info2", "in" : { "User" : "$h__TransparentIdentifier1.h__TransparentIdentifier0.u", "Age" : "$h__TransparentIdentifier1.a2", "Info" : "$$info2" } } }, "_id" : 0 } }, { "$unwind" : "$_v" }, { "$limit" : NumberLong(1) }], "cursor" : { }, "$db" : "MyTestDB", "lsid" : { "id" : CSUUID("bb4e2da5-bedb-4a8e-b1f0-92e5889bc71d") } }
通过三表联合查询，lookup 了两次，应该是正确的。不过里面是否有一些无用并且会影响性能的语法，熟悉 MongoDB 语法的朋友可以来发表一下意见。
服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
机房租用，北京机房租用，IDC机房托管， http://www.fwqtg.net
//查看server_id show variables like ‘server_id’;服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net 机房租用，北京机房租用，IDC机房托管， http://www.fwqtg.net…
