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:

Approach 1:

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)

Approach 2

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.

  • Depending on how many records are in [dbo].[vw_LogisticsManagement], your most expensive performance cost is probably the amount of network traffic an unfiltered query (Approach 2) is going to cause. – Robert Harvey May 18 '19 at 3:17
  • 1
    That SELECT * is also some potentially low-hanging fruit that may be costing you performance in both approaches. – Robert Harvey May 18 '19 at 3:18
  • In any case, I'm glad to see someone else out there who can clearly see the advantages of using a micro-orm like Dapper. Not sure what, if anything, this question has to do with CQRS, though. – Robert Harvey May 18 '19 at 3:20
  • 1
    All that said, I use both approaches, depending on the specific situation. If I need to keep a small dataset in memory and apply different filters to it, I'll use the second approach. If I need retrieve a one-time, highly-focused data set as quickly as possible, I'll use the first approach. – Robert Harvey May 18 '19 at 3:20
  • 2
    Why exactly don't you want to use EF on the Query side? Wouldn't that solve your problem, I mean, it's probably performant enough and allows you to query directly into the database with LINQ. – Rik D May 18 '19 at 7:47

Browse other questions tagged or ask your own question.