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..
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.
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);
Join always gets me lost :)