Creating a collection of SelectListItem with LINQ

Question

I'm trying to display a dropdown list of users in my view. Here is the code I'm using in my controller method:

var users = _usersRepository.Users.Select(u => new SelectListItem
                                    {
                                        Text = u.FirstName + " " + u.LastName,
                                        Value = u.UserID.ToString()
                                    }

return View(new MyViewModel { Users = users });

I get an error trying to convert UserID to a string:

LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression.

How I create a collection of SelectListItem from my entities?

Answer 1

ToString() can only be used in Linq to Objects. A simple solution is to insert .ToList() as follows:

var users = _usersRepository.Users.ToList().Select(u => new SelectListItem
                                    {
                                        Text = u.FirstName + " " + u.LastName,
                                        Value = u.UserID.ToString()
                                    });

return View(new MyViewModel { Users = users });

This is going to return all users from your User table. If you can reduce the amount of users obtained from the database your query will be more efficient, e.g.

var users = _usersRepository.Users.Where( u => .... ).ToList().Select(u => new SelectListItem
                                    {
                                        Text = u.FirstName + " " + u.LastName,
                                        Value = u.UserID.ToString()
                                    });

return View(new MyViewModel { Users = users });