Linq. Select

[shapper]

Hello,

I have 3 tables with the following fields:

Posts: PostID, Title, Body
Tags: TagID, Name
PostsTags: PostID, TagID

1. I need to select all Tags (TagID and Name) that exist in PostsTags
adding a field Count which holds the frequency of how often each tag
is associated to a post, i.e., the number of times it shows in
PostsTags.

2. And would be also possible to group the tags as follows:
If Count <= 10 then group all tags and give Weight = 1
If 10 < Count < 20 then group all tags and give Weight = 2
If Count >= 20 then group all tags and give Weight = 3

How can I create this using LINQ?

Thanks,
Miguel

 

[Pavel Minaev]

I have 3 tables with the following fields:

Posts: PostID, Title, Body
Tags: TagID, Name
PostsTags: PostID, TagID

1. I need to select all Tags (TagID and Name) that exist in PostsTags
adding a field Count which holds the frequency of how often each tag
is associated to a post, i.e., the number of times it shows in
PostsTags.

from t in Tags
join pt in PostsTags on t.TagID equals pt.TagID into pts
let count = pts.Count()
where count != 0
select new { t.TagID, t.Name, Count = count }

2. And would be also possible to group the tags as follows:
    If Count <= 10 then group all tags and give Weight = 1
    If 10 < Count < 20 then group all tags and give Weight = 2
    If Count >= 20 then group all tags and give Weight = 3

from t in Tags
join pt in PostsTags on t.TagID equals pt.TagID into pts
let count = pts.Count()
let weight = (count <= 10) ? 1 : (count >= 20) ? 3 : 2
where count != 0
group new { t.TagID, t.Name, Count = count } by weight