大约有这么多的问题，但不够确切的答案，尤其是使用SQL Server而我在这种情况下。

There are many questions about this, but not enough definitive answers, especially using SQL Server which I am in this case.

我有3个表重新present博客文章和与之相关联的标签。我想运行，将得到所有的职位，每个职位的标签与它的查询 - 我需要两个标签的ID和名称。其结果必须是易于序列化到我的C＃类。我也尝试使用Dapper.net但这并不是最重要的部分。

I have 3 tables to represent blog posts and tags associated with it. I want to run a query that will get all the posts and each posts tags with it - I need both the tag id and name. The result needs to be easily serializable into my C# classes. I am also trying to use Dapper.net but that is not the most important part.

显然，很多网站这样做，我想知道的最佳方式以及应如何在现实世界中做什么？我能得到所有的职位，然后运行为每一个之后，将返回的标签为每个岗位多个查询。但肯定必须有一个更好的办法？

Obviously many websites do this, I want to know the best way and how it should be done in the real world? I could get all the posts, then run multiple queries for each one afterwards that would return the tags for each post. But surely there has to be a better way?

如果我只是试图获得一个职位，那么我会只写了多个选择。其中一个为后，一个用于标签。但我想所有的帖子。是否有这样做不复制后的信息返回的每个标签行的方式？

If I was just attempting to get one post then I would just write multiple selects. One for the post and one for the tags. But I want all of the posts. Is there a way of doing this without replicating the post information for each tag row that is returned?

如果标签的每个岗位都序列化为一个列有他们的逗号分隔，例如，一个人如何将能够获得这两个ID和姓名？恩code中的字符串？

If the tags for each post are serialized into a column that has them comma separated for example, how would one be able to get both the id and the name? Encode the string?

SELECT * FROM dbo.Posts    
SELECT * FROM dbo.Tags
SELECT * FROM dbo.PostTags

Posts
Id  Title       Content
===============================
1   First Post  First Content
3   Second      Second Content

Tags
Id  Name
============
1   C#
2   SQL
3   IIS
4   Steam
5   OpenID

PostTags
PostId  TagId
=============
1       1
1       2
3       3
3       4

只需使用以下查询连接表

Simply joining the tables with the following query:

SELECT p.*, t.Name
FROM dbo.Posts p
LEFT JOIN dbo.PostTags pt ON p.id = pt.PostId
LEFT JOIN dbo.Tags t ON t.id = pt.TagId

通过重复的帖子内容为与它相关联的每个标签提供了很多的冗余数据

Id Title Content Name ====================================== 1 First Post First Post C# 1 First Post First Post SQL 3 Second Post Second Content IIS 3 Second Post Second Content Steam

推荐答案

这是纯粹的练习，让我preface该说的数据是最有可能的量被复制是不是一个大问题。但如果职位的尺寸非常大，有很多人，它确实开始变得更有意义，以避免重复。

This being purely an exercise, let me preface this by saying that most likely the amount of data being duplicated isn't a big deal. Although if the posts are very large in size and there are lots of them, it does start to make more sense to avoid duplication.

此外，使用C＃LINQ到SQL或实体框架，对象关系将制定出你和你的发布实体将有一个列表＆LT;标签＆GT; ，您可以访问属性

Further, using C# Linq-to-Sql or Entity Framework, the object relationships will be worked out for you and your Post entity will have a List<Tag> property that you can access.

不过，如果你想推出自己的类型的东西，一个选项涉及只有一个数据库往返，没有重复的数据是写一个存储过程，让你回到2的记录（2个独立的select语句） - 之一，帖子的内容，以及一个带标签的内容。

However if you want to roll your own type of thing, one option that involves just one DB round trip and no duplication of data is to write a stored proc that gets you back 2 recordsets (2 separate select statements) - one with Post content, and one with Tag content.

然后，它将是pretty的简单的创建一个重新presents一个发布，只是有一个列表℃的C＃类;标签＆GT; 并从存储过程的结果将其

It would then be pretty simple to create a C# class that represents a Post and just has a List<Tag> and pull it from the stored proc results.

Create Procedure GetPostTags
As

-- We will use the GotTags column here to loop through and get tabs later
Declare @Posts Table (
    PostID varchar(50), 
    PostTitle varchar(50), 
    PostContent varchar(50),
    GotTags bit default 0
)

/* Assuming you care about the ID's, this will get you all of 
   the tags without duplicating any post content */
Declare @PostTags Table (
    PostID int,
    TagID int,
    TagName varchar(50)
)

-- Populate posts from the main table
Insert Into @Posts (PostID, PostTitle, PostContent)
Select * From Posts

-- Now loop through and get the tags for each post. 
Declare @CurrentPostID int
Set @CurrentPostID = (Select Top 1 PostID From @Posts Where GotTags = 0)
While @CurrentPostID Is Not Null
    Begin
        Insert Into @PostTags (PostId, TagID, TagName)
        Select pt.postid, pt.tagid, t.name
        From Tags t 
            Join PostTags pt
                On t.id = pt.tagid
        Where pt.postid = @CurrentPostID

        -- Set next loop
        Update @Posts Set GotTags = 1 Where PostID = @CurrentPostID
        Set @CurrentPostID = (Select Top 1 PostID From @Posts Where GotTags = 0)
    End

-- Return 2 recordsets, which are related by the PostID column found in both sets
Select * from @Posts
Select * From @PostTags

我preFER这种类型的解决方案在连接字符串连接成一个字符串，再后来分裂他们;它可以更容易地与数据这种方式工作，允许更多的面向对象的C＃，并让您跟踪标签ID的情况下更容易标签需要删除或添加/从一个职位，你不需要按名称查找一个标签或比赛，因为你已经拥有的ID。

I prefer this type of solution over concatenating strings into one string and then splitting them later; it makes it easier to work with the data this way, allows to be more object oriented in C#, and lets you keep track of Tag ID's easier in case tags need to be removed or added to/from a post, you don't need to find a tag or match by name since you already have the ID.