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

Question

I have this linq query:

private void GetReceivedInvoiceTasks(User user, List<Task> tasks)
{
    var areaIds = user.Areas.Select(x => x.AreaId).ToArray();

    var taskList = from i in _db.Invoices
                   join a in _db.Areas on i.AreaId equals a.AreaId
                   where i.Status == InvoiceStatuses.Received && areaIds.Contains(a.AreaId)
                   select new Task {
                       LinkText = string.Format(Invoice {0} has been received from {1}, i.InvoiceNumber, i.Organisation.Name),
                       Link = Views.Edit
                   };
}

It has issues though. I'm trying to create tasks. For each new task when I set the link text to a constant string like "Hello" it is fine. However above I'm trying to build the property linktext using properties of the invoice.

I get this error:

base {System.SystemException} = {"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."}

Anyone know why? Anyone know an alternative way of doing this to make it work?

Answer 1

Entity Framework is trying to execute your projection on the SQL side, where there is no equivalent to string.Format. Use AsEnumerable() to force evaluation of that part with Linq to Objects.

Based on the previous answer I have given you I would restructure your query like this:

int statusReceived = (int)InvoiceStatuses.Received;
var areaIds = user.Areas.Select(x=> x.AreaId).ToArray();

var taskList = (from i in _db.Invoices
               where i.Status == statusReceived && areaIds.Contains(i.AreaId)
               select i)
               .AsEnumerable()
               .Select( x => new Task()
               {
                  LinkText = string.Format("Invoice {0} has been received from {1}", x.InvoiceNumber, x.Organisation.Name),
                  Link = Views.Edit
                });

Also I see you use related entities in the query (Organisation.Name) make sure you add the proper Include to your query, or specifically materialize those properties for later use, i.e.:

var taskList = (from i in _db.Invoices
               where i.Status == statusReceived && areaIds.Contains(i.AreaId)
               select new { i.InvoiceNumber, OrganisationName = i.Organisation.Name})
               .AsEnumerable()
               .Select( x => new Task()
               {
                  LinkText = string.Format("Invoice {0} has been received from {1}", x.InvoiceNumber, x.OrganisationName),
                  Link = Views.Edit
                });

Answer 2

IQueryable derives from IEnumerable, the main resemblance is that when you make your query it is posted to the database engine in it's language, the thin moment is where you tell C# to handle the data on the server(not client side) or to tell SQL to handle data.

So basically when you say IEnumerable.ToString(), C# gets the data collection and calls ToString() on the object. But when you say IQueryable.ToString() C# tells SQL to call ToString() on the object but there is no such method in SQL.

The drawback is that when you handle data in C# the whole collection that you are looking through must be built up in memory before C# applies the filters.

Most efficient way to do it is to make the query as IQueryable with all the filters that you can apply.

And then build it up in memory and make the data formatting in C#.

IQueryable<Customer> dataQuery = Customers.Where(c => c.ID < 100 && c.ZIP == 12345 && c.Name == "John Doe");

 var inMemCollection = dataQuery.AsEnumerable().Select(c => new
                                                  {
                                                     c.ID
                                                     c.Name,
                                                     c.ZIP,
                                                     c.DateRegisterred.ToString("dd,MMM,yyyy")
                                                   });

Answer 3

While SQL does not know what to do with a string.Format it can perform string concatenation.

If you run the following code then you should get the data you are after.

var taskList = from i in _db.Invoices
               join a in _db.Areas on i.AreaId equals a.AreaId
               where i.Status == InvoiceStatuses.Received && areaIds.Contains(a.AreaId)
               select new Task {
                   LinkText = "Invoice " + i.InvoiceNumber + "has been received from " + i.Organisation.Name),
                   Link = Views.Edit
               };

Once you actually perform the query this should be marginally faster than using AsEnumerable (at least that's what I found in my own code after having the same original error as you). If you are doing something more complex with C# then you will still need to use AsEnumerable though.