Entity Framework: Creating a model using views instead of tables

In Entity Framework, Views are “readonly” due to the DefiningQuery element. There are two approaches to make a view editable:

  1. Create stored procedures for Insert/Update/Delete: This is described in this document and this walkthrough.
  2. Make Entity Framework treat views as tables by doing some manual modifications in the generated EDMX file: This is described in this post.

A few days back, I created an entity model on top of views instead of tables. Since my physical model consisted of Views and not tables, so Entity Designer was not able to infer primary keys/relations etc., and I had to create them manually. In this post, I will highlight the steps I took to create a working entity model by hand. Please read at your own risk and feel free to contribute your ideas using the comments section. Note that not all views are updatable, and this MSDN document describes certain properties that a view definition must hold in order to be updatable.

Assume we have a JobWBS and a ResourceAssignment view with a One-to-Many relationship as depicted below:

db-model

If we try to create an “ADO.NET Entity Data Model” for the above views, Visual Studio will generate a model similar to this one.

generated-model

Notice that the designer marked all fields as primary keys. We need to manually edit the generated edmx file by opening it with “XML editor”.

open-with-xml-editor

You will also notice the following warnings inside the edmx xml:

Errors Found During Generation:
warning 6002: The table/view 'testdb.dbo.vw_JobWBS' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view.

The edmx file mainly contains 3 parts (4th part is related to visual designer which is not significant for us): storage model, conceptual model and the mapping between them. You can identify each part easily as it is decorated with XML comments.

Assigning Key Columns

We first need to remove the non-primarykey columns from the key attributes of both entries from storage and conceptual model. For this, just remove non-primary columns within <Key> and </Key> boundaries under storage model as well as conceptual model.

Making views writable

Another point to note is that the entity are defined with a DefiningQuery instead of the physical view/table. This prevents any inserts/updates to the database and if we try to do so, we will get something like:

Unable to update the EntitySet xxx because it has a DefiningQuery and no <UpdateFunction> element exists in the <ModificationFunctionMapping> element to support the current operation.

To solve this, remove the <DefininingQuery> .. </DefiningQuery> block under <edmx:StorageModels> / <Schema> / <EntityContainer> / <EntitySet>. Then change <EntitySet EntityType="…" store:Type="Views" store:Schema="dbo" store:Name="…"> to <EntitySet EntityType="…" Schema="dbo">.

Adding associations

That’s all regarding CRUD operations. Now we need to add a One-to-Many relationship between JobWBS and ResourceAssignment. For this, we need to open the visual designer,  add a 1-* association between JobWBS and ResourceAssignment, and map it to ResourceAssignment table. Then we need to remove the JobNo and JobWBS fields from our ResourceAssignment entity to get rid of the following error:

Non-Primary-Key column(s) [JobNo, JobWBSCode] are being mapped in both fragments to different conceptual side properties - data inconsistency is possible because the corresponding conceptual side properties can be independently modified.

At this point, our model is ready to be consumed via its domain context.

Adding association to storage schema

Notice that while working with RIA Domain Services, I found that we need to add the One-Many relation in the storage schema also, otherwise we will get the error: Unable to retrieve AssociationType for association 'FK_ResourceAssignment_JobWBS'. To add the association to the storage schema, add the following under <edmx:StorageModels> / <Schema> / <EntityContainer> just after the entities are defined:

          <AssociationSet Name="FK_ResourceAssignment_JobWBS" Association="MyModelNamespace.Store.FK_ResourceAssignment_JobWBS">
            <End Role="JobWBS" EntitySet="JobWBS" />
            <End Role="ResourceAssignment" EntitySet="ResourceAssignment" />
          </AssociationSet>

Then define the association under <edmx:StorageModels> / <Schema> as:

        <Association Name="FK_ResourceAssignment_JobWBS">
          <End Role="JobWBS" Type="MyModelNamespace.Store.JobWBS" Multiplicity="1" />
          <End Role="ResourceAssignment" Type="MyModelNamespace.Store.ResourceAssignment" Multiplicity="*" />
          <ReferentialConstraint>
            <Principal Role="JobWBS">
              <PropertyRef Name="JobNo" />
              <PropertyRef Name="JobWBSCode" />
            </Principal>
            <Dependent Role="ResourceAssignment">
              <PropertyRef Name="JobNo" />
              <PropertyRef Name="JobWBSCode" />
            </Dependent>
          </ReferentialConstraint>
        </Association>

Now our RIA domain service will not have any such complain.

A final comment:

Note that anytime you update the model from database, it will reset the SSDL and we will manually need to redo all the steps (related to storage model) as described above.

Finally, here’s the generated edmx and the modified one. You can run a compare on the two versions to see what changes we have made.

Generated

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="1.0" xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx">
    <!-- EF Runtime content -->
    <edmx:Runtime>
        <!-- SSDL content -->
        <edmx:StorageModels>
            <Schema Namespace="MyModelNamespace.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2005" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl">
                <EntityContainer Name="MyModelNamespaceStoreContainer">
                    <EntitySet Name="JobWBS" EntityType="MyModelNamespace.Store.JobWBS" store:Type="Views" store:Schema="dbo" store:Name="JobWBS">
                        <DefiningQuery>
                            SELECT
                            [JobWBS].[timestamp] AS [timestamp],
                            [JobWBS].[JobNo] AS [JobNo],
                            [JobWBS].[JobWBSCode] AS [JobWBSCode],
                            [JobWBS].[Description] AS [Description],
                            [JobWBS].[Level] AS [Level],
                            [JobWBS].[StartDate] AS [StartDate],
                            [JobWBS].[EndDate] AS [EndDate],
                            [JobWBS].[Duration] AS [Duration],
                            [JobWBS].[LastModified] AS [LastModified]
                            FROM [dbo].[JobWBS] AS [JobWBS]
                        </DefiningQuery>
                    </EntitySet>
                    <EntitySet Name="ResourceAssignment" EntityType="MyModelNamespace.Store.ResourceAssignment" store:Type="Views" store:Schema="dbo" store:Name="ResourceAssignment">
                        <DefiningQuery>
                            SELECT
                            [ResourceAssignment].[timestamp] AS [timestamp],
                            [ResourceAssignment].[AssignmentID] AS [AssignmentID],
                            [ResourceAssignment].[JobNo] AS [JobNo],
                            [ResourceAssignment].[JobWBSCode] AS [JobWBSCode],
                            [ResourceAssignment].[ResourceNo] AS [ResourceNo],
                            [ResourceAssignment].[StartDate] AS [StartDate],
                            [ResourceAssignment].[EndDate] AS [EndDate],
                            [ResourceAssignment].[HoursPerDay] AS [HoursPerDay]
                            FROM [dbo].[ResourceAssignment] AS [ResourceAssignment]
                        </DefiningQuery>
                    </EntitySet>
                </EntityContainer>
                <!--Errors Found During Generation:
      warning 6002: The table/view 'testdb.dbo.JobWBS' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view.
      -->
                <EntityType Name="JobWBS">
                    <Key>
                        <PropertyRef Name="JobNo" />
                        <PropertyRef Name="JobWBSCode" />
                        <PropertyRef Name="Description" />
                        <PropertyRef Name="Level" />
                        <PropertyRef Name="StartDate" />
                        <PropertyRef Name="EndDate" />
                        <PropertyRef Name="Duration" />
                        <PropertyRef Name="LastModified" />
                    </Key>
                    <Property Name="timestamp" Type="timestamp" Nullable="false" StoreGeneratedPattern="Computed" />
                    <Property Name="JobNo" Type="varchar" Nullable="false" MaxLength="20" />
                    <Property Name="JobWBSCode" Type="varchar" Nullable="false" MaxLength="60" />
                    <Property Name="Description" Type="varchar" Nullable="false" MaxLength="50" />
                    <Property Name="Level" Type="int" Nullable="false" />
                    <Property Name="StartDate" Type="datetime" Nullable="false" />
                    <Property Name="EndDate" Type="datetime" Nullable="false" />
                    <Property Name="Duration" Type="decimal" Nullable="false" Precision="38" Scale="20" />
                    <Property Name="LastModified" Type="datetime" Nullable="false" />
                </EntityType>
                <!--Errors Found During Generation:
      warning 6002: The table/view 'testdb.dbo.ResourceAssignment' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view.
      -->
                <EntityType Name="ResourceAssignment">
                    <Key>
                        <PropertyRef Name="AssignmentID" />
                        <PropertyRef Name="JobNo" />
                        <PropertyRef Name="JobWBSCode" />
                        <PropertyRef Name="ResourceNo" />
                        <PropertyRef Name="StartDate" />
                        <PropertyRef Name="EndDate" />
                        <PropertyRef Name="HoursPerDay" />
                    </Key>
                    <Property Name="timestamp" Type="timestamp" Nullable="false" StoreGeneratedPattern="Computed" />
                    <Property Name="AssignmentID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
                    <Property Name="JobNo" Type="varchar" Nullable="false" MaxLength="20" />
                    <Property Name="JobWBSCode" Type="varchar" Nullable="false" MaxLength="60" />
                    <Property Name="ResourceNo" Type="varchar" Nullable="false" MaxLength="20" />
                    <Property Name="StartDate" Type="datetime" Nullable="false" />
                    <Property Name="EndDate" Type="datetime" Nullable="false" />
                    <Property Name="HoursPerDay" Type="decimal" Nullable="false" Precision="38" Scale="20" />
                </EntityType>
            </Schema>
        </edmx:StorageModels>
        <!-- CSDL content -->
        <edmx:ConceptualModels>
            <Schema Namespace="MyModelNamespace" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2006/04/edm">
                <EntityContainer Name="MyEntries">
                    <EntitySet Name="JobWBS" EntityType="MyModelNamespace.JobWBS" />
                    <EntitySet Name="ResourceAssignment" EntityType="MyModelNamespace.ResourceAssignment" />
                </EntityContainer>
                <EntityType Name="JobWBS">
                    <Key>
                        <PropertyRef Name="JobNo" />
                        <PropertyRef Name="JobWBSCode" />
                        <PropertyRef Name="Description" />
                        <PropertyRef Name="Level" />
                        <PropertyRef Name="StartDate" />
                        <PropertyRef Name="EndDate" />
                        <PropertyRef Name="Duration" />
                        <PropertyRef Name="LastModified" />
                    </Key>
                    <Property Name="timestamp" Type="Binary" Nullable="false" MaxLength="8" FixedLength="true" />
                    <Property Name="JobNo" Type="String" Nullable="false" MaxLength="20" Unicode="false" FixedLength="false" />
                    <Property Name="JobWBSCode" Type="String" Nullable="false" MaxLength="60" Unicode="false" FixedLength="false" />
                    <Property Name="Description" Type="String" Nullable="false" MaxLength="50" Unicode="false" FixedLength="false" />
                    <Property Name="Level" Type="Int32" Nullable="false" />
                    <Property Name="StartDate" Type="DateTime" Nullable="false" />
                    <Property Name="EndDate" Type="DateTime" Nullable="false" />
                    <Property Name="Duration" Type="Decimal" Nullable="false" Precision="38" Scale="20" />
                    <Property Name="LastModified" Type="DateTime" Nullable="false" />
                </EntityType>
                <EntityType Name="ResourceAssignment">
                    <Key>
                        <PropertyRef Name="AssignmentID" />
                        <PropertyRef Name="JobNo" />
                        <PropertyRef Name="JobWBSCode" />
                        <PropertyRef Name="ResourceNo" />
                        <PropertyRef Name="StartDate" />
                        <PropertyRef Name="EndDate" />
                        <PropertyRef Name="HoursPerDay" />
                    </Key>
                    <Property Name="timestamp" Type="Binary" Nullable="false" MaxLength="8" FixedLength="true" />
                    <Property Name="AssignmentID" Type="Int32" Nullable="false" />
                    <Property Name="JobNo" Type="String" Nullable="false" MaxLength="20" Unicode="false" FixedLength="false" />
                    <Property Name="JobWBSCode" Type="String" Nullable="false" MaxLength="60" Unicode="false" FixedLength="false" />
                    <Property Name="ResourceNo" Type="String" Nullable="false" MaxLength="20" Unicode="false" FixedLength="false" />
                    <Property Name="StartDate" Type="DateTime" Nullable="false" />
                    <Property Name="EndDate" Type="DateTime" Nullable="false" />
                    <Property Name="HoursPerDay" Type="Decimal" Nullable="false" Precision="38" Scale="20" />
                </EntityType>
            </Schema>
        </edmx:ConceptualModels>
        <!-- C-S mapping content -->
        <edmx:Mappings>
            <Mapping Space="C-S" xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS">
                <EntityContainerMapping StorageEntityContainer="MyModelNamespaceStoreContainer" CdmEntityContainer="MyEntries">
                    <EntitySetMapping Name="JobWBS">
                        <EntityTypeMapping TypeName="IsTypeOf(MyModelNamespace.JobWBS)">
                            <MappingFragment StoreEntitySet="JobWBS">
                                <ScalarProperty Name="timestamp" ColumnName="timestamp" />
                                <ScalarProperty Name="JobNo" ColumnName="JobNo" />
                                <ScalarProperty Name="JobWBSCode" ColumnName="JobWBSCode" />
                                <ScalarProperty Name="Description" ColumnName="Description" />
                                <ScalarProperty Name="Level" ColumnName="Level" />
                                <ScalarProperty Name="StartDate" ColumnName="StartDate" />
                                <ScalarProperty Name="EndDate" ColumnName="EndDate" />
                                <ScalarProperty Name="Duration" ColumnName="Duration" />
                                <ScalarProperty Name="LastModified" ColumnName="LastModified" />
                            </MappingFragment>
                        </EntityTypeMapping>
                    </EntitySetMapping>
                    <EntitySetMapping Name="ResourceAssignment">
                        <EntityTypeMapping TypeName="IsTypeOf(MyModelNamespace.ResourceAssignment)">
                            <MappingFragment StoreEntitySet="ResourceAssignment">
                                <ScalarProperty Name="timestamp" ColumnName="timestamp" />
                                <ScalarProperty Name="AssignmentID" ColumnName="AssignmentID" />
                                <ScalarProperty Name="JobNo" ColumnName="JobNo" />
                                <ScalarProperty Name="JobWBSCode" ColumnName="JobWBSCode" />
                                <ScalarProperty Name="ResourceNo" ColumnName="ResourceNo" />
                                <ScalarProperty Name="StartDate" ColumnName="StartDate" />
                                <ScalarProperty Name="EndDate" ColumnName="EndDate" />
                                <ScalarProperty Name="HoursPerDay" ColumnName="HoursPerDay" />
                            </MappingFragment>
                        </EntityTypeMapping>
                    </EntitySetMapping>
                </EntityContainerMapping>
            </Mapping>
        </edmx:Mappings>
    </edmx:Runtime>
    <!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) -->
    <edmx:Designer xmlns="http://schemas.microsoft.com/ado/2007/06/edmx">
        <edmx:Connection>
            <DesignerInfoPropertySet>
                <DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" />
            </DesignerInfoPropertySet>
        </edmx:Connection>
        <edmx:Options>
            <DesignerInfoPropertySet>
                <DesignerProperty Name="ValidateOnBuild" Value="true" />
            </DesignerInfoPropertySet>
        </edmx:Options>
        <!-- Diagram content (shape and connector positions) -->
        <edmx:Diagrams>
            <Diagram Name="MyDataModel">
                <EntityTypeShape EntityType="MyModelNamespace.JobWBS" Width="1.5" PointX="0.75" PointY="0.75" Height="2.41404296875" IsExpanded="true" />
                <EntityTypeShape EntityType="MyModelNamespace.ResourceAssignment" Width="1.5" PointX="3.875" PointY="0.75" Height="2.2496956380208331" IsExpanded="true" />
            </Diagram>
        </edmx:Diagrams>
    </edmx:Designer>
</edmx:Edmx>

Modified:

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="1.0" xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx">
    <!-- EF Runtime content -->
    <edmx:Runtime>
        <!-- SSDL content -->
        <edmx:StorageModels>
            <Schema Namespace="MyModelNamespace.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2005" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl">
                <EntityContainer Name="MyModelNamespaceStoreContainer">
                    <EntitySet Name="JobWBS" EntityType="MyModelNamespace.Store.JobWBS" Schema="dbo">
                    </EntitySet>
                    <EntitySet Name="ResourceAssignment" EntityType="MyModelNamespace.Store.ResourceAssignment" Schema="dbo">
                    </EntitySet>
                    <AssociationSet Name="FK_ResourceAssignment_JobWBS" Association="MyModelNamespace.Store.FK_ResourceAssignment_JobWBS">
                        <End Role="JobWBS" EntitySet="JobWBS" />
                        <End Role="ResourceAssignment" EntitySet="ResourceAssignment" />
                    </AssociationSet>
                </EntityContainer>
                <EntityType Name="JobWBS">
                    <Key>
                        <PropertyRef Name="JobNo" />
                        <PropertyRef Name="JobWBSCode" />
                    </Key>
                    <Property Name="timestamp" Type="timestamp" Nullable="false" StoreGeneratedPattern="Computed" />
                    <Property Name="JobNo" Type="varchar" Nullable="false" MaxLength="20" />
                    <Property Name="JobWBSCode" Type="varchar" Nullable="false" MaxLength="60" />
                    <Property Name="Description" Type="varchar" Nullable="false" MaxLength="50" />
                    <Property Name="Level" Type="int" Nullable="false" />
                    <Property Name="StartDate" Type="datetime" Nullable="false" />
                    <Property Name="EndDate" Type="datetime" Nullable="false" />
                    <Property Name="Duration" Type="decimal" Nullable="false" Precision="38" Scale="20" />
                    <Property Name="LastModified" Type="datetime" Nullable="false" />
                </EntityType>
                <EntityType Name="ResourceAssignment">
                    <Key>
                        <PropertyRef Name="AssignmentID" />
                    </Key>
                    <Property Name="timestamp" Type="timestamp" Nullable="false" StoreGeneratedPattern="Computed" />
                    <Property Name="AssignmentID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
                    <Property Name="JobNo" Type="varchar" Nullable="false" MaxLength="20" />
                    <Property Name="JobWBSCode" Type="varchar" Nullable="false" MaxLength="60" />
                    <Property Name="ResourceNo" Type="varchar" Nullable="false" MaxLength="20" />
                    <Property Name="StartDate" Type="datetime" Nullable="false" />
                    <Property Name="EndDate" Type="datetime" Nullable="false" />
                    <Property Name="HoursPerDay" Type="decimal" Nullable="false" Precision="38" Scale="20" />
                </EntityType>
                <Association Name="FK_ResourceAssignment_JobWBS">
                    <End Role="JobWBS" Type="MyModelNamespace.Store.JobWBS" Multiplicity="1" />
                    <End Role="ResourceAssignment" Type="MyModelNamespace.Store.ResourceAssignment" Multiplicity="*" />
                    <ReferentialConstraint>
                        <Principal Role="JobWBS">
                            <PropertyRef Name="JobNo" />
                            <PropertyRef Name="JobWBSCode" />
                        </Principal>
                        <Dependent Role="ResourceAssignment">
                            <PropertyRef Name="JobNo" />
                            <PropertyRef Name="JobWBSCode" />
                        </Dependent>
                    </ReferentialConstraint>
                </Association>
            </Schema>
        </edmx:StorageModels>
        <!-- CSDL content -->
        <edmx:ConceptualModels>
            <Schema Namespace="MyModelNamespace" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2006/04/edm">
                <EntityContainer Name="MyEntries">
                    <EntitySet Name="JobWBS" EntityType="MyModelNamespace.JobWBS" />
                    <EntitySet Name="ResourceAssignment" EntityType="MyModelNamespace.ResourceAssignment" />
                    <AssociationSet Name="FK_ResourceAssignment_JobWBS" Association="MyModelNamespace.FK_ResourceAssignment_JobWBS">
                        <End Role="JobWBS" EntitySet="JobWBS" />
                        <End Role="ResourceAssignment" EntitySet="ResourceAssignment" />
                    </AssociationSet>
                </EntityContainer>
                <EntityType Name="JobWBS">
                    <Key>
                        <PropertyRef Name="JobNo" />
                        <PropertyRef Name="JobWBSCode" />
                    </Key>
                    <Property Name="timestamp" Type="Binary" Nullable="false" MaxLength="8" FixedLength="true" />
                    <Property Name="JobNo" Type="String" Nullable="false" MaxLength="20" Unicode="false" FixedLength="false" />
                    <Property Name="JobWBSCode" Type="String" Nullable="false" MaxLength="60" Unicode="false" FixedLength="false" />
                    <Property Name="Description" Type="String" Nullable="false" MaxLength="50" Unicode="false" FixedLength="false" />
                    <Property Name="Level" Type="Int32" Nullable="false" />
                    <Property Name="StartDate" Type="DateTime" Nullable="false" />
                    <Property Name="EndDate" Type="DateTime" Nullable="false" />
                    <Property Name="Duration" Type="Decimal" Nullable="false" Precision="38" Scale="20" />
                    <Property Name="LastModified" Type="DateTime" Nullable="false" />
                    <NavigationProperty Name="ResourceAssignment" Relationship="MyModelNamespace.FK_ResourceAssignment_JobWBS" FromRole="JobWBS" ToRole="ResourceAssignment" />
                </EntityType>
                <EntityType Name="ResourceAssignment">
                    <Key>
                        <PropertyRef Name="AssignmentID" />
                    </Key>
                    <Property Name="timestamp" Type="Binary" Nullable="false" MaxLength="8" FixedLength="true" />
                    <Property Name="AssignmentID" Type="Int32" Nullable="false" />
                    <Property Name="ResourceNo" Type="String" Nullable="false" MaxLength="20" Unicode="false" FixedLength="false" />
                    <Property Name="StartDate" Type="DateTime" Nullable="false" />
                    <Property Name="EndDate" Type="DateTime" Nullable="false" />
                    <Property Name="HoursPerDay" Type="Decimal" Nullable="false" Precision="38" Scale="20" />
                    <NavigationProperty Name="JobWBS" Relationship="MyModelNamespace.FK_ResourceAssignment_JobWBS" FromRole="ResourceAssignment" ToRole="JobWBS" />
                </EntityType>
                <Association Name="FK_ResourceAssignment_JobWBS">
                    <End Type="MyModelNamespace.JobWBS" Role="JobWBS" Multiplicity="1" />
                    <End Type="MyModelNamespace.ResourceAssignment" Role="ResourceAssignment" Multiplicity="*" />
                </Association>
            </Schema>
        </edmx:ConceptualModels>
        <!-- C-S mapping content -->
        <edmx:Mappings>
            <Mapping Space="C-S" xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS">
                <EntityContainerMapping StorageEntityContainer="MyModelNamespaceStoreContainer" CdmEntityContainer="MyEntries">
                    <EntitySetMapping Name="JobWBS">
                        <EntityTypeMapping TypeName="IsTypeOf(MyModelNamespace.JobWBS)">
                            <MappingFragment StoreEntitySet="JobWBS">
                                <ScalarProperty Name="timestamp" ColumnName="timestamp" />
                                <ScalarProperty Name="JobNo" ColumnName="JobNo" />
                                <ScalarProperty Name="JobWBSCode" ColumnName="JobWBSCode" />
                                <ScalarProperty Name="Description" ColumnName="Description" />
                                <ScalarProperty Name="Level" ColumnName="Level" />
                                <ScalarProperty Name="StartDate" ColumnName="StartDate" />
                                <ScalarProperty Name="EndDate" ColumnName="EndDate" />
                                <ScalarProperty Name="Duration" ColumnName="Duration" />
                                <ScalarProperty Name="LastModified" ColumnName="LastModified" />
                            </MappingFragment>
                        </EntityTypeMapping>
                    </EntitySetMapping>
                    <EntitySetMapping Name="ResourceAssignment">
                        <EntityTypeMapping TypeName="IsTypeOf(MyModelNamespace.ResourceAssignment)">
                            <MappingFragment StoreEntitySet="ResourceAssignment">
                                <ScalarProperty Name="timestamp" ColumnName="timestamp" />
                                <ScalarProperty Name="AssignmentID" ColumnName="AssignmentID" />
                                <ScalarProperty Name="ResourceNo" ColumnName="ResourceNo" />
                                <ScalarProperty Name="StartDate" ColumnName="StartDate" />
                                <ScalarProperty Name="EndDate" ColumnName="EndDate" />
                                <ScalarProperty Name="HoursPerDay" ColumnName="HoursPerDay" />
                            </MappingFragment>
                        </EntityTypeMapping>
                    </EntitySetMapping>
                    <AssociationSetMapping Name="FK_ResourceAssignment_JobWBS" TypeName="MyModelNamespace.FK_ResourceAssignment_JobWBS" StoreEntitySet="ResourceAssignment">
                        <EndProperty Name="ResourceAssignment">
                            <ScalarProperty Name="AssignmentID" ColumnName="AssignmentID" />
                        </EndProperty>
                        <EndProperty Name="JobWBS">
                            <ScalarProperty Name="JobWBSCode" ColumnName="JobWBSCode" />
                            <ScalarProperty Name="JobNo" ColumnName="JobNo" />
                        </EndProperty>
                    </AssociationSetMapping>
                </EntityContainerMapping>
            </Mapping>
        </edmx:Mappings>
    </edmx:Runtime>
    <!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) -->
    <edmx:Designer xmlns="http://schemas.microsoft.com/ado/2007/06/edmx">
        <edmx:Connection>
            <DesignerInfoPropertySet>
                <DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" />
            </DesignerInfoPropertySet>
        </edmx:Connection>
        <edmx:Options>
            <DesignerInfoPropertySet>
                <DesignerProperty Name="ValidateOnBuild" Value="true" />
            </DesignerInfoPropertySet>
        </edmx:Options>
        <!-- Diagram content (shape and connector positions) -->
        <edmx:Diagrams>
            <Diagram Name="MyDataModel">
                <EntityTypeShape EntityType="MyModelNamespace.JobWBS" Width="1.5" PointX="0.75" PointY="0.75" Height="2.41404296875" IsExpanded="true" />
                <EntityTypeShape EntityType="MyModelNamespace.ResourceAssignment" Width="1.5" PointX="3.875" PointY="0.75" Height="2.085348307291667" IsExpanded="true" />
                <AssociationConnector Association="MyModelNamespace.FK_ResourceAssignment_JobWBS">
                    <ConnectorPoint PointX="2.25" PointY="1.7926741536458335" />
                    <ConnectorPoint PointX="3.875" PointY="1.7926741536458335" />
                </AssociationConnector>
            </Diagram>
        </edmx:Diagrams>
    </edmx:Designer>
</edmx:Edmx>
About these ads

28 Responses to “Entity Framework: Creating a model using views instead of tables”

  1. RIA Services: Working with Foreign Keys/Associations in Entity Framework « Mehroz’s Experiments Says:

    [...] Its probably because your SSDL schema does not have the proper associations defined. Have a look at this post on how to manually define associations in the physical (storage) model. Possibly related [...]

  2. Tanveer Badar Says:

    You would be much better off using nHibernate than EF. It is a half baked solution which has been a disappointment for ORM users from day 1. Microsoft over promised and under delivered just like Windows Vista.

  3. girx Says:

    Thank you very much for tis article – it was extrimly usefull

  4. Entity Framework And Encrypted Fields « Coding Headache Says:

    [...] if you want it to have associations.  You can find instructions for how to do this over at the Mehroz’s Experiments blog. However, before you decided to go this route, look at all the steps that are required, and make [...]

  5. Rocky Says:

    Brilliant thanks saved my brain for another day :)

  6. weitzhandler Says:

    Will the modified SSDL be affected if I edit the edmx with the designer?

  7. Syed Mehroz Alam Says:

    @weitzhandler

    Tweaking the edmx through designer won’t affect any of the manual SSDL changes. The SSDL will only be reset when you “update the model from database”.

  8. weitzhandler Says:

    well, is there any ‘clean’ way to do it? i mean with the designer, cuz i use ‘update model from db a lot.
    NOTE: I use Entity-Framework on VS 2010 on a .NET 4.0 application.

  9. Syed Mehroz Alam Says:

    @weitzhandler, you could write some RegEx script(s) and run those everytime you update your model. Going further, you can even write an small automation utility to apply those regex scripts.

  10. weitzhandler Says:

    Can you please redirect me to any blog or place where I can learn how to do it? Or if you know about open-source for it.
    Is it possible to edit the .edmx file extension generator template?

  11. Syed Mehroz Alam Says:

    You could try searching for Regular Expressions for some tutorials. After that, you can use some online regex tools e.g. this one to analyze and create a pattern for your edmx file. Also, once you have created your regex patterns, you can write a small program in .NET utilizing Regex classes to run your scripts on your edmx file.

    Hope that helps.

  12. weitzhandler Says:

    I know how to write regexes.
    I don’t know how to edit the edmx.

    Is there a way to edit the edmx generator template?

  13. Syed Mehroz Alam Says:

    I don’t know of anyway to edit the generator template.
    However, since the edmx is an xml file in your harddisk, so you can write a small program that takes a file as an input, apply certain regex replace patterns and saves the file back. After this, when you reopen the file in VS designer and hit “save”, your entity classes will be regenerated according to the modified edmx.

    Alternatively, you can open your edmx file in XML editor (right click and select ‘Open With’), copy all the contents into a regular expression editor, apply your patterns to modify the xml, and then put the modified xml back in the edmx file. Now when you save the edmx, EF will generate your data classes based on the modifications.

  14. weitzhandler Says:

    I don’t like the idea of external tools that have to run before app build, it’s called hardcoding.
    I guess this time there no other choice.

  15. weitzhandler Says:

    But I probably wouldn’t do it with regex, but rather with LINQ to XML.

  16. weitzhandler Says:

    I managed to create an automation of your blog content (will post when done), I was wondering if I can make it run after every “Update Model From Database”, whatcha say?

  17. weitzhandler Says:

    Alright.

    Take a look:

    http://visualstudiogallery.msdn.microsoft.com/en-us/d9b76b5d-d45c-4e79-8d28-31444be582de

    But I didn’t remove the store:Type=”Views”, since the designer treats the view as it doesn’t exist and shows it again in the “Update Model From Database” wizard.

    Anyway I posted a connection to MS, please vote/reproduce: https://connect.microsoft.com/data/feedback/details/590591

  18. Praveen Says:

    I really like your ‘note’ that says by updating the model from database, you will lose all your changes :D

  19. Greg Gum Says:

    Another trick is to create the view. Then create a table with the same fieldnames and types as the view. Add the Table to the edmx.

    Then go to the database, rename the table to something else, and rename the view to the same name as the table.

    This will fool EF into using your view as if it were a table.

    If you update from the database, you need to rename your view/table again back to the way it was, do the update, then rename it back. A hassle for sure, but there is no other way that I know of.

  20. El Matador Says:

    If I need to update my model from the database, and I don’t want to lose any custom changes that I have made, I do the following:

    1. Update your model from the database.
    2. Compare the xml file from BEFORE the update from database and the one generated AFTER the update from database.
    3. Identify any new xml code that was ADDED to your model during the compare.
    4. Manually add the code that was added/modified from the AFTER update edmx to your BEFORE update edmx.

    DONE.

    Thanks

  21. Bertrand J Says:

    here is a workaround to define the key columns of a sql server view:

    http://stackoverflow.com/questions/2326813/how-to-make-a-view-column-not-null

    By default EF defines all non-nullable columns to key columns, with this trick you can choose wich column will be resolved in a key or not… dirty but effective

  22. bhoomin patel Says:

    Brilliant thanks. saved lots of time

  23. bhoomin patel Says:

    i m a new in silverlight 5 with WCF ria services, can u please tell me what the reason behind that….what we need to do this kind of activity.

  24. Laksiri Says:

    Great work !!!
    Some of above can be done in designer…
    In the entity module, make StoreGeneratedPattern -> Generated for the properties bound to fields of non base table(s). It will solve “View or function ‘dbo.xxxxxxx’ is not updatable because the modification affects multiple base tables” error on insert and update.

  25. Laksiri Says:

    Correction
    StoreGeneratedPattern -> Computed

  26. gomesnayagam Says:

    i dont use edmx, MSFT has improved a lot in EF4.3(aka code first approach), i design the tables first and then use data annotations to define similar to db, ti works fine. I am also using view along with instead of trigger to update,insert data into tables.

  27. eka808 Says:

    You saved my day (and my bandwidth) with this article who is still relevant for EF 4.3
    Thank’s !


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 57 other followers

%d bloggers like this: