Entity Framework: Mapped multiple Tables into one Entity -Insert problems

Question

Hi all!

Today I have an Entity Framework Question. I have two Tables: Projects and Projects_Rights

• 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 Name="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.

Answer 1

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