Lab Lab
Lab Lab

Reputation: 801

LINQ to Entities does not recognize the method "GetUserById"

I've tried to do this (load some data from a db to send it by JSON) But I've catched an excpetion:

Additional information: LINQ to Entities does not recognize the method 'Model.Models.User GetUserById(Int32)' method, and this method cannot be translated into a store expression

How can I avoid this exception and get the userId which I needed for the each record

Upvotes: 1

Views: 413

Answers (1)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236208

Entity Framework tries to convert your query into SQL query. But it fails to convert GetUserById method call into SQL (no surprise here). You should move this part of linq query to client side:

var favList = context.FavoriteMessages
        .Where(m => m.UserId == user.UserId && m.OnlineRoomId == user.OnlineRoomId)
        .Select(m => new {
            m.UserId,   // return user id from database
            m.Instance,
            m.CreatedOn
        })
        .AsEnumerable() // further part of query is not translated into SQL
        .Select(i => new {
            UserName = userService.GetUserById(i.UserId).Name, // get name locally
            i.Instance,
            i.CreatedOn
        }).ToList();

But if you'll think about filtering part of your query, you'll see that you are selecting only messages which have userId equal to user.UserId. That means - all of selected messages will have same user id, and what is more important - that will be id of user which you already have. And even more - you already have name of user in User.Identity.Name. So, the query should look like:

var user = userService.GetUserByName(User.Identity.Name);
var favorites = from m in context.FavoriteMessages
                where m.UserId == user.UserId && m.OnlineRoomId == user.OnlineRoomId 
                select new {
                   UserName = user.Name,
                   m.Instance,
                   m.CreatedOn
                };

return Json(favorites.ToList(), JsonRequestBehavior.AllowGet);

Upvotes: 4

Related Questions