SQL query LINQ To SQL equivalent

Question

Please, tell me how to convert this SQL query in LINQ To SQL? Thanks in advance.

SELECT [Movies].[Name]
FROM
(
    SELECT [Value]
    FROM [Index]
    WHERE ('WORD1' = [Word] and [MatchCount] = 1) 
    OR    ([Word] = 'WORD2' AND [MatchCount] = 1)
    GROUP BY [Value]
    HAVING COUNT([Value]) = 2
) AS [Guids]
LEFT OUTER JOIN [Movies] ON [Movies].[Guid] = [Guids].[Value]

score 1 · Answer 1 · edited May 23 '17 at 11:55

1

In a previous question, someone suggested Linqer.

I've never used it myself, honestly, but I have used LinqPad extensively. It does the opposite (as well as converting LINQ to lambda expressions), and is one of my favorite tools.

edited May 23 '17 at 11:55

Community

1
1

answered Dec 15 '10 at 18:38

jwheron

2,553
2
30
40

Thank you for your reply. I tried the Linqer, but for a given query, it generated a very difficult solution, which executes much longer than SQL query. – Boris Mitchenko Dec 15 '10 at 18:49
I'd verify the Linqer query in LinqPad to see if the SQL generated from that LINQ statement actually matched the SQL query you provided above. – jwheron Dec 15 '10 at 18:51

score 1 · Accepted Answer · answered Dec 15 '10 at 21:50

Something like this? (Warning, following code is untested!)

var q = from i in Index
  where i.MatchCount == 1 &&
  (i.Word == "WORD1" || i.Word == "WORD2")
  group i by i.Value into g
  where g.Count() == 2
  from m in Movies.Where(x => x.Guid == g.Key).DefaultIfEmpty()
  select m.Name;

SQL query LINQ To SQL equivalent

2 Answers2