I have a relational model with an associative table. But in addition to the related keys, this table also has a flag. I would like to define two associations: one where the flag is true and another where it is false. The EF designer can add a condition to an entity, but not to an association.
我有一个关联模型与关联表。但除了相关的键,这个表还有一个标志。我想定义两个关联:一个是标志为真,另一个是假。 EF设计人员可以向实体添加条件,但不能向关联添加条件。
The associative table looks like this:
关联表如下所示:
UserPrivilege
-------------
UserId int (FK1)
PrivilegeId int (FK2)
IsGranted bit
I would like to create two associations between the User entity and the Privilege entity: PrivilegesGranted and PrivilegesDenied.
我想在User实体和Privilege实体之间创建两个关联:PrivilegesGranted和PrivilegesDenied。
1 个解决方案
#1
You can't do this directly through the designer.
你不能直接通过设计师这样做。
But this is possible in the XML using DefiningQuery and Create and Delete sprocs. See this old post on my blog for more: Associations with Payloads.
但是这可以在XML中使用DefiningQuery和Create and Delete sprocs。请参阅我的博客上的这篇旧帖子了解更多信息:与Payloads的关联。
The only thing that is a bit interesting is I assume the PK is just UserId and PrivilegeId, which means a user can't be granted and denied for a particular privilege at the same time.
唯一有趣的是我假设PK只是UserId和PrivilegeId,这意味着用户不能同时被授予和拒绝特定权限。
So if you write code like this:
所以,如果你编写这样的代码:
Privilege p = user.Granted.First();
user.Granted.Remove(p);
user.Denied.Add(p);
ctx.SaveChanges();
Update ordering is important. because you are using a DefiningQuery for both associations, the EF doesn't know they are related, and that it needs to do the delete before it can do the update.
更新订购很重要。因为您正在为两个关联使用DefiningQuery,所以EF不知道它们是相关的,并且它需要在它可以执行更新之前执行删除。
So you might end up with PK violations.
所以你最终可能会违反PK。
A way to address this issue is in the Insert and Delete sprocs for each association, you can essentially make them change the current row for the User and Privilege pair, if it exists update it with the correct IsGranted value, if not create it, i.e. make it an upsert.
解决此问题的一种方法是在每个关联的Insert和Delete sprocs中,基本上可以让它们更改User和Privilege对的当前行,如果存在,则使用正确的IsGranted值更新它,如果不创建它,即让它成为一个甜头。
Let me know how you go here
让我知道你怎么去这里
Alex
#1
You can't do this directly through the designer.
你不能直接通过设计师这样做。
But this is possible in the XML using DefiningQuery and Create and Delete sprocs. See this old post on my blog for more: Associations with Payloads.
但是这可以在XML中使用DefiningQuery和Create and Delete sprocs。请参阅我的博客上的这篇旧帖子了解更多信息:与Payloads的关联。
The only thing that is a bit interesting is I assume the PK is just UserId and PrivilegeId, which means a user can't be granted and denied for a particular privilege at the same time.
唯一有趣的是我假设PK只是UserId和PrivilegeId,这意味着用户不能同时被授予和拒绝特定权限。
So if you write code like this:
所以,如果你编写这样的代码:
Privilege p = user.Granted.First();
user.Granted.Remove(p);
user.Denied.Add(p);
ctx.SaveChanges();
Update ordering is important. because you are using a DefiningQuery for both associations, the EF doesn't know they are related, and that it needs to do the delete before it can do the update.
更新订购很重要。因为您正在为两个关联使用DefiningQuery,所以EF不知道它们是相关的,并且它需要在它可以执行更新之前执行删除。
So you might end up with PK violations.
所以你最终可能会违反PK。
A way to address this issue is in the Insert and Delete sprocs for each association, you can essentially make them change the current row for the User and Privilege pair, if it exists update it with the correct IsGranted value, if not create it, i.e. make it an upsert.
解决此问题的一种方法是在每个关联的Insert和Delete sprocs中,基本上可以让它们更改User和Privilege对的当前行,如果存在,则使用正确的IsGranted值更新它,如果不创建它,即让它成为一个甜头。
Let me know how you go here
让我知道你怎么去这里
Alex