Entity Framework: EDMX

My previous post showed how to quickly generate the EDMX file and corresponding codebehind classes using VS.NET 2008.

The EDMX itself contains the contents of core model/mapping files for Entity Framework:

• Conceptual schema definition language (.csdl) - defines the conceptual model.
• Store schema definition language file (.ssdl) - defines the storage model, aka logical model.
• Mapping specification language file (.msl) - defines the mapping between the storage and conceptual models.

It also contains information for the IDE to display the EDMX file in Designer View.

Here is an article from the ADO.NET team blog on Extracting CSDL from EDMX.

Here is my generated EDMX file:

<?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="PersonModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl">
        <EntityContainer Name="PersonModelStoreContainer">
          <EntitySet Name="Person" EntityType="PersonModel.Store.Person" store:Type="Tables" Schema="dbo" />
          <EntitySet Name="Profile" EntityType="PersonModel.Store.Profile" store:Type="Tables" Schema="dbo" />
          <AssociationSet Name="FK_Profile_Person" Association="PersonModel.Store.FK_Profile_Person">
            <End Role="Person" EntitySet="Person" />
            <End Role="Profile" EntitySet="Profile" />
          </AssociationSet>
        </EntityContainer>
        <EntityType Name="Person">
          <Key>
            <PropertyRef Name="PersonID" />
          </Key>
          <Property Name="PersonID" Type="bigint" Nullable="false" />
          <Property Name="LoginID" Type="varchar" Nullable="false" MaxLength="100" />
          <Property Name="Password" Type="varchar" Nullable="false" MaxLength="32" />
        </EntityType>
        <EntityType Name="Profile">
          <Key>
            <PropertyRef Name="ProfileID" />
          </Key>
          <Property Name="ProfileID" Type="bigint" Nullable="false" />
          <Property Name="PersonID" Type="bigint" Nullable="false" />
          <Property Name="FirstName" Type="nvarchar" Nullable="false" MaxLength="100" />
          <Property Name="LastName" Type="nvarchar" Nullable="false" MaxLength="100" />
          <Property Name="EmailAddress" Type="nvarchar" Nullable="false" MaxLength="255" />
        </EntityType>
        <Association Name="FK_Profile_Person">
          <End Role="Person" Type="PersonModel.Store.Person" Multiplicity="1" />
          <End Role="Profile" Type="PersonModel.Store.Profile" Multiplicity="*" />
          <ReferentialConstraint>
            <Principal Role="Person">
              <PropertyRef Name="PersonID" />
            </Principal>
            <Dependent Role="Profile">
              <PropertyRef Name="PersonID" />
            </Dependent>
          </ReferentialConstraint>
        </Association>
      </Schema>
    </edmx:StorageModels>
    <!-- CSDL content -->
    <edmx:ConceptualModels>
      <Schema Namespace="PersonModel" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2006/04/edm">
        <EntityContainer Name="PersonEntities">
          <EntitySet Name="Person" EntityType="PersonModel.Person" />
          <EntitySet Name="Profile" EntityType="PersonModel.Profile" />
          <AssociationSet Name="FK_Profile_Person" Association="PersonModel.FK_Profile_Person">
            <End Role="Person" EntitySet="Person" />
            <End Role="Profile" EntitySet="Profile" />
          </AssociationSet>
        </EntityContainer>
        <EntityType Name="Person">
          <Key>
            <PropertyRef Name="PersonID" />
          </Key>
          <Property Name="PersonID" Type="Int64" Nullable="false" />
          <Property Name="LoginID" Type="String" Nullable="false" MaxLength="100" Unicode="false" FixedLength="false" />
          <Property Name="Password" Type="String" Nullable="false" MaxLength="32" Unicode="false" FixedLength="false" />
          <NavigationProperty Name="Profile" Relationship="PersonModel.FK_Profile_Person" FromRole="Person" ToRole="Profile" />
        </EntityType>
        <EntityType Name="Profile">
          <Key>
            <PropertyRef Name="ProfileID" />
          </Key>
          <Property Name="ProfileID" Type="Int64" Nullable="false" />
          <Property Name="FirstName" Type="String" Nullable="false" MaxLength="100" Unicode="true" FixedLength="false" />
          <Property Name="LastName" Type="String" Nullable="false" MaxLength="100" Unicode="true" FixedLength="false" />
          <Property Name="EmailAddress" Type="String" Nullable="false" MaxLength="255" Unicode="true" FixedLength="false" />
          <NavigationProperty Name="Person" Relationship="PersonModel.FK_Profile_Person" FromRole="Profile" ToRole="Person" />
        </EntityType>
        <Association Name="FK_Profile_Person">
          <End Role="Person" Type="PersonModel.Person" Multiplicity="1" />
          <End Role="Profile" Type="PersonModel.Profile" 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="PersonModelStoreContainer" CdmEntityContainer="PersonEntities">
          <EntitySetMapping Name="Person">
            <EntityTypeMapping TypeName="IsTypeOf(PersonModel.Person)">
              <MappingFragment StoreEntitySet="Person">
                <ScalarProperty Name="PersonID" ColumnName="PersonID" />
                <ScalarProperty Name="LoginID" ColumnName="LoginID" />
                <ScalarProperty Name="Password" ColumnName="Password" />
              </MappingFragment>
            </EntityTypeMapping>
          </EntitySetMapping>
          <EntitySetMapping Name="Profile">
            <EntityTypeMapping TypeName="IsTypeOf(PersonModel.Profile)">
              <MappingFragment StoreEntitySet="Profile">
                <ScalarProperty Name="ProfileID" ColumnName="ProfileID" />
                <ScalarProperty Name="FirstName" ColumnName="FirstName" />
                <ScalarProperty Name="LastName" ColumnName="LastName" />
                <ScalarProperty Name="EmailAddress" ColumnName="EmailAddress" />
              </MappingFragment>
            </EntityTypeMapping>
          </EntitySetMapping>
          <AssociationSetMapping Name="FK_Profile_Person" TypeName="PersonModel.FK_Profile_Person" StoreEntitySet="Profile">
            <EndProperty Name="Person">
              <ScalarProperty Name="PersonID" ColumnName="PersonID" />
            </EndProperty>
            <EndProperty Name="Profile">
              <ScalarProperty Name="ProfileID" ColumnName="ProfileID" />
            </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="Person">
        <EntityTypeShape EntityType="PersonModel.Person" Width="1.5" PointX="0.75" PointY="1" Height="1.592306315104167" IsExpanded="true" />
        <EntityTypeShape EntityType="PersonModel.Profile" Width="1.5" PointX="3" PointY="0.875" Height="1.7566536458333333" IsExpanded="true" />
        <AssociationConnector Association="PersonModel.FK_Profile_Person" ManuallyRouted="false">
          <ConnectorPoint PointX="2.25" PointY="1.7961531575520835" />
          <ConnectorPoint PointX="3" PointY="1.7961531575520835" /></AssociationConnector></Diagram></edmx:Diagrams>
  </edmx:Designer>
</edmx:Edmx>

Here is how it appears in Visual Studio Design View:

Entity Framework Quick Start Tutorial

Entity Framework is an OR/M that began shipping with .NET Framework 3.5 SP1 as part of ADO.NET.


In order to begin working with Entity Framework in VS.NET 2008, right click on a Project and select Add New Item.


Under the New Item dialog window, select data, and choose ADO.NET Entity Data Model.



This will create the EDMX file and corresponding codebehind classes.

Next, we need to choose the model contents, which will either start from a database source, or be a blank, new model.



I chose "Generate from Database Source", the quickest and easiest way to get up and running.

From here, you need to provide information about a database connection and database:



Based on the information provided, you will be given the option to choose database objects to include in the model.



Finally, this will create the EDMX file and corresponding codebehind partial classes for the model.

ADO.NET: Exception Handling

From MSDN entry on System.Data.SqlClient.SqlException:

"Messages that have a severity level of 10 or less are informational and indicate problems caused by mistakes in information that a user has entered. Severity levels from 11 through 16 are generated by the user, and can be corrected by the user. Severity levels from 17 through 25 indicate software or hardware errors. When a level 17, 18, or 19 error occurs, you can continue working, although you might not be able to execute a particular statement.

The SqlConnection remains open when the severity level is 19 or less. When the severity level is 20 or greater, the server ordinarily closes the SqlConnection. However, the user can reopen the connection and continue. In both cases, a SqlException is generated by the method executing the command."

The SqlException itself extends DbException. It contains a collection, Error, of type SqlError.

There are parallel exception classes for OleDb, OracleClient, and Odbc. However, these exception classes did not have the same level of detail and specific behavior as that of the SqlException class.

ADO.NET Overview

I have done quite a bit of hands-on development with ADO.NET, but I eventually switched to NHibernate for my DAL.

I also did quite a bit of LINQ-to-SQL (migrated from NHibernate to LINQ-to-SQL), so I am familiar with LINQ syntax.

When I first looked at Entity Framework during DevConnections 2008, I was a bit turned off by its complexity. It was in beta at that point, and I never went back and revisited it. I guess I'll be doing so now, as it is a decent portion (11% according to the Skills Measured) on the Exam 70-561: ADO.NET Application Development.

Starting with a basic review of ADO.NET (just wanted to jot down the basics that I recall off the top of my head, I'll fill in more of the detailed nuances from MSDN later):

Can work in a connected or disconnected data architecture.

Disconnected data architecture:

• Disconnected architecture makes use of DataSet, DataTable, DataColumn, DataRow to hold a copy of the data in memory.
• These classes belong to the System.Data namespace and are Database vendor agnostic.
  
• One DataSet contains one-to-many DataTables.
• One DataTable contains one-to-many DataColumns, which represent the structure of the table.
• The DataTable contains zero-to-many DataRows, which represent the content of the table.

Bridging DataSet to DB implementation (Populating DataSets):

• DataSets are populated via DataAdapters: calling DataAdapter.Fill(DataSet). The DataTables can also be filled by calling DataAdapter.Fill(DataTable). DataAdapter.Fill is overloaded.
  
• DataAdapters are Database vendor specific.
  
• DataAdapters use Commands, which are also Database vendor specific. There are properties on DataAdapter: SelectCommand, UpdateCommand, InsertCommand, DeleteCommand.
• Commands contain a reference to a Connection, which represents the actual physical connection to the Database instance. Connections are Database vendor specific.

Here is a list of the classes (DataAdapter, Command, Connection) and the corresponding version by Database vendor:

DataAdapter

• System.Data.SqlClient.SqlDataAdapter
• System.Data.OracleClient.OracleDataAdapter
• System.Data.OleDb.OleDbDataAdapter
• System.Data.Odbc.OdbcDataAdapter

Command

• System.Data.SqlClient.SqlCommand
• System.Data.OracleClient.OracleCommand
• System.Data.OleDb.OleDbCommand
• System.Data.Odbc.OdbcCommand

Connection

• System.Data.SqlClient.SqlConnection
• System.Data.OracleClient.OracleConnection
• System.Data.OleDb.OleDbConnection
• System.Data.Odbc.OdbcConnection

ADO.NET studies and AdventureWorks DB

After setting up SQL Server 2008 Express Edition and the Microsoft SQL Server Management Studio, I went to download the AdventureWorks sample database.

Apparently, AdventureWorks is the new sample database, taking the place of the classic Northwind sample database. Downloaded it from the following site:

Microsoft SQL Server Product Samples: Database - Release SQL Server 2008 SR3

This turned out to be much more painful than expected, mainly because the AdventureWorks DB requires both Full Text Search and FileStream.

Here are the Prereqs for the AdventureWorks DB.

Unfortunately, I installed the DB Runtime with SSMS only (no Advanced Services).

I'm going to uninstall the SQL Server 2008 instance and install the version that includes:

• DB Runtime
• SSMS
• Advanced Services

Passed Microsoft Exam: 70-503

Today I passed Exam 70-503: .Microsoft .NET Framework 3.5 - Windows Communication Foundation.

This was the hardest of the exams I've taken thus far in going for the
Microsoft Certified Professional Developer (MCPD): Enterprise Application Developer 3.5.

I probably spent the most time studying for this exam (other than the Fundamentals), and am glad to be done with it.

Now, onto ADO.NET, LINQ, and Entity Framework.