.net 简单的 linq to sql 不支持转换为 SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/332670/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
simple linq to sql has no supported translation to SQL
提问by Nesizer
i have this in my BlogRepository
我的 BlogRepository 中有这个
public IQueryable<Subnus.MVC.Data.Model.Post> GetPosts()
{
var query = from p in db.Posts
let categories = GetCategoriesByPostId(p.PostId)
let comments = GetCommentsByPostId(p.PostId)
select new Subnus.MVC.Data.Model.Post
{
Categories = new LazyList<Category>(categories),
Comments = new LazyList<Comment>(comments),
PostId = p.PostId,
Slug = p.Slug,
Title = p.Title,
CreatedBy = p.CreatedBy,
CreatedOn = p.CreatedOn,
Body = p.Body
};
return query;
}
and
和
public IQueryable<Subnus.MVC.Data.Model.Comment> GetCommentsByPostId(int postId)
{
var query = from c in db.Comments
where c.PostId == postId
select new Subnus.MVC.Data.Model.Comment
{
Body = c.Body,
EMail = c.EMail,
Date = c.CreatedOn,
WebSite = c.Website,
Name = c.Name
};
return query;
}
private IQueryable<Subnus.MVC.Data.Model.Category> GetCategoriesByPostId(int postId)
{
var query = from c in db.Categories
join pcm in db.Post_Category_Maps on c.CategoryId equals pcm.CategoryId
where pcm.PostId == postId
select new Subnus.MVC.Data.Model.Category
{
CategoryId = c.CategoryId,
Name = c.Name
};
return query;
}
and when i aplly this filter
当我应用这个过滤器时
namespace Subnus.MVC.Data
{
public static class BlogFilters
{
public static IQueryable<Post> WherePublicIs(this IQueryable<Post> qry,bool state)
{
return from p in qry
where p.IsPublic == state
select p;
}
}
}
}
all this is in the same namespace if that help namespace Subnus.MVC.Data
如果帮助命名空间 Subnus.MVC.Data，所有这些都在同一个命名空间中
when i try to do this
当我尝试这样做时
public class BlogService : IBlogService
{
...
public IList<Post> GetPublicPosts()
{
return repository.GetPosts().WherePublicIs(true).ToList();
}
...
}
that is in the namespace Subnus.MVC.Service it throws the error
在命名空间 Subnus.MVC.Service 中它抛出错误
Method 'System.Linq.IQueryable`1[Subnus.MVC.Data.Model.Comment] GetCommentsByPostId(Int32)' has no supported translation to SQL.
回答by Bryan Watts
You are calling GetCommentsByPostIdwithin what is ultimately an expression tree. That tree, when composed in BlogService.GetPublicPosts, is converted to SQL.
您GetCommentsByPostId在最终是一个表达式树中调用。该树在 中组合时BlogService.GetPublicPosts将转换为 SQL。
During that conversion, it is just a method call, nothing more. Linq to Sql understands certain method calls, and yours is not one of them. Hence the error.
在那个转换过程中，它只是一个方法调用，仅此而已。Linq to Sql 理解某些方法调用，而你的不是其中之一。因此错误。
On the surface, this seems like it should work. You write reusable queries and compose them from other queries. However, what you are actually saying is: "during the processing of each row on the database server, call this method", which it obviously can't do. The fact that it takes an IQueryable<T>and returns an IQueryable<T>does not make it special.
从表面上看，这似乎应该可行。您编写可重用的查询并从其他查询中组合它们。但是，您实际上是在说：“在数据库服务器上处理每一行的过程中，调用此方法”，这显然是做不到的。它接受 anIQueryable<T>并返回 an的事实IQueryable<T>并没有使它特别。
Think about it this way: you are passing postIdto GetCategoriesByPostId. You can't call that method until you have a postId, and you don't have one of those until you are on the server in the query.
可以这样想：您正在传递postId给GetCategoriesByPostId. 除非您拥有postId，否则您无法调用该方法，并且在您在查询中的服务器上之前您没有其中之一。
You would probably need to define common Expression<>instances for the sub-queries and use those in the composition. I haven't thought about what this would look like but it's certainly doable.
您可能需要Expression<>为子查询定义公共实例并在组合中使用这些实例。我还没有想过这会是什么样子，但这肯定是可行的。
Edit:
编辑：
If you replace
如果你更换
let categories = GetCategoriesByPostId(p.PostId)
let comments = GetCommentsByPostId(p.PostId)
...
Categories = new LazyList<Category>(categories),
Comments = new LazyList<Comment>(comments),
with
和
Categories = new LazyList<Category>(GetCategoriesByPostId(p.PostId)),
Comments = new LazyList<Comment>(GetCommentsByPostId(p.PostId)),
the query will no longer throw an exception.
查询将不再抛出异常。
This is because letdeclares range variables, which are in scope for each row. They mustbe calculated on the server.
这是因为let声明了范围变量，这些变量在每一行的范围内。它们必须在服务器上计算。
Projections, however, allow you to put arbitrary code in assignments, which is then executed while building results on the client. This means both methods will be called, each of which will issue its own query.
然而，投影允许您将任意代码放入赋值中，然后在客户端上构建结果时执行这些代码。这意味着将调用两个方法，每个方法都会发出自己的查询。
