I have been teaching in the past few weeks many people on how to use Entity Framework. I have decided to provide some of the samples I am using in my classes.

First let’s try to define what EF is and why it is going to help us to create easily data-centric applications.Entity Framework is an object-relational mapping (ORM) framework for the .NET Framework.EF addresses the problem of Object-relational impedance mismatch. I will not be talking about that mismatch because it is well documented in many sites on the Internet. Through that framework we can program against a conceptual application model instead of programming directly against a relational schema-model. By doing so we can decrease the amount of code we do write to access a data storage and thus decrease maintenance time. You can find many posts on Entity Framework in this blog.Have a look if you want.

A lot of people where interested in the various customisations we can have in the EF trough the EDM Editor.

One of the main advantages of EF is that we can customise it in many ways to address the needs of the business model.       

In this post I will show you a step by step example on how to use Entity Splitting.Entity splitting is also known as Horizontal Splitting and it allows us to combine multiple database tables in a single entity.Those two tables that are will be combined in the single entity must share the same primary key.

If you are interested in another very common EDM customisation, Table Splitting have a look at this post.

Obviously we need to have a database to go on with this hands-on example. I will use a brand new database. I assume that you have access to a version of SQL Server.If you do not, you can download and install the free SQL Server Express edition from here. This database is going to hold data about footballers.This is the T-SQL code that generates both the schema and the data. Launch SQL Server and in a new query window type

**********************************************

 USE [master]
GO

CREATE DATABASE [FootballersData] ON  PRIMARY
( NAME = N'FootballersData', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FootballersData.mdf' , SIZE = 176384KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'FootballersData_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FootballersData_log.LDF' , SIZE = 114432KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO



USE [FootballersData]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Footballers](
    [FootballerID] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](50) NOT NULL,
    [LastName] [varchar](50) NOT NULL,
    [isCaptain] [bit] NULL,
PRIMARY KEY CLUSTERED
(
    [FootballerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[footballers] ON
INSERT [dbo].[footballers] ([FootballerID], [FirstName], [LastName], [isCaptain]) VALUES (1, N'Steven', N'Gerrard', 1)
INSERT [dbo].[footballers] ([FootballerID], [FirstName], [LastName], [isCaptain]) VALUES (2, N'Luis', N'Suarez', 0)
INSERT [dbo].[footballers] ([FootballerID], [FirstName], [LastName], [isCaptain]) VALUES (3, N'Dirk', N'Kuyt', 0)
INSERT [dbo].[footballers] ([FootballerID], [FirstName], [LastName], [isCaptain]) VALUES (4, N'Maxi ', N'Rodríguez', 0)
SET IDENTITY_INSERT [dbo].[footballers] OFF


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FootballersPersonalInfo](
    [FootballerID] [int]  NOT NULL,
    [WeeklyWages] [money] NOT NULL,
    [ContractExpires] [datetime] NOT NULL,
 CONSTRAINT [PK_FootballersPersonalInfo] PRIMARY KEY CLUSTERED
(
    [FootballerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


INSERT [dbo].[FootballersPersonalInfo] ([FootballerID], [WeeklyWages], [ContractExpires]) VALUES (1, 150.0000, CAST(0x0000A3F400000000 AS DateTime))
INSERT [dbo].[FootballersPersonalInfo] ([FootballerID], [WeeklyWages], [ContractExpires]) VALUES (2, 120.0000, CAST(0x0000A61800000000 AS DateTime))
INSERT [dbo].[FootballersPersonalInfo] ([FootballerID], [WeeklyWages], [ContractExpires]) VALUES (3, 115.0000, CAST(0x0000A2A600000000 AS DateTime))
INSERT [dbo].[FootballersPersonalInfo] ([FootballerID], [WeeklyWages], [ContractExpires]) VALUES (4, 100.0000, CAST(0x0000A13800000000 AS DateTime))

ALTER TABLE [dbo].[footballers]  WITH CHECK ADD  CONSTRAINT [FK_footballers_footballers] FOREIGN KEY([FootballerID])
REFERENCES [dbo].[footballers] ([FootballerID])
GO
ALTER TABLE [dbo].[footballers] CHECK CONSTRAINT [FK_footballers_footballers]
GO
ALTER TABLE [dbo].[FootballersPersonalInfo]  WITH CHECK ADD  CONSTRAINT [FK_FootballersPersonalInfo_footballers] FOREIGN KEY([FootballerID])
REFERENCES [dbo].[footballers] ([FootballerID])
GO
ALTER TABLE [dbo].[FootballersPersonalInfo] CHECK CONSTRAINT [FK_FootballersPersonalInfo_footballers]
GO

*************************************

1) Launch Visual Studio 2010 (express edition will work fine). Create a new empty website and choose a suitable name for it. Choose C# as the development language.

2) Add a new item to your site, a web form. Leave the default name, Default.aspx

3) Add a new project to your solution, a class library project.Remove the class1.cs file from the project.

4) Add a new item to your class library project, a ADO.Net Entity Data model. Choose a suitable name for it, e.g Footballers.edmx.

5) Then the Wizard pops up. Choose "Generate from Database". Hit the Next button.

6) In the next step of the wizard "Choose your Data Connection" we choose the connection to connect to our database.Have a look at the entity connection string.Have a look at the picture below.

 7) Hit the Next button.Now the wizard will identify the database objects and let us choose which database objects we want to include in our model.I will select all tables.Have a look at the picture below

 Hit the Finish button.

 8) Before we go on and customise our EDM, we need to add some references to the empty website. We need to add a reference to the class library project where our model lives and to the System.Data.Entity assembly.Then we need to copy the connection string information from the App.config file to the web.config file. 

 <connectionStrings><add name="FootballersDataEntities"

 connectionString="metadata=res://*/Footballers.csdl|res://*/Footballers.ssdl|res:

//*/Footballers.msl;provider=System.Data.SqlClient;provider connection string=&quot;

data source=.;initial catalog=FootballersData;integrated security=True;

multipleactiveresultsets=True;App=EntityFramework&quot;" 

providerName="System.Data.EntityClient" /></connectionStrings>

9) Now we are ready to combine those two entities into one.  I will cut the properties WeeklyWages,ContractExpires from the FootballersPersonalInfo entity and I will paste them in the Footballer entity.Then I will delete the FootballersPersonalInfo entity from our model.

10) Now we need to map the the properties WeeklyWages, ContractExpires of the Footballer entity to the FootballersPersonalInfo table.Right-click on the Footballer entity and select Table Mapping.

Have a look at the picture below to see what our final model looks like

11) Now we are ready to consume the model.In the Page_Load event handling routine type of the Default.aspx page

     protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            FetchData();
        }
        
    }

 This is just the name of a private method that I will implement below 

private void FetchData()
    {
 using (FootballersDataEntities ctx = new FootballersDataEntities())
        {
    var FootballeQuery = from footballer in ctx.Footballers
                         select footballer;
 
 foreach (var footballer in FootballeQuery)
            {
 Response.Write(String.Format("{0}-{1}", footballer.FirstName,

footballer.LastName));

 Response.Write("<br/>");
 Response.Write(footballer.WeeklyWages);
 Response.Write("<hr/>");
 
            }
        }
    }

12) Build and Run your application. You will see the results printed out in the screen from both tables.

13) Now let's try to add a new Footballer object to our model and then to the underlying database.Add a button web server control on the page

14) In the Button1_Click event handling routine type

using (FootballersDataEntities ctx = new FootballersDataEntities())
        {
 
            var footballer = new Footballer
 
            {
                FirstName = "Pepe",
                LastName = "Reina",
                isCaptain = false,
                WeeklyWages = 100000,
                ContractExpires = DateTime.Now.AddDays(500)
 
 
            };
 
            ctx.AddToFootballers(footballer);
            ctx.SaveChanges();
 
            FetchData();
 
        }

 We are trying to insert the newly created object in the database and then call FetchData method again.

 15) Before your run your application, launch SQL Server Profiler.Create a trace and name it as you wish. There should be two events in this trace SQL:BatchCompleted and RPC:Completed.Run the trace.

16) Run your application. Click on the button to insert a new row in both tables.

Have a look at the picture below to see that 2 T-SQL statements were generated to insert data in both tables

17) You can verify that the data was inserted correctly in both tables if you query those tables (Footballers,FootballersPersonalInfo) directly from within SQL Server.  

Hope it helps !!!