CODEWITHSUNDEEP
c#linqlinq-to-entities
roryok
roryok

Reputation: 9645

Converting Int to String inside a LINQ expression

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!

Upvotes: 4

Views: 5380

Answers (2)

AakashM
AakashM

Reputation: 63338

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.

Upvotes: 2

Pawan Mishra
Pawan Mishra

Reputation: 7268

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

Upvotes: 3

Related Questions