EntityFramework NotSupportedException Join with embedded Join

Question

For some reason the code below gets this error:

An exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll but was not handled in user code

Additional information: LINQ to Entities does not recognize the method 'System.Linq.IQueryable1[DansBlog.Domain.BlogEntities.Post] Retrieve(System.Linq.Expressions.Expression1[System.Func`2[DansBlog.Domain.BlogEntities.Post,System.Boolean]])' method, and this method cannot be translated into a store expression.

Code:

var blogRepository = this.repositoryFactory.Create<Blog>(unitOfWork);
var userRepository = this.repositoryFactory.Create<User>(unitOfWork);
var postRepository = this.repositoryFactory.Create<Post>(unitOfWork);

var allResult = blogRepository.Retrieve().Join(
    userRepository.Retrieve(),
    b => b.UserId,
    u => u.Id,
    (blog, user) => new GUI.Models.Blogging.Blog()
    {
        Id = blog.Id,
        Content = blog.Content,
        Title = blog.Title,
        DateAdded = blog.DateAdded,
        User = new GUI.Models.Blogging.User()
        {
            UserId = user.Id,
            FirstName = user.FirstName,
            LastName = user.LastName,
            Email = user.Email,
            UserName = user.UserName
        },
        Posts =  postRepository.Retrieve(p => p.BlogId.Equals(blog.Id)).Join(
            userRepository.Retrieve(),
            p => p.UserId,
            u => u.Id,
            (post, postUser) => new GUI.Models.Blogging.Post()
                {
                    Content = post.Content,
                    DateAdded = post.DateAdded,
                    Id = post.Id,
                    User = new GUI.Models.Blogging.User()
                    {
                        UserId = postUser.Id,
                        UserName = postUser.UserName,
                        FirstName = postUser.FirstName,
                        LastName = postUser.LastName,
                        Email = postUser.Email
                }})});

It's saying it can't convert Retrieve into valid SQL. You have to come up with a different statement that can be converted. — jamesSampica
Because of delayed execution, EF is trying to execute Retrieve() on the database side, and doesn't know how. Have you tried assigning the results of all Retrive() calls to variables and joining that? — ESG
To be honest it is my first look into joins :) so not entirely clued up yet. I will try out the answer below as I believe that is what you're describing — Callum Linington

t3z t3z · Accepted Answer · 2014-08-10T17:51:05

Your sub-call to postRepository.Retrieve is not supported by LINQ to Entities. To make this work, you'd need to re-structure your query and pull your Posts collection earlier in your query using GroupJoin method and combine Posts with a Blog using anonymous types. The resulting query structure should look like this:

var allResult = blogRepository.Retrieve()
               .Join(userRepository.Retrieve(), b => b.UserId, u => u.Id, (blog, user) => new { blog, user})
               .GroupJoin(postRepository.Retrieve(), 
                                     .GroupJoin(userRepository.Retrieve(), p => p.UserId, pu => pu.Id, 
                                        (post, authors) => new GUI.Models.Blogging.Post()
                                        {
                                            Id = post.Id,
                                            BlogId = post.BlogId,
                                            User = authors.Select(pa => new GUI.Models.Blogging.User()
                                            {
                                                UserId = pa.Id,
                                                UserName = pa.UserName,
                                                // etc
                                            }).FirstOrDefault()
                                        }),
                      blogData => blogData.blog.Id, p => p.BlogId, (blogData, posts) => new { blogData, posts })
                 .Select(projection => new GUI.Models.Blogging.Blog()
                 {
                    Id = projection.blogData.blog.Id,
                    // etc
                    User = new GUI.Models.Blogging.User()
                    {
                        UserId = projection.blogData.user.Id,
                        UserName = projection.blogData.user.UserName,
                        // etc
                    },
                    Posts =  projection.posts
                 }
             );

Note that LINQ queries written using Method syntax are not that readable as it might seem from the first look. Consider using Query syntax when it's possible (fallback to Method if not) as it improves readability and makes your code much cleaner in most cases. For example, above query would look like:

var allResult = from blog in blogRepository.Retrieve()
                join user in userRepository.Retrieve() on b.UserId equals u.Id
                join p in postRepository.Retrieve()
                                        .GroupJoin(userRepository.Retrieve(), p => p.UserId, pu => pu.Id, 
                                            (post, authors) => new GUI.Models.Blogging.Post()
                                            {
                                                Id = post.Id,
                                                BlogId = post.BlogId,
                                                User = (from pu in authors
                                                        select new GUI.Models.Blogging.User()
                                                        {
                                                            UserId = pu.Id,
                                                            UserName = pu.UserName,
                                                            // etc
                                                        }).FirstOrDefault()
                                            }),
                          on blog.Id equals p.BlogId into posts
                 select new GUI.Models.Blogging.Blog()
                     {
                        Id = blog.Id,
                        // etc
                        User = new GUI.Models.Blogging.User()
                        {
                            UserId = user.Id,
                            UserName = user.UserName,
                            // etc
                        },
                        Posts = posts
                     }
                 );

EntityFramework NotSupportedException Join with embedded Join

1 Answers