在自引用表中预先加载 Linq to SQL 实体

发布于 08-02 10:19 字数 2522 浏览 7 评论 0原文

我有 2 个相关的 Linq to SQL 问题。请参见下图,看看我的模型是什么样子。

问题 1

我试图弄清楚如何在我的 User 类/表上预先加载 User.AddedByUser 字段。该字段是根据 User.AddedByUserId 字段上的关系生成的。该表是自引用的,我试图弄清楚如何让 Linq to SQL 立即加载 User.AddedByUser 属性,即每当任何 User 实体出现时加载/获取时,它还必须获取 User.AddedByUser 和 User.ChangedByUser。但是,我知道这可能会成为一个递归问题...

更新 1.1:

我尝试按如下方式使用 DataLoadOptions:

var options = new DataLoadOptions();
options.LoadWith<User>(u => u.ChangedByUser);
options.LoadWith<User>(u => u.AddedByUser);

db = new ModelDataContext(connectionString);
db.LoadOptions = options;

但这不起作用,我在第 2 行收到以下异常:

System.InvalidOperationException occurred
  Message="Cycles not allowed in LoadOptions LoadWith type graph."
  Source="System.Data.Linq"
  StackTrace:
       at System.Data.Linq.DataLoadOptions.ValidateTypeGraphAcyclic()
       at System.Data.Linq.DataLoadOptions.Preload(MemberInfo association)
       at System.Data.Linq.DataLoadOptions.LoadWith[T](Expression`1 expression)
       at i3t.KpCosting.Service.Library.Repositories.UserRepository..ctor(String connectionString) in C:\Development\KP Costing\Trunk\Code\i3t.KpCosting.Service.Library\Repositories\UserRepository.cs:line 15
  InnerException:

这个例外是不言自明的 - 对象图不允许是循环的。 另外,假设第 2 行没有抛出异常,我很确定第 3 行会抛出异常,因为它们是重复的键。

更新 1.2

以下内容也不起作用(不与上面的更新 1.1 结合使用):

var query = from u in db.Users
            select new User()
            {
                Id = u.Id,
                // other fields removed for brevityy
                AddedByUser = u.AddedByUser,
                ChangedByUser = u.ChangedByUser,

            };
return query.ToList();

它抛出以下不言自明的异常:

System.NotSupportedException occurred
Message="Explicit construction of entity type 'i3t.KpCosting.Shared.Model.User' in query is not allowed."

我现在真的处于不知道如何解决这个问题。请帮忙!

问题 2

在我的数据库中的每个其他表以及 Linq to SQL 模型中,我有两个字段,Entity.ChangedByUser(链接到 Entity.ChangedByUserId) code> 外键/关系)和 Entity.AddedByUser (链接到 Entity.AddedByUserId 外键/关系)

如何让 Linq to SQL 急切地为我加载这些字段?我是否需要对查询进行简单的联接?或者还有其他方法吗?

Linq to SQL 在自引用表上急切加载 http://img245.imageshack.us/img245 /5631/linqtosql.jpg

I have 2 related Linq to SQL questions. Please see the image below to see what my Model looks like.

Question 1

I am trying to figure how to eager load the User.AddedByUser field on my User class/table. This field is generated from the relationship on the User.AddedByUserId field. The table is self-referencing, and I am trying to figure out how to get Linq to SQL to load up the User.AddedByUser property eagerly, i.e. whenever any User entity is loaded/fetched, it must also fetch the User.AddedByUser and User.ChangedByUser. However, I understand that this could become a recursive problem...

Update 1.1:

I've tried to use the DataLoadOptions as follows:

var options = new DataLoadOptions();
options.LoadWith<User>(u => u.ChangedByUser);
options.LoadWith<User>(u => u.AddedByUser);

db = new ModelDataContext(connectionString);
db.LoadOptions = options;

But this doesn't work, I get the following exception on Line 2:

System.InvalidOperationException occurred
  Message="Cycles not allowed in LoadOptions LoadWith type graph."
  Source="System.Data.Linq"
  StackTrace:
       at System.Data.Linq.DataLoadOptions.ValidateTypeGraphAcyclic()
       at System.Data.Linq.DataLoadOptions.Preload(MemberInfo association)
       at System.Data.Linq.DataLoadOptions.LoadWith[T](Expression`1 expression)
       at i3t.KpCosting.Service.Library.Repositories.UserRepository..ctor(String connectionString) in C:\Development\KP Costing\Trunk\Code\i3t.KpCosting.Service.Library\Repositories\UserRepository.cs:line 15
  InnerException:

The exception is quite self-explanatory - the object graph isn't allowed to be Cyclic.
Also, assuming Line 2 didn't throw an exception, I'm pretty sure Line 3 would, since they are duplicate keys.

Update 1.2:

The following doesn't work either (not used in conjuction with Update 1.1 above):

var query = from u in db.Users
            select new User()
            {
                Id = u.Id,
                // other fields removed for brevityy
                AddedByUser = u.AddedByUser,
                ChangedByUser = u.ChangedByUser,

            };
return query.ToList();

It throws the following, self-explanatory exception:

System.NotSupportedException occurred
Message="Explicit construction of entity type 'i3t.KpCosting.Shared.Model.User' in query is not allowed."

I am now REALLY at a loss on how to solve this. Please help!

Question 2

On every other table in my DB, and hence Linq to SQL model, I have two fields, Entity.ChangedByUser (linked to Entity.ChangedByUserId foreign key/relationship) and Entity.AddedByUser (linked to Entity.AddedByUserId foreign key/relationship)

How do I get Linq to SQL to eageryly load these fields for me? Do I need to do a simple join on my queries?, or is there some other way?

Linq to SQL eager loading on self referencing table http://img245.imageshack.us/img245/5631/linqtosql.jpg

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

知你几分2024-08-09 10:19:10

不允许任何类型的循环。由于 LoadWith< /code>AssociateWith应用于上下文中的每种类型,没有内部方法可以防止无限循环。更准确地说,它只是对如何创建 SQL 感到困惑,因为 SQL Server 没有 CONNECT BYCTE 确实超出了 Linq 可以使用提供的框架自动生成的内容。

您可用的最佳选择是手动对两个子项的用户表进行一级连接,并使用匿名类型返回它们。抱歉,这不是一个干净/简单的解决方案,但它确实是迄今为止 Linq 可用的所有解决方案。

Any type of cycles just aren't allowed. Since the LoadWith<T> or AssociateWith<T> are applied to every type on the context, there's no internal way to prevent an endless loop. More accurately, it's just confused on how to create the SQL since SQL Server doesn't have CONNECT BY and CTEs are really past what Linq can generate automatically with the provided framework.

The best option available to you is to manually do the 1 level join down to the user table for both of the children and an anonymous type to return them. Sorry it's not a clean/easy solution, but it's really all that's available thus far with Linq.

也许您可以尝试退后一步,看看您想对这种关系做什么?我假设您想向用户显示此信息,例如“由 Iain Galloway 8 小时前修改”。

像下面这样的东西可以工作吗? :-

var users = from u in db.Users
            select new
            {
              /* other stuff... */
              AddedTimestamp = u.AddedTimestamp,
              AddedDescription = u.AddedByUser.FullName,
              ChangedTimestamp = u.ChangedTimestamp,
              ChangedDescription = u.ChangedByUser.FullName
            };

为了(imo)清晰起见,我在那里使用了匿名类型。如果您愿意,可以将这些属性添加到您的用户类型中。

至于你的第二个问题,你正常的 LoadWith(x => x.AddedByUser) 等应该工作得很好 - 尽管我倾向于将描述字符串直接存储在数据库中 - 你需要在描述之间进行权衡当 ChangedByUser.FullName 更改时进行更新,并且如果 ChangedByUser 被删除,则必须执行一些复杂且可能违反直觉的操作(例如 ON DELETE CASCADE,或在代码中处理 null ChangedByUser)。

Maybe you could try taking a step back and seeing what you want to do with the relation? I'm assuming you want to display this information to the user in e.g. "modified by Iain Galloway 8 hours ago".

Could something like the following work? :-

var users = from u in db.Users
            select new
            {
              /* other stuff... */
              AddedTimestamp = u.AddedTimestamp,
              AddedDescription = u.AddedByUser.FullName,
              ChangedTimestamp = u.ChangedTimestamp,
              ChangedDescription = u.ChangedByUser.FullName
            };

I've used an anonymous type there for (imo) clarity. You could add those properties to your User type if you preferred.

As for your second question, your normal LoadWith(x => x.AddedByUser) etc. should work just fine - although I tend to prefer storing the description string directly in the database - you've got a trade-off between your description updating when ChangedByUser.FullName changes and having to do something complicated and possibly counterintuitive if the ChangedByUser gets deleted (e.g. ON DELETE CASCADE, or dealing with a null ChangedByUser in your code).

花伊自在美2024-08-09 10:19:10

不确定 Linq to Sql 是否有解决此问题的方法。如果您使用的是 Sql Server 2005,您可以定义一个(类似递归的)存储过程,它使用公用表表达式来获取所需的结果,然后使用 DataContext.ExecuteQuery 执行该结果。

Not sure there is a solution to this problem with Linq to Sql. If you are using Sql Server 2005 you could define a (recursive like) Stored Procecdure that uses common table expressions to get the result that you want and then execute that using DataContext.ExecuteQuery.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文