转换SQL查询到LINQ（包含联接左） [英] Convert SQL Query to Linq (contains left joins)

问题描述

我在SQL完美的作品的查询，但我具有damnedest时间将其转换为LINQ。下表（表1以下）持有的多个记录类型状态的变化。连接需要两个字段被设置为创建有效联接：A SubmissionId（状态涉及表中的峰），和一个SubmissionTypeId（确定什么表的状态涉及）

I have a query that works perfectly in SQL, but I'm having the damnedest time converting it to linq. The table (Table1 below) holds status changes for multiple record types. The join requires two fields to be set to create the valid join: A SubmissionId (pk of the table the status pertains to), and a SubmissionTypeId (determines what table the status pertains to).

CREATE TABLE ##Table1 (Id int, Status varchar(50), SubmissionId int, SubmissionTypeId int)
insert into ##Table1(Id, Status, SubmissionId, SubmissionTypeId)
select 1 ,'Status1' ,1 , 1    
union select 2,'Status2',1, 2

CREATE TABLE ##Table2 (ID int, Value varchar(50))
insert into ##Table2 (ID, Value)
select 1, 'Value1Table2'

CREATE TABLE ##Table3 (ID int, Value varchar(50))
insert into ##Table3 (ID, Value)
select 1, 'Value1Table3'

select ds.* from ##Table1 ds
left join ##Table2 di
on ds.SubmissionId = di.Id and ds.SubmissionTypeId = 2
left join ##Table2 dr
on ds.SubmissionId = dr.Id and ds.SubmissionTypeId = 1
where SubmissionTypeId in (1,2)

我试过一对夫妇使用迭代成x.DefaultIfEmpty从Y X（），我不能设置在正确的位置在where子句。我要开始与表1查询，因为这是值的来源。

I've tried a couple of iterations using the into x from y in x.DefaultIfEmpty() and I can't set the where clause in the right location. I need to start the query with Table1 since that is where the values are coming from.

作为一个工作，我周围的分裂查询分成两部分，刚才添加的状态数据依次到列表，但它似乎必须有一个更好的办法。

As a work around I split the query into two parts and just added the status data sequentially to a list, but it seems there must be a better way.

感谢您。

推荐答案

我觉得你的SQL的直接翻译是这样的：

I think a direct translation of your SQL would look like this:

var q = from ds in table1
        where ds.SubmissionTypeId == 1 || ds.SubmissionTypeId == 2
        from di in table2
        from dr in table2
        where (ds.SubmissionTypeId == 2 && ds.SubmissionId == di.Id)
           || (ds.SubmissionTypeId == 1 && ds.SubmissionId == dr.Id)
        select ds;

不过，似乎不太可能，这是你想要的。如果我可以揣测一下你的意图的逻辑是什么，我想你想要更多的东西是这样的：

However, it seems unlikely that this is what you want. If I may speculate about what your intended logic is, I think you want something more like this:

var q = from ds in table1
        where (ds.SubmissionTypeId == 2 && table2.Any(di => ds.SubmissionId == di.Id))
           || (ds.SubmissionTypeId == 1 && table3.Any(dr => ds.SubmissionId == dr.Id))
        select ds;

这篇关于转换SQL查询到LINQ（包含联接左）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！