简单的 linq to sql 不支持转换为 SQL [英] simple linq to sql has no supported translation to SQL

问题描述

我的博客库中有这个

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;
    }

和

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;
    }

当我应用这个过滤器时

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;
    }
 }

}

如果帮助命名空间 Subnus.MVC.Data，所有这些都在同一个命名空间中

all this is in the same namespace if that help namespace Subnus.MVC.Data

当我尝试这样做时

public class BlogService : IBlogService
{
...
    public IList<Post> GetPublicPosts()
    {
         return repository.GetPosts().WherePublicIs(true).ToList();
    }
 ...
 }

在命名空间 Subnus.MVC.Service 中它抛出错误

that is in the namespace Subnus.MVC.Service it throws the error

Method 'System.Linq.IQueryable`1[Subnus.MVC.Data.Model.Comment] GetCommentsByPostId(Int32)' has no supported translation to SQL.

推荐答案

您正在最终是一个表达式树中调用 GetCommentsByPostId.该树在 BlogService.GetPublicPosts 中组合时会转换为 SQL.

You are calling GetCommentsByPostId within what is ultimately an expression tree. That tree, when composed in BlogService.GetPublicPosts, is converted to SQL.

在那个转换过程中，它只是一个方法调用，仅此而已.Linq to 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.

从表面上看，这似乎应该可行.您编写可重用的查询并从其他查询中组合它们.但是，您实际上是在说:在数据库服务器上处理每一行的过程中，调用此方法"，这显然是做不到的.它接受一个 IQueryable 并返回一个 IQueryable 的事实并没有使它特别.

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.

这样想:您将 postId 传递给 GetCategoriesByPostId.在您拥有 postId 之前，您无法调用该方法，并且在您在查询中的服务器上之前，您没有其中之一.

Think about it this way: you are passing postId to 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.

您可能需要为子查询定义通用的 Expression<> 实例并在组合中使用这些实例.我还没有想过这会是什么样子，但它肯定是可行的.

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.

如果更换

let categories = GetCategoriesByPostId(p.PostId)
let comments = GetCommentsByPostId(p.PostId)
...
Categories = new LazyList<Category>(categories),
Comments = new LazyList<Comment>(comments),

与

Categories = new LazyList<Category>(GetCategoriesByPostId(p.PostId)),
Comments = new LazyList<Comment>(GetCommentsByPostId(p.PostId)),

查询将不再抛出异常.

这是因为 let 声明了范围变量，这些变量在每一行的范围内.它们必须在服务器上计算.

This is because let declares range variables, which are in scope for each row. They must be calculated on the server.

然而，投影允许您将任意代码放入赋值中，然后在客户端上构建结果时执行这些代码.这意味着将调用两个方法，每个方法都会发出自己的查询.

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.

这篇关于简单的 linq to sql 不支持转换为 SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！