Using LINQ-To-Entities to Generate Information

I am working on a website where a user can add tags to their posted books, much like is currently done for questions on Stack Overflow.

Classes:

Books
{
bookId,
Title
}

Tags
{
Id
Tag
}

BooksTags
 {
 Id
 BookId
 TagId
 }

Here are few sample records.

Books
BookId Title
113421  A
113422  B

Tags
Id Tag
1  ASP 
2  C#
3  CSS
4  VB
5  VB.NET
6  PHP
7  java
8  pascal

 BooksTags   
 Id  BookId  TagId
 1  113421    1
 2  113421    2
 3  113421    3
 4  113421    4开发者_高级运维
 5  113422    1
 6  113422    4
 7  113422    8

Questions

1. I need to write something in LINQ to entity queries which gives me data according to the tags:

   Query: bookIds where tagid = 1

   Returns: bookid: 113421, 113422

   Query 2: tags 1 and 2

   Returns: 113421

2. I need tags and their count to to show in related tags, so in first case my related tags class should have following result.

   RelatedTags Tag Count 2 1 3 1 4 2 8 1

Second Case:

RelatedTags
Tag Count
3   1
4   1

How do I do this in LINQ?

On the first part, the interesting restriction is that the book has to match every tag entered, so a where clause of "where tagid == someId" wouldn't really work. I envision something like this (LINQ-to-objects example)

List<int> selectedTagIds = new List<int>() { 1, 2 };
var query = from book in books
            join booktag in booktags 
            on book.Id equals booktag.BookId 
            join selectedId in selectedTagIds 
            on booktag.TagId equals selectedId 
            group book by book into bookgroup 
            where bookgroup.Count() == selectedTagIds.Count
            select bookgroup.Key;

Which basically performs a join from books to booktags and also to the list of selected tag ids and restricts the selection to where the count of book->tag matches equals the count of selected tag ids.

To pull the related tags, maybe something like this

var relatedTags = from book in query // use original query as base
                    join booktag in booktags
                    on book.Id equals booktag.BookId
                    join tag in tags
                    on booktag.TagId equals tag.Id
                    where !selectedTagIds.Contains(tag.Id) // exclude selected tags from related tags
                    group tag by tag into taggroup
                    select new
                    {
                        Tag = taggroup.Key,
                        Count = taggroup.Count()
                    };

Full code for the quick example. Not fully OOP, but you get the idea.

using System;
using System.Collections.Generic;
using System.Linq;

namespace StackOverflow
{
    class Program
    {
        static void Main()
        {
            List<Book> books = new List<Book>() 
            {
                new Book() { Id = 113421, Title = "A" },
                new Book() { Id = 113422, Title = "B" }
            };

            List<Tag> tags = new List<Tag>()
            {
                new Tag() { Id = 1, Name = "ASP" },
                new Tag() { Id = 2, Name = "C#" },
                new Tag() { Id = 3, Name = "CSS" },
                new Tag() { Id = 4, Name = "VB" },
                new Tag() { Id = 5, Name = "VB.NET" },
                new Tag() { Id = 6, Name = "PHP" },
                new Tag() { Id = 7, Name = "Java" },
                new Tag() { Id = 8, Name = "Pascal" }
            };

            List<BookTag> booktags = new List<BookTag>()
            {
                new BookTag() { Id = 1, BookId = 113421, TagId = 1 },
                new BookTag() { Id = 2, BookId = 113421, TagId = 2 },
                new BookTag() { Id = 3, BookId = 113421, TagId = 3 },
                new BookTag() { Id = 4, BookId = 113421, TagId = 4 },
                new BookTag() { Id = 5, BookId = 113422, TagId = 1 },
                new BookTag() { Id = 6, BookId = 113422, TagId = 4 },
                new BookTag() { Id = 7, BookId = 113422, TagId = 8 }
            };


            List<int> selectedTagIds = new List<int>() { 1,2 };

            // get applicable books based on selected tags

            var query = from book in books
                        join booktag in booktags
                        on book.Id equals booktag.BookId
                        join selectedId in selectedTagIds
                        on booktag.TagId equals selectedId
                        group book by book into bookgroup
                        where bookgroup.Count() == selectedTagIds.Count
                        select bookgroup.Key;

            foreach (Book book in query)
            {
                Console.WriteLine("{0}\t{1}",
                    book.Id,
                    book.Title);
            }

            // get related tags for selected tags

            var relatedTags = from book in query // use original query as base
                              join booktag in booktags
                              on book.Id equals booktag.BookId
                              join tag in tags
                              on booktag.TagId equals tag.Id
                              where !selectedTagIds.Contains(tag.Id) // exclude selected tags from related tags
                              group tag by tag into taggroup
                              select new
                              {
                                  Tag = taggroup.Key,
                                  Count = taggroup.Count()
                              };

            foreach (var relatedTag in relatedTags)
            {
                Console.WriteLine("{0}\t{1}\t{2}",
                    relatedTag.Tag.Id,
                    relatedTag.Tag.Name,
                    relatedTag.Count);
            }

            Console.Read();
        }
    }

    class Book
    {
        public int Id { get; set; }
        public string Title { get; set; }
    }

    class Tag
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    class BookTag
    {
        public int Id { get; set; }
        public int BookId { get; set; }
        public int TagId { get; set; }
    } 
}

So for selected tags 1 & 2, you'll get book A, and the related tags would be 3 (CSS) and 4 (VB).

Just do Foreign keys mapping the tables in 1:N or 1:1 relations, and let the designer create navigation properties for you. (Books:BooksTags maps 1:N from Books.BookID to BooksTags.BookID, and BooksTags.TagID maps 1:1 to Tags.TagID). This is actually a disguised N:M relation. I don't know if the designer picks this up directly, but with some fiddling you can get the navigation properties right.

Now for the questions:

1. model.Tags.Where(t => t.ID == 1).Books.Select(b => b.ID)

2. Get all the tags present for a book, and join that table on the BooksTags, by this you can simply use Count() to get the count.

This isn't directly related to an answer as such but you might want to take a look at linqpad as it'll help you build L2S statements directly from your database.