Post
Ask Question

Repository And UnitOfWork Pattern - Part Three

In the previous article “Repository and UnitOfWork Pattern - Part Two”, we implemented the following concepts.
  • Implementation of Repository pattern example using UnitOfWork Pattern. UnitOfWork (UOW), is the common pattern that is used to resolve data concurrency issues which arise when each repository implements and maintains separate Data context object. The UnitOfWork (UOW) pattern implementation manages in-memory database operations on entities as one transaction. So, if one of the operations is failing then entire database operations will be rolled back.

  • Designed a database model as shown below. This database contains two tables, “Order” and “OrderItem”. Table “Order” contains column “OrderId” of type varchar and defined as primary key and non-identity column ( Non-identity column specifies that value should be supplied from the application/user). Create a foreign key relationship between primary key column “OrderId” of table “Order” and column “OrderId” of table “OrderItem” which means value of “OrderItem.OrderId” should match the value of “Order.OrderId
Repository And UnitOfWork Pattern
  • Always generating the new “Order.OrderId” using Guid.NewGuid().ToString() and passing the same value to OrderItem.OrderId
  • Making the entries to both tables “Order” and “OrderItem” in a single transaction. In case, any transaction fails then we rollback the entries from both the tables
Now a question arises if the “Order.OrderId” is set to Identity Column (Identity column of a table is a column whose value increases automatically. The value in an identity column is created by the database server) in database then how will auto generated value of “Order.OrderId” be retrieved and passed to column “OrderItem.OrderId”, and still have the execution of these statements happen within a single transaction.
 
Now, let’s have a look at the implementation of Repository pattern example using UnitOfWork Pattern and Primary key (identity column).
 

Database Model Changes

  •  Change the type of “Order.OrderId” from varchar to int
  • Add column “OrderItem.ItemId” of type int
  •  Add unique constraint on column “OrderItem.OrderId” and “OrderItem.ItemId” to ensure that order must contain one entry of each unique item
Repository And UnitOfWork Pattern
 
Database Script 
  1. -- --------------------------------------------------  
  2. -- Entity Designer DDL Script for SQL Server 2005, 2008, 2012 and Azure  
  3. -- --------------------------------------------------  
  4. -- Date Created: 08/24/2019 19:18:08  
  5. -- Generated from EDMX file: C:\Personal Data\Training\RepositoryPatternExample\RepositoryPatternExample\DataModel\OrderManagementModel.edmx  
  6. -- --------------------------------------------------  
  7.   
  8. SET QUOTED_IDENTIFIER OFF;  
  9. GO  
  10. USE [OrderManagement];  
  11. GO  
  12. IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');  
  13. GO  
  14.   
  15. -- --------------------------------------------------  
  16. -- Dropping and create Table [dbo].[Orders]  
  17. -- --------------------------------------------------  
  18.   
  19. /****** Object:  Table [dbo].[Orders]    Script Date: 08/24/2019 19:20:39 ******/  
  20. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Orders]') AND type in (N'U'))  
  21. DROP TABLE [dbo].[Orders]  
  22. GO  
  23.   
  24. /****** Object:  Table [dbo].[Orders]    Script Date: 08/24/2019 19:20:39 ******/  
  25. SET ANSI_NULLS ON  
  26. GO  
  27.   
  28. SET QUOTED_IDENTIFIER ON  
  29. GO  
  30.   
  31. CREATE TABLE [dbo].[Orders](  
  32.     [OrderId] [int] IDENTITY(1,1) NOT NULL,  
  33.     [OrderDate] [datetime] NOT NULL,  
  34.     [OrderStatus] [nvarchar](50) NOT NULL,  
  35.  CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED   
  36. (  
  37.     [OrderId] ASC  
  38. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  
  39. ) ON [PRIMARY]  
  40.   
  41. GO  
  42.   
  43. SET IDENTITY_INSERT [dbo].[Orders] ON   
  44.   
  45. GO  
  46. -- --------------------------------------------------  
  47. -- Dropping existing FOREIGN KEY constraints  
  48. -- --------------------------------------------------  
  49.   
  50. IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_OrderItems_Orders]') AND parent_object_id = OBJECT_ID(N'[dbo].[OrderItems]'))  
  51. ALTER TABLE [dbo].[OrderItems] DROP CONSTRAINT [FK_OrderItems_Orders]  
  52. GO  
  53.   
  54. -- --------------------------------------------------  
  55. -- Dropping and create Table [dbo].[OrderItems]  
  56. -- --------------------------------------------------  
  57.   
  58. /****** Object:  Table [dbo].[OrderItems]    Script Date: 08/24/2019 19:22:18 ******/  
  59. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OrderItems]') AND type in (N'U'))  
  60. DROP TABLE [dbo].[OrderItems]  
  61. GO  
  62.   
  63.   
  64. CREATE TABLE [dbo].[OrderItems](  
  65.     [OrderItemId] [varchar](50) NOT NULL,  
  66.     [OrderId] [int] NOT NULL,  
  67.     [ItemName] [varchar](50) NOT NULL,  
  68.     [Quantity] [int] NOT NULL,  
  69.     [ItemId] [int] NOT NULL,  
  70.  CONSTRAINT [PK_OrderItems] PRIMARY KEY CLUSTERED   
  71. (  
  72.     [OrderItemId] ASC  
  73. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  
  74. ) ON [PRIMARY]  
  75.   
  76. GO  
  77.   
  78. SET ANSI_PADDING OFF  
  79. GO  
  80.   
  81. -- --------------------------------------------------  
  82. -- Create FOREIGN KEY constraints  
  83. -- --------------------------------------------------  
  84. ALTER TABLE [dbo].[OrderItems]  WITH CHECK ADD  CONSTRAINT [FK_OrderItems_Orders] FOREIGN KEY([OrderId])  
  85. REFERENCES [dbo].[Orders] ([OrderId])  
  86. GO  
  87.   
  88. ALTER TABLE [dbo].[OrderItems] CHECK CONSTRAINT [FK_OrderItems_Orders]  
  89. GO  
  90.   
  91.   
  92. USE [OrderManagement]  
  93. GO  
  94.   
  95. /****** Object:  Index [IX_UQ_OrderItems]    Script Date: 08/24/2019 19:32:47 ******/  
  96. IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[OrderItems]') AND name = N'IX_UQ_OrderItems')  
  97. DROP INDEX [IX_UQ_OrderItems] ON [dbo].[OrderItems] WITH ( ONLINE = OFF )  
  98. GO  
  99.   
  100. USE [OrderManagement]  
  101. GO  
  102.   
  103. /****** Object:  Index [IX_UQ_OrderItems]    Script Date: 08/24/2019 19:32:47 ******/  
  104. CREATE NONCLUSTERED INDEX [IX_UQ_OrderItems] ON [dbo].[OrderItems]   
  105. (  
  106.     [OrderId] ASC,  
  107.     [ItemId] ASC  
  108. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  
  109. GO  
  110.   
  111.   
  112. -- --------------------------------------------------  
  113. -- Script has ended  
  114. -- --------------------------------------------------  

BaseRepository class

 
Please note that no changes are required in the BaseRepository class. To get the auto generated value of identity we do not need to save changes in the database context after every add operation. We will save changes once to save all the transactions together or collectively.
 
Please refer to Part Two for detailed source code.
 

UnitOfWork Class

 
Please note that no changes are required in the UnitOfWork class. Please refer to Part Two for detailed source code.
 
Program
 
This function PlaceOrderToBuyUniqueItems() is used to place an order to buy Jeans and Shirts (unique items) . Please do not initialize the value of order.orderId while initializing an instance of Order class because the database will automatically assign the value as soon as UnitOfWork.SaveChanges() is called and generated “order.orderId” value will automatically pass to “OrderItem.OrderId” for saving in the database. 
  1. /// <summary>  
  2.         /// This method will add order and order will unique items (Item Id =3 and Item id = 4) within the order   
  3.         /// </summary>  
  4.         private static void PlaceOrderToBuyUniqueItems()  
  5.         {  
  6.             try  
  7.             {  
  8.                 Console.WriteLine("Place order for items (Jeans (item id = 3) and Shirt (item id = 4))");  
  9.                 //Note that OrderId  is not initialize - Identity column   
  10.                 Order order = new Order { OrderDate = DateTime.Now, OrderStatus = "In Process" };  
  11.   
  12.                 using (var unitOfWork = new UnitOfWork())  
  13.                 {  
  14.                     unitOfWork.OrderRepository.Add(order);  
  15.   
  16.                     order.OrderItems = new List<OrderItem>  
  17.                     {  
  18.                         new OrderItem { OrderId = order.OrderId, ItemName = "Shirt", OrderItemId = Guid.NewGuid().ToString(), Quantity = 1, ItemId = 4 },  
  19.                         new OrderItem { OrderId = order.OrderId, ItemName = "Jeans", OrderItemId = Guid.NewGuid().ToString(), Quantity = 4, ItemId = 3 }  
  20.                     };  
  21.   
  22.                     foreach (var orderItem in order.OrderItems)  
  23.                     {  
  24.                         var orderItemAddMessage = $"Add Order Item { orderItem.ItemName} added with unique identifier { orderItem.OrderItemId}";  
  25.                         Console.WriteLine(orderItemAddMessage.PadLeft(5 + orderItemAddMessage.Length));  
  26.                     }  
  27.   
  28.                     Console.WriteLine("========================================================================");  
  29.                     unitOfWork.OrderItemRepository.AddRange(order.OrderItems);  
  30.                     unitOfWork.SaveChanges();  
  31.                     Console.WriteLine("order placed successfully");  
  32.                 }  
  33.             }  
  34.             catch (Exception e)  
  35.             {  
  36.                 Console.WriteLine("order failed");  
  37.                 var message = GetExceptionMessage(e);  
  38.                 Console.WriteLine(message);  
  39.             }  
Now execute the SELECT statement in the database if any data already exists,
 
Repository And UnitOfWork Pattern
 
Write a program to execute method PlaceOrderToBuyUniqueItems()
  1. static void Main(string[] args)  
  2.         {  
  3.             Console.WriteLine("========================================================================");  
  4.             PlaceOrderToBuyUniqueItems();  
  5.             Console.WriteLine("========================================================================");  
  6.             Console.ReadLine();          
  7. }  
Repository And UnitOfWork Pattern
 
Again, execute the SELECT statement in the database if is any data got saved
 
Repository And UnitOfWork Pattern
 
Again, execute method PlaceOrderToBuyUniqueItems()and SELECT statement to validate if data is inserted for second order and order contains item Jeans and Shirt,
 
Repository And UnitOfWork Pattern
 
This function PlaceOrderToBuySameItems() is used to place an order to buy two Shirts (same item) . Please do not initialize the value of order.orderId while initializing an instance of Order class because database will automatically assign the value as soon as UnitOfWork.SaveChanges() is called and the generated “order.orderId” value will automatically pass to “OrderItem.OrderId” for saving in the database but this will violate the unique index constraint and the complete transaction will be rolled back. 
  1. private static void PlaceOrderToBuySameItems()  
  2.         {  
  3.             try  
  4.             {  
  5.                 Console.WriteLine("Place order for items (Jeans (item id = 3) and Shirt (item id = 4))");  
  6.                 //Note that OrderId  is not initialize - Identity column   
  7.                 Order order = new Order { OrderDate = DateTime.Now, OrderStatus = "In Process" };  
  8.   
  9.                 using (var unitOfWork = new UnitOfWork())  
  10.                 {  
  11.                     unitOfWork.OrderRepository.Add(order);  
  12.   
  13.                     order.OrderItems = new List<OrderItem>  
  14.                     {  
  15.                         new OrderItem { OrderId = order.OrderId, ItemName = "Shirt", OrderItemId = Guid.NewGuid().ToString(), Quantity = 1, ItemId = 4 },  
  16.                         new OrderItem { OrderId = order.OrderId, ItemName = "Shirt", OrderItemId = Guid.NewGuid().ToString(), Quantity = 4, ItemId = 4 }  
  17.                     };  
  18.   
  19.                     foreach (var orderItem in order.OrderItems)  
  20.                     {  
  21.                         var orderItemAddMessage = $"Add Order Item { orderItem.ItemName} added with unique identifier { orderItem.OrderItemId}";  
  22.                         Console.WriteLine(orderItemAddMessage.PadLeft(5 + orderItemAddMessage.Length));  
  23.                     }  
  24.   
  25.                     Console.WriteLine("========================================================================");  
  26.                     unitOfWork.OrderItemRepository.AddRange(order.OrderItems);  
  27.                     unitOfWork.SaveChanges();  
  28.                     Console.WriteLine("order placed successfully");  
  29.                 }  
  30.             }  
  31.             catch (Exception e)  
  32.             {  
  33.                 Console.WriteLine("order failed");  
  34.                 var message = GetExceptionMessage(e);  
  35.                 Console.WriteLine(message);  
  36.             }  
  37.         }  
Write a program to execute method PlaceOrderToBuyUniqueItems() and observe the output
  1. static void Main(string[] args)  
  2.         {  
  3.             Console.WriteLine("========================================================================");  
  4.             PlaceOrderToBuySameItems();  
  5.             Console.WriteLine("========================================================================");  
  6.             Console.ReadLine();  
  7.         }  
 
Again, execute the SELECT statement to see if there is any change in the database – with no doubt, there should not be any impact on the data stored in the database,
 
 

Conclusion

 
As we have seen that there is no need to call SaveChanges() on database context after every add operation. Identity column value will be first generated and passed to the foreign key columns in a single transaction.


Similar Articles