Self-Referencing Keys Gotcha in EF

Looking at the Employee table in Northwind, there is a self-referencing key on the ‘ReportsTo’ field

When a series of new employees are created, you might run into problems trying to commit those changes all at once.  For example:

When you run this, you get this kind of exception:

The reason is that the PK of the 1st employee is 0 and the PK of the second employee is ….. 0.  Since both are created on the client and have not had the ‘real’ primary key assigned by the database, the FK gets confused because there are 2 Primary Keys with the same value.  And according to these calculations, that is impossible.

The way to fix this problem is to move the SaveChanges() to after each add like this:

Or to not associate the FK until the 1st bulk commit like this;

Switch Databases Using EF: Part Deux

Consider you are a company where you want to keep each of your customer’s data in its own database on the same server.  Each of the databases has the identical schema.  For example:

In the Data Access Layer, you are using Entity Framework and you want to move between databases at run time.  I blogged about one possible solution to this problem here, but I was thinking about this some more and I have a different (better?) idea. 

The first thing to realize is that EF does not make this easy.  There is not a Contxext.ChangeDatabase(String) method and the constructor does not allow to pass in a connection string.  This tight-coupling smells of poor design, but fortunately this is not insurmountable.

The first step is to create a Model (NorthwindModel in this case).  When you inspect the context, you can see that Microsoft made the entities a partial class

So you can add a class to your project like so:

Then, in your consuming class, you can create a method like this:

And then you can switch databases.  Just pass in the default connection string and the new database name like so:

And you can change databases on the fly:

The ’gotcha’ to realize is that Entity Framework requires that you have a default value in the app/web.config file.  If not, you will get this exception:

This smells like too much coupling, but that is a common theme with EF.  So make sure you have a default EF connection string in the .config – even if you don’t even use it.

Entity Framework–> Change Database

I was working with Entity Frameworks over the weekend when I ran into some sloppy programming on Microsoft’s part.  I made a project that uses Entity Frameworks to connection to Northwind.  I then added method to change the database at runtime like so:

public class NorthwindFactory
{
    public static void ChangeDatabase(String newDatabaseName)
    {
        using (NorthwindEntities entities = new NorthwindEntities())
        {
            entities.Connection.ChangeDatabase(newDatabaseName);
        }
    }
}

When I checked out intellisense on the ChangeDatabase method, there was nothing to indicate anything unusual.

When I invoked the method, I got the following exception:

Sure enough, when I go to MSDN help online, ChangeDatabase() is not supported in 4.5 and 4.0, but it is supported in 3.5. 

This is a clear violation of the Liskov Substitution Principle.  It is a also sloppy – they could have marked it obsolete or, at least, changed the XML code comments to reflect the fact that it is not supported.  Instead, they left it up to the developer to get the exception the first time s/he invokes it.

So instead of using the ChangeDatabase() method, I needed to use the SqlConnectionStringBuilder and EntityConnectionStringBuilder like so:

SqlConnectionStringBuilder sqlConnectionStringBuilder = 
    new SqlConnectionStringBuilder();
sqlConnectionStringBuilder.DataSource = ".";
sqlConnectionStringBuilder.InitialCatalog = newDatabaseName;
sqlConnectionStringBuilder.IntegratedSecurity = true;

EntityConnectionStringBuilder entityConnectionStringBuilder = 
    new EntityConnectionStringBuilder();
entityConnectionStringBuilder.Metadata = 
    @"res://*/NorthwindModel.csdl|res://*/NorthwindModel.ssdl|res://*/NorthwindModel.msl";
entityConnectionStringBuilder.Provider = "System.Data.SqlClient";
entityConnectionStringBuilder.ProviderConnectionString = 
    sqlConnectionStringBuilder.ConnectionString;

The problem is that I can’t use that entityConnectionStringBuilder inside of the using block of the Entity that I want to change:

So to switch databases, I have to drop the “using” statement.  The below code snippet works

NorthwindEntities entities = new NorthwindEntities();
SqlConnectionStringBuilder sqlConnectionStringBuilder = 
    new SqlConnectionStringBuilder();
sqlConnectionStringBuilder.DataSource = ".";
sqlConnectionStringBuilder.InitialCatalog = newDatabaseName;
sqlConnectionStringBuilder.IntegratedSecurity = true;

EntityConnectionStringBuilder entityConnectionStringBuilder = 
    new EntityConnectionStringBuilder();
entityConnectionStringBuilder.Metadata = 
    @"res://*/NorthwindModel.csdl|res://*/NorthwindModel.ssdl|res://*/NorthwindModel.msl";
entityConnectionStringBuilder.Provider = "System.Data.SqlClient";
entityConnectionStringBuilder.ProviderConnectionString = 
    sqlConnectionStringBuilder.ConnectionString;

entities = new NorthwindEntities(entityConnectionStringBuilder.ConnectionString);

I am not sure Microsoft thought this one through…

Entity Framework Using Stored Procedures

Entity Framework is really designed to access your tables directly, but you can use stored procedures.  This is typical where you have a legacy system where there is lots of business logic embedded in your stored procedures and you can’t afford to do a rewrite.

Taking the Northwind database, I added stored procedures to handle the typical CRUD operations to the Product table:

Create procedure usp_GetProducts
as
Select * from Products
GO

Create procedure usp_UpdateProduct
@productId int,
@productName nvarchar(40)
as
Update Products
Set ProductName = @productName
Where ProductID = @productId
GO

Create procedure usp_DeleteProduct
@productId int
as
Delete Products
Where ProductID = @productId
GO

Create procedure usp_InsertProduct
@productName nvarchar(40)
as
Insert Products
Values(
@productName,null,null,null,null,
null,null,null,0)

GO

I then wired up a EF model in Visual Studio using the EF designer support pointing at the tables so I wouldn’t have to create the entities by hand:

I then added the four stored procedures that I just created – note that the .edmx designer does not show the sps graphically.

Taking the Product entity, I then selected the “Stored Procedure Mapping” context menu:

To get the following menu:

It was easy enough then to add the stored procedures that I just created to the entity:

The problem is then with the R of CRUD – there is no designer support for Selecting the Products.  You have to do this in code like so:

public NorthwindRepository()
{
    using (NorthwindEntities entities = new NorthwindEntities())
    {
        String commandText = "usp_GetProducts";
        var productQuery = entities.ExecuteStoreQuery<Product>(commandText,null);
        foreach (Product product in productQuery)
        {
            //TODO
        }
    }
}

Once you have that, you can use stored procedures in your project.  Note that many stored procedures are not simple select statements but bring in extra fields like this:

Create procedure usp_GetProducts
as
Select
P.ProductID,
P.ProductName,
P.SupplierID,
S.CompanyName
from Products as P
Inner join Suppliers as S
on P.SupplierID = S.SupplierID
GO

Is that is the case, you CAN’T just use the entity based on the table and pump in that stored procedure – the fields must match exactly – name, data types, etc.  If they don’t match, you get a exception like this: