使用 C# 聚合 $lookup [英] Aggregate $lookup with C#

问题描述

我有以下 MongoDb 查询工作:

I have the following MongoDb query working:

db.Entity.aggregate(
    [
        {
            "$match":{"Id": "12345"}
        },
        {
            "$lookup": {
                "from": "OtherCollection",
                "localField": "otherCollectionId",
                "foreignField": "Id",
                "as": "ent"
            }
        },
        { 
            "$project": { 
                "Name": 1,
                "Date": 1,
                "OtherObject": { "$arrayElemAt": [ "$ent", 0 ] } 
            }
        },
        { 
            "$sort": { 
                "OtherObject.Profile.Name": 1
            } 
        }
    ]
)

这会检索与来自另一个集合的匹配对象连接的对象列表.

This retrieves a list of objects joined with a matching object from another collection.

有人知道我如何在 C# 中使用 LINQ 或使用这个确切的字符串吗?

Does anybody know how I can use this in C# using either LINQ or by using this exact string?

我尝试使用以下代码，但似乎找不到 QueryDocument 和 MongoCursor 的类型 - 我认为它们已被弃用?

I tried using the following code but it can't seem to find the types for QueryDocument and MongoCursor - I think they've been deprecated?

BsonDocument document = MongoDB.Bson.Serialization.BsonSerializer.Deserialize<BsonDocument>("{ name : value }");
QueryDocument queryDoc = new QueryDocument(document);
MongoCursor toReturn = _connectionCollection.Find(queryDoc);

推荐答案

不需要解析JSON.这里的一切实际上都可以直接使用 LINQ 或 Aggregate Fluent 接口完成.

There is no need to parse the JSON. Everything here can actually be done directly with either LINQ or the Aggregate Fluent interfaces.

只是使用一些演示课程，因为这个问题并没有太多的继续.

Just using some demonstration classes because the question does not really give much to go on.

基本上我们这里有两个集合，分别是

Basically we have two collections here, being

实体

{ "_id" : ObjectId("5b08ceb40a8a7614c70a5710"), "name" : "A" }
{ "_id" : ObjectId("5b08ceb40a8a7614c70a5711"), "name" : "B" }

和其他人

{
        "_id" : ObjectId("5b08cef10a8a7614c70a5712"),
        "entity" : ObjectId("5b08ceb40a8a7614c70a5710"),
        "name" : "Sub-A"
}
{
        "_id" : ObjectId("5b08cefd0a8a7614c70a5713"),
        "entity" : ObjectId("5b08ceb40a8a7614c70a5711"),
        "name" : "Sub-B"
}

以及将它们绑定到的几个类，就像非常基本的示例一样:

And a couple of classes to bind them to, just as very basic examples:

public class Entity
{
  public ObjectId id;
  public string name { get; set; }
}

public class Other
{
  public ObjectId id;
  public ObjectId entity { get; set; }
  public string name { get; set; }
}

public class EntityWithOthers
{
  public ObjectId id;
  public string name { get; set; }
  public IEnumerable<Other> others;
}

 public class EntityWithOther
{
  public ObjectId id;
  public string name { get; set; }
  public Other others;
}

查询

流畅的界面

var listNames = new[] { "A", "B" };

var query = entities.Aggregate()
    .Match(p => listNames.Contains(p.name))
    .Lookup(
      foreignCollection: others,
      localField: e => e.id,
      foreignField: f => f.entity,
      @as: (EntityWithOthers eo) => eo.others
    )
    .Project(p => new { p.id, p.name, other = p.others.First() } )
    .Sort(new BsonDocument("other.name",-1))
    .ToList();

请求发送到服务器:

[
  { "$match" : { "name" : { "$in" : [ "A", "B" ] } } },
  { "$lookup" : { 
    "from" : "others",
    "localField" : "_id",
    "foreignField" : "entity",
    "as" : "others"
  } }, 
  { "$project" : { 
    "id" : "$_id",
    "name" : "$name",
    "other" : { "$arrayElemAt" : [ "$others", 0 ] },
    "_id" : 0
  } },
  { "$sort" : { "other.name" : -1 } }
]

可能是最容易理解的，因为流畅的界面与一般的 BSON 结构基本相同.$lookup 阶段包含所有相同的论点和 $arrayElemAt 是用 First() 表示.对于 $sort 你可以简单提供 BSON 文档或其他有效表达式.

Probably the easiest to understand since the fluent interface is basically the same as the general BSON structure. The $lookup stage has all the same arguments and the $arrayElemAt is represented with First(). For the $sort you can simply supply a BSON document or other valid expression.

另一种形式是 $lookup 带有用于 MongoDB 3.6 及更高版本的子管道语句.

An alternate is the newer expressive form of $lookup with a sub-pipeline statement for MongoDB 3.6 and above.

BsonArray subpipeline = new BsonArray();

subpipeline.Add(
  new BsonDocument("$match",new BsonDocument(
    "$expr", new BsonDocument(
      "$eq", new BsonArray { "$$entity", "$entity" }  
    )
  ))
);

var lookup = new BsonDocument("$lookup",
  new BsonDocument("from", "others")
    .Add("let", new BsonDocument("entity", "$_id"))
    .Add("pipeline", subpipeline)
    .Add("as","others")
);

var query = entities.Aggregate()
  .Match(p => listNames.Contains(p.name))
  .AppendStage<EntityWithOthers>(lookup)
  .Unwind<EntityWithOthers, EntityWithOther>(p => p.others)
  .SortByDescending(p => p.others.name)
  .ToList();

请求发送到服务器:

[ 
  { "$match" : { "name" : { "$in" : [ "A", "B" ] } } },
  { "$lookup" : {
    "from" : "others",
    "let" : { "entity" : "$_id" },
    "pipeline" : [
      { "$match" : { "$expr" : { "$eq" : [ "$$entity", "$entity" ] } } }
    ],
    "as" : "others"
  } },
  { "$unwind" : "$others" },
  { "$sort" : { "others.name" : -1 } }
]

Fluent "Builder" 还不直接支持语法，LINQ 表达式也不支持 $expr 运算符，但是您仍然可以使用 BsonDocument 和 BsonArray 或其他有效表达式进行构造.在这里，我们还键入" $unwind 结果以应用 $sort 使用表达式而不是前面所示的 BsonDocument.

The Fluent "Builder" does not support the syntax directly yet, nor do LINQ Expressions support the $expr operator, however you can still construct using BsonDocument and BsonArray or other valid expressions. Here we also "type" the $unwind result in order to apply a $sort using an expression rather than a BsonDocument as shown earlier.

除了其他用途之外，子管道"的主要任务是减少 $lookup.此外，这里的 $unwind实际上被合并"的目的关于服务器执行的 $lookup 语句，因此这通常比仅获取结果数组的第一个元素更有效.

Aside from other uses, a primary task of a "sub-pipeline" is to reduce the documents returned in the target array of $lookup. Also the $unwind here serves a purpose of actually being "merged" into the $lookup statement on server execution, so this is typically more efficient than just grabbing the first element of the resulting array.

var query = entities.AsQueryable()
    .Where(p => listNames.Contains(p.name))
    .GroupJoin(
      others.AsQueryable(),
      p => p.id,
      o => o.entity,
      (p, o) => new { p.id, p.name, other = o.First() }
    )
    .OrderByDescending(p => p.other.name);

请求发送到服务器:

[ 
  { "$match" : { "name" : { "$in" : [ "A", "B" ] } } },
  { "$lookup" : {
    "from" : "others",
    "localField" : "_id",
    "foreignField" : "entity",
    "as" : "o"
  } },
  { "$project" : {
    "id" : "$_id",
    "name" : "$name",
    "other" : { "$arrayElemAt" : [ "$o", 0 ] },
    "_id" : 0
  } },
  { "$sort" : { "other.name" : -1 } }
]

这几乎是相同的，只是使用了不同的接口并产生了稍微不同的 BSON 语句，实际上只是因为函数语句中的简化命名.这确实带来了另一种可能性，即简单地使用 $unwind 从 SelectMany() 产生:

This is almost identical but just using the different interface and produces a slightly different BSON statement, and really only because of the simplified naming in the functional statements. This does bring up the other possibility of simply using an $unwind as produced from a SelectMany():

var query = entities.AsQueryable()
  .Where(p => listNames.Contains(p.name))
  .GroupJoin(
    others.AsQueryable(),
    p => p.id,
    o => o.entity,
    (p, o) => new { p.id, p.name, other = o }
  )
  .SelectMany(p => p.other, (p, other) => new { p.id, p.name, other })
  .OrderByDescending(p => p.other.name);

请求发送到服务器:

[
  { "$match" : { "name" : { "$in" : [ "A", "B" ] } } },
  { "$lookup" : {
    "from" : "others",
    "localField" : "_id",
    "foreignField" : "entity",
    "as" : "o"
  }},
  { "$project" : {
    "id" : "$_id",
    "name" : "$name",
    "other" : "$o",
    "_id" : 0
  } },
  { "$unwind" : "$other" },
  { "$project" : {
    "id" : "$id",
    "name" : "$name",
    "other" : "$other",
    "_id" : 0
  }},
  { "$sort" : { "other.name" : -1 } }
]

通常放置一个 $unwind 直接跟在 $lookup 其实就是聚合的"优化模式"框架.然而，.NET 驱动程序确实在这种组合中搞砸了，它通过在两者之间强制使用 $project 而不是在 "as" 上使用隐含的命名.如果不是这样，这实际上比 $arrayElemAt 当你知道你有一个"相关的结果时.如果你想要 $unwind "合并"，那么你最好使用流畅的界面，或者稍后演示的不同形式.

Normally placing an $unwind directly following $lookup is actually an "optimized pattern" for the aggregation framework. However the .NET driver does mess this up in this combination by forcing a $project in between rather than using the implied naming on the "as". If not for that, this is actually better than the $arrayElemAt when you know you have "one" related result. If you want the $unwind "coalescence", then you are better off using the fluent interface, or a different form as demonstrated later.

var query = from p in entities.AsQueryable()
            where listNames.Contains(p.name) 
            join o in others.AsQueryable() on p.id equals o.entity into joined
            select new { p.id, p.name, other = joined.First() }
            into p
            orderby p.other.name descending
            select p;

请求发送到服务器:

[
  { "$match" : { "name" : { "$in" : [ "A", "B" ] } } },
  { "$lookup" : {
    "from" : "others",
    "localField" : "_id",
    "foreignField" : "entity",
    "as" : "joined"
  } },
  { "$project" : {
    "id" : "$_id",
    "name" : "$name",
    "other" : { "$arrayElemAt" : [ "$joined", 0 ] },
    "_id" : 0
  } },
  { "$sort" : { "other.name" : -1 } }
]

一切都非常熟悉，实际上只是功能命名.就像使用 $unwind选项:

All pretty familiar and really just down to functional naming. Just as with using the $unwind option:

var query = from p in entities.AsQueryable()
            where listNames.Contains(p.name) 
            join o in others.AsQueryable() on p.id equals o.entity into joined
            from sub_o in joined.DefaultIfEmpty()
            select new { p.id, p.name, other = sub_o }
            into p
            orderby p.other.name descending
            select p;

请求发送到服务器:

[ 
  { "$match" : { "name" : { "$in" : [ "A", "B" ] } } },
  { "$lookup" : {
    "from" : "others",
    "localField" : "_id",
    "foreignField" : "entity",
    "as" : "joined"
  } },
  { "$unwind" : { 
    "path" : "$joined", "preserveNullAndEmptyArrays" : true
  } }, 
  { "$project" : { 
    "id" : "$_id",
    "name" : "$name",
    "other" : "$joined",
    "_id" : 0
  } }, 
  { "$sort" : { "other.name" : -1 } }
]

实际上是在使用 "优化合并" 形式.译者还是坚持要加一个$project 因为我们需要中间的 select 来使语句有效.

Which actually is using the "optimized coalescence" form. The translator still insists on adding a $project since we need the intermediate select in order to make the statement valid.

因此，有很多方法可以基本上得出具有完全相同结果的基本相同查询语句.虽然您可以"将 JSON 解析为 BsonDocument 表单并将其提供给流畅的 Aggregate() 命令，但通常最好使用自然构建器或 LINQ 接口，因为它们轻松映射到同一个语句.

So there are quite a few ways to essentially arrive at what is basically the same query statement with exactly the same results. Whilst you "could" parse the JSON to BsonDocument form and feed this to the fluent Aggregate() command, it's generally better to use the natural builders or the LINQ interfaces as they do easily map onto the same statement.

$unwind 主要显示，因为即使使用单一"匹配，合并"形式实际上比使用 $arrayElemAt 获取第一个"数组元素.考虑到 BSON 限制之类的事情，这甚至变得更加重要，其中 $lookup 目标数组可能会导致父文档超过 16MB，而无需进一步过滤.Aggregate $lookup 匹配管道中文档的总大小超过了最大文档大小上的另一篇文章我实际上讨论了如何避免使用此类选项或其他 Lookup() 语法仅在此时可用于流畅界面的限制.

The options with $unwind are largely shown because even with a "singular" match that "coalescence" form is actually far more optimal then using $arrayElemAt to take the "first" array element. This even becomes more important with considerations of things like the BSON Limit where the $lookup target array could cause the parent document to exceed 16MB without further filtering. There is another post here on Aggregate $lookup Total size of documents in matching pipeline exceeds maximum document size where I actually discuss how to avoid that limit being hit by using such options or other Lookup() syntax available to the fluent interface only at this time.

这篇关于使用 C# 聚合 $lookup的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！