在EF 4.1中，使用LINQ进行涉及多-多关系的比较最有效的方法是什么?

本文关键字：关系 比较 是什么 方法 有效 EF 使用 LINQ | 更新日期: 2023-09-27 18:11:56

在我的数据库中，我有以下表:

  • 文章
  • InterestTag

Person-InterestTag和Post-InterestTag之间存在多-多关系

我需要在EF 4.1中执行linq查询，以拉回包含至少一个与给定用户相关的至少一个兴趣标签匹配的兴趣标签的任何帖子。

一个人有以下兴趣:

    汽车
    • <
  • 体育/gh>
  • 健身

我需要返回任何与汽车、运动或健身相关的帖子。

在性能方面，写这个查询最有效的方法是什么?

编辑

根据下面给出的答案遇到错误…

编译正常，但在运行时抛出错误:

var matchingPosts = posts.Where(post => post.Topics.Any(postTopic =>   person.Interests.Contains(postTopic)));

错误是:

Unable to create a constant value of type 'System.Collections.Generic.ICollection`1'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

有什么办法解决这个问题吗?

编辑2

所以我的类是这样组织的:

public class Person
{
    public int PersonID {get; set;}
    public string FirstName {get; set;}
    public string LastName {get; set;}
    //other properties of types string, int, DateTime, etc.
    public ICollection<InterestTag> InterestTags {get; set;}      
}

public class Post
{
    public int PostID {get; set;}
    public string Title{get; set;}
    public string Content {get; set;}
    //other properties of types string, int, DateTime, etc.
    public ICollection<InterestTag> InterestTags {get; set;}

}
public class InterestTag
{
    public int InterestTagID { get; set; }
    public string InterestDescription { get; set; }
    public bool Active { get; set; }
    public ICollection<Person> Persons { get; set; }
    public ICollection<Post> Posts { get; set; }
}

在我的Context类中，我重写OnModelCreating来定义我的DB表名

modelBuilder.Entity<Person>().HasMany(u => u.InterestTags).WithMany(t => t.Persons)
    .Map(m =>
    {
        m.MapLeftKey("PersonID");
        m.MapRightKey("InterestTagID");
        m.ToTable("PersonInterestTags");
    });
modelBuilder.Entity<Post>().HasMany(u => u.InterestTags).WithMany(t => t.Posts)
    .Map(m =>
    {
        m.MapLeftKey("PostID");
        m.MapRightKey("InterestTagID");
        m.ToTable("PostInterestTags");
    });

在我的查询方法中，我带回了Post的IQueryable并应用了一些过滤器，包括我试图在这个问题中完成的子句。

 var person = personRepository.Get(x => x.PersonID = 5);
 var posts = postRepository.GetQueryable();
 //I have tried this and get the error above
 posts= posts.Where(x => x.InterestTags.Any(tag => person.InterestTags.Contains(tag)));

在EF 4.1中，使用LINQ进行涉及多-多关系的比较最有效的方法是什么?

如果您只从给定的personId(或userId)开始，则可以在一次往返中执行此查询，如下所示:

var posts = context.Posts
    .Intersect(context.People
        .Where(p => p.Id == givenPersonId)
        .SelectMany(p => p.InterestTags.SelectMany(t => t.Posts)))
    .ToList();

转换成SQL中的INTERSECT语句。

您也可以在两次往返中这样做:

var interestTagsOfPerson = context.People.Where(p => p.Id == givenPersonId)
    .Select(p => p.InterestTags.Select(t => t.Id))
    .SingleOrDefault();
// Result is an IEnumerable<int> which contains the Id of the tags of this person
var posts = context.Posts
    .Where(p => p.InterestTags.Any(t => interestTagsOfPerson.Contains(t.Id)))
    .ToList();
// Contains translates into an IN clause in SQL

在第二个查询中使用原语类型列表(interestTagsOfPerson是int的集合)也修复了您在问题中的Edit中提到的错误。对于Contains，你不能在LINQ中使用对象引用，因为EF不知道如何将其转换为SQL。

我不知道这两种方法中哪一种更快(SQL专家可能有更好的主意)，但可能会开始测试第一种选择。(我已经测试了一点，它似乎返回正确的结果，但这是我第一次使用Intersect。)

编辑

给出生成的SQL(从SQL Profiler捕获)的概念:

第一个查询(使用Intersect)创建这个SQL查询:

SELECT 
[Intersect1].[Id] AS [C1], 
[Intersect1].[Name] AS [C2], 
FROM  (SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Name] AS [Name], 
    FROM [dbo].[Posts] AS [Extent1]
INTERSECT
    SELECT 
    [Join1].[Id] AS [Id], 
    [Join1].[Name] AS [Name], 
    FROM  [dbo].[PersonInterestTags] AS [Extent2]
        INNER JOIN (SELECT [Extent3].[TagId] AS [TagId],
                           [Extent4].[Id] AS [Id],
                           [Extent4].[Name] AS [Name]
                    FROM  [dbo].[PostInterestTags] AS [Extent3]
                    INNER JOIN [dbo].[Posts] AS [Extent4]
                        ON [Extent3].[PostId] = [Extent4].[Id] ) AS [Join1]
            ON [Extent2].[TagId] = [Join1].[TagId]
    WHERE 1 = [Extent2].[PersonId]) AS [Intersect1]

第二个选项:

Query1(查询人员的标签id列表):

SELECT 
[Project1].[Id] AS [Id], 
[Project1].[C1] AS [C1], 
[Project1].[TagId] AS [TagId]
FROM ( SELECT 
    [Limit1].[Id] AS [Id], 
    [Extent2].[TagId] AS [TagId], 
    CASE WHEN ([Extent2].[PersonId] IS NULL)
                 THEN CAST(NULL AS int)
                 ELSE 1
             END AS [C1]
    FROM   (SELECT TOP (2) [Extent1].[Id] AS [Id]
        FROM [dbo].[People] AS [Extent1]
        WHERE 1 = [Extent1].[Id] ) AS [Limit1]
    LEFT OUTER JOIN [dbo].[PersonInterestTags] AS [Extent2]
            ON [Limit1].[Id] = [Extent2].[PersonId]
)  AS [Project1]
ORDER BY [Project1].[Id] ASC, [Project1].[C1] ASC

查询2的最终职位:

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[Name] AS [Name], 
FROM [dbo].[Posts] AS [Extent1]
WHERE  EXISTS (SELECT 
            1 AS [C1]
        FROM [dbo].[PostInterestTags] AS [Extent2]
WHERE ([Extent1].[Id] = [Extent2].[PostId]) AND ([Extent2].[TagId] IN (1,2,3))
)

在本例中，查询1返回(1,2,3)，因此查询2的IN子句中的(1,2,3)

Edit:我不得不编辑我的帖子，因为我忘记了帖子和主题之间的多-多-关系。现在应该可以工作了。

我不能告诉你这是否是最有效的方式，但它将是使用LINQ查询的一种方式，所以它应该是高效的:

var matchingPosts = posts.Where(post => post.Topics.Any(postTopic => person.Interests.Contains(postTopic)));

如果你想使用并行执行，你可以这样修改:

var matchingPosts = posts.AsParallel().Where(post => post.Topics.Any(postTopic => person.Interests.Contains(postTopic)));

当你使用EF时，你需要这个查询:

var matchingPosts = from post in posts
    where post.Topics.Any(topic => person.Interests.Contains(topic))
    select post;

这个怎么样:

context.Persons
       .Where(p => p.Name == "x")
       .SelectMany(p => p.Interests.SelectMany(i => i.Posts))
       .Distinct()
       .Take(10)
       .ToList();

Take()的存在是出于性能和分页的考虑。你不应该选择所有的记录，因为首先没有用户会读一个包含数千条记录的列表，其次结果集在未来可能会增长，查询将无法扩展。

相关文章：