In Entity Framework, Views are “readonly” due to the DefiningQuery element. There are two approaches to make a view editable:
- Create stored procedures for Insert/Update/Delete: This is described in this document and this walkthrough.
- 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:
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.
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”.
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>
August 13, 2009 at 4:37 PM
[...] 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 [...]
October 5, 2009 at 9:28 PM
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.
December 20, 2009 at 4:41 AM
Thank you very much for tis article – it was extrimly usefull
January 21, 2010 at 9:49 AM
[...] 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 [...]
February 13, 2010 at 12:29 AM
Brilliant thanks saved my brain for another day
August 17, 2010 at 10:44 PM
Will the modified SSDL be affected if I edit the edmx with the designer?
August 19, 2010 at 7:49 AM
@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”.
August 29, 2010 at 4:04 AM
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.
August 30, 2010 at 12:10 PM
@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.
August 30, 2010 at 11:28 PM
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?
August 31, 2010 at 8:33 AM
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.
August 31, 2010 at 9:44 AM
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?
August 31, 2010 at 2:44 PM
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.
August 31, 2010 at 2:47 PM
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.
August 31, 2010 at 2:48 PM
But I probably wouldn’t do it with regex, but rather with LINQ to XML.
September 1, 2010 at 8:32 AM
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?
September 2, 2010 at 10:46 AM
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
November 9, 2010 at 3:41 AM
I really like your ‘note’ that says by updating the model from database, you will lose all your changes
February 4, 2011 at 12:13 AM
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.
February 17, 2011 at 5:53 AM
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
September 15, 2011 at 10:31 PM
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