Reputation: 801
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
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