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?

take a look at this: http://stackoverflow.com/questions/5899683/linq-to-entities-does-not-recognize-the-method-system-string-tostring-method — Sofian Hnaide, Mar 21 '12 at 20:14

score 40 · Accepted Answer · edited Dec 07 '16 at 23:51

40

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 });

edited Dec 07 '16 at 23:51

SteveCav

6,649
1
50
52

answered Mar 21 '12 at 20:12

Phil

42,255
9
100
100

1

if you don't want to receive all columns you can also use : `var users = (from u in _usersRepository.Users where .... select new {u.FirstName, u.LastName, u.UserID}).ToList().Select(u => new SelectListItem { Text = u.FirstName + " " + u.LastName, Value = u.UserID.ToString() });` – arun.m Dec 12 '13 at 10:12
What if we want it Distinct by `FirstName`? – Halter May 18 '17 at 15:31
@Halter You wouldn't be able to do that in a one-liner with Linq. After getting your Text/Value pairs, you'd have to iterate through each item, add it to a List, then loop through your Linq result again and check if the List contained your item, already - adding the items to a new "corrected list" if they were ok. – vapcguy Oct 04 '18 at 23:25

score 9 · Answer 2 · answered Mar 21 '12 at 20:23

9

I think you're looking for SqlFunctions

using System.Data.Objects.SqlClient;

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

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

answered Mar 21 '12 at 20:23

Sorax

2,205
13
14

Will this preserve deferred execution of the query, though? Because if so (I honestly don't know off the top of my head), this might be a better option if that is important. – James McConnell Mar 21 '12 at 22:48
1

Deferred execution is preserved. LINQ to Entities providers are able to parse the methods of `SqlFunctions` into their equivalent SQL. So you are able to compose and execute this query the same as a more typical query. – Sorax Mar 22 '12 at 13:29
Good to know! Bookmarking this question for future reference, thanks for the clarification! – James McConnell Mar 22 '12 at 17:17

Creating a collection of SelectListItem with LINQ

2 Answers2

Linked