Convert Int to String inside LINQ expression

Question

I am creating a SelectList to populate a dropdown from a LINQ query. It works:

// my temp class for including the member count public class GroupWithCount { public string group_name { get; set; } public int group_id { get; set; } public int members { get; set; } } var groups = from g in DB.Groups where g.user_id == user_id let mC = (from c in DB.Contacts where c.group_id == g.group_id select c).Count() select new GroupWithCount { members = mC, group_id = g.group_id, group_name = g.group_name }; model.Groups = new SelectList(groups, "group_id", "group_name"); 

However, I want the selectItem text to be in the format "group_name (members)", but I cannot do this. if i try

  select new GroupWithCount { members = mCount, group_id = g.group_id, group_name = g.group_name + mCount }; 

I get "Cannot apply type" System.Int32 "to type" System.Object "." If i try

  group_name = g.group_name + mCount.ToString() 

I get the "LINQ to Entities does not recognize the method" System.String ToString () "method". If i try

  group_name = string.Format("{0}{1}", g.group_name,mCount) 

I get the "LINQ to Entities does not recognize the method" System.String Format (System.String, System.Object, System.Object) "method".

It just won't let me convert this Int32 to string. It seems the only option is to create a foreach method that iterates through GroupWithCount objects and creates a SelectListItems collection, but this is crazy. There must be a better way!

Answer 1

If the environment does not allow you to make changes to the LINQ query, you can achieve it after receiving a result set. In the result set, you already have the value "mCount", you can iterate over the collection and change the value of group_name accordingly. E.g.

 var groups = // your query; groups.ForEach(item => item.group_name = item.group_name + item.mCount.ToString); 

Answer 2

When you work with Linq To Entities, you often need to know the boundary between what is running on the server (database) and what is running on the client. A useful starting point (although by no means the whole story!), To work on this, it is observed when you work with IQueryable (server) and when you work with IEnumerable (client).

If you look at the statute that you have, it works and hover over var declaring groups , you will see that the compiler has IQueryable<GroupWithCount> type IQueryable<GroupWithCount> . This means that when the group iteration is repeated, the code will be executed on the server. For this to work, everything in the code must be translatable by the Entity Framework in SQL.

The Entity Framework is good, but not perfect, and one of the things that it does not know how to do is translate the Int32.ToString() call into SQL. Therefore, all this is rejected.

In this case, fixing is easy. All the information we need is in every GroupWithCount , so we can simply add .AsEnumerable() to force a server-side request evaluation and then project it into the desired form:

 var groups = (from g in DB.Groups where g.user_id == user_id let mC=(from c in DB.Contacts where c.group_id == g.group_id select c).Count() select new GroupWithCount { members = mC, group_id = g.group_id, group_name = g.group_name }) .AsEnumerable() // come to the client // next Select is evaluated on the client .Select(gwc => new GroupWithCount { members = gwc.members, group_id = gwc.group_id, group_name = gwc.group_name + gwc.members.ToString() }; 

Everything after AsEnumerable will be evaluated on the client, so it can include arbitrary C # code, including the required ToString call. Examining var will now show that the compiler has IEnumerable<GroupWithCount> type IEnumerable<GroupWithCount> .

Often this will be the case that we want to do, say, complex filtering on the server, so it would be nice to return everything with AsEnumerable - but here it is fine.