526互联

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

发布时间 2023-07-04 17:44:07作者: IWing

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

输出查询语句

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

            var settings = MongoClientSettings.FromConnectionString("mongodb://192.168.11.137:27017");

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

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

            var client = new MongoClient(settings);
            var database = client.GetDatabase("MyTestDB");

            var userinfos = database.GetCollection<UserInfo>("UserInfo").AsQueryable();
            var ages = database.GetCollection<UserAge>("UserAges").AsQueryable();
            var ageinfos = database.GetCollection<AgeInfo>("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 语法的朋友可以来发表一下意见。