How can I turn SQL query that joins two columns and groups by count of one column and a column of ea-CodePudding

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.

CodePudding user response：

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
}