I have two simple views
I need to create an association between the CustomerOrderView and the CustomerView using the CustomerID.
I added the Association like this:
No here is where it seems to cause me problems.
I double click on the association line and try to add a new Referential Constraint.
If I choose CustomerOrderView as the Principal I get the following.
This will not work because I am trying to get both CustomerID's in both views to connect.
Round 2... So this time I change the Principal to the CustomerView and now I can see that both the CustomerID's connect. Like so.
But if I try to save the entity file I will get the following errors.
"Error 111: Properties referred by the Principal Role CustomerOrderView must be exactly identical to the key of the EntityType TestModel.CustomerOrderView referred to by the Principal Role in the relationship constraint for Relationship TestModel.CustomerOrderViewCustomerView. Make sure all the key properties are specified in the Principal Role."
AND
"Error 5 Running transformation: Multiplicity is not valid in role 'CustomerView' in relationship 'CustomerOrderViewCustomerView'. Valid values for multiplicity for Principal Role are '0..1' or '1'."
AND
"Error 6 Running transformation: Multiplicity is not valid in Role 'CustomerOrderView' in relationship 'CustomerOrderViewCustomerView'. Because the Dependent Role properties are not the key properties, the upper bound of the multiplicity of the Dependent Role must be *."
So how do I connect views together in the entity framework?
Here is the edmx for more help.
<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="3.0" xmlns:edmx="http://schemas.microsoft.com/ado/2009/11/edmx">
<!-- EF Runtime content -->
<edmx:Runtime>
<!-- SSDL content -->
<edmx:StorageModels>
<Schema Namespace="TestModel.Store" Provider="System.Data.SqlClient" ProviderManifestToken="2012" Alias="Self" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns:customannotation="http://schemas.microsoft.com/ado/2013/11/edm/customannotation" xmlns="http://schemas.microsoft.com/ado/2009/11/edm/ssdl">
<!--Errors Found During Generation:
warning 6002: The table/view 'Test.dbo.CustomerOrderView' 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="CustomerOrderView">
<Key>
<PropertyRef Name="CustomerOrderID" />
</Key>
<Property Name="CustomerOrderID" Type="numeric" Precision="9" Scale="0" Nullable="false" />
<Property Name="OrderStatus" Type="char" MaxLength="1" />
<Property Name="CustomerID" Type="numeric" Precision="6" Scale="0" />
</EntityType>
<!--Errors Found During Generation:
warning 6002: The table/view 'Test.dbo.CustomerView' 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="CustomerView">
<Key>
<PropertyRef Name="CustomerID" />
</Key>
<Property Name="CustomerID" Type="numeric" Precision="6" Scale="0" Nullable="false" />
<Property Name="CustomerName" Type="varchar" MaxLength="30" />
</EntityType>
<EntityContainer Name="TestModelStoreContainer">
<EntitySet Name="CustomerOrderView" EntityType="Self.CustomerOrderView" store:Type="Views" store:Schema="dbo">
<DefiningQuery>SELECT
[CustomerOrderView].[CustomerOrderID] AS [CustomerOrderID],
[CustomerOrderView].[OrderStatus] AS [OrderStatus],
[CustomerOrderView].[CustomerID] AS [CustomerID]
FROM [dbo].[CustomerOrderView] AS [CustomerOrderView]</DefiningQuery>
</EntitySet>
<EntitySet Name="CustomerView" EntityType="Self.CustomerView" store:Type="Views" store:Schema="dbo">
<DefiningQuery>SELECT
[CustomerView].[CustomerID] AS [CustomerID],
[CustomerView].[CustomerName] AS [CustomerName]
FROM [dbo].[CustomerView] AS [CustomerView]</DefiningQuery>
</EntitySet>
</EntityContainer>
</Schema></edmx:StorageModels>
<!-- CSDL content -->
<edmx:ConceptualModels>
<Schema Namespace="TestModel" Alias="Self" annotation:UseStrongSpatialTypes="false" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" xmlns:customannotation="http://schemas.microsoft.com/ado/2013/11/edm/customannotation" xmlns="http://schemas.microsoft.com/ado/2009/11/edm">
<EntityContainer Name="TestEntities" annotation:LazyLoadingEnabled="true" >
<EntitySet Name="CustomerOrderViews" EntityType="TestModel.CustomerOrderView" />
<EntitySet Name="CustomerViews" EntityType="TestModel.CustomerView" />
<AssociationSet Name="CustomerOrderViewCustomerView" Association="TestModel.CustomerOrderViewCustomerView">
<End Role="CustomerOrderView" EntitySet="CustomerOrderViews" />
<End Role="CustomerView" EntitySet="CustomerViews" />
</AssociationSet>
</EntityContainer>
<EntityType Name="CustomerOrderView">
<Key>
<PropertyRef Name="CustomerOrderID" />
</Key>
<Property Name="CustomerOrderID" Type="Decimal" Nullable="false" Precision="9" Scale="0" />
<Property Name="OrderStatus" Type="String" MaxLength="1" FixedLength="true" Unicode="false" />
<Property Name="CustomerID" Type="Decimal" Precision="6" Scale="0" />
<NavigationProperty Name="CustomerViews" Relationship="TestModel.CustomerOrderViewCustomerView" FromRole="CustomerOrderView" ToRole="CustomerView" />
</EntityType>
<EntityType Name="CustomerView">
<Key>
<PropertyRef Name="CustomerID" />
</Key>
<Property Name="CustomerID" Type="Decimal" Nullable="false" Precision="6" Scale="0" />
<Property Name="CustomerName" Type="String" MaxLength="30" FixedLength="false" Unicode="false" />
<NavigationProperty Name="CustomerOrderView" Relationship="TestModel.CustomerOrderViewCustomerView" FromRole="CustomerView" ToRole="CustomerOrderView" />
</EntityType>
<Association Name="CustomerOrderViewCustomerView">
<End Type="TestModel.CustomerOrderView" Role="CustomerOrderView" Multiplicity="1" />
<End Type="TestModel.CustomerView" Role="CustomerView" Multiplicity="*" />
<ReferentialConstraint>
<Principal Role="CustomerView">
<PropertyRef Name="CustomerID" />
</Principal>
<Dependent Role="CustomerOrderView">
<PropertyRef Name="CustomerID" />
</Dependent>
</ReferentialConstraint>
</Association>
</Schema>
</edmx:ConceptualModels>
<!-- C-S mapping content -->
<edmx:Mappings>
<Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2009/11/mapping/cs">
<EntityContainerMapping StorageEntityContainer="TestModelStoreContainer" CdmEntityContainer="TestEntities" >
<EntitySetMapping Name="CustomerOrderViews">
<EntityTypeMapping TypeName="TestModel.CustomerOrderView">
<MappingFragment StoreEntitySet="CustomerOrderView">
<ScalarProperty Name="CustomerID" ColumnName="CustomerID" />
<ScalarProperty Name="OrderStatus" ColumnName="OrderStatus" />
<ScalarProperty Name="CustomerOrderID" ColumnName="CustomerOrderID" />
</MappingFragment>
</EntityTypeMapping>
</EntitySetMapping>
<EntitySetMapping Name="CustomerViews">
<EntityTypeMapping TypeName="TestModel.CustomerView">
<MappingFragment StoreEntitySet="CustomerView">
<ScalarProperty Name="CustomerName" ColumnName="CustomerName" />
<ScalarProperty Name="CustomerID" ColumnName="CustomerID" />
</MappingFragment>
</EntityTypeMapping>
</EntitySetMapping>
</EntityContainerMapping>
</Mapping>
</edmx:Mappings>
</edmx:Runtime>
<!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) -->
<Designer xmlns="http://schemas.microsoft.com/ado/2009/11/edmx">
<Connection>
<DesignerInfoPropertySet>
<DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" />
</DesignerInfoPropertySet>
</Connection>
<Options>
<DesignerInfoPropertySet>
<DesignerProperty Name="ValidateOnBuild" Value="true" />
<DesignerProperty Name="EnablePluralization" Value="true" />
<DesignerProperty Name="IncludeForeignKeysInModel" Value="True" />
<DesignerProperty Name="UseLegacyProvider" Value="false" />
<DesignerProperty Name="CodeGenerationStrategy" Value="None" />
</DesignerInfoPropertySet>
</Options>
<!-- Diagram content (shape and connector positions) -->
<Diagrams></Diagrams>
</Designer>
</edmx:Edmx>
EDIT -- For more examples
I have swapped the 1 to many so that "1" CustomerView can have "Many" CustomerOrderView.
Now I get the following errors:
"Error 4 Error 3031: Problem in mapping fragments starting at line 90:Non-nullable column CustomerOrderView.CustomerID in table CustomerOrderView is mapped to a nullable entity property." -- This is not true. CustomerOrderView.CustomerID is nullable but CustomerView.CustomerID is not nullable.
AND
"Error 5 Running transformation: Multiplicity is not valid in Role 'CustomerView' in relationship 'CustomerViewCustomerOrderView'. Because all the properties in the Dependent Role are nullable, multiplicity of the Principal Role must be '0..1'."
I then changed CustomerOrderView.CustomerID to non-nullable even though in reality there can be order not yet assigned to a CustomerID. But after that change I get the following error at runtime.
"The 'CustomerID' property on 'CustomerOrderView' could not be set to a 'null' value. You must set this property to a non-null value of type 'System.Decimal'."
So I went into the CustomerOrderView in SQL and add an ISNULL(CustomerID,-1).
Now it works BUT this is now what I want. CustomerOrderView should be able to be Nullable.
I feel like I am doing something wrong or have setup something wrong.
EDIT -- With the fix.
Basically I needed to use a 0..1 to many relationship where the 0..1 is on the CustomerOrderView side and many is on the CustomerView side. Next I told it not to add the foreign key property and manually added it with CustomerView as the Principal and CustomerOrderView as the Dependent.
I think the problem is with your relation ship (1 to many relationship). I think one side should to be Customer View Table. Then It will be fine.
UPDATED
Found the problem I think these two properties doesn't meet requirements each other. that's why error pops out. Check CustomerView.CustomerID
nullable property is true. if it is, then it change to false like following