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


    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 语法的朋友可以来发表一下意见。

  • 相关阅读:
    [TAPL] 概念笔记
    k8s 亲和、反亲和、污点、容忍
    【每日一题】打卡 55
    基于Arrow的轻量线程池
    数据管理能力成熟度评估模型_企业如何实施DCMM
    PHP session反序列化学习
    lvresize与lvextend扩容逻辑卷的区别
    Java常见设计模式入门与实践
    Qt制作dll并调用,以及解决QWidget: Must construct a QApplication before a QWidget
    Windows系统提示“ping”不是内部或外部命令,也不是可运行的程序或批处理文件解决办法
  • 原文地址:https://www.cnblogs.com/IWings/p/17525166.html