LINQtoEntity 不识别方法‘ System.String Format (System.String，System.Object，System.Object)’

我有这样一个问题:

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

但也有问题。我在尝试创造任务。对于每个新任务，当我将链接文本设置为一个常量字符串(如“ Hello”)时，没有问题。然而，在上面我尝试使用发票的属性来构建属性链接文本。

我得到了这个错误:

基数{系统。SystemException } = {“ LINQtoEntity 不识别方法‘ System”。字符串格式(系统。字符串，系统。目标、系统。方法，此方法无法转换为存储表达式。”}

有人知道为什么吗? 有人知道另一种解决方法吗？

103150 次浏览
小开
最佳答案

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
});
小开

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")
});
小开

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.


提交答案