EntityFramework 4.0 and SQL 2000

If you are like me, some customer just can’t upgrade from SQL 2000, so how do you use the EntityFramework 4.0+ with SQL Server 2000?

Here is one solution that has worked for me:

Here are the steps to add a new Entity Framework 4.0+ entity context to a Visual Studio 2010 project:

  • Download the base entity data model for SQL Server 2000 file and save it to the project folder to which you wish to add the SQL 2000 entity context. Do NOT add it to your project yet.
  • Rename the EDMX file to the name of the data context.
  • Open the EDMX file in a text editor.
  • Find/replace all references to %DB_NAME% with your own value.
  • Add this new connection string section to your App or Web.config and find/replace with your own values:
<connectionStrings>
  <add name="%DB_NAME%Entities" connectionString="metadata=res://*/%DB_NAME%.csdl|res://*/%DB_NAME%.ssdl|res://*/%DB_NAME%.msl;provider=System.Data.SqlClient;provider connection string="Data Source=%DB_HOST_NAME%;Initial Catalog=%DB_NAME%;Persist Security Info=True;User ID=%DB_USER%;Password=%DB_USER_PWD%;MultipleActiveResultSets=False"" providerName="System.Data.EntityClient" />
</connectionStrings>

Please note: the connection string name MUST MATCH the EntityContainer section name you specified in the EDMX file.

  • Find/replace all references to %DB_NAME% in the App or Web.config file with your own value as above.
  • After the .edmx and web.config files configured, add them to the project and verify the new entity data source appears after refreshing the Data Source manager window.
  • If you can’t double click on your *.edmx file you’ll need to right click on it and select “Open with”, and choose “ADO.NET Entity Data Model Designer”
  • If the new entity data source appears, open the .edmx file in design view and refresh the design model.

Now you have your Entity XML file next you can generate your Code by using the T4 templete code generation.  To do this just right click in the EDMX model and select “Add Code Generation Item” this will give you the installed Generators (you can get more from NUGET, like dbcontext”

I’ve found this very useful and gives you a head start on old databases.

Entity Framework Issues

I’ve come across two issues with the Entity Framework, one that effects any language and one just for VB.NET

It’s quite simple, the Entity Framework generates edmx files, however that Inherits Global.System.Data.Objects.DataClasses.EntityObject, which means when you create your Partial class you can not Inherit from your own base class.

The second issue is related to VB.NET, as if you are intending to use Dependence Injection or IoC you need to have an Interface defined and to implement an Interface in VB.NET you must add Implements after each Property and Method, which you can not do as the framework generates it’s own code.

I’ve attached both C# and VB.NET examples, none of which compile

Entity Framework Issues.zip (42.58 kb)

Creating an Entity Connection for the Entity Framework

When you start to play with the Entity Framework you will soon come up with the need to store your connection in a common place.  To do this you will need to with provide the connection string to the Entity Object Context or provide the Entity Connection.  I prefer to us the Entity Connection as you can explicity define each section of the connection.

C#

VB.NET

and if you need to pass in the UserID and Password

For some more help on setting this up check out Ricka on Dynamic Data

LINQ to SQL v LINQ to Entities

Whatare the differences between LINQ to SQL v LINQ to Entities, well:

LINQ to SQL uses tables as entities

LINQ to Entities allows me to write C# LINQ queries that run against a conceptual data model.

The following table provides a summary of the features within each LINQ object

So this means that LINQ to SQL uses a database model to create its entities, where as the Entity Framework work on a layer of abstraction above the data, this is a most important feature.  As most database changes are taken care of by the schema and mapping without requiring a change to the obect model – making it so you do not have to refactor and rebuild your objects.

The second difference is the Entity Framework has the ability to allow the entity inheritance and entity composition.  This means that you can create an entity that is composed of colums originating in multiple tables without any complex join logic.

In summary:

  • If you want the added security of insulation and loose coupling from the underlying database schema to make your object model more resilient to change, use the Entity Framework
  • If you find that you need the features of entity inheritance and entity composition, use the Entity Framework
  • If you already have a large LINQ to SQL codebase that is running just fine without entities, you probably don’t need to spend the time to refactor out LINQ to SQL to replace it with LINQ to Entities.
  • If you want to run LINQ queries against an object model, but your object model is a 1:1 mirror of the tables in your database, you probably don’t need the Entity Framework.

LINQ Entity Framework

Microsoft has released it’s Entity Framework to the work, so does this mean the death of LINQ to SQL?  Check out my blog on this, Death of LINQ to SQL

So why is LINQ so good, well the problems with data has always been around the different and many way you need to know to interface and programme to.  For example if you conntect to a Microsoft SQL Server database, you need to know SQL, to read and write to XML files you need to learn XPath or XQuery, to query Active Directory you need to know LDAP (Lightweigth Directory Access Protocol).  To know all of these are more is quite a task.

SO along comes LINQ, which is a intergrated query language, which provides the same query language and grammer features, no matter where the data is held.

Microsoft started off by allowing developers to use LINQ to XML, LINQ to L, and LINQ to Objects.  These are all good starting blocks, but I have always been aware that Microsofts intention has been to pull all of these together and under one method, and that is the Entity Framework.  Which is built on top of LINQ and ADO.NET 3.0.

In order to use the Entity Framework you will nedd Visual Studio 2008, along with Service Pack 1 and .NET 3.5

Defining an Entiry Data Model

Once you have created your project, say an Console Application, you can get down to generating an Entity Data Model

 

  1. Make sure you have Visual Studio 2008 SP1, otherwisethe Entity Framework will not be available.
  2. Add a New Item to your project, and select ADO.NET Entity Data Model.
  3. Change the name to read NorthwindModel.edmx
  4. Select to Generate From Database
  5. Choose you data connection you wish to use, select the Northwind database
  6. Save the connection, you should find your app.config should now have the NorthwindEntities
  7. Select the database objects you wish to choose, in ower example select Customer, Orders, Order Details and Products.
  8. Change the Model Namespace to NorthwindModel and click finish.
You should end up with an .EDMX file looking something like this:

Okay, now it is time to query the data

Querying Products

What is nice about LINQ is it looks and feels very much like SQL.  With one exception, you have to write the syntax back to front.  Why I hear you ask, I don’t know the official answer to this, but my thoughts are that it is due to intellisense, as by writing you syntax backwards the intellisense can find the information you need.

Okay to query the Products table, you will first need to create an instance of the NorthwindEntities Class, where did this come from?  Well it was created when you created the .edmx file.

 

NorthwindEntities entities = new NorthwindEntities();

Dim db As New NorthwindEntities()

 

Next we have to create the LINQ statement to read the Products table / entity

 

var products = from p in entities.Products

                           where p.ProductID == 7
                           select p;

Dim products = From p In entities.Products _
                       Where p.ProductID = 7 _
                       Select p

Okay now you have the products you can loop through the list by using a For Each method
foreach (var p in products)
{
      Console.WriteLine(p.ProductName);
}
For Each p In products
      Console.WriteLine(p.ProductName)
Next
How simple could it be?
Check out my other blog entries in using LINQ to find out more information on what you can do with LINQ

To find out more about LINQ and the Entiry Framework check out The ADO.NET Entity Framework OverviewThe ADO.NET Entity Framework Overview or Introduction to ASP.NET Dynamic Data