Converting Int to String inside a LINQ expression

Question

I'm creating a SelectList to populate a dropdown list from a LINQ query. This 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 can't seem to do that. if I try

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

I get "Unable to cast the type 'System.Int32' to type 'System.Object'. " If I try

             group_name = g.group_name + mCount.ToString()

I get "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 "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 that Int32 into a string. It seems like the only option is to create a foreach method that iterates through the GroupWithCount objects and creates a collection of SelectListItems, but that's madness. There must be a better way!

Pawan Mishra · Accepted Answer · 2011-11-17 14:53:16Z

3

If framework is not allowing you to make the change in LINQ query, you can then achieve it once you have received the resultset. In the resultset, you are already having "mCount" value, you can iterate over the collection and modify the value of group_name accordingly. For e.g.

var groups = // your query;

groups.ForEach(item => item.group_name = item.group_name + item.mCount.ToString);

answered Nov 17, 2011 at 14:53

Pawan Mishra

7,2786 gold badges31 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

AakashM · Accepted Answer · 2011-11-17 15:15:35Z

When you work with Linq To Entities, you often need to be aware of the boundary between what gets executed on the (database) server, and what gets executed on the client. A useful starting point (though by no means the entire story!) to working this out is observing when you are working with IQueryable (server) and when you are working with IEnumerable (client).

If you look at the statment you have, that works, and cursor over the var that declares groups, you will see that the compiler has inferred the type IQueryable<GroupWithCount>. That means that when you iterate groups, code will execute on the server. In order for this to work, everything in the code needs to be translatable by Entity Framework into SQL.

Entity Framework is good but not perfect, and one of the things it doesn't know how to do is translate a call to Int32.ToString() into SQL. So the whole thing gets rejected.

In this case, the fix is easy. All the information we need is in each GroupWithCount, so we can simply add an .AsEnumerable(), to force server-side evaluation of the query, and then project into the form we want:

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 the AsEnumerable will be evaluated on the client, so can include arbitrary C# code, including the required ToString call. Examining the var will now show the compiler has inferred a type of IEnumerable<GroupWithCount>.

Often it will be the case that we want to do, say, complex filtering on the server, so it wouldn't be a good idea to bring everything back with AsEnumerable - but here it's fine.

I guess second "Select" is not required. It is unnecessarily projecting the collection of "GroupWithCount" into a new collection with one field modified. Rather we can use "ForEach" and modify the original collection itself.

Collectives™ on Stack Overflow

Converting Int to String inside a LINQ expression

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related