CODEWITHSUNDEEP
c#sql-serverlinq
jmath412
jmath412

Reputation: 519

How can I turn SQL query that joins two columns and groups by count of one column and a column of each joined table into LINQ?

In my database, each URI has associated tags (Tag table) and each pageview (PageView table) is associated with someone viewing a particular page. I want to return a list of URIs that have the same tags as a given URI, by count of each URI that shares those tag(s). My SQL query looks like this:

select count(URI) as 'Count', p.URI, t.Name
from tracking.PageView as p
inner join  Tracking.Tag as t on p.ID = t.PageViewID
where t.name in 
    (select t.Name
    from tracking.PageView as p
    inner join  Tracking.Tag as t on p.ID = t.PageViewID
    where p.URI = 'URI WE WANT TAGS OF'
    )
and p.uri like '%/articles/%'
group by p.URI , t.name
order by Count desc

My apologies if the description is too vague for the query or if the query itself is rough. It was just the first one that worked. I've tried to separate the subquery into a variable and select values in that subquery, but it's been some time since I've used LINQ and I'm spinning wheels at this point.

Upvotes: 0

Views: 38

Answers (1)

Charlieface
Charlieface

Reputation: 71544

The following is pretty much an exact translation of your current SQL query, which should get you started.

from p in tracking.PageView
join t in Tracking.Tag on p.ID equals t.PageViewID
where p.uri.Contains("/articles/")
   && (
       from p2 in tracking.PageView
       join t2 in Tracking.Tag on p2.ID equals t2.PageViewID
       where p2.URI == "URI WE WANT TAGS OF"
       select t2.name
      ).Contains(t.name)
group new { p, t } by new { p.URI, t.name } into g
orderby g.Count() descending
select new {
    Count = g.Count(),
    g.Key.URI,
    g.Key.Name
}

Upvotes: 1

Related Questions