Best way to concenate strings in LINQ to Entities?

IceThatJaw · December 25th, 2011, 09:50 PM

I looked online and this was the best solution I could find but it's doesn't compile correctly.

Code:

     var myFight = from r in myEntity.FIGHTs
                    let WvsL = string.Format("{0} vs {1}", r.Winner, r.Loser)  
                    select new {
                    Match = WvsL,
                    r.Result,
                    r.RoundEnd,
                    r.TimeEnd,
                    r.FOTN,
                    r.KOTN,
                    r.SOTN,
                    r.TitleFight
                    };

I want to concatenate the 2 fighters names into one column called Match. My compiler error is as follows:

Code:

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

What would be the best to go about this?

And I really appreciate the quick resonses so far. I have spent all of my schooling doing mostly C++ programming and this web stuff has so much syntax to learn it makes my head explode. lol

Imar · December 26th, 2011, 05:46 AM

Hi there,

For questions not directly related to my book - such as this one - please post in a more general ASP.NET / .NET category that you can find here: http://p2p.wrox.com/asp-net-4-539/ / http://p2p.wrox.com/

That said, LINQ to EF has trouble translating the String.Format into a SQL expression. What you can do is execute a EF query first (which results in the generated SQL) and then use LINQ to Objects to concatenate the values. Using the PlanetWrox database, something like this would work:

Code:

 
using (PlanetWroxEntities context = new PlanetWroxEntities())
{
  var reviews = (from r in context.Reviews
                where r.Authorized == true
                select new { r.Id, r.Title }).ToList();
 
  var concatenatedRviews = from r in reviews
                           select new
                           { 
                             r.Id,
                             Joined = string.Format("{0} - {1}", r.Id, r.Title)
                           };
  GridView1.DataSource = concatenatedRviews;
  GridView1.DataBind();
}

Hope this helps,

Imar