none
Linq. Select RRS feed

  • Question

  • 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
    Thursday, August 28, 2008 11:42 AM

Answers

  • I'll give this a shot but as Damien says it might not be what you want. Also I will assume you want it in C# not VB (there is a difference in LINQ, as some queries are easier in VB)

     

    First rule of development: break down big problems into smaller ones

     

    So first I totalled the PostsTags using a Group By. Then I joined this to the Tags to get the tagid, tagname and count. Then I computed the weights, and finally grouped by the weight.

     

    Code Snippet

    var db = new YourDataContext();

    db.Log = Console.Out;

     

    // get totals

    var totals = from x in db.PostsTags

                 group x by x.TagID

                 into g

                 select new {TagID= g.Key, Total = g.Count()};

     

    // join tags and totals

    var tags = from x in db.Tags

               join t in totals on x.TagID equals t.TagID

               where t.Total >0

               select new { x.TagID, x.Name, t.Total };

     

    // add weights

    var weighted = from t in tags

                   let weight = (t.Total <= 10 ? 1 : (t.Total >= 20 ? 3 : 2))

                   select new { t.TagID, t.Name, weight, t.Total };

     

    // group by weights and use .ToList to enumerate

    var result = (from w in weighted

                     group w by w.weight into g

                     select new { g.Key, g }).ToList();

     

    foreach (var item in result)

    {

    Console.WriteLine("Weight: {0}", item.Key);

    foreach (var tag in item.g.OrderBy(x => x.Total))

    {

    Console.WriteLine(" {0}: {1}", tag.TagID, tag.Total);

    }

    }

    Console.ReadKey(true);

     

     

    Wednesday, September 3, 2008 8:19 AM
    Answerer

All replies

  • I'm not sure I follow your requirements - do you already have this query in SQL?

     

    [)amien

     

    Tuesday, September 2, 2008 6:02 PM
    Moderator
  • I'll give this a shot but as Damien says it might not be what you want. Also I will assume you want it in C# not VB (there is a difference in LINQ, as some queries are easier in VB)

     

    First rule of development: break down big problems into smaller ones

     

    So first I totalled the PostsTags using a Group By. Then I joined this to the Tags to get the tagid, tagname and count. Then I computed the weights, and finally grouped by the weight.

     

    Code Snippet

    var db = new YourDataContext();

    db.Log = Console.Out;

     

    // get totals

    var totals = from x in db.PostsTags

                 group x by x.TagID

                 into g

                 select new {TagID= g.Key, Total = g.Count()};

     

    // join tags and totals

    var tags = from x in db.Tags

               join t in totals on x.TagID equals t.TagID

               where t.Total >0

               select new { x.TagID, x.Name, t.Total };

     

    // add weights

    var weighted = from t in tags

                   let weight = (t.Total <= 10 ? 1 : (t.Total >= 20 ? 3 : 2))

                   select new { t.TagID, t.Name, weight, t.Total };

     

    // group by weights and use .ToList to enumerate

    var result = (from w in weighted

                     group w by w.weight into g

                     select new { g.Key, g }).ToList();

     

    foreach (var item in result)

    {

    Console.WriteLine("Weight: {0}", item.Key);

    foreach (var tag in item.g.OrderBy(x => x.Total))

    {

    Console.WriteLine(" {0}: {1}", tag.TagID, tag.Total);

    }

    }

    Console.ReadKey(true);

     

     

    Wednesday, September 3, 2008 8:19 AM
    Answerer