用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.

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

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生成器"尚不直接支持语法，LINQ表达式也不支持 $expr 运算符，但是您仍然可以使用BsonDocument和BsonArray或其他有效表达式进行构造.在这里，我们还键入" $unwind 结果以使用表达式而不是BsonDocument来应用 $sort 如先前所示.

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.

除其他用途外，子管道"的主要任务是减少在 $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 "coalescence"，那么您就是最好使用流畅的界面或稍后演示的其他形式.

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，而无需进一步过滤.在聚合$ lookup上还有另一篇文章.匹配管道中文档的总大小超过了最大文档大小，我实际上在讨论如何避免仅通过使用此类选项或仅在当前有效的fluent接口可用的其他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屋！