i have this in my 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
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
Method 'System.Linq.IQueryable`1[Subnus.MVC.Data.Model.Comment] GetCommentsByPostId(Int32)' has no supported translation to SQL.
You are calling GetCommentsByPostId
within what is ultimately an expression tree. That tree, when composed in BlogService.GetPublicPosts
, is converted 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.
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.
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.
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.
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 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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With