Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Intro to Entity Framework with SQL Server


By:   |   Last Updated: 2010-03-16   |   Comments (8)   |   Related Tips: More > 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:

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

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:

Add the EF Model to a Project

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

Adding an ADO.NET Entity Data Model to your project

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

Step 2: Specify a Database Connection:

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

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

I have a SQL Server 2008 database called EFSample that we will use

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:

Select the tables, views and stored procedures that you want to include in the model

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:

they each have a property called Entity Set Name

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.


Last Updated: 2010-03-16


get scripts

next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, April 14, 2010 - 10:33:16 AM - raybarley Back To Top

The benefit of the entity framework is that you don't have to write any stored procedures.  I'm just speaking in terms of SQL Server here but the dynamic sql that's generated uses the sp_executesql stored proc which I think provides essentially the same query plan reuse as stored procedures.

 We could argue dynamic sql versus stored procs forever. I actually like stored procs myself because then I have total control over what goes on. 

The main point is that if you're okay with dynamic sql - great.  If not that's okay too, you can write stored procs and entity framework can use them.


Wednesday, April 14, 2010 - 10:20:47 AM - bashforth Back To Top

I noticed that the original question from the dba noted that he was concerned that it appeared that dynamic SQL was being used. It would appear that this methodoloyg would allow the development team to write an entire application in this manner? If so, wouldn't they lose the performance gains usually seen when using stored procedures and views vs dynamic sql?  The article appeared to be a reasonable intro but failed to answer the underlying question.  :)


Wednesday, April 14, 2010 - 8:36:42 AM - Tom Garth Back To Top

EF sounds like a new name for an old idea. In Visual Studio 6.0 there was the Data Environment. Before that you had to write your own queries, but you could still use stored procedures even with Access and DAO.

As far as I know,stored Procedures are still the best protection against SQL Injection. As a developer myself, I've found that in the long run, stored procedures offer better more centralized code debugging and maintainability. They also provide a more transparent view of whatever DML permissions are required, so that a DBA can easily understand and maintain it down the road.

 As a DBA I would encourage the developers to do part of their development writing T-SQL stored procedures, and removing any hurdles that might be discouraging them from using it. The main hurdle is usually a lack understanding and/or training.

Tom Garth

R.L. Nelson and Associates, Inc.

http://www.VerticalSol.com

 


Wednesday, April 14, 2010 - 7:03:24 AM - CSharpDeveloper Back To Top

Having worked on two projects with Entity Framework as a developer this is my view.

1.  Correct in the default sence.  Views is one option but you can still use Use stored procedures for all data access.  That is the major difference between Entity Framewok (Enterprise Applications) and LinqtoSQL (Rapid Application Development).  Entity Framework is more flexible with its options.

2.  Yes but the dependencies are very easy to find and fix since the compiler will immediatly complain that the model has changed.

3.  Yes it is possible since you can use Stored Procedures just as you could before.

4.  In the default scenario this might "sometimes" be true but once again Stored Procedures to the rescue if necessary.

My only complaint is that Entity Framework works really well with "clean" databases but databases that are not well normalized can be a challenge but not impossible.  ORM tools can be a real productivity improvement for large applications though.  Bugs that might have taken several hours to fix before can now be fixed in a few minutes. 

 


Tuesday, March 16, 2010 - 1:10:33 PM - raybarley Back To Top

This was an intro tip, I didn't go in to any detail about the EF model.  I did note in the tip that there are 3 parts to the model - the conceptual model is what is exposed to your application and you are free to craft that however you like.  In other words it doesn't have to expose your data model although in the tip I just used the wizard and that is the result that you get out of the box.

Complicated related logic would go in your .NET code using EF.

There are a number of ways to handle complicated queries - you can create views, you can put your SQL directly into the model using a feature called DefinedQuery, or you could call your own stored procedures.  I would say that the generated SQL will probably be fine for any simple CRUD application. As soon as you go beyond that you may want to look at views, DefinedQuery or stored procedures.

I would look at EF as a tool to eliminate the basic CRUD for sure.


Tuesday, March 16, 2010 - 12:14:46 PM - aprato Back To Top
With this Framework, I see the following issues

1. The base tables require exposure to the application though this could be mitigated somewhat with views

2. Any changes to the data model now require sifting through this code to find dependencies (again, views *may* help here).

3. It's probably not feasible to create complicated, related logic here (i.e.working with intermediate table results,  multiple related DML statements, etc)

4.  The query produced for more complicated queries may not necessarily be the most optimal

Are my assessments accurate?  I've never used it (I work in Java environments) so I'm looking for an educated opinion.  I've always hated auto generated SQL.

 


Tuesday, March 16, 2010 - 9:18:16 AM - raybarley Back To Top

I thought for an intro tip it would be better to show one way to query. 

I ran SQL Server Profiler while running the sample code.  Here is the pertinent output from the profiler:

 exec sp_executesql N'insert [dbo].[Contact]([LastName], [FirstName])
values (@0, @1)
select [ContactID]
from [dbo].[Contact]
where @@ROWCOUNT > 0 and [ContactID] = scope_identity()',N'@0 nvarchar(5),@1 nvarchar(3)',@0=N'JONES',@1=N'SAM'


exec sp_executesql N'insert [dbo].[Address]([AddressType], [ContactID], [AddressLine1], [AddressLine2], [City], [State], [ZipCode])
values (@0, @1, @2, null, @3, @4, @5)
select [AddressID]
from [dbo].[Address]
where @@ROWCOUNT > 0 and [AddressID] = scope_identity()',N'@0 nvarchar(4),@1 int,@2 nvarchar(11),@3 nvarchar(9),@4 nvarchar(2),@5 int',@0=N'HOME',@1=12,@2=N'101 MAIN ST',@3=N'BALTIMORE',@4=N'MD',@5=21202


SELECT
[Extent1].[ContactID] AS [ContactID],
[Extent1].[LastName] AS [LastName],
[Extent1].[FirstName] AS [FirstName]
FROM [dbo].[Contact] AS [Extent1]


SELECT
1 AS [C1],
[Extent1].[AddressID] AS [AddressID],
[Extent1].[AddressType] AS [AddressType],
[Extent1].[AddressLine1] AS [AddressLine1],
[Extent1].[AddressLine2] AS [AddressLine2],
[Extent1].[City] AS [City],
[Extent1].[State] AS [State],
[Extent1].[ZipCode] AS [ZipCode],
[Extent2].[ContactID] AS [ContactID],
[Extent2].[LastName] AS [LastName],
[Extent2].[FirstName] AS [FirstName]
FROM  [dbo].[Address] AS [Extent1]
LEFT OUTER JOIN [dbo].[Contact] AS [Extent2] ON [Extent1].[ContactID] = [Extent2].[ContactID]

 


Tuesday, March 16, 2010 - 8:22:47 AM - unclebiguns Back To Top
Be nice to see the SQL generated by the Linq queries. Also would have been nice to have explained that there are other ways to query using EF, like Entity SQL

Learn more about SQL Server tools