My team has recently decided to adopt the CQRS pattern similar to the one used in the famous Microsoft EShopsOnContainers repository. In this solution, the queries are made in Query classes using Dapper and Commands are done in command handlers using EF Core.
The queries that I am doing on my domain are quite complex and require a lot of filtering of the data. Without using LINQ. I am relying on using purely dynamic SQL statements (not my strong point) to query my domain model with multiple
WHERE clauses etc.
This approach would make it extremely hard to unit test my queries.
Here are the two approaches I am considering:
string sql = @" SELECT * FROM [dbo].[vw_LogisticsManagement] WHERE ([Status] NOT IN @WorkOrderStatuses ) AND ([PickupDate] IS NOT NULL) AND (0 = (DATEDIFF (day, [PickupDate], getdate())) AND (0 != (DATEDIFF (day, [CheckInDate], getdate()))) ORDER BY [PickupDate]"; //Add parameters etc... var result = await connection.QueryAsync<viewModel>(sql, parameters);
- Very Performant
- Difficult to test
- Difficult to write (for someone without much SQL experience)
string sql = @" SELECT * FROM [dbo].[vw_LogisticsManagement] var result = await connection.QueryAsync<viewModel>(sql, parameters); var wos = result.Where(wo => wo.PickupDate.HasValue && wo.PickupDate.Value.Date == DateTime.Now.Date && wo.CheckInDate.Value.Date != DateTime.Now.Date && wo.Status != Status.Shipped && wo.Status != Status.PartiallyShipped) .OrderBy(wo => wo.PickupDate).ToList();
- Very easy to write (someone with a lot of LINQ experience)
- Easy to test filters: I could mock the result that Dapper returns (no need for in memory db) and then have a predefined list that I could apply the filters using LINQ and have an expected result
- Not as performance. Enough to even matter?
So basically I am trying to weigh out the pros and cons of each, in my opinion testability outweighs the potential performance boost in this case, however not everyone from my team would be likely to agree.
Ultimately approach 1, with proper testing in place would be ideal as many of the queries I am dealing with are far more complicated.