什么是最有效的方式做到涉及与LINQ多对多的关系，EF 4.1的比较？ | 什么是最有效的方式做到涉及与LINQ多对多的关系

本文介绍了什么是最有效的方式做到涉及与LINQ多对多的关系，EF 4.1的比较？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

在我的数据库，我有以下表格：

发表

InterestTag

多对多的关系与人InterestTag和Post-InterestTag

之间存在

我需要执行EF 4.1 LINQ查询给拉了回来，其中包含与给定用户至少一个兴趣标签匹配至少一个兴趣标签任何职务。

示例

一个人有以下利益：

汽车

体育

健身

我需要返回的是与不是汽车，体育，健身或张贴任何

什么是写在性能方面此查询的最有效方法是什么？

修改

运行到一个错误的基础上下面给出了答案......

这编译正常，但在运行时抛出一个错误：

  VAR matchingPosts = posts.Where（后= GT; post.Topics.Any（postTopic =＆GT; person.Interests.Contains（postTopic）））;

该错误是：

 无法创建类型的常值'System.Collections.Generic.ICollection`1。只有原始类型（'如的Int32，String和的Guid'）在这方面的支持。

任何想法如何解决这一问题？

编辑2

所以我的课的结构如此：

 公共类Person
{
    公众诠释是PersonID {搞定;组;}
    公共字符串名字{获得;组;}
    公共字符串名字{获得;组;}
    字符串类型，整型，日期时间等//其他属性    公众的ICollection＆LT; InterestTag＆GT; InterestTags {搞定;组;}
}
公共类帖子
{
    公众诠释{帖子ID获取;组;}
    公共字符串名称{搞定;组;}
    公共字符串内容{搞定;组;}
    字符串类型，整型，日期时间等//其他属性    公众的ICollection＆LT; InterestTag＆GT; InterestTags {搞定;组;}
}公共类InterestTag
{
    公众诠释InterestTagID {搞定;组; }
    公共字符串InterestDescription {搞定;组; }
    公共BOOL活跃{搞定;组; }    公众的ICollection＆LT;＆人GT;人{搞定;组; }
    公众的ICollection＆LT;邮政和GT;帖子{搞定;组; }
}

在我的上下文类我重写OnModelCreating来定义我的数据库表名

  modelBuilder.Entity＆LT;＆人GT;（）的hasMany（U =＆GT; u.InterestTags）。.WithMany（T =＆GT; t.Persons）
    .MAP（M =＆GT;
    {
        m.MapLeftKey（是PersonID）;
        m.MapRightKey（InterestTagID）;
        m.ToTable（PersonInterestTags）;
    }）;modelBuilder.Entity＆LT;邮政＆GT;（）的hasMany（U =＆GT; u.InterestTags）。.WithMany（T =＆GT; t.Posts）
    .MAP（M =＆GT;
    {
        m.MapLeftKey（帖子ID）;
        m.MapRightKey（InterestTagID）;
        m.ToTable（PostInterestTags）;
    }）;

在我的查询方法，我带回后的一个IQueryable和应用一些过滤器，包括其中我想在这个问题上要完成的条款。

 变种人= personRepository.Get（X =＆GT; x.PersonID = 5）;
 VAR帖= postRepository.GetQueryable（）; //我曾经尝试这样做，并得到上述错误
 帖= posts.Where（X =＆GT; x.InterestTags.Any（标签=＆GT; person.InterestTags.Contains（标签）））;

解决方案

如果你开始只是一个给定的 PERSONID （或用户id ），你可以做这个查询在一个往返像这样：

  VAR帖= context.Posts
    .Intersect（context.People
        。凡（P =＆GT; p.Id == givenPersonId）
        .SelectMany（P =＆GT; p.InterestTags.SelectMany（T =＆GT; t.Posts）））
    .ToList（）;

这意味着在SQL的 INTERSECT 语句。

您也可以做到这一点在两个往返：

  VAR interestTagsOfPerson = context.People.Where（P =＆GT; p.Id == givenPersonId）
    。选择（P =＆GT; p.InterestTags.Select（T =＆GT; t.Id））
    .SingleOrDefault（）;
//结果是一个IEnumerable＆LT; INT＆GT;其中包含此人的标签的标识VAR帖= context.Posts
    。凡（P =＆GT; p.InterestTags.Any（T =＆GT; interestTagsOfPerson.Contains（t.Id）））
    .ToList（）;
//包含转化成SQL IN子句

使用第二个查询原始类型（ interestTagsOfPerson 是 INT 的集合）还修复了列表错误你在你的问题中提到编辑。对于包含您不能使用LINQ中的对象引用到实体，因为EF不知道如何翻译成SQL。

我不知道这两种方法哪个更快（SQL专家可能有一个更好的主意），但可能会开始测试的第一个选项。（我测试过一点点，它似乎返回正确的结果，但它是我用相交。第一次）

修改

要给予生成的SQL（从SQL事件探查器捕获）的一个想法：

第一个查询（用相交）创建此SQL查询：

  SELECT
[Intersect1]。[ID] AS [C1]
[Intersect1]。[名] AS [C2]
FROM（SELECT
    [Extent1]。[ID] AS [ID]
    [Extent1]。[名] AS [名]
    FROM [DBO]。[文章] AS [Extent1]
相交
    选择
    [Join1]。[ID] AS [ID]
    [Join1]。[名] AS [名]
    FROM [DBO]。[PersonInterestTags] AS [Extent2]
        INNER JOIN（SELECT [Extent3]。[TAGID] AS [TAGID]
                           [Extent4]。[ID] AS [ID]
                           [Extent4]。[名] AS [名]
                    FROM [DBO]。[PostInterestTags] AS [Extent3]
                    INNER JOIN [DBO]。[文章] AS [Extent4]
                        ON [Extent3] [帖子ID] = [Extent4]。[ID]）AS [Join1]
            ON [Extent2]。[TAGID] = [Join1]。[TAGID]
    WHERE 1 = [Extent2]。[PERSONID]）AS [Intersect1]

第二个选项：

查询1（此人的标签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）
                 再投（NULL为int）
                 ELSE 1
             END AS [C1]
    FROM（SELECT TOP（2）[Extent1]。[ID] AS [ID]
        FROM [DBO]。[人物] 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]。[名] AS [名]
FROM [DBO]。[文章] AS [Extent1]
在存在（选择
            1 AS [C1]
        FROM [DBO]。[PostInterestTags] AS [Extent2]
WHERE（[Extent1]。[ID] = [Extent2] [帖子ID]）AND（[Extent2]。[TAGID] IN（1,2,3））
）

在这个例子中，查询1返回（1,2,3），因此（1,2,3）中的查询2的中子句中

In my database I have the following tables:

Person
Post
InterestTag

Many-Many relationships exist between Person-InterestTag and Post-InterestTag

I need to perform a linq query in EF 4.1 to pull back any post that contains at least one interest tag that matches at least one interest tag related to the given user.

Example

A person has the following interests:

Cars
Sports
Fitness

I need to return any Post that is related to either cars, sports, or fitness.

What is the most efficient way to write this query in terms of performance?

Edit

Running into an error based on the answer given below...

This compiles fine but throws an error at runtime:

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

The error is:

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.

Any ideas how to fix this?

EDIT 2

So my classes are structured as such:

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; }
}

In my Context class I am overriding OnModelCreating to define my DB table names

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");
    });

In my query method I am bring back an IQueryable of Post and applying some filters, including the clause in which I am trying to accomplish in this question.

 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)));

解决方案

If you start with just a given personId (or userId) you can do this query in one roundtrip like so:

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

This translates into an INTERSECT statement in SQL.

You can also do this in two roundtrips:

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

Using a list of primitive types in the second query (interestTagsOfPerson is a collection of int) also fixes the error you mentioned in your Edit in the question. For Contains you cannot use object references in LINQ to Entities because EF doesn't know how to translate that into SQL.

I have no clue which of the two approaches is faster (SQL experts might have a better idea) but would probably start to test the first option. (I've tested a little bit and it seemed to return the correct results, but it's the first time that I have used Intersect.)

Edit

To give an idea of the generated SQL (captured from SQL Profiler):

The first query (with Intersect) creates this SQL query:

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]

The second option:

Query1 (for the list of the person's tag Ids):

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

Query 2 for the final posts:

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))
)

In this example the query 1 returned (1,2,3), hence the (1,2,3) in the IN clause in query 2.

这篇关于什么是最有效的方式做到涉及与LINQ多对多的关系，EF 4.1的比较？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！