问题描述
我开始使用 这个问题,我回答了那里,现在我在这里问更基本的问题.我已将查询简化为:
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 是一个将 Number 和 Extension 字段组合成一个格式整齐的字符串的属性.这是导致的错误:
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 更改为简单的 Number,则一切正常.
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 是某种将两者合并并合并它们的方法,大概是从您的其他(属性)代码中重用的.
where FormatNumber is some method that takes the two and merges them, presumably re-used from your other (property) code.
对于 LINQ-to-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)