SQL self join query to LINQ query

Question

I am bit stuck with the converting a SQL query into LINQ. Can any body please help me out. Here is my query

SELECT x.*
  FROM FilterType x
  JOIN (SELECT t.FilterType
          FROM FilterType t
          where FilterId in (7,15)
      GROUP BY t.FilterType
        HAVING COUNT(t.FilterType) > 1) y ON y.FilterType = x.FilterType

Thanks in advance.

@JibranKhan I believe that LINQPad does opposite thing - it shows SQL generated for LINQ query. For generating LINQ you need something like Linqer — Sergey Berezovskiy, Apr 15 '13 at 07:27
Then, there is one more called Linqer but do review the answers here http://stackoverflow.com/questions/12238423/linqpad-convert-sql-to-linq-command — Jibran Khan, Apr 15 '13 at 07:30

score 1 · Accepted Answer · answered Apr 15 '13 at 07:34

Suppose you have int[] ids = { 7, 15 }. Then query will look like:

from t in FilterType.Where(x => ids.Contains(x.FilterId))
group t by t.FilterType into g
where g.Count() > 1
from f in g
select f

Or with method syntax:

FilterType.Where(x => ids.Contains(x.FilterId))
          .GroupBy(t => t.FilterType)
          .Where(g => g.Count() > 1)
          .SelectMany(g => g);

Generated SQL will not be exactly as yours, but result should be same.

score 0 · Answer 2 · answered Apr 15 '13 at 07:34

from a in FilterType
join b in
    (
        from x in FilterType
        where (new int[]{7, 15}).Contains(x.FilterID)
        group x by new {x.FilterType} into g
        where g.Count() > 1
        select new {FilterType = g.Key.FilterType}
    ) on a.FilterType equals b.FilterType
select a;

SQL self join query to LINQ query

2 Answers2