在自引用表中预先加载 Linq to SQL 实体
我有 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?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
发布评论
评论(3)
也许您可以尝试退后一步,看看您想对这种关系做什么?我假设您想向用户显示此信息,例如“由 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)。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
不允许任何类型的循环。由于
LoadWith< /code>
或
AssociateWith
应用于上下文中的每种类型,没有内部方法可以防止无限循环。更准确地说,它只是对如何创建 SQL 感到困惑,因为 SQL Server 没有CONNECT BY
和 CTE 确实超出了 Linq 可以使用提供的框架自动生成的内容。您可用的最佳选择是手动对两个子项的用户表进行一级连接,并使用匿名类型返回它们。抱歉,这不是一个干净/简单的解决方案,但它确实是迄今为止 Linq 可用的所有解决方案。
Any type of cycles just aren't allowed. Since the
LoadWith<T>
orAssociateWith<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 haveCONNECT 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.