LINQ的"无法转换前pression ......到SQL,不能把它作为一个地方的前pression&QUOT。 [英] Linq "Could not translate expression... into SQL and could not treat it as a local expression."
问题描述
我开始了<一个href=\"http://stackoverflow.com/questions/1262736/only-one-ex$p$pssion-can-be-specified-in-the-select-list-when-the-subquery-is-not\">this问题,这我有点回答<一个href=\"http://stackoverflow.com/questions/1262736/only-one-ex$p$pssion-can-be-specified-in-the-select-list-when-the-subquery-is-not/1262794#1262794\">there,现在我在这里要求更根本的问题。我已经简化了查询到这一点:
I started out with this question, which I sort of answered there, and now I'm asking the more fundamental question here. I've simplified the query down to this:
var q = from ent in LinqUtils.GetTable<Entity>()
from tel in ent.Telephones.DefaultIfEmpty()
select new {
Name = ent.FormattedName,
Tel = tel != null ? tel.FormattedNumber : "" // this is what causes the error
};
tel.FormattedNumber
是结合了编号
和扩展$属性C $ C>字段成整齐的格式化字符串。而这里的导致错误:
tel.FormattedNumber
is a property that combines the Number
and Extension
fields into a neatly formatted string. And here's the error that results:
System.InvalidOperationException: Could not translate expression 'Table(Entity).SelectMany(ent => ent.Telephones.DefaultIfEmpty(), (ent, tel) => new <>f__AnonymousType0`2(Name = ent.FormattedName, Tel = IIF((tel != null), tel.FormattedNumber, "")))' into SQL and could not treat it as a local expression.
如果我更改参考上述从 FormattedNumber
来只是普通的编号
,一切工作正常。
If I change the reference above from FormattedNumber
to just plain Number
, everything works fine.
不过,我想格式化数字在我的名单上完美呈现。你有什么建议的最巧妙的,这样做的干净的方式?
But I do want the formatted number to display nicely in my list. What do you recommend as the neatest, cleanest way of doing so?
推荐答案
您可以使用 AsEnumerable
的实体,但是这将迫使它带回所有列(即使不使用);或许反而是这样的:
You could use AsEnumerable
on the entity, but that would force it to bring back all the columns (even if not used); perhaps instead something like:
var q1 = from ent in LinqUtils.GetTable<Entity>()
from tel in ent.Telephones.DefaultIfEmpty()
select new {
Name = ent.FormattedName,
Number = (tel == null ? null : ent.Number),
Extension = (tel == null ? null : ent.Extension)
};
var q2 = from row in q1.AsEnumerable()
select new {
row.Name,
FormattedNumber = FormatNumber(row.Number, row.Extension)
};
其中, FormatNumber
一些方法,它采用了两个合并它们,从您的其他(财产)code presumably再使用。
where FormatNumber
is some method that takes the two and merges them, presumably re-used from your other (property) code.
使用LINQ到SQL,另一个选择是揭露一个UDF上,做数据库内的数据格式化上下文;稍有不同的例子:
With LINQ-to-SQL, another option is to expose a UDF on the data-context that does the formatting inside the database; a slightly different example:
var qry = from cust in ctx.Customers // and tel
select new {
cust.Name,
FormattedNumber = ctx.FormatNumber(tel.Number, tel.Extension)
};
(将做的工作在数据库;不论这是否是一个好主意;-p)
(which will do the work at the database; whether or not that is a good idea ;-p)
这篇关于LINQ的&QUOT;无法转换前pression ......到SQL,不能把它作为一个地方的前pression&QUOT。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!