Linq GroupBy给出无法格式化要执行的节点"New"作为SQL异常 [英] Linq GroupBy gives Could not format node 'New' for execution as SQL exception
问题描述
不幸的是，我是公司中唯一拥有Linq丰富经验的人，所以当我遇到麻烦时，我无处可寻:S
我有一个查询，其中当只有一行存在时，其中一列必须显示联接表中的单行，但是当存在多个联接行时，必须显示不同的值.
我最初在连接表中具有此列，因此查询与主查询是分开的，但最终我将需要将两者集成在一起.
无论如何-随手可得的代码:
Hi,
Unfortunately, I am the only person in the company who hand much experience with Linq and so I have no-where else to turn when I get in a jam :S
I have a query where one of the columns must show a single row from a joined table when only one row exists, but must show a different value when there are multiple joined rows.
I originally had this column in a joined table so the query is separate to the main query but I will eventually need to integrate the two together.
Anyway - On with the code at hand:
private class WaitingReport
{
public int RunId { get; set; }
public int PackageId { get; set; }
public string DecoDescription { get; set; }
public string WaitingOnText { get; set; }
public string WaitingOnLink { get; set; }
}
private class RunComponent
{
public int RunId { get; set; }
public string ComponentName { get; set; }
}
private void PopulateWaitingTable()
{
WaitingCarmaTable.Reset();
CarmaContext db = new CarmaContext();
IQueryable<WaitingReport> iqWaitingReport =
RunProperty.QueryByNameAndNotValue(PropertyFacade.RUN_USER_INPUT_URL, "empty", db)
.Join(
db.Runs,
rp => rp.run_id,
r => r.run_id,
(rp, r) => new {r.run_id, rp.property_value, r.deco_id, r.package_id, r.action_id}
)
.Join(
db.Actions,
n => n.action_id,
a => a.action_id,
(n, a) => new WaitingReport
{
RunId = n.run_id,
PackageId = n.package_id.GetValueOrDefault(0),
DecoDescription = Deco.GetDecoDescription(n.deco_id,db),
WaitingOnText = a.action_name,
WaitingOnLink = n.property_value
}
);
IQueryable<RunComponent> iqComponent = iqWaitingReport
.Join(
db.Runs.Where(r => _showAll || r.contact.ToLower().Equals(WebInterfaceUtils.GetUserName().ToLower())),
rp => rp.RunId,
r => r.run_id,
(rp, r) => r)
.Join(
db.Deliveries,
r => r.package_id,
d => d.package_id,
(r, d) => new {r.run_id, d.component_id}
)
.Join(
db.Components,
n => n.component_id,
c => c.component_id,
(n, c) => new RunComponent { RunId = n.run_id, ComponentName = c.name }
)
.GroupBy(rc => rc.RunId)
.Select(rcg => new RunComponent
{
RunId = rcg.Key,
ComponentName = rcg.Count() > 1
? Segment.QueryByRunId(rcg.Key,db).segment_name
: rcg.Max().ComponentName
});
//...
我有一个扩展方法将它们转换为DataTables，但是任何类型的查询激活都会触发错误 Could not format node ''New'' for execution as SQL
我发现最终的.Select()方法是引发错误的原因，但我不明白为什么.
有什么想法吗?
我将密切监视此线程，并愿意进行任何更改以缩小错误原因的范围
TIA ^ _ ^
I have an extension method to convert these into DataTables but any kind of query activation triggers the error Could not format node ''New'' for execution as SQL
I have found that the final .Select() method is what throws the error but I cannot see why.
Any ideas?
I''ll closely monitor this thread and I am willing to make any changes to narrow down the reason for the error
TIA ^_^
推荐答案
此处的问题是Select语句中的逻辑不能转换为SQL SELECT语句，因为它也是如此复杂(具有嵌入式逻辑).您需要做的是创建一个不带SQL的Linq-to-SQL查询，然后在带有选择的查询结果上进行Linq-to-object查询.
我不太确定该怎么做(您不想将查询评估到内存中，仍然希望推迟执行，但是在您的过程中不在SQL数据源中).但是我很确定这就是问题所在.希望我们的Linq专家中的一位能够看到这一点并能够提供代码解决方案.
The problem here is that the logic in the Select statement can''t be converted to a SQL SELECT statement, because it''s too complex (it has embedded logic). What you need to do is create a Linq-to-SQL query without it, and then a Linq-to-object query on the result of that query with the selection.
I''m not quite sure how to do that (you don''t want to evaluate the query into memory, you still want deferred execution, but in your process not in the SQL data source). But I''m pretty sure that''s what the problem is. Hopefully one of our Linq experts will see this and be able to provide a code solution.
我有一个解决方案，但它确实从数据库中返回了比我想要的更多的信息:
I have a solution but it does return much more from the db than I would like:
IEnumerable<runcomponent> ieComponent = iqWaitingReport
.Join(
db.Runs.Where(r => _showAll || r.contact.ToLower().Equals(WebInterfaceUtils.GetUserName().ToLower())),
rp => rp.RunId,
r => r.run_id,
(rp, r) => r)
.Join(
db.Deliveries,
r => r.package_id,
d => d.package_id,
(r, d) => new {r.run_id, d.component_id}
)
.Join(
db.Components,
n => n.component_id,
c => c.component_id,
(n, c) => new RunComponent { RunId = n.run_id, ComponentName = c.name }
).AsEnumerable();
DataTable dtComponents =
ieComponents
.GroupBy(rc => rc.RunId)
.Select(rcg => new RunComponent
{
RunId = rcg.Key,
ComponentName = rcg.Count() > 1
? Segment.QueryByRunId(rcg.Key,db).segment_name
: rcg.Max().ComponentName
}).AsDataTable();
//Forgive the custom extensions but they pretty much do as they say on the tin
</runcomponent>
请注意，我的解决方案是在执行Group By子句之前查询数据库.我希望iif逻辑可以转换为SQL中的大小写选择.有人知道可以做到这一点的方法吗?
Note that my solution was to query the database just before I perform the Group By clause. I was hoping that the iif logic would translate to a case select in the SQL. Does anyone know of a way that this could be done?
这篇关于Linq GroupBy给出无法格式化要执行的节点"New"作为SQL异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
