Click here to Skip to main content
15,867,453 members
Articles / All Topics
Technical Blog

Using Entity Framework and the Store Procedure Framework To Achieve CQRS - Part #1

Rate me:
Please Sign up or sign in to vote.
4.91/5 (11 votes)
11 Aug 2016CPOL5 min read 13.9K   11   2
Using Entity Framework and the Store Procedure Framework To Achieve CQRS - Part#1

CQRS stands for Command Query Responsibility Segregation. At its heart is the notion that you can use a different model to update information than the model you use to read information.

-- Martin Fowler

This blog post is going to show you how you can achieve this separation of data concerns using Entity Framework for your command stack and the Stored Procedure Framework for your query stack.

Background

We are probably all aware of the benefits that using Entity Framework brings when using for data access in a .NET application, for example type safety, object representation, change tracking and the unit of work pattern providing transactional safety. We have probably all worked on projects where we have used EF for querying, but this can all be a bit "heavy" when all you really need is a quick query to show a list of objects, with maybe only a subset of the objects properties. We may also have accidentally coded in an N+1 error and hit the database thousands of times for what really should have been a simple query.

So is there an alternative? Of course there is, there are many alternatives. What I'd like to demonstrate today is one of these alternatives and that is to separate the writes from the reads, the commands from the queries, and show a basic implementation of the CQRS using Entity Framework within the Command Stack and the Stored Procedure Framework in the Query Stack.

Below is a high level diagram of how the architecture will look. Starting at the user interface, all interaction will be through a services layer. This could easily be WebApi, MVC controllers, a stand Windows service or any other endpoint that you choose. In our case, we will use a service DLL and our "UI" will actually be a "Integration Test" project. The service will have two stacks to interact with for data access.

High Level CQRS Diagram

The first is the Query Stack which will be used for pulling data out of the data store, which in our case will be a SQL Server database. The query stack will house the ReadModel which will leverage the Stored Procedure Framework (known as SprocF in this blog post) to return light weight DTOs to the service.

The second stack is the Command Stack which will contain any Domain objects or Domain logic that should be applied to the objects and the Domain Objects will be contained in a repository. Database access will be leveraged though Entity Framework (known as EF in this blog post). Lastly, the Command Stack will contain the commands which the service will use to manipulate the data within the database via the Domain and EF.

Assumptions

This blog post assumes the reader is competent in C#, and a number of common patterns and practices.

Solution Setup

Before we can start to write any code, we will set up the solution to organise the code, so let's set up some solution folders to organize the code. So we will start off with folders for Client, Services, Query Stack, Command Stack and lastly Database.

|
+--Code
|  +-- 01.Client
|  +-- 02.Services
|  +-- 03.QueryStack
|  +-- 04.CommandStack
|  +-- 05.Database
|

Client

For the "client" in a further effort to keep things simple, I am just going to create a Unit Test Project and use that as an integration test harness for the Service project.

Services

For the services, I will create a C# Class Library called "Blogs.EfAndSprocfForCqrs.Services".

Query Stack

In the Query Stack, I will create a C# Class Library read model called "Blogs.EfAndSprocfForCqrs.ReadModel".

Command Stack

In the Command Stack, I will create a C# Class Library read model called "Blogs.EfAndSprocfForCqrs.DomainModel". Normally, I might separate the domain model, domain services and repository out into separate DLLs. I would also likely use interfaces to reduce coupling and promote testability. But as that is not what this post is about, I'll try and keep it a little more simple.

Database

We will create a database project "Blogs.EfAndSprocfForCqrs.Database" to hold our schema and seeding data.

Full Solution Set-up

So the full solution set-up can be seen in the image below:

Solution Set-up

Data Setup

So now let's take a look at the data we want to set up. We are going to be concentrating on three entities; "Order", "Product", "Customer" with a relationship that allows each single "Customer" to have many "Orders" and each single "Order" to have many "Products". We will need some addition linking tables like the "ProductOrdered" table which identifies a product which is on an order.

Order Database Entities

Using the "Script.PostDeployment.sql" below, we will seed the tables with the data that follows:

SQL
:r "..\SeedData\dbo.Product.data.sql"
:r "..\SeedData\dbo.Customer.data.sql"
:r "..\SeedData\dbo.Order.data.sql"
:r "..\SeedData\dbo.ProductOrdered.data.sql"

dbo.Product

SQL
    SET IDENTITY_INSERT [dbo].[Product] ON
    INSERT INTO [dbo].[Product] ([Id], [Key], [Name], [Description], [CreatedTimestamp]) VALUES (2, N'compressor-mac-2hp', N'2HP MAC Tools Compressor', N'Super dandy 2HP  portable compressor by MAC Tools ', N'2015-11-23 16:00:23')
    INSERT INTO [dbo].[Product] ([Id], [Key], [Name], [Description], [CreatedTimestamp]) VALUES (5, N'snapon-ratchet-ring-metric-10-21', N'Snap-On Metric Ratchet Ring Set 10-21mm', N'Snap-On Metric Ratchet set 10mm - 21mm', N'2015-11-24 00:00:00')
    INSERT INTO [dbo].[Product] ([Id], [Key], [Name], [Description], [CreatedTimestamp]) VALUES (7, N'snap-on-ratchet-screwdriver-red', N'Snap-On Ratchet Screwdriver in Red', N'Snap-On Ratchet Screwdriver in Red with six bits included', N'2015-11-24 01:01:24')
    INSERT INTO [dbo].[Product] ([Id], [Key], [Name], [Description], [CreatedTimestamp]) VALUES (8, N'usmash-4lb-hammer', N'U-Smash 4lb lump hammer', N'U-Smash 4lb lump hammer', N'2015-11-25 16:00:35')
    INSERT INTO [dbo].[Product] ([Id], [Key], [Name], [Description], [CreatedTimestamp]) VALUES (9, N'pry-master-prybar', N'Pri-Master 24" Pry-Bar', N'Pri-Master 24" Pry-Bar with plastic ergonmoic handle', N'2015-11-26 17:00:02')
    INSERT INTO [dbo].[Product] ([Id], [Key], [Name], [Description], [CreatedTimestamp]) VALUES (10, N'snap-on-6-inch-slip-plyers', N'Snap-On 6 Inch Slip Plyers', N'Snap-On 6 Inch Slip Plyers, ideal for removing brake spring clips', N'2015-12-02 09:33:22')
    SET IDENTITY_INSERT [dbo].[Product] OFF

dbo.Customer

SQL
    INSERT INTO [dbo].[Customer] ([Id], [Name], [RegisteredDate], [Active]) VALUES (N'17e3a22e-07e5-4ab2-8e62-1b15f9916909', N'Mike Finnegan', N'1961-01-19', 1)
    INSERT INTO [dbo].[Customer] ([Id], [Name], [RegisteredDate], [Active]) VALUES (N'baded780-bbfa-4a61-a22a-7746d87be19c', N'David Frieburger', N'1969-09-26', 1)

dbo.Order

SQL
    INSERT INTO [dbo].[Order] ([Id], [CustomerId], [CustomerOrderNumber], [CreatedOnTimeStamp]) VALUES (N'4a61a22a-bade-d780-bbfa-be19c7746d87',    N'17e3a22e-07e5-4ab2-8e62-1b15f9916909', N'0000001', N'2016-01-02 11:08:34')

dbo.ProductOrdered

SQL
    SET IDENTITY_INSERT [dbo].[ProductOrdered] ON
    INSERT INTO [dbo].[ProductOrdered] ([Id], [OrderId], [ProductId], [PurchasePrice]) VALUES (1, N'4a61a22a-bade-d780-bbfa-be19c7746d87', 5, CAST(102.0000 AS Money))
    INSERT INTO [dbo].[ProductOrdered] ([Id], [OrderId], [ProductId], [PurchasePrice]) VALUES (2, N'4a61a22a-bade-d780-bbfa-be19c7746d87', 7, CAST(75.0000 AS Money))
    INSERT INTO [dbo].[ProductOrdered] ([Id], [OrderId], [ProductId], [PurchasePrice]) VALUES (3, N'4a61a22a-bade-d780-bbfa-be19c7746d87', 9, CAST(25.0000 AS Money))
    SET IDENTITY_INSERT [dbo].[ProductOrdered] OFF

Validate the Data is Correct

While we are here, let's just validate that the data we have seeded relates correctly. Using the "[dbo].[GetOrderDetailsForOrderId]" Stored procedure, let's just check we have an "Order", a "Customer", and three "Products" on the order. (We will use this stored procedure later, so it is worth creating now as its value is two-fold.

[dbo].[GetOrderDetailsForOrderId]

If you want to see the result, just select and run all of the code from the "DECLARE" within the comment block until the just before the END keyword.

SQL
CREATE PROCEDURE [dbo].[GetOrderDetailsForOrderId]
(
    @OrderId uniqueidentifier
)
AS
BEGIN
    /*
    DECLARE @OrderId uniqueidentifier = '4A61A22A-BADE-D780-BBFA-BE19C7746D87';
    -- */

    /* Order */
    SELECT      [Id]
    ,           [CustomerId]
    ,           [CustomerOrderNumber]
    ,           [CreatedOnTimeStamp]
    FROM        [dbo].[Order]
    WHERE       [Id] = @OrderId;

    /* Customer who ordered */
    SELECT      [customer].[Id]         [Id]
    ,           [Name]
    ,           [RegisteredDate]
    ,           [Active]
    FROM        [dbo].[Customer]        [customer]
    INNER JOIN  [dbo].[Order]           [order]
            ON  [order].[CustomerId]    = [customer].[Id]
    WHERE       [order].[Id]            = @OrderId;

    /* Products on the order */
    SELECT      [ordered].[Id]
    ,           [ordered].[ProductId]
    ,           [product].[Key]
    ,           [product].[Name]
    ,           [product].[Description]
    ,           [ordered].[PurchasePrice]
    FROM        [dbo].[ProductOrdered]  [ordered]
    INNER JOIN  [dbo].[Product]         [product]
            ON  [product].[Id]          = [ordered].[ProductId]
    WHERE       [ordered].[OrderId]     = @OrderId;
END

We should see three RecordSets, like below:

Id CustomerId CustomerOrderNumber CreatedOnTimeStamp
4A61A22A-BADE-D780-BBFA-BE19C7746D87 17E3A22E-07E5-4AB2-8E62-1B15F9916909 0000001 2016-01-02 11:08:34.000

 

Id Name RegisteredDate Active
17E3A22E-07E5-4AB2-8E62-1B15F9916909 Mike Finnegan 1961-01-19 1

 

Id ProductId Key Name Description PurchasePrice
1 5 snapon-ratchet-ring-metric-10-21 Snap-On Metric Ratchet Ring Set 10-21mm Snap-On Metric Ratchet set 10mm - 21mm 102.00
2 7 snap-on-ratchet-screwdriver-red Snap-On Ratchet Screwdriver in Red Snap-On Ratchet Screwdriver in Red with six bits included 75.00
3 9 pry-master-prybar Pri-Master 24" Pry-Bar 24" Pry-Bar with plastic ergonmoic handle 25.00

 

 

All going well, we shall call this a day and in the next article, move on to the ReadModel and pull this data from the database using the Stored Procedure framework.

Part 2

 

This post and all corresponding code and data can be found on my GitHub project:

Disclaimer

I am the author of the Stored Procedure Framework.

License

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


Written By
Software Developer
United Kingdom United Kingdom
Duane has worked in a commercial software development environment for 9 years, with all but three for a global fashion retailer.

He is proficient in ASP.Net, MVC, C#, HTML, CSS, JavaScript, SQL Server TSQL.

Comments and Discussions

 
QuestionSomething has gone awry with the code samples? Pin
Duncan Edwards Jones3-Aug-16 3:07
professionalDuncan Edwards Jones3-Aug-16 3:07 
AnswerRe: Something has gone awry with the code samples? Pin
dibley19733-Aug-16 18:12
dibley19733-Aug-16 18:12 

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.