Convert SQL Query to Linq (contains left joins)

I have a query that works perfectly in SQL, but I'm having the damnedest time converting it to linq. The tab开发者_JAVA百科le (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)

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.

Thank you.

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;