Entity Framework : Part 9 Use Stored Procedures with Entity Framework

Background

This is the ninth part of the Entity Framework 4 learning series. In this post I’ll be demonstrating how to use Stored Procedure with Entity Framework. If we are working on a green field project, it becomes easier to implement concepts like model first development. Using this approach we can define our entities first and based on the entity model we can arrive at the database model required for persisting the data.

In many cases we already have a set of database objects in place. We might have spent lot of time writing complex business logic in database objects such as Stored Procedures and Views. From a return on investment (ROI) point of view it might make sense to reuse them to reduce the time required to convert the complex logic into an object oriented code.

Entity Framework 4 supports stored procedures and views as you might have already seen if you have been following the complete learning series. This is presented as an option in the wizard which prompts us to select the database objects which we want to convert into entities during the generation of entity model.

Pre Requisites

I’ll be reusing the Northwind sample database we have been using all this while for this series. If you don’t have a copy of the database I would recommend you follow the steps from first part of this series to set up the database.

How to use Stored Procedures with Entity Framework 4

If we want to use a stored procedure, we need to follow the steps similar to all other earlier posts until we reach the step in the wizard where we are asked to select the database objects as shown below

I have selected the 3 stored procedures

1. Sales By Year
2. SalesByCategory
3. Ten Most Expensive Products

After finishing the wizard, you might notice that the designer file is empty. Unlike on previous occasions where we would get to see the list of entities for the selected tables we do not get anything for the selected stored procedures. We need to access the model explorer window which displays the stored procedures as shown in the screenshot

If the model browser is not visible along side solution explorer we can navigate to it by selecting View –> Other Windows –> Entity Data Model Browser. In order to use the stored procedure in Entity Framework 4, we need to use a feature called Function Import. What this does is it tries to map the stored procedure to a function. We can invoke the stored procedure as if we would be invoking a function. So lets look at the simplest stored procedure which is Top_Ten_Most_Expensive_Products.Select this stored procedure from the model browser and right click which will display the context menu. Select Add Function Import option which shows a popup with various options

This popup has lots of options like name of the function, return values which can be a list, a scalar value, a complex type or an entity which is already present in our entity model. If I am not sure what columns are returned by the stored procedure , I can click on the Get Column Information button to query the metadata about the stored procedure itself.

For the purpose of this demo I have created a temporary class which can be done by selecting the returns Complex option and clicking on the Create New Complex Type button towards the bottom. This would add one item each to the Function Imports folder and Complex Types folder under the model browser.

Now I can query the database using these functions with the help of an object context like

            NORTHWNDEntities context = new NORTHWNDEntities();

 

            var topTenMostExpensiveProducts = context.TenMostExpensiveProducts();

 

            foreach (var expensiveProduct in topTenMostExpensiveProducts)

            {

                Console.WriteLine(expensiveProduct.UnitPrice);

            }

I started off as usual with an instance of NorthWNDEntities class. I made a call to the TenMostExpensiveProducts function and stored the results in an variable. I loop over the results to display the UnitPrice of the top ten most expensive products.

If you run the project now you should be bale to see the ten most expensive products from the database. This was a simple stored procedure which did not take any input parameters.

Lets look at the other stored procedure which requires input parameters. We’ll use the Sales_By_Year which requires Begining_date and Ending_date parameters to be passed as inputs. We’ll import this stored procedure into a function as

I’ll again store the resultset into a complex type. In order to execute this stored procedure I’ll follow

            var salesByYear = context.Sales_by_Year(DateTime.Now.AddYears(-20), DateTime.Today);

 

            foreach (var salesByYearResult in salesByYear)

            {

                Console.WriteLine("{0} - {1}", salesByYearResult.Year, salesByYearResult.Subtotal);

            }

If you execute the program now, you’ll be able to see the year and the sub total for those years in the results.

Conclusion

As we saw from the code above, its very easy to invoke a stored procedure using Entity Framework 4. We can call all types of stored procedures and handle different types of results like lists, scalar values etc. Here I have demonstrated using the results directly, but we can use options like filtering the results what the stored procedure returns. I’ll leave it to the viewers to try those filter options to get better understanding of the framework.

For most part of my career I have had the privilege of working on green field applications. I personally follow the TDD approach more. Since it becomes very difficult to test the logic residing in database objects like stored procedures, triggers and views I tend to stay away from them. Also debugging stored procedures is difficult. Another disadvantage of using stored procedures could be that the application logic is spread across different layers apart from business logic layer or service layer. If we are using any ORM tools like Entity Framework 4 or NHibernate I would try and stay away from stored procedures as much as possible.

As always I have uploaded the compete working solution to dropbox which can be downloaded as Part9_StoredProcedures.zip

Until next time Happy Programming :)

Further Reading

Here are some books I recommend related to the topics in this post.

Continue Reading...

Unit Test Background Worker

Background

I was refactoring a piece of code with a colleague when we encountered a multi threaded code. We were refactoring some unit tests. As part of the test we needed to cover this code which was using BackgroundWorker to execute a long running task and updating the user interface once the background thread had completed its job. In this post I am going to demonstrate how to unit test the multi threaded code.

 

Scenario for using Background Worker

Assume that we have a requirement to fetch some data using a service and display it in the user interface. The user interface calls a method on a object which delegates the call to the service. While the service is processing, we do want the user interface to be responsive and give a visible feedback to the user. We are making use of the MVP pattern to separate the concerns in our project. So the view is abstracted and we program to an interface. So we are really not worried about how the view display the information to the user. We can just set a property on the view to display something like an hourglass symbol.

We call a method on the background thread using Background worker. In MVP the presenter takes care of handling such logic. So the model would abstract the part which relates to fetching the data from the data source. Lets assume that we have a repository which does this job for us. Based on these things lets start building the code.

 

Code which makes use of Background Worker

For the kind of scenario described above you’ll have a Windows or Web UI based application with a front end developed using either WPF, Winforms, WebForms or ASP.Net MVC. For simplicity I have created a simple console application just for demo purpose. I am not going to show any visual stuff hence I decided to use console application.

I have created a ProductPresenter which is defined as

        private readonly IProductRepository productRepository;
 
        public ProductPresenter(IProductRepository productRepository)
        {
            this.productRepository = productRepository;
        }
 
        public IProductListView ProductListView
        {
            get;
            set;
        }
 
        public void GetAllProducts()
        {
            BackgroundWorker worker = new BackgroundWorker();
 
            worker.DoWork += new DoWorkEventHandler(worker_DoWork);
 
            worker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(worker_RunWorkerCompleted);
 
            ProductListView.DisplayHourGlass = true;
 
            worker.RunWorkerAsync();
        }

The constructor has a dependency on IProductRepository which I’ll mock using RhinoMocks during testing phase. If you look at the complete source code you’ll notice that I have not implemented the IProductRepository interface. From the point of TDD it makes sense because I am programming to an interface. I have property ProductListView which is of type IProductListView. This is also an abstraction of the view and I do not have any concrete code for ProductListView.

Then comes the method which is the point of contention for this blog post GetAllProducts. This method creates an instance of the of BackgroundWorker class available in the DotNet Framework 2.0 onwards. It assigns the delegate for the method which will run a piece of code on the worker or background thread using DoWork event. We have assigned a method named worker_DoWork. Assume that the long running process of contacting the service and fetching the data will happen in this method.

We also need to specify the callback method which will perform the UI updates once the background worker has finished its job. This is achieved by subscribing to the RunWorkerCompleted event of the background worker. Once the background thread has completed its work it calls this method back on the main thread. This can be used to update the controls on the user interface in a real life application. In my case I have abstracted the view. When the long running task is completed I hide the hourglass symbol displayed on the view.

Next question you’ll ask me is when did we show the hourglass symbol. We would do this just before starting the worker thread using the background workers RunWorkerAsync method. The background worker spawns a thread only after we call the RunWorkerAsync method. 

The worker_DoWork mthod implimentation looks like

        private void worker_DoWork(object sender, DoWorkEventArgs e)
        {
            e.Result = productRepository.GetAllProducts();
        }

We use the injected product repository to make a call to GetAllProducts on the service. We assign the result back to the DoWorkEventsArgs which gets returned to the method which subscribes to the RunWorkerCompleted event. This method is defined as

        private void worker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            ProductListView.DisplayHourGlass = false;
 
            ProductListView.Products = (IList<Product>)e.Result;
        }

The method is responsible for updating the product list on the user interface. Finally the Product is a domain object or the model which has few properties like

    public class Product
    {
        public int ProductId { get; set; }
 
        public string Name { get; set; }
 
        public string Description { get; set; }
 
        public int UnitPrice { get; set; }
    }

 

Unit Test Background Worker

Lets start with the unit tests. Here is the code which mocks the dependencies using Rhino Mocks and creates an instance of the presenter class which happens to be our system under test (SUT).

        private ProductPresenter presenter;
 
        private MockRepository mocks;
 
        private IProductListView mockView;
 
        private IProductRepository mockRepository;
 
        [TestInitialize]
        public void Setup()
        {
            mocks = new MockRepository();
            mockView = mocks.StrictMock<IProductListView>();
            mockRepository = mocks.StrictMock<IProductRepository>();
 
            presenter = new ProductPresenter(mockRepository) { ProductListView = mockView };
        }

I have created a mock view and a mock repository. I just need to test the presenter method.

        [TestMethod]
        public void GetAllProductsTest()
        {
            IList<Product> expectedProducts = new List<Product>
                {
                    new Product { ProductId = 1, Description = "Parle G Burboun Biscuit", Name = "Burboun Biscuit", UnitPrice = 15 },
                    new Product { ProductId = 2, Description = "Amul Ice Cream", Name = "Ice Cream", UnitPrice = 45 }
                };
 
            mockView.Expect(mv => mv.DisplayHourGlass).PropertyBehavior();
 
            mockRepository.Expect(mr => mr.GetAllProducts()).Return(expectedProducts);
 
            mockView.Expect(mv => mv.Products).PropertyBehavior();
 
            mocks.ReplayAll();
 
            presenter.GetAllProducts();
 
        }

In this test I have created 2 products which the service call will return to the presenter. I am expecting a call to the DisplayHourGlass property on the view and I have mocked it by setting the property behavior. I am also setting an expectation for the GetAllProducts method to be called on the mock repository which will return me the expected products.  And finally I am setting an expectation on the Products property of the view. If you execute the test now it passes as can be see from the screenshot below.

But as per the TDD rules we are missing a point here. We haven’t verified the behavior of this test. After we executed the method we should have done an Assert as part of the AAA theory. Here I can do an assert on the view’s Products property to check that the expected products and actual products match. I’ll leave it to the readers to implement it.

But the other point we are missing here is that we haven’t verified that all our expectations have been met during the execution of the test. I can do it in the Teardown method as shown below

        [TestCleanup]
        public void Cleanup()
        {
            mocks.VerifyAll();
        }

calling VerifyAll on the mock repository will ensure that all the expectations have been met. Try running the test once again and you’ll most likely get the error as shown below

If you look carefully it says that ExpectationViolationException. Expected #1, Actual 0. And the expectation under consideration happens to be IProductRepository.GetAllProducts. What could be the reason for this. We have set the expectation but its not getting called.

Any guesses? This happens because of the threading. If you go back to the presenter and see which piece of code calls the productRepository.GetAllProducts method, it happens to be the one which runs on the background worker thread. In our case we are calling this code from the test runner. Because the call to DoWork method is asynchronous, our test runner does not wait for it to complete its work. The test runner continues with the next statement. And the teardown method gets invoked. In the teardown we are verifying that all the expectations have been met. It is possible that the background thread did not execute the service call in this duration and the expectation is not met. Hence we get this error while running this multi threaded code.

The simplest fix for this problem is to purposefully introduce some delay between the calling of the method on the presenter and the teardown method. This allows the background thread to complete its work and the callback method gets executed on the presenter as expected. After a short delay the teardown or cleanup is also executed.  You can modify the test as shown below and rerun to see that it fixes the exception.

        [TestMethod]
        public void GetAllProductsTest()
        {
            IList<Product> expectedProducts = new List<Product>
                {
                    new Product { ProductId = 1, Description = "Parle G Burboun Biscuit", Name = "Burboun Biscuit", UnitPrice = 15 },
                    new Product { ProductId = 2, Description = "Amul Ice Cream", Name = "Ice Cream", UnitPrice = 45 }
                };
 
            mockView.Expect(mv => mv.DisplayHourGlass).PropertyBehavior();
 
            mockRepository.Expect(mr => mr.GetAllProducts()).Return(expectedProducts);
 
            mockView.Expect(mv => mv.Products).PropertyBehavior();
 
            mocks.ReplayAll();
 
            presenter.GetAllProducts();
 
            Thread.Sleep(TimeSpan.FromMilliseconds(3));
        }

Conclusion

It is always difficult to unit test the multi threaded code. It took us almost a day to find out what was causing this problem. Because in the debug mode things would work fine and after removing the breakpoint we would encounter exception. After putting the test runner thread to sleep everything started working fine.

Please note that I have used a delay of 3 milliseconds. But if you have complex logic it might take you a little bit longer to execute them on the worker thread. You might need to change that value to a different one based on trail and error method.

As usual I have uploaded the complete source code to dropbox which you can download BackgroundWorkerUnitTest.

There might be a simpler way of unit testing a background worker related code. I would love to know about it. Until next time Happy programming :)

Continue Reading...

Refactoring : Clean Code using Resharper And Stylecop

Background

I have been using Resharper for past few months. Over a period of time I have become a big fan of this refactoring tool. Now I realize why many people can’t work without Resharper. The productivity improvements you get using this are really good. I would like to thank guys from ThoughtWorks who introduced us to this tool. It has really helped my team ever since we started using it. In this post I am going to demonstrate how we can make use of Resharper and StyleCop to improve the quality of code.

 

Advantages of using Resharper

Some of the benefits I see in using Resharper is its able to highlight issues with code like

• Highlight Unused Using statements.
• Find unused members of a class like Methods, Properties and Variables.
• Suggestions like converting a LINQ expression into a Lambda expression.
• While using String.Format method, Resharper can validate that the placeholders match the supplied values.
• While using logical constructs like If Else it can suggest better options like ?:
• When using multiple if statements it can Refactor into a Switch case loop.
• With methods which return values, and if we are using If Else construct to return two different values based on certain conditions then Resharper can suggest changes to redundant else conditions.
• Based on the usage of methods, Reshaper can suggest to change the methods to static methods or even the variables to readonly variable.

These are very minimal features of Resharper. I don’t want to reinvent the wheel in documenting them. You can visit the Resharper documentation to find out more details about the capabilities of this indispensible tool for any developer.

 

StyleCop for Resharper Addin

There are many plug-ins available for Resharper. You can find out about various plug-ins on the Resharper website. One plug-in which I have been using extensively is the StyleCop for Resharper. StyleCop is a Microsoft tool for validating the code for conformance to standards and best practices. It parses the source code in each file and identifies the lines of code which does not follow the various rules defined by Microsoft. These rules are similar to the ones defined in the FxCop. The main difference is FxCop analyzes the compiled code while StyleCop is used for analyzing the source code.

StyleCop can analyze source code for various rules which can be classified into

• Documentation Rules
• Layout Rules
• Maintainability Rules
• Naming Rules
• Ordering Rules
• Readability Rules
• Spacing Rules

You can see these in the screenshot below

To know about each rule which falls under these categories I would suggest you look at the StyleCop documentation. Installing StyleCop plugin for Resharper is pretty simple. You just download a zip file and run a batch file to register the plugin. Now you get the power of Resharper and StyleCop right inside Visual Studio IDE. Instead of wasting too much time talking about these tools lets get started with the action.

 

Code containing StyleCop And Resharper warning

Let me implement a class which ahs many of the commonly displayed warning and suggestions by both these tools in VS 2010 IDE.

public class ClassWithWarnings
{
    private string _name;
 
    public string Name
    {
        set { _name = value; }
 
        get { return _name; }
    }
 
    public ClassWithWarnings()
    {
 
    }
 
    private void DummyMethod()
    {
        Console.WriteLine("This method does nothing");
    }
 
    public ClassWithWarnings(string input)
    {
 
        Console.WriteLine(input);
 
    }
}

Because of the stylesheet of my blog you might not be able to see each and every warning highlighted in the above code. I would suggest you download the trial version of Resharper if you don’t have a copy and add StyleCop plugin to see how it works.

You can move the mouse pointer over each warning to find out more details about it. You can also use F12 to move to the next error or warning if you are using the shortcut keys mapped to the Resharper shortcuts. Once you hit F12 it will navigate to the next possible error or warning and you can hit Alt + Enter to get the available options to refactor the code. This is helpful if there are only very few errors and warning in the file. But it becomes quite boring to keep pressing the above combinations and resolve each error individually. And some of the warning are very trivial that they can be automatically sorted out like removing unused usings and ordering them in correctly.

Resharper and the StyleCop plugin comes to the rescue once again. It has an option called Cleanup code which we can access by right clicking on any class file in the Solution Explorer window

Selecting this option pops up another window which by default has two profiles created for us

• Full Cleanup



• Reformat code

You can select each profile to see what all settings it can apply for us to the code if we run code cleaup activity.

As can be seen from the above screen shot Full Cleanup profile has some settings set like

• Arrange ‘this’ qualifier



• Remove code redundancies



• use Auto-property, if possible

and many more options set up for us by default. Similarly Reformat code profile also has some predefined settings. If we are not happy with the default profile settings we can create our own profile by clicking on Edit Profile button on the bottom left hand corner. We cannot edit the settings for the two default profiles but we can create a new profile. This gives a much finer control over what settings we want to set for cleanup operation as shown below

We can set whatever options we want for each of the available settings and save the profile. Final thing which remains is to execute the cleanup action using a particular profile and your code gets reformatted as per the chosen settings.

 

Clean Code

After cleaning up the code my final output looks like

namespace CleanCodeUsingStyleCop
{
    using System;
 
    public class ClassWithWarningsRemoved
    {
        public ClassWithWarningsRemoved() { }
 
        public ClassWithWarningsRemoved(string input)
        {
            Console.WriteLine(input);
        }
 
        public string Name { get; set; }
 
        private void DummyMethod()
        {
            Console.WriteLine("This method does nothing");
        }
    }
}

The feature of code cleanup can be applied at either an individual file level or project level or even solution level. I haven’t tried the solution level option. Incase it doesn’t work at solution level please ignore it, but it certainly works at file and project levels :).

Conclusion

Resharper is helping me and my team a lot in refactoring the code. Some of the suggestions provided by Resharper are really helpful. I am not a really big fan of using mouse while developing. Resharper just enhances that experience to a great extent. I can do so much just using the keyboard that only in extreme cases do I need to use the mouse while working with Resharper.

The combination of Resharper and StyleCop is an icing on the cake because we use the code analysis features of both StyleCop as well as FxCop in our integrated build server which is running automated builds using Cruise Control.Net (CCNET). The warnings that we would find in the integrated build can be found right inside the Visual Studio IDE and can be fixed inplace using the technique mentioned in this post.

As usual I have uploaded the complete source code to dropbox and can be downloaded as CleanCodeUsingStyleCop.zip

until next time Happy Programming :)

Continue Reading...

Refactoring : Extract Method Refactoring Technique

Background

 

Few weeks back I was giving a presentation to my team on some of the best practices and  guidelines on design and Architecture of software systems. One of the topic for discussion was related to Refactoring. One of the team member requested me to show hands on refactoring techniques. I have read martin Fowlers book called Refactoring – Improving the Design of Existing Code and also gone through the online catalog that is maintained by him. I also referred the refactoring catalog available at industriallogic.com.

Refactoring is a simple technique which changes the internal behavior of code structure without affecting its functionality. One of the important prerequisite for refactoring is a good set of unit tests. This is my personal opinion based on my experiences with TDD. This doesn’t mean that you cannot apply refactoring techniques to legacy code which might not have been unit tested. If you have a set of unit tests it only helps to prove that the functionality is working the same way before refactoring the piece of code.

In this post I would like to demonstrate one of the most commonly used refactoring which is extracting a method. This method of refactoring is used when code is duplicated across two methods and it can be centralized in a single method. We can parameterize some of the inputs to the method if there is any need to do so.

Example without refactoring

Lets take a small example where we want to start a new process programmatically. Lets assume we want to start Notepad.exe and Microsoft Word exe using C# code. Here is how I would do it.

    public class ClassWithoutRefactoring
    {
        public void StartNotepadProcess()
        {
            Process notepadProcess = new Process
                {
                    StartInfo =
                        {
                            FileName = "Notepad.exe",
                            WindowStyle = ProcessWindowStyle.Maximized,
                        }
                };
 
            notepadProcess.Start();
        }
 
        public void StartMicrosoftWordProcess()
        {
            Process winwordProcess = new Process
            {
                StartInfo =
                {
                    FileName = "Winword.exe",
                    WindowStyle = ProcessWindowStyle.Maximized,

                }
            };
 
            winwordProcess.Start();
        }
    }

I have named the class as ClassWithoutRefactoring and defined two methods on it

• StartNotepadProcess



• StartMicrosoftWordProcess

I am using the Process class from System.Diagnostics namespace to create these processes. I have made use of the object initializer syntax to set the required properties for StartInfo property of process class. I would like to start the processes in Maximized mode which I have set using appropriate property. Finally I call the Start method to actually invoke the process.

To invoke these methods I create an instance of this class in the Main method and invoke the two methods as

            ClassWithoutRefactoring classWithoutRefactoring = new ClassWithoutRefactoring();
 
            classWithoutRefactoring.StartNotepadProcess();
 
            classWithoutRefactoring.StartMicrosoftWordProcess();

If we build and run the solution now we should see both the Notepad window as well as a Microsoft Word window open up in maximized mode. I am assuming that you have any one of the version of Microsoft Word installed on your machine. If not you can replace the Winword.exe with any exe name which you can directly invoke from run prompt like cmd.exe.

If you notice carefully, the two methods are exactly the same except for the name of the executable file that they are opening. Now assume that I want to start the two processes in normal window rather than maximized window.

For such a small method definition its not a big problem to make changes to any of these methods .Seldom in real life applications you’ll find methods which are very small and are used in only one or two places. It will be a problem to go and change in each and every place where similar code is repeated.

To avoid such situation we can restructure the implementation such that with minimal changes we can adapt to the changes.

Example with Extract Method Refactoring Technique

    public class ClassWithRefactoring
    {
        public void StartNotepadProcess()
        {
            StartProcess("Notepad.exe");
        }
 
        public void StartMicrosoftWordProcess()
        {
            StartProcess("Winword.exe");
        }
 
        private void StartProcess(string processName)
        {
            Process notepadProcess = new Process
                {
                    StartInfo =
                        {
                            FileName = processName,
                            WindowStyle = ProcessWindowStyle.Normal,
                            CreateNoWindow = true
                        }
                };
 
            notepadProcess.Start();
        }
    }

I could have modified the same methods but for the same of clarity I created a class called ClassWithRefactoring and implemented the methods as shown above. As can be seen I did not change the signatures of the public methods. They remain the same. But I extracted the common piece of code into a private method. I have also added the process name as the parameter to this method. Now I can make the change to open the process in Normal window. I need to make this change in only one place and it gets reflected in both the places.

This was a very simple example. You can also use extract method if a single method is doing too much of work. We can split a big method definition into smaller logical chunks to improve readability and maintainability of the code.

In fact the above method can be made even more easier by extracting some of the input parameters that we are passing to the StartInfo object. I would leave it to the readers to implement it themselves.

 

Conclusion

Refactoring helps us to improve the quality of code. It makes the code more readable, more understandable and most importantly more maintainable. If you have the tooling support it becomes even more easier. I have used the refactoring capabilities of both Visual Studio as well as Resharper. I prefer Resharper because it offers far more features than Visual Studio. May be that’s another post in itself for some other time.

I have uploaded the complete source code to dropbox which you can download and experiment ExtractMethodRefactoring.zip

Unit next time Happy Programming :)

Continue Reading...