LINQ to SQL复合体在混合条件下联接 [英] LINQ to SQL complex join with mixed conditions

问题描述

我有一个SQL语句，我试图将其转换为LINQ to SQL，并且设法设法将其转换为大多数，但是遇到了一个语句，我无法用LINQ来解决. /p>

导致头痛的SQL查询部分是:

SELECT *
FROM step
INNER JOIN action on 
    (step.NextAction = action.ID and step.ActionStatus != 4) or 
    (step.ACTION = action.ID and step.ActionStatus = 4)

step是包含一系列操作的表，action是可用操作的列表. ActionStatus是状态列表的索引-4 =='失败'.

基本上，对于未失败的操作，它需要返回下一步操作.如果操作失败失败，则返回当前操作.

这只是联接之一(完整查询中总共有10个表)，其中大多数是直接等值联接，其中一些是在多个条件下进行的，但是我已经能够用LINQ编写它们，而没有问题.但是，我看不出它是怎么写的.

我看到了此答案，但也看不到在这种情况下如何应用.有什么想法吗?

解决方案

from s in step
from a in action
where (s.NextAction = a.ID && s.ActionStatus != 4) || (s.Action = a.ID && s.ActionStatus = 4)
select new { Step = s, Action = a };

您可能需要查看生成的SQL，并在需要时进行优化.

I have a SQL statement which I am trying to convert into LINQ to SQL, and I've managed to get most of it converted, but have come across one statement which I can't wrap my head around in LINQ.

The section of the SQL query that's causing the headache is:

SELECT *
FROM step
INNER JOIN action on 
    (step.NextAction = action.ID and step.ActionStatus != 4) or 
    (step.ACTION = action.ID and step.ActionStatus = 4)

step is a table containing a sequence of actions, action is the list of actions available. ActionStatus is an index into a list of statuses - 4 == 'Failed'.

Basically, for actions which are not Failed, it needs to return the next action. If the action has Failed, it returns the current action.

This is just one of the joins (there are a total of 10 tables in the full query), most of them are straight forward equijoins, some on multiple conditions, but I've been able to write them in LINQ with no issue. This one though, I can't see how it would be written.

I saw this answer, but also can't see how to apply that in this scenario. Any ideas?

解决方案

from s in step
from a in action
where (s.NextAction = a.ID && s.ActionStatus != 4) || (s.Action = a.ID && s.ActionStatus = 4)
select new { Step = s, Action = a };

You might want to look at the SQL generated and optimise if needed.

这篇关于LINQ to SQL复合体在混合条件下联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！