I was generally trying to get around a query which had multiple inner joins and also had a blend of 'EXISTS' clause or "IN" clause via LINQ. I searched for sometime over the web and couldn't find nothing, but simple Linq queries... nothing that I could take and reuse really. Here's the query that I was trying to convert to Linq: 

SELECT [t1].[ASListID], [t1].[QID]
FROM [Question] AS [t0]
INNER JOIN [QuestionSelection] AS [t1] ON [t0].[QID] = [t1].[QID]
WHERE EXISTS(
SELECT t3.ASListID FROM [AnswerStep] AS [t2]
INNER JOIN [AnswerStepSelection] AS [t3] ON [t2].[ASID] = [t3].[ASID]
INNER JOIN [AnswerStepList] AS [t4] ON [t3].[ASListID] = [t4].[ASListID]
WHERE ([t3].[ASListID] = [t1].[ASListID]) AND ([t4].[QuestionAreaID] = 1)
)

Now, it may not be the most complicated query in the world (I know), but it can be tricky to convert the whole expression to LINQ.

The best way to approach the problem is to break down the query into two parts
1. Everything uptill "Where Exists"
2. Everything after "Where Exists"

After doing that it might make sense to build the query expression in LINQ for the 2nd part first then check the results, match it with the actual SQL query. If it works fine then proceed to solving the second part.

Likewise to cut the big story short here's what the LINQ query should look like

Part 1: 

from p in context.GetTable<ConsoleApplication3.AnswerStep>()
join p1 in context.GetTable<ConsoleApplication3.AnswerStepSelection>()
on p.ASID equals p1.ASID
join p2 in context.GetTable<ConsoleApplication3.AnswerStepList>()
on p1.ASListID equals p2.ASListID
where ((p2.QuestionArea == 1))
select p1.ASListID

Part 2: 

from t in context.GetTable<ConsoleApplication3.MyQuestion>()
join t1 in context.GetTable<ConsoleApplication3.MyQuestionSelection>()
on t.QID equals t1.QID 
select new { t1.ASListID, t1.QID }

Now that we have both these queries in place we can simply combine them using a "WHERE" and "CONTAINS" clause/methods like this: 

from t in context.GetTable<ConsoleApplication3.MyQuestion>()
join t1 in context.GetTable<ConsoleApplication3.MyQuestionSelection>()
on t.QID equals t1.QID
select new { t1.ASListID, t1.QID }).Where(n => (from p in context.GetTable<ConsoleApplication3.AnswerStep>()
join p1 in context.GetTable<ConsoleApplication3.AnswerStepSelection>()
on p.ASID equals p1.ASID
join p2 in context.GetTable<ConsoleApplication3.AnswerStepList>()
on p1.ASListID equals p2.ASListID
where ((p2.QuestionArea == 1))
select p1.ASListID).Contains (n.ASListID)

 

I hope that helps...feel free to send in your comments. Thanks.