documentdb与linq一起加入

本文介绍了documentdb与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.