EF Code First（RC）中的多对多关系问题 [英] Problem with many-to-many relationship in EF Code First (RC)

问题描述

我有以下3个表格：

CREATE TABLE [dbo].[Items](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [Name] [nvarchar](50) NOT NULL)

CREATE TABLE [dbo].[Params](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [Name] [nvarchar](50) NOT NULL,
  [DisplayName] [nvarchar](50) NOT NULL)

CREATE TABLE [dbo].[Params2Items](
  [ParamID] [int] NOT NULL,
  [ItemID] [int] NOT NULL,
  [ID] [int] IDENTITY(1,1) NOT NULL)

and 2 corresponding classes

public class Item
  {
    public int ID { get; set; }
    public string Name { get; set; }

    public virtual ICollection<ItemParameter> Params { get; set; }
  }


 public class ItemParameter
  {
    public int ID { get; set; }      
    public string DisplayName { get; set; }

    public virtual ICollection<Item> Items { get; set; }
  }

推荐答案

因为你有嵌套的foreach循环并且延迟加载是由默认情况下，您正在尝试在内部循环中向数据库发送查询，而外部查询尚未完成。这在默认情况下不起作用，因为默认情况下，一次只能有
一个活动查询。你可以在这里做几件事。首先，您可以通过启用MARS（多个活动结果集）来更改一次只打开一个DataReader的默认行为。这很容易做到。您只需要附加以下
设置您的连接字符串"MultipleActiveResultSets = True" （更多细节在这里：
http://msdn.microsoft.com/en-us/library /h32h3abf.aspx ）。虽然启用MARS可能是最容易做到的事情，但如果考虑性能，它可能不是最好的。启用MARS后，对于
每个Item 实体，您将向数据库发送单独的查询 - 因此，如果您在Items表中有1000个实体，您将总共发送1001个查询（一个用于获取项目，一个用于获取参数 对于每个项目）。如果您只能通过单个查询发送
，那会不会更好？我相信，这是第二个解决方案。你只需稍微改变你的代码 - 第一个foreach循环需要像这样改变：

Because you have got nested foreach loops and lazy loading is enabled by default you are trying sending queries to the database in the inner loop while the outer query has not completed yet. This won't work by default as by default you can have only one active query at a time. There are a couple things you can do here. First, you can change the default behavior of having just one open DataReader at a time by enabling MARS (Multiple Active Result Set). This is easy to do. You just need to append the following setting you your connection string "MultipleActiveResultSets=True" (more details here: http://msdn.microsoft.com/en-us/library/h32h3abf.aspx). While enabling MARS might be the easiest thing to do here it may not be the best if you consider performance. With MARS enabled, for each Item entity you will send a separate query to the database - so if you have 1000 entities in the Items table you will send 1001 queries total (one to get items and one to get params for each item). Would not it be better if you could send just a single query? I believe so, and this is the second solution. You just need to slightly change your code - the first foreach loop needs to be changed like this:

foreach (var s in context.Items.Include("Params"))

这篇关于EF Code First（RC）中的多对多关系问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！