Click here to Skip to main content
15,867,308 members
Articles / Database Development / SQL Server

Business Logic with Entity Framework

Rate me:
Please Sign up or sign in to vote.
4.42/5 (18 votes)
26 May 2009CPOL7 min read 68.8K   2.4K   70   7
How to use Entity framework to do some basic logic when a change happens in related Entities or Associations
Image 1

Before You Start Reading

I assume you have basic Entity Framework knowledge and good C# 3.x coding skills in order to understand my code and use it. If not, please check some other articles or documentation regarding Entity Framework.

Introduction 

In this article, I'm going to demonstrate how you can use Entity framework to do some basic logic when a change happens in related entities or “Associations” as it is called. And to keep it simple, I'm going to illustrate an update of an Order’s Total Amount when its Order Details get changed, added or deleted.

Background

Well, some developers love to stick to what they know to solve every problem just as the old saying “when you have a hammer, everything looks like a nail to you”, but for me I'd like to explore new stuff and know the difference.

First of all I'd like to give you an idea of the possible alternatives that we have to solve this problem and their challenges:

1. Using Database Triggers on the child table to update the parent table.

Challenges
  • You will do the calculations on the UI while the user is updating the DATA and once again when issuing the update or change back to the database and that’s duplicate work.
  • Your application will not get notified of the change if it was done on the database unless you are using a database engine that has some mechanism of notification.
  • You don't know much SQL or vendor-specific language or extensions to implement such triggers and all you've got is your IDE and programming language.

2. Use Database Views to do the calculation when needed to be shown to the user.

Challenges
  • You will put most of your Business-Logic in the database and need a GOOD DB administrator to maintain your database performance and code.
  • The calculations only occur when the user requests to see the data and that could be problematic in some systems that need to maintain some logic on the changes of the calculation logic.
  • Again if you don't know much of SQL or would like to stick to your IDE, this is not the way to go unless you are willing to learn how to manage a database the right way.

3. Use the solution provided here in this article by attaching your business logic to your entities.

Challenges
  • You need to get some basic grasp on ORM techniques and a lot of OOP knowledge.
  • Your logic is now stuck with your client application or Data Access Code and that’s against N-Tier architecture.
  • Anything else you think is a problem? { it’s up to you to use your imagination }

Solution

Ok, now we have an idea of what is good and what is bad, so let us go ahead and start learning our solution. To demonstrate our solution, I've created a small database of two tables as you can see from the following diagram:

EntityFrameworkLogic2.png

So we have Orders and OrderItems and what we want to achieve here is that when the OrderItems of an order change by adding, deleting or updating we want to update the TotalAmount of the parent Order to reflect the sum of (Quantity x Price) of its child OrderItems.

And to make it even harder, we want the calculation to include all the OrderItems that we have in our memory or cache and also any other OrderItem in the database that we didn't have in memory when the update was initiated.

And let us start by creating our solution in Visual Studio, and in this case you will have to use VS 2008 with SP1 installed otherwise I'm not going to explain any alternatives.

I've created a new Windows Forms application called “EFLogicDemo“ and added a new ADO.NET Entity Data Model to it and named it “OrdersModel”.

EntityFrameworkLogic3.png

A wizard will ask you if you want to generate the Model from a database and that’s fine in our case because we are not going to do anything advanced or fancy, so just direct the wizard to connect to our database that we created earlier and let it do the magic and don't forget to let the wizard save our connection string in App.Config.

And as a tweak EF does not distinguish plural names like its old LINQ to SQL does, so I changed the default names for the “Entity Set Name” and “Name” for each of the generated entity to make it like the following:

  1. SalesOrders
    • Entity Set Name = SalesOrders
    • Name = SalesOrder
  2. OrderItems
    • Entity Set Name = OrderItems
    • Name = OrderItem

And here is how our Entity Model looks like after it is completed:

EntityFrameworkLogic4.png

Okay, now we are clear to give our coding skills a spin, right? We can start by adding a new class to our project and call it exactly as the Parent Entity that we want to implement its logic, in our case SalesOrder as illustrated below:

EntityFrameworkLogic5.png

And make sure you change its access modifier to public partial in order to extend the existing class that EF generated for our entity.

Using the Code

Now we need a starting point, in this case we want to listen to the event AssociationChanged on our related child entity and the best place to do that is the constructor of the class as it makes sure our event listener will be called on any instance created inside our application, and here is the code to do so:

C#
public SalesOrder()
{
    this.OrderItems.AssociationChanged += 
        new CollectionChangeEventHandler(OrderItems_AssociationChanged);
}

So far so good, we have a method called OrderItems_AssociationChanged that will be called when any changes happen on an OrderItem that is related to the current instance of SalesOrder and this method’s type has an argument of type System.ComponentModel.CollectionChangeEventArgs which gives us two properties called Action and Element that tell us what happened and where.

Now in our method, we get notified of changes that we need to act upon. So what I'm going to do is to eagerly load all the details if possible from the database and then do the calculation on them and update our SalesOrder by the new calculated TotalAmount. To do just that, I create a new method to do the calculation and called it UpdateTotalAmount and here is the code:

C#
private void UpdateTotalAmount()
{
    // load all our OrderItems from the database if and ONLY if this SalesOrder
    // was saved in the database, otherwise the load will give an exception
    if (this.EntityState == System.Data.EntityState.Unchanged || 
        this.EntityState == System.Data.EntityState.Modified)
    {
        // while loading back database changes, we want to preserve any modification\
        // that was made to our local instances
        this.OrderItems.Load(System.Data.Objects.MergeOption.PreserveChanges);
    }
        
    // calculate the new TotalAmount if there is any OrderItems found in the collection
    var newAmount = OrderItems == null ? 0 : OrderItems.Sum(
        d => d.Quantity * d.UnitPrice);

    // apply the changes and update the current instance with the new value
    this.TotalAmount = newAmount - (this.Discount ?? 0);
}

Well, the code is pretty easy and commented and really does not need more explanation.

Now all we need to do is to call this method in our event listener OrderItems_AssociationChanged but there is one more thing here before we go ahead, what if the OrderItem had its properties changed? What will happen?

Well, we are only monitoring the changes of the relationship or association and if the child entity got its property values changed, we will never get notified UNLESS we listen to its changes and here is how it can be done using the Holy Interface of INotifyPropertyChanged. All we need to do is to attach another listener to that event on every item of our collection for the event PropertyChanged and we get notified of any change that happened on our child elements, or even we go ahead and do some login on the child element class itself and it should be easier, but I chose to do it all in one place and here is the final code of our AssociationChanged:

C#
private void OrderItems_AssociationChanged(object sender, 
    System.ComponentModel.CollectionChangeEventArgs e)
{
    // if a new SalesItem is being added to this SalesOrder
    if (e.Action == CollectionChangeAction.Add)
    {
        var detail = (OrderItem)e.Element;
        // attache some code when this SalesItem has its properties changed
        // the following code uses Lambda expression and anonymous method syntax
        detail.PropertyChanged += (s, a) =>
        {
            // now we are here invoked from the SalesItem that was changed and
            // we have 2 parameters we can use
            // (s) which is the instance of SalesItem that was changes
            // (a) which is the arguments that will tell us what exactly did change
            
            // now we only want to re-calculate if the Quantity or Unit price changed
            if (a.PropertyName == "Quantity" || a.PropertyName == "UnitPrice")
            {
                // do the re-calculation
                UpdateTotalAmount();
            }
        };
    }
    
    // we are called here by AssociationChanged and this means 
    // we need to recalculate too.
    UpdateTotalAmount();
}

Testing the Solution

Now I can hear someone talking there about whether it will work, and I reply: Unit Testing ;) because it's fast, it's easy and it needs no effort in designing anything fancy to start using your code, and I've developed this unit test to test most conditions that could happen to our Entities such as Adding, Deleting and Editing/Updating the Child Entity ... so let's give it a spin here:

C#
[TestMethod()]
public void SalesOrderLogicTest()
{
    string connection = 
    "metadata=res://*/OrdersModel.csdl|res://*/OrdersModel.ssdl|res://*/" +
    "OrdersModel.msl;provider=System.Data.SqlClient;"
        + "provider connection string='Data Source=.\\SQLExpress;" +
        "Initial Catalog=EFLogicDemo;Integrated Security=True;" +
        "MultipleActiveResultSets=True'";

    using (EFLogicDemoEntities db = new EFLogicDemoEntities(connection))
    {
        SalesOrder order = new SalesOrder();
        order.CustomerName = "Me";
        order.OrderDatetime = DateTime.Now;
        order.Discount = 2;
        db.AddToSalesOrders(order);

        OrderItem item1 = new OrderItem();
        item1.ProductName = "an Apple";
        item1.Quantity = 5;
        item1.UnitPrice = 2;
        order.OrderItems.Add(item1);

        Assert.IsTrue(order.TotalAmount == 8);

        OrderItem item2 = new OrderItem();
        item2.ProductName = "an Orange";
        item2.Quantity = 1;
        item2.UnitPrice = 10;
        order.OrderItems.Add(item2);
        
        Assert.IsTrue(order.TotalAmount == 18);

        db.DeleteObject(item1);

        Assert.IsTrue(order.TotalAmount == 8);

        item2.Quantity = 3;

        Assert.IsTrue(order.TotalAmount == 28);
    }
}

That test worked like a charm and now you can Add and Remove and change about anything in your Entity’s properties and relations and rest assured that your calculation and logic will always be working behind the scenes.

Finally, as you enjoyed the article and got some free code and knowledge, please vote for the article and share your opinions and suggestions to keep me running and thinking of adding more articles.

Points of Interest

Entity framework is not really a new technology, it is as old as ORM but the ADO.NET team is really putting much more attention to it to become the next Data Access technology, so keeping an eye out for this product is going to help you learn fast about the future of Database Applications.

History

  • Tuesday, May 26, 2009 - First release

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect Dot Labs Pty Ltd
Australia Australia
A trusted technology adviser and a seasoned consultant with 15 years of experience in IT industry focused on helping organisations build modern IT solutions that drive innovation and increase efficiency and end-user value.

With a strong business acumen, an entrepreneur character, and vast experience in software engineering, I started my own consulting business (Dot Labs Pty Ltd) with a mission to drive adoption of modern solution architecture, public cloud infrastructure, and industry best practices.

During my career, I gained an extensive hands-on experience of software engineering through architecture standards, patterns, and best practice through my work with various enterprise organisations and startups, while keeping track of emerging technology and industry trends.

Comments and Discussions

 
GeneralMy vote 5 Pin
xusan11-Nov-12 15:49
xusan11-Nov-12 15:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.