Entity framework database first

Hello guys, we are back again with yet another blog on Entity Framework. In my previous blog I have demonstrated the steps to connect multiple database servers using single .NET application using code first approach. In this blog I’m going to demonstrate the same using database first approach.

Pre-requisites:

Before we are going to start, I suggest you to follow the steps mentioned in either of these MSDN blogs; https://msdn.microsoft.com/en-us/library/jj206878(v=vs.113).aspx (for Console application), https://msdn.microsoft.com/en-us/library/jj682076(v=vs.113).aspx (for WinForms application); which I used to develop most of my application:

Modfiy App.config file:

At first, we’ll again modify our App.config file in similar manner as before, entity framework configuration and connection string will be changed with respect to database server.

App.config for MS SQL Server connection:

Entity framework database first

App.config for MySQL connection:

Entity framework database first - 2

Modify .edmx file:

Secondly, the additional step in database first approach is to modify .edmx file. EDMX file primarily contains the storage and conceptual schema of your application for database connectivity. I’ll modify the storage model schema provider along with necessary changes in field property types and lengths, which can vary between different database server, to connect both MS SQL Server and MySQL databases in single .edmx file.

.edmx file for MS SQL Server database:

<?xml version=”1.0″ encoding=”utf-8″?>

<!– SQL Server –>

<edmx:Edmx Version=”3.0″ xmlns:edmx=”http://schemas.microsoft.com/ado/2009/11/edmx”>

<edmx:Runtime>

<edmx:StorageModels>

<Schema Namespace=”SalesModel.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”>

<EntityType Name=”SalesTables”>

<Key>

<PropertyRef Name=”salesId” />

</Key>

<Property Name=”salesId” Type=”nvarchar” MaxLength=”128″ Nullable=”false” />

<Property Name=”itemId” Type=”nvarchar(max)” />

<Property Name=”qty” Type=”real” Nullable=”false” />

</EntityType>

<EntityContainer Name=”SalesModelStoreContainer”>

<EntitySet Name=”SalesTables” EntityType=”Self.SalesTables” Schema=”dbo” store:Type=”Tables” />

</EntityContainer>

</Schema>

</edmx:StorageModels>

<edmx:ConceptualModels>

<Schema Namespace=”SalesModel” 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”>

<EntityType Name=”SalesTable”>

<Key>

<PropertyRef Name=”salesId” />

</Key>

<Property Name=”salesId” Type=”String” MaxLength=”128″ FixedLength=”false” Unicode=”true” Nullable=”false” />

<Property Name=”itemId” Type=”String” MaxLength=”Max” FixedLength=”false” Unicode=”true” />

<Property Name=”qty” Type=”Single” Nullable=”false” />

</EntityType>

<EntityContainer Name=”SalesEntities” annotation:LazyLoadingEnabled=”true”>

<EntitySet Name=”SalesTables” EntityType=”Self.SalesTable” />

</EntityContainer>

</Schema>

</edmx:ConceptualModels>

<edmx:Mappings>

<Mapping Space=”C-S” xmlns=”http://schemas.microsoft.com/ado/2009/11/mapping/cs”>

<EntityContainerMapping StorageEntityContainer=”SalesModelStoreContainer” CdmEntityContainer=”SalesEntities”>

<EntitySetMapping Name=”SalesTables”>

<EntityTypeMapping TypeName=”SalesModel.SalesTable”>

<MappingFragment StoreEntitySet=”SalesTables”>

<ScalarProperty Name=”salesId” ColumnName=”salesId” />

<ScalarProperty Name=”itemId” ColumnName=”itemId” />

<ScalarProperty Name=”qty” ColumnName=”qty” />

</MappingFragment>

</EntityTypeMapping>

</EntitySetMapping>

</EntityContainerMapping>

</Mapping>

</edmx:Mappings>

</edmx:Runtime>

<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>

<Diagrams></Diagrams>

</Designer>

</edmx:Edmx>

.edmx file for MySQL database:

<?xml version=”1.0″ encoding=”utf-8″?>

<!– MySQL –>

<edmx:Edmx Version=”3.0″ xmlns:edmx=”http://schemas.microsoft.com/ado/2009/11/edmx”>

<edmx:Runtime>

<edmx:StorageModels>

<Schema Namespace=”SalesModel.Store” Provider=”MySql.Data.MySqlClient” ProviderManifestToken=”6.3″ 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”>

<EntityType Name=”SalesTables”>

<Key>

<PropertyRef Name=”salesId” />

</Key>

<Property Name=”salesId” Type=”varchar” MaxLength=”45″ Nullable=”false” />

<Property Name=”itemId” Type=”varchar” MaxLength=”45″/>

<Property Name=”qty” Type=”float”/>

</EntityType>

<EntityContainer Name=”SalesModelStoreContainer”>

<EntitySet Name=”SalesTables” EntityType=”Self.SalesTables” Schema=”sales” store:Type=”Tables” />

</EntityContainer>

</Schema>

</edmx:StorageModels>

<edmx:ConceptualModels>

<Schema Namespace=”SalesModel” 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”>

<EntityType Name=”SalesTable”>

<Key>

<PropertyRef Name=”salesId” />

</Key>

<Property Name=”salesId” Type=”String” MaxLength=”45″ FixedLength=”false” Unicode=”true” Nullable=”false” />

<Property Name=”itemId” Type=”String” MaxLength=”45″ FixedLength=”false” Unicode=”true” />

<Property Name=”qty” Type=”Single” Nullable=”false” />

</EntityType>

<EntityContainer Name=”SalesEntities” annotation:LazyLoadingEnabled=”true”>

<EntitySet Name=”SalesTables” EntityType=”Self.SalesTable” />

</EntityContainer>

</Schema>

</edmx:ConceptualModels>

<edmx:Mappings>

<Mapping Space=”C-S” xmlns=”http://schemas.microsoft.com/ado/2009/11/mapping/cs”>

<EntityContainerMapping StorageEntityContainer=”SalesModelStoreContainer” CdmEntityContainer=”SalesEntities”>

<EntitySetMapping Name=”SalesTables”>

<EntityTypeMapping TypeName=”SalesModel.SalesTable”>

<MappingFragment StoreEntitySet=”SalesTables”>

<ScalarProperty Name=”salesId” ColumnName=”salesId” />

<ScalarProperty Name=”itemId” ColumnName=”itemId” />

<ScalarProperty Name=”qty” ColumnName=”qty” />

</MappingFragment>

</EntityTypeMapping>

</EntitySetMapping>

</EntityContainerMapping>

</Mapping>

</edmx:Mappings>

</edmx:Runtime>

<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>

<Diagrams></Diagrams>

</Designer>

</edmx:Edmx>

Check your output:

Here is the output when application is ran using MS SQL Server database:

Entity framework database first - 4

And while using MySQL database:

Entity framework database first - 3

Leave a Reply

Recent Comments

    Archives

    Categories