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:

 

image

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:

 

image

To get the following menu:

image

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

image

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:

image

Advertisements

Business Logic: I Know It When I See It!

So we all know that business logic is supposed to be separate from any other layer in your application.  Other logic like database access and user interface logic should not be mixed in with the business logic.  This is all well and good but it begs the question – what is business logic?  How do I recognize it in code?

Is this business logic?

String sql = "Select * from Customer";

The answer is no – it is database access logic?

Is this business logic?

String sql = String.Format("Select * from Customer where customerId = {0}", customerId);

It might be.  If the application’s UI is simply displaying a customer’s record – then this is not business logic? However, if the application does something different, does a calculation, etc.. depending on the result of this statement, then it is business logic?

How about this?

Int32 customerTypeId=0;
customerTypeId = this.customerTypeDropDownList.SelectedIndex;
if(customerTypeId == 1)
{
    Response.Redirect("RegularCustomer.aspx");
}
else
{
    Response.Redirect("SpecialCustomer.aspx");
}

Is basic form navigation considered  business logic? 

And what about this?

if(customerTypeId == 1)
{
    InsertNewOrder();
}
else
{
    UpdateNewOrder();
}

Is basic control of flow of the application considered business logic?

Finally, what about?

foreach (LineItem lineItem in order.LineItems)
{
    total += lineItem.Price;
}

total += total * salesTax;

That sure looks like business logic – because it is doing a calculation?

Examples aside, can every line of code be categorized?  If so, what are the categories?

  • User Navigation?
  • Input Validation?
  • Database Interaction?
  • Business Logic?
    I went and did a search on “business logic” and there is little out there –and certainly nothing canonical.
    I would assume that business logic is context-free.  So If I took a line of code from one place in my application and put it in another, it still would be business logic.

I assume that code can morph into (or away from) business logic.  For example, that simple sql select statement might not be business logic, but once I all all these crazy where clauses, it becomes business logic – or if I add an order by statement it becomes presentation logic.

Another consideration is that the visual database controls that comes our of the box in Win and Web Forms

image

Automatically mix database and presentation logic together and if you add any conditional statements to the control, if becomes a three-headed monster of database, presentation, and business logic.

Similarly, all stored procedures that have an “IF” or “CASE” in them are probably business logic – business logic that is tightly coupled (indeed embedded) in the database CRUD.

Does that mean that any application that uses a visual control or a stored procedure has an incorrect architecture?  Maybe.  Perhaps separation of concerns is like the Open/Closed principle – you never get there but it is worth trying.

VS2012, EF, and Mocking

A couple of years ago I presented at TriNug’s code camp a way to build an interface for EF 1.0 using partial classes.  I thought I would revisit that with VS2012 to see if it is still possible using EF 5.0 and the mocking framework in VS2012.

My first stop was to  install SQL Server Express and then Run Northwind.exe.  Note that you can’t just attach to the Northwind .mdf because of version incompatibilities.  Rather, you need to open SQL Server Express and run the scripts that came with the install.  In any event, once it is installed, it was straight forward to add an EF representation to the project.  The one cool thing that I noticed is that you can change the color of different tables via the property panel:

image

Sure enough, by changing the fill color, you get a really cool effect like this:

image

With EF added to the project, coded up a factory like this:

public List<Territory> GetTerritoriesForARegionId(Int32 regionId)
{
    List<Territory> territories = null;

    if (regionId < 0)
    {
        throw new ArgumentOutOfRangeException("regionId cannot be less than 0");
    }

    using (NorthwindEntities entities = new NorthwindEntities())
    {
        territories = entities.Territories.Where(t => t.RegionID == regionId).ToList();
    }

    return territories;
}

I then went to right click –> add Tests like you do in VS2010 and… Oh No!  That feature is not in VS2012

I then went to hand-code a unit test project, class, and method like this:

[TestMethod]
public void GetTerritoryForARegionIdWithRegionIdOne_ReturnsThreeRecords()
{
    TerritoryFactory factory = new TerritoryFactory();
    List<Territory> territories = factory.GetTerritoriesForARegionId(1);

    Int32 expected = 3;
    Int32 actual = territories.Count;

    Assert.AreEqual(expected, actual);
}

When I ran it, I got this:

image

I needed to copy/paste the .config over.  (Which is the 1st clue that my test is is actually an integration test).  I then ran the test and got red:

image

So there are 19 records in the database.  I can do a couple of things here.  I could check that any value is coming out of the database:

[TestMethod]
public void GetTerritoryForARegionIdWithRegionIdOne_ReturnsThreeRecords()
{
    TerritoryFactory factory = new TerritoryFactory();
    List<Territory> territories = factory.GetTerritoriesForARegionId(1);
    Assert.IsNotNull(territories);

}

The problem is that the working code will always return a instance – unless an exception is thrown. I suppose I can leave the list as null and put a try…catch around the lambda, but that doesn’t really get you anything.  So really, what do you need to test? It is the lambda expression in the working code:

territories = entities.Territories.Where(t => t.RegionID == regionId).ToList();

The problem is that the working code (and therefore the test) is dependent on the entity framework. Breaking the expression into 2 parts, I don’t need to test if the EF does its job, I only need to test my code:

t => t.RegionID == regionId

So how to I make sure you are writing the correct statement to pull down the results you expect? I need to isolate the statement and following functional programming, guarantee that the result will be the same.

To that end, I need to remove the dependency on volatile data. So no database. So how do you make a EF that is not database specific? You sub it out. There are a couple of options (xUnit Test Patterns p.171) . The way I favor is to first inject that context into any class that is using it (and I prefer property injection over constructor injection). 

Note that the dependent class no longer controls the lifetime of the context, so no “using” statement in it.

public class TerritoryFactory
{
    public NorthwindEntities NorthwindEntities { get; set; }

    public List<Territory> GetTerritoriesForARegionId(Int32 regionId)
    {
        List<Territory> territories = null;

        if (regionId < 0)
        {
            throw new ArgumentOutOfRangeException("regionId cannot be less than 0");
        }

        territories = this.NorthwindEntities.Territories.Where(t => t.RegionID == regionId).ToList();

        return territories;
    }
}

Once I am injecting the context, I can pass in a local instance of an EF Context. A If I want to sub out a different version, you need to implement an interface.

The problem is that EF is auto generated – so you can’t just make an interface and have it implement it.

image

This is the problem I solved at the code camp 2 years ago (Note that this would be so much easier if MSFT added interfaces to their auto generated classes.  Just sayin’).  In any event, the way around it is to use a partial class and then extract the interface from that partial class.  To that end, I added a new class to the project with the following code:

public partial class NorthwindEntities 
{

}

I then used Refactor –> Extract Interface and I got an interface:

interface INorthwindEntities
{
System.Data.Entity.DbSet<Category> Categories { get; set; }
System.Data.Entity.DbSet<CustomerDemographic> CustomerDemographics { get; set; }
System.Data.Entity.DbSet<Customer> Customers { get; set; }
System.Data.Entity.DbSet<Employee> Employees { get; set; }
System.Data.Entity.DbSet<Order_Detail> Order_Details { get; set; }
System.Data.Entity.DbSet<Order> Orders { get; set; }
System.Data.Entity.DbSet<Product> Products { get; set; }
System.Data.Entity.DbSet<Region> Regions { get; set; }
System.Data.Entity.DbSet<Shipper> Shippers { get; set; }
System.Data.Entity.DbSet<Supplier> Suppliers { get; set; }
System.Data.Entity.DbSet<Territory> Territories { get; set; }
}

I then changed my unit test code to use the interface like so:

[TestMethod]
public void GetTerritoryForARegionIdWithRegionIdOne_ReturnsThreeRecords()
{
    TerritoryFactory factory = new TerritoryFactory();
    INorthwindEntities entities = new NorthwindEntities();
    factory.NorthwindEntities = entities;
    List<Territory> territories = factory.GetTerritoriesForARegionId(1);

    Int32 expected = 3;
    Int32 actual = territories.Count;

    Assert.AreEqual(expected, actual);
}

When I ran the test, I got the same red (red>green>refactor?  how about red > redder > refactor).

image

So the last thing is the implementation.  How do we build a “fake” in memory instance of the Northwind Entities that returns 3 Territories?  Enter Mocking Framework!

image

 

And then switch out the implementation for the Mock:

[TestMethod]
public void GetTerritoryForARegionIdWithRegionIdOne_ReturnsThreeRecords()
{
    TerritoryFactory factory = new TerritoryFactory();

    //Replace the actual implementation with the Fake
    //INorthwindEntities entities = new Tff.NorthwindApp.NorthwindEntities();
    INorthwindEntities entities = new Fakes.StubNorthwindEntities();

    //Add in Stub Data
    entities.Territories.Add(new Territory { TerritoryID = "1", RegionID = 1 });
    entities.Territories.Add(new Territory { TerritoryID = "2", RegionID = 1 });
    entities.Territories.Add(new Territory { TerritoryID = "3", RegionID = 1 });

    factory.NorthwindEntities = entities;
    List<Territory> territories = factory.GetTerritoriesForARegionId(1);
    Int32 expected = 3;
    Int32 actual = territories.Count;

    Assert.AreEqual(expected, actual);

}

I half expected that running this would get green.  I assumed that the default constructor of EF (which is what the Mocking framework would use) would just give an empty graph without going to the database.  Alas, I was wrong:

image

So the Stub is still going out to the database.

clip_image001

I then spent a couple of hours of trying to get around this “feature” of the EF being so tightly coupled to the database and I gave up.  I then tried the same technique with Linq to Sql.  Unfortunately, I ran into the same problem.  I then went to stack overflow and got nothing.

To me, this is a real limitation of EF.  EF should be able to be stubbed easily.  MSFT missed a real opportunity here…

Object Oriented Software Construction && Design Patterns

I was watching some of Uncle Bob’s clean coder video casts when he mentioned this book in the ISP one:image

I picked up used copy for $5 on Amazon marketplace and dug in.  Perhaps the markup ($140 new, $.99 used) should have tipped me off that this was a text book, but I didn’t realize that until reading the first couple of paragraphs.  This is a text book, and reads like one.  There might be some good information in it, but the dense prose and concept-first organization makes it really hard to get through – and you certainly can’t skim it.  After reading the first couple of chapters, I realized that most of what he is saying is already out there in the public domain (in much more accessible form) so I put it up on the bookshelf.

Also, Uncle Bob mentioned a couple of patterns in the DI video cast: Visitor Pattern and Adapter Pattern.  I grabbed my Gof4 book Design Patterns and starting digging in. 

image

I quickly noticed that the way that the Gof4 present these patterns and the way Uncle Bob describes them are very different – and not in a good way.  I then stopped reading the Gof4 book and will try a couple of other resources like Pluralsite to help me here.

All in all, I didn’t really enjoy either exercise.  I am going to put The Annotated Turning back on my nightstand….