Converting SQL query to Linq

Question

I want to convert this query to LINQ / EF

select a.ArticleId, COUNT(*) as total from Articles a
inner join MetaKeywords b on a.ArticleId = b.ArticleId 
where b.MetaKeyword in ('_catalog', '_register')
group by a.ArticleId
having COUNT(*) >= 2

I tried many options but result was not as desired.

In the above query 2 is number of keywords to searched from child table..

"I tried many options but result was not as desired." => please post what you've tried. — ken2k, Nov 12 '13 at 12:47
Do you want to have this exact query translated to LINQ, or do you want a query that will "find all ArticleIDs that have some MetaKeyword duplicated" (like, more than one _register or more than one _catalog or more than one ...)? — quetzalcoatl, Nov 12 '13 at 12:49
http://stackoverflow.com/questions/530925/linq-using-inner-join-group-and-sum — Vimal CK, Nov 12 '13 at 12:52
possible duplicate of [How to convert sql query to linq query](http://stackoverflow.com/questions/1531241/how-to-convert-sql-query-to-linq-query) — Monika, Nov 12 '13 at 13:03

Jens Kloster · Accepted Answer · 2013-11-12T13:04:03.113

2

try this:

var lst= new string[]{"_catalog","_register"};

var qry = (from a in db.Articles 
          join b MetaKeywords on a.ArticleId equals b.ArticleId
          where lst.Contains(b.MetaKeyword)
          group a by a.ArticleId into g
          where g.Count() >= 2
          select new {ArticleId = g.Key, Total = g.Count()} );

You should note that the lst is declared outside the actual query.

edited Nov 12 '13 at 13:04

answered Nov 12 '13 at 12:52

Jens Kloster

11,099
5
40
54

Carles Company · Answer 2 · 2013-11-12T13:08:33.030

1

Using method chaining:

var list=new[]{"_catalog","_register"};
var result=Articles
    .Join(MetaKeyWords,t=>t.ArticleId,t=>t.ArticleId,(article,metakeywords)=>new{article,metakeywords})
    .Where(t=>list.Contains(t.metakeywords.MetaKeyword))
    .GroupBy(t=>t.article.ArticleId)
    .Select(t=>new{ ArticleId=t.Key,Total=t.Count()})
    .Where(t=>t.Total>=2);

edited Nov 12 '13 at 13:08

answered Nov 12 '13 at 12:56

Carles Company

7,118
5
49
75

Much better (+1) but *I* think it is still pretty hard to read... syntax-wise. the `Join` always gets me lost :) – Jens Kloster Nov 12 '13 at 13:21

Converting SQL query to Linq

2 Answers2