Lab Lab Lab Lab -4 years ago 38
ASP.NET (C#) Question

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)

public JsonResult LoadFavorites()
{
var user = userService.GetUserByName(User.Identity.Name);
var favList =
context.FavoriteMessages
.Where(x => x.UserId == user.UserId && x.OnlineRoomId == user.OnlineRoomId)
.Select(i => new
{
UserName = userService.GetUserById(i.UserId).Name,
i.Instance,
i.CreatedOn
}).ToList();
return Json(favList, JsonRequestBehavior.AllowGet);


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

Answer Source

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);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download