usign methods within Linq projection

Question

Given the following code :

        var EmployeeXPosition = from emp in context.WTDEmployee
                                from ep in emp.WTDEmployeeXOXPosition
                                select new { 
                                    EmployeeId = emp.id, 
                                    FullNameAndPosition =  string.Format("{0} {1} : {2}", emp.FirstName, emp.LastName, ep.WTDPosition.Position) 
                                };

It gives the error :

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

Sure enough I could do :

 emp.FirstName+" "+ emp.LastName +" : " + ep.WTDPosition.Position

But it just looks ugly, any suggestions on how to use string.Format instead ?

Answer 1

I usually solve this by creating two statements - one in LinqToEntities (or ToSql, or whatever) and then another in LinqToObjects. I do what I need to do in the store, and then do a separate processing step with the objects in memory. This mixes the best of both worlds.

 var EmployeeXPosition = from emp in context.WTDEmployee
                                from ep in emp.WTDEmployeeXOXPosition
                                select new { 
                                    emp.id, 
                                    emp.FirstName, 
                                    emp.LastName,
                                    ep.WTDPosition.Position
                                };

var result = from item in EmployeeXPosition.ToList()
             select new 
             {
                EmployeeId = item.id, 
                FullNameAndPosition =  string.Format("{0} {1} : {2}", item.FirstName, item.LastName, item.Position) 
             };