Entity Framework: Mapped multiple Tables into one Entity -Insert problems
Today I have an Entity Framework Question. I have two Tables: Projects and Projects_Rights
alt text http://www.zaczek.net/EF-stackoverflow.jpg
- Projects contains some Projects...
- Project_Rights contains access rights for each Identity (=User) for each Project. This Table is computed by triggers/functions.
Mapping those table into one entity was no challenge:
class Project
{
int ID;
double AufwandGes;
DateTime CreatedOn;
...
int CurrentIdentity__Implementation__;
int CurrentAccessRights__Implementation__;
}
<EntitySetMapping Name="Projekt">
<EntityTypeMapping TypeName="IsTypeOf(Model.Projekt)">
<MappingFragment StoreEntitySet="Projekt">
<ScalarProperty Name="ID" ColumnName="ID" />
<ScalarProperty Name="AufwandGes" ColumnName="AufwandGes" />
<ScalarProperty Name="ChangedOn" ColumnName="ChangedOn" />
<ScalarProperty Name="CreatedOn" ColumnName="CreatedOn" />
<ScalarProperty Name="Kundenname" ColumnName="Kundenname" />
<ScalarProperty Name="Name" ColumnName="Name" />
</MappingFragment>
<MappingFragment StoreEntitySet="Projekt_Rights">
<ScalarProperty Name="ID" ColumnName="ID" />
<ScalarProperty Name="CurrentIdentity__Implementation__" ColumnName="Identity" />
<ScalarProperty Name="CurrentAccessRights__Implementation__" ColumnName="Right" />
</MappingFragment>
</EntityTypeMapping>
</EntitySetMapping>
Selecting projects either:
var prjList = ctx.GetQuery<Project>()
// This condition is added automatically by a custom Linq Provider
.Where(p => p.CurrentIdentity__Implementation__ == Thread.CurrentPrincipal.Identity.ID);
I've figured out how to prevent Entity Framework from updating CurrentIdentity__Implementation__(=Identity) & CurrentAccessRights__Implementation__(=Right). This is done in the SSDL by setting StoreGeneratedPattern="Computed".
<EntityType N开发者_开发百科ame="Projekte_Rights">
<Key>
<PropertyRef Name="ID" />
</Key>
<Property Name="ID" Type="int" Nullable="false" />
<Property Name="Identity" Type="int" Nullable="false" StoreGeneratedPattern="Computed" />
<Property Name="Right" Type="int" Nullable="false" StoreGeneratedPattern="Computed" />
</EntityType>
I've declared a cascade delete in my SQL Server and SSDL. Works great!
My problem is: How can I prevent Entity Framework from inserting a record into the Project_Rights table? Adding records there is done by a trigger/function.
Thanks for pointing me to the right direction!
EDIT:
I found another way. Thanks to Alex for helping me leaving this path.
I've created a View
create view Projekte_with_Rights as
select tbl.*, r.[Identity], r.[Right]
from Projekte tbl
inner join Projekte_Rights r on tbl.ID = r.id
This View is updatable. To be able to delete a row I implemented this trigger:
create trigger Projekte_with_Rights_DeleteTrigger
ON Projekte_with_Rights
INSTEAD OF DELETE AS
BEGIN
DELETE FROM Projekte WHERE ID in (SELECT ID FROM deleted)
END
Now I can map this view as a "table" in Entity Framework. [Identity] and [Rights] are mapped as computed columns.
Another Trigger is now responsible for filling in the correct Identities and Rights. And here I have another Problem. If I insert more then one Row into the Projekte_Rights Table EF claims that a Entity returned more than one Row. but that's another story and out of scope of this question.
If you want more granular control over inserts & updates you need to use StoredProcedures for insert/update/delete operations.
Unfortunately the scope of an update/insert/delete is the Entity, you can't get more granular and configure a Sproc (for one table) and standard T-SQL (for the other table).
This means if you need to override control for the Projekt_Rights table you have to do it for the Projekt table too.
Check out this sample for more.
Note: if you need to do this for insert, unfortunately you have to do it for update and delete too.
Hope this helps
Alex
精彩评论