使用LINQ查询语句获取所有符合所有值的记录

Question

我该如何将这个查询转换为C#的LINQ代码呢？

SELECT k1.Ad_Id FROM KeywordAdCategories k1, KeywordAdCategories k2
WHERE k1.Keyword_Id =  (SELECT Id FROM keywords WHERE name = 'ALFA')
AND k2.Keyword_Id = (SELECT Id FROM keywords WHERE name = '145')
AND k1.Ad_Id = k2.Ad_Id

这个想法是基于关键词数量（可以是N个）构建一个动态查询，例如在这个例子中有两个关键词：ALFA和145。

谢谢。

编辑：

KeywordAdCategory模型：

public class KeywordAdCategory
{
    [Key]
    [Column("Keyword_Id", Order = 0)]
    public int Keyword_Id { get; set; }

    [Key]
    [Column("Ad_Id", Order = 1)]
    public int Ad_Id { get; set; }

    [Key]
    [Column("Category_Id", Order = 2)]
    public int Category_Id { get; set; }
}

关键词模型：

public class Keyword
{
    // Primary properties
    public int Id { get; set; }
    public string Name { get; set; }
}

如果我有5个关键字，我将得到以下结果：

SELECT k1.Ad_Id FROM KeywordAdCategories k1, KeywordAdCategories k2, KeywordAdCategories k3, KeywordAdCategories k4, KeywordAdCategories k5
WHERE k1.Keyword_Id =  (SELECT Id FROM keywords WHERE name = 'KEYWORD1')
AND k2.Keyword_Id = (SELECT Id FROM keywords WHERE name = 'KEYWORD2')
AND k1.Ad_Id = k2.Ad_Id
AND k3.Keyword_Id =  (SELECT Id FROM keywords WHERE name = 'KEYWORD3')
AND k2.Ad_Id = k3.Ad_Id
AND k4.Keyword_Id =  (SELECT Id FROM keywords WHERE name = 'KEYWORD4')
AND k3.Ad_Id = k4.Ad_Id
AND k5.Keyword_Id =  (SELECT Id FROM keywords WHERE name = 'KEYWORD5')
AND k4.Ad_Id = k5.Ad_Id

- Patrick

---

你的C#类是什么样子的？此外，如果关键字数量为5，你能举个SQL的例子吗？我真的不明白为什么KeywordAdCategories表要使用两次...可能有更好的方法来编写这个SQL，这将导致更好的LINQ示例。 - Peter

@ɹǝʇǝd 我认为SQL可以重写得更好。然而，这个SO问题指出，“更干净”的解决方案性能较差：https://dev59.com/5HNA5IYBdhLWcg3wNa2T - Justin Pihony

@Peter，我刚刚编辑了问题并添加了更多信息。 - Patrick

Answer 1

这比我一开始想象的要棘手得多，所以我可能过于深思熟虑了。但是，这里有一个解决方案，我相信它会对你有用：

var keywordList = new List<string>();
keywordList.Add("ALFA");
keywordList.Add("145");

var results = KeywordAdCategories.Select (kac => kac.Ad_Id).Distinct()
            .Select (a => 
                new
                { 
                    AdId=a,
                    Keywords=KeywordAdCategories.Where(kac => kac.Ad_Id == a).Select(kac => kac.Keyword_Id)
                })
            .Where(ac => ac.Keywords.Intersect(Keywords.Where(kw => keywordList.Contains(kw.Name)).Select (kw => kw.Id)).Count() == keywordList.Count())
            .Select (ac => ac.AdId);

现在我来解释一下我在这里做了什么。首先，我想从所有可能的广告 ID 开始，所以我们有了这行代码：

KeywordAdCategories.Select (kac => kac.Ad_Id).Distinct()

当然，这可能更容易从广告表中进行选择，但我尝试只使用您定义的内容。

接下来，我将每个广告 ID 及其相关关键词集合选择为匿名类型对象列表：

.Select (a => 
              new
              { 
                  AdId=a,
                  Keywords=KeywordAdCategories.Where(kac => kac.Ad_Id == a).Select(kac => kac.Keyword_Id)
              })

接下来，我将结果进行过滤，仅保留包含关键字集合的对象，并且该集合与我们的关键字列表相交，并且具有与我们的关键字列表相同数量的结果元素。

.Where(ac => ac.Keywords.Intersect(Keywords.Where(kw => keywordList.Contains(kw.Name)).Select (kw => kw.Id)).Count() == keywordList.Count())

最后，我只选择产生广告 ID 的结果。

.Select (ac => ac.AdId);

如果您感兴趣，这里是生成的SQL语句

-- Region Parameters
DECLARE @p0 NVarChar(1000) = 'ALFA'
DECLARE @p1 NVarChar(1000) = '145'
DECLARE @p2 Int = 2
-- EndRegion
SELECT [t1].[Ad_Id]
FROM (
    SELECT DISTINCT [t0].[Ad_Id]
    FROM [KeywordAdCategories] AS [t0]
    ) AS [t1]
WHERE ((
    SELECT COUNT(*)
    FROM (
        SELECT DISTINCT [t2].[Keyword_Id]
        FROM [KeywordAdCategories] AS [t2]
        WHERE (EXISTS(
            SELECT NULL AS [EMPTY]
            FROM [Keywords] AS [t3]
            WHERE ([t2].[Keyword_Id] = [t3].[Id]) AND ([t3].[Name] IN (@p0, @p1))
            )) AND ([t2].[Ad_Id] = [t1].[Ad_Id])
        ) AS [t4]
    )) = @p2

我相信有更优雅的方法来实现你想要做的事情，但我想看看是否可以在单个linq查询中完成所有操作。希望这能帮到你。

- Brent Stewart

---

谢谢！它起作用了！您有没有可能将此代码与此代码合并：https://dev59.com/G2Yq5IYBdhLWcg3wpiGk？ - Patrick

Answer 2

List<string> keywordnames = new List<string>();
// populate keywordsnames list    

IQueryable<int> AdIds = from k in KeywordAdCategories
    where 
        (from kw in Keywords where keywordnames.Contains(kw.name) select kw.id)
        .Contains(k.keyword_id)
    select k.Ad_Id;

或者

List<string> keywordnames = new List<string>();
// populate keywordsnames list   

List<int> keywordnameids = (from kw in ctx.Keywords
                            where keywordnames.Contains(kw.name)
                            select kw.id).ToList();

List<int> AdIds = (from k in ctx.KeywordAdCategories
                   where keywordnameids.Contains(k.Keyword_Id)
                   select k.Ad_Id).ToList();

- pwnyexpress

---

谢谢你的回答，但是它返回的Ad_Id只包含其中一个关键词，而目标是获取包含所有关键词的Ad_Id。 - Patrick

@Patrick，请仔细检查keywordnames是否被填充了你所寻找的所有名称，并且在进行字符串比较时考虑大小写敏感性。此外，请尝试我的最近编辑中的第二个示例。 - pwnyexpress

我尝试了第二个例子，但仍然返回所有具有一个关键字或顺序的广告，而不是两者都符合的广告。 - Patrick

Answer 3

public static int GetAdId(string KeywordId)
{
    var dbContext = new MyDbContext();

    var result = (from k in in dbContext.KeywordAdCategories
                   where k.Keyword_Id == KeywordId
                   select k.Ad_Id).SingleOrDefault();

    return result;
}

运行所有关键字 ID 通过这个方法，你将得到相应的广告 ID。我建议你阅读 LINQ 相关的资料。一旦你了解它，它是一个非常棒的工具，而且学习起来并不难，尽管一开始可能会有点吓人。Pluralsight.com 有很多很棒的视频，容易理解和学习 :)

- Maritim

---

这并没有考虑到“AND”。更多的是一个“OR”。 - Justin Pihony

把这个想法写在评论里会比仅仅对一个回答进行负面评价更有建设性。 - Maritim

答案是错误的，所以我两个都做了。这就是给负分的目的，确保人们意识到答案错误。如果它接近正确，我不会给负分，但如果完全错误，我就会给负分。 - Justin Pihony

我的代码是基于所提供的信息编写的。他有一些关键字ID列表，可以循环遍历并运行每个迭代的方法。我不会说它是错误的，我会称其为另一种方法。 - Maritim