Intro to Entity Framework with SQL Server

By: Ray Barley   |   Comments (8)   |   Related: > Entity Framework

Problem

I'm a DBA and the developers in my organization are starting to use something called the Entity Framework (EF) in their applications.  The developers are telling me that with EF you don't use stored procedures, you simply write .NET code to perform all of your data access.  When I use the SQL Server Profiler to take a look at what they're doing, all I see is a bunch of dynamic SQL being executed.  Naturally I'm a little skeptical of this, so can you help me to understand what is this Entity Framework?

Solution

The Entity Framework (EF) is an Object Relational Mapping (ORM) tool that indeed allows developers to work with a database by simply writing .NET code.  Stored procedures are optional with EF; if you want to use them you can. 

The starting point with EF is to generate a model that is made up of three distinct pieces:

• The Conceptual layer is the object-oriented representation of the database; i.e. it specifies the .NET classes that are used to query the data and perform inserts, updates and deletes.
• The Storage layer represents the underlying database; i.e. the tables, views, primary keys, foreign keys, stored procedures, etc.
• The Mapping layer joins the Conceptual layer to the Storage layer.

As a general rule, you can generate the model using Visual Studio by simply pointing to your existing database.  You can of course customize the model, but out of the box you get something that you can begin using immediately.  The following is an example of a very simple model that was generated using Visual Studio:

In the underlying database we have a Contact table and an Address table; there is a 1 to many relationship between Contact and Address.  The Scalar Properties are the columns in the respective tables; the Navigation Properties allow us to retrieve the Addresses for a Contact and the Contact for an Address.  As a result of generating the model, there is a Contact class and an Address class that we use to work with the data.  There are other classes as well that are generated and we will take a look at some of those shortly.

In this introductory tutorial we are going to walk through the following steps:

• Add the EF model to an existing Visual Studio project
• Show a code sample that performs some inserts and queries

Add the EF Model to a Project

In this tip I am going to assume that we have an existing Visual Studio project that is a simple Console application.  Right-click on the project in the solution explorer, select Add New Item, then select the ADO.NET Entity Data Model template:

Adding an ADO.NET Entity Data Model to your project launches a wizard to guide you through the following steps:

Step 1: Choose Model Contents

Click on Generate from database then click Next.  This will build the model from an existing database.

Step 2: Specify a Database Connection:

You can choose an existing database connection or create a new one.  I have a SQL Server 2008 database called EFSample that we will use.  Click the checkbox to save the connection information in the App.Config file.  The text below the checkbox will be used as the name of the main class that's generated for working with EF.

Step 3: Choose Database Objects

Select the tables, views and stored procedures that you want to include in the model.  We are just going to choose the tables as shown above.  Click Finish to complete the wizard.  At this point we have created the model as shown below:

This is the same model I described above.  I'm going to make a couple of cosmetic changes to the model:

• Contact and Address are called entities; they each have a property called Entity Set Name which essentially means the name for the collection of entities.  Click on the entity in the diagram then change the Entity Set name in the properties window from Contact to Contacts and Address to Addresses.
• Click on Address in the Contact Navigation Properties and change the Name in the properties window to Addresses.

After making the above changes the model now looks like this:

While these changes are minor they do enhance the model a bit.  We'll see this in the sample code.

Sample Code

After completing the EF wizard there will be two new files added to the project; in my example ContactModel.edmx contains the model and ContactModel.Designer.cs contains the generated code.  We are now ready to review some sample code that will work with our database.

The sample code with comments is shown below:

using (EFSampleEntities ctx = new EFSampleEntities())
{
  // create a new contact
  Contact newContact = Contact.CreateContact(0, "JONES", "SAM");
  ctx.AddToContacts(newContact);
  // create an address for the contact
  Address newAddress = Address.CreateAddress(0, "HOME", "101 MAIN ST",
                    "BALTIMORE", "MD", 21202);
  // attach the contact to the address
  newAddress.Contact = newContact;
  ctx.AddToAddresses(newAddress);
  // save the contact and the address
  ctx.SaveChanges();
  // query all contacts
  var queryResults = from c in ctx.Contacts
                     select c;
  foreach (Contact c in queryResults)
  {
    Console.WriteLine("{0} {1} {2}", c.ContactID,
       c.FirstName, c.LastName);
  }
}

The following are the main points about the above code sample:

• EFSampleEntities is a class generated from the model; it acts as the container for the entities.
• Contact and Address are entity classes generated from the model; they represent the database tables of the same name.
• Each entity class has a Create<EntityName> method that you use to create a new entity.  The parameters to the method are all columns that do not allow NULL; the class has properties for every column in the underlying table and you can set them as necessary by normal assignment.
• In the case of the new Address you need to associate it with a Contact.
• The EFSampleEntities class has an AddTo<EntitySet> method for each entity; you invoke this method to add a new entity to the collection.
• The EFSampleEntities class has a SaveChanges method that is called to persist whatever has changed to the database; this includes any inserts, updates and deletes.
• The code right below the query all contacts comment is an example of a LINQ to Entities query; LINQ stands for Language INtegrated Query; it is one of several options you have for querying data.  You can specify where criteria, order by, etc.  Just about anything you can do in SQL is supported although the syntax is a little different. 
• The foreach code iterates through the collection of Contacts and prints out the values to the screen.

Another bit of sample code that retrieves every Address and its associated Contact is shown below:

  // query all contacts and their addresses
  var queryResults2 = from a in ctx.Addresses
                      .Include("Contact")
                      select a;
  foreach (Address a in queryResults2)
  {
    Console.WriteLine("{0} {1} {2} {3} {4} {5}",
      a.Contact.ContactID,
      a.Contact.FirstName, a.Contact.LastName,
      a.City, a.State, a.ZipCode);
  }

The above code snippet shows one of the powerful features of EF; the Include automatically retrieves the Contact for every Address. 

Next Steps

• The Entity Framework is very interesting option for working with databases in .NET; it cuts out much of the mundane code that we have been writing for many years. 
• EF automatically generates the necessary SQL and executes it for you based on the ADO.NET provider that you select when you create your connection.
• You can download a backup of the SQL Server 2008 sample database and the Visual Studio 2008 sample project here and use it as a basis for your own testing and experimentation. 
• Stay tuned for a future tip on how to use stored procedures with EF.

About the author

Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips