documentdb与linq一起加入 [英] documentdb join with linq

问题描述

我正在尝试使用linq在DocumentDb上运行以下查询

I am trying to run the following query on DocumentDb with linq

SELECT p.id 
FROM p JOIN filter IN p.Filters
WHERE filter.Id = "686e4c9c-f1ab-40ce-8472-cc5d63597263" 
OR filter.Id = "caa2c2a0-cc5b-42e3-9943-dcda776bdc20"

我的json就是这样

 {
  "id": "a3dc570b-26e2-40a9-8777-683186965f78",
  "Filters": [
    {
      "Id": "686e4c9c-f1ab-40ce-8472-cc5d63597263"
    },
    {
      "Id": "caa2c2a0-cc5b-42e3-9943-dcda776bdc20"
    }
  ]
}

我发现我应该使用SelectMany进行联接，但是我无法使其正常工作，我尝试了不同的尝试，但始终会遇到运行时错误.

I found that I'm supposed to use SelectMany to do the join, but I can't get it to work, I've tried different things but always get runtime errors.

这是我尝试过的查询，它给出指定的参数超出有效值范围.参数名称:意外的SQL标量表达式种类转换"

edit: Here's the query I've tried, this gives "Specified argument was out of the range of valid values.Parameter name: Unexpected Sql Scalar expression kind Conversion"

    public void TryOutQuery(IEnumerable<string> ids)
    {
        var options = new FeedOptions { MaxItemCount = 10, RequestContinuation = "" };
        var queryable = _client.CreateDocumentQuery<PostModel>(UriFactory.CreateCollectionUri(_collectionConn.DatabaseName, _collectionConn.CollectionName), options)                
            .SelectMany(p => p.Filters
            .Where(f => ids.Contains(f.Id))
            .Select(f => p)
            .Where(post => post.Status == PostStatus.Published)
            )
            ;
        var query = queryable.AsDocumentQuery();
        var asyn = query.ExecuteNextAsync<PostModel>();
        var result = asyn.Result; 
    }

我还尝试了另一种顺序，该顺序给出了查询输出类型不一致的类型:Post.PostModel和Post.Filter"

I also tried in another order, which gives "Inconsistent types for query output type: Post.PostModel and Post.Filter"

    public void TryOutQuery(IEnumerable<string> ids)
    {
        var options = new FeedOptions { MaxItemCount = 10, RequestContinuation = "" };
        var queryable = _client.CreateDocumentQuery<PostModel>(UriFactory.CreateCollectionUri(_collectionConn.DatabaseName, _collectionConn.CollectionName), options)
            .Where(p => p.Status == PostStatus.Published)
            .SelectMany(p => p.Filters
            .Where(f => ids.Contains(f.Id))
            .Select(f => p)                
            )
            ;
        var query = queryable.AsDocumentQuery();
        var asyn = query.ExecuteNextAsync<PostModel>();
        var result = asyn.Result; 
    }

这是我的模特

public class Filter
{
    public Filter(string id)
    {
        Id = id;
    }       
    public String Id { get; set; }
}

public class PostModel
{    
    public PostModel()
    {
        _filters = new List<Filter>();
    }
    [JsonProperty("id")]
    public String Id { get; set; }
    //Since DocumentDb doesn't work properly with IList
    public IEnumerable<Filter> Filters { get { return _filters; } }
    private IList<Filter> _filters;
    public PostStatus Status { get; set; }

    public void AddFilter(string filterId)
    {
        _filters.Add(new Filter(filterId));
    }

}

public enum PostStatus
{
    Draft,
    Published,
    Deleted
}

推荐答案

我将使用带有两个JOIN和一个过滤器的查询作为示例:

I'll use a query w/ two JOINs and a filter as an example:

// SQL
var familiesChildrenAndPets = client.CreateDocumentQuery<dynamic>(collectionLink,
        "SELECT f.id as family, c.FirstName AS child, p.GivenName AS pet " +
        "FROM Families f " +
        "JOIN c IN f.Children " +
        "JOIN p IN c.Pets " +
        "WHERE p.GivenName = 'Fluffy'");

// LINQ
var familiesChildrenAndPets = client.CreateDocumentQuery<Family>(collectionLink)
        .SelectMany(family => family.Children
        .SelectMany(child => child.Pets
        .Where(pet => pet.GivenName == "Fluffy")
        .Select(pet  => new
        {
            family = family.Id,
            child = child.FirstName,
            pet = pet.GivenName
        }
        )));

查看此DocumentDB Github存储库以获取更多的示例.

Check out this DocumentDB Github repo for more examples.

这篇关于documentdb与linq一起加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！