实体框架查询逗号分隔的字段 [英] Entity Framework query a comma separated field

问题描述

我有一个名为Article的表

I have a table called Article:

-Id
-Title
-Content
-Tags (This is a comma seperated list of tags e.g. 'Sports,Italy,Ferrari')

使用实体框架，我希望能够找到所有具有特定标签的文章.

Using entity framework i want to be able to find all articles that have certain tags.

List<Article> GetArticles(List<String> tags)
{
  //do an entity framework query here to find all articles which have the tags specified
} 

返回的条目应包含所有指定的标签.例如如果该功能的输入为"car"，"blue"，则应返回至少包含这两个标签的所有条目.

The returned entries should contain all the tags specified. e.g. If the input to the function was 'car','blue', then all entries with these at least these 2 tags should be returned.

我无法开始想象如何实现此目标.我知道如何使用存储过程来实现这一目标，这是我的计划b.

I can't begin to imagine how i might implement this. I know how to achieve this using a stored procedure which is my plan b.

推荐答案

实际上，存储过程实际上是一个不错的选择，即使不是最好的选择.但是，您仍然可以使用 Contains :

A stored procedure is actually a good option, if not the best option, really. However, you can still achieve it with Contains:

var articles = db.Articles.Where(m => tags.Any(t => m.Tags.Contains(t)));

这基本上是对传入的标签进行迭代，并创建一个查询，以测试 Tags 是否类似于其中的任何一个.请记住，因为这将是一个类似LIKE的查询，所以它将成为sloooooowwwww，除非您为 Tags 列建立了索引，然后这也需要将其定义为长度，而不是NVARCHAR(MAX).

What this does is basically iterate over the passed in tags and creates a query that tests whether Tags is LIKE any of those. Bear in mind that because this is going to be a LIKE query that it's going to be sloooooowwwww, unless your Tags column is indexed, which then also requires that be a defined length, rather than something like NVARCHAR(MAX).

另一个潜在的问题是假匹配.假设您有"Read"和"Reading"标签，因为这是一个LIKE查询，因此搜索"Read"标签将同时返回这两个标签.实际上，解决此问题的唯一方法是在数据中添加标记分隔符，例如:"[Read]，[Reading]".然后，您搜索而不是搜索"[Read]"，而不是"Read"，并且可以保证只拉回正确的一个.

Another potential issue is false matches. Let's say you have the tags, "Read" and "Reading", because this is a LIKE query, search for the "Read" tag, will return both. The only way around this, really, is to add a tag-delimiter to your data, for instance: "[Read],[Reading]". Then, you search instead of for "[Read]", instead of "Read", and you're guaranteed to only pull back the right one.

当我需要像这样打包/解包字符串时，我通常会做类似的事情:

When I need to pack/unpack strings like this, I'll typically do something like:

public string Tags
{
    get
    {
        return TagsList != null
            ? String.Join(",", TagsList.Select(tag => "[" + tag + "]"))
            : null;
    }
    set
    {
        TagsList = !String.IsNullOrWhiteSpace(value)
            ? value.Replace("[", "").Replace("]", "").Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList()
            : new List<string>();
    }
}

[NotMapped]
public List<string> TagsList { get; set; }

这看起来相当复杂，但是所有的事情就是设置器正在解包定界符上的标签列表，删除我选择使用的短语定界字符， [和] .getter将列表打包成包含所有内容的字符串.

That looks fairly complicated, but all that's going on is that the setter is unpacking the list of tags on the delimiter, removing the phrase-delimiting characters I chose to use, [ and ]. The getter packs the list back into a string with all that.

这使您可以只使用 TagsList ，并且字符串版本将自动保留，而无需考虑它.但是，在查询时，您仍然必须在 Tags 上进行查询，因为这是实际的数据库支持属性.

This allows you to then just work with TagsList and the string version will be persisted automatically without having to think about it. However, when querying, you will still have to query on Tags, since that is the actual database-backed property.

这篇关于实体框架查询逗号分隔的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！