This article reconsiders the problem (well, a solution) of filtering in software being built for a book publishing company. Leaning on lessons from Domain-Driven Design (DDD), the company's team (including devs, domain experts, and more) embarked on the initial discovery phase and strategic design. They then selected one particular bounded context that came out of strategic design - the contracting bounded context - to focus on for the initial tactical design phase. That particular bounded context presented some interesting complexity because of the many iterations a contract may go through as well as the possibility of co-authors on a book. The added complexity makes the bounded context a great candidate for applying DDD patterns.
The outcome of the tactical design is a contract aggregate (see Figure 1) defined by its entities, value objects, invariants and other business rules. The contract entity itself is the root of the aggregate. A contract has one or more versions including a version with default values and default specifications for every new contract. Each version includes a set of specifications that are encapsulated in a value object. And each version also has one or more authors.
The author class is also a value object that leans on yet another value object: PersonName. Figure 2 shows the properties of each of the aggregate classes in Visual Studio's class designer. There's a lot of logic involved to create default versions, create revisions, finalize contracts, etc. If you're curious about that logic, it's all detailed as part of my Pluralsight course: EF Core 6 and Domain-Driven Design. If you're not familiar with DDD, you may want to start with the Domain-Driven Design Fundamentals course also on Pluralsight that I co-authored with Steve Smith.
The focus of this article is on enabling users to find pre-existing contracts by filtering. That seems like a problem with a well-known and obvious solution. But is it really?
As the developers incorporate the contract aggregate further into their solution, it's important that they make it easy for users to find a contract to work on, whether they want to make tweaks, create a new revision, or just look at some details. Users might want to search by author names, contract status, or one of the relevant dates, such as when the contract was first initiated or when an author needs to respond to a version during negotiations.
Although the lessons of this article apply to any stack, I'll be using .NET Core and EF Core with a SQL Server database to explain the solution. The demo code is on my GitHub account at https://github.com/julielerman/FilteringwithEFCoreandDDD.
Factors that Lead to Filtering Problems
In the solution, the EF Core DbContext designed to support the aggregate takes care of ensuring that the aggregate is mapped correctly to the relational database. Additionally, in order to protect the aggregate, this DbContext only exposes the aggregate root - the contract entity - for querying and updates. It's possible to use the DbContext.Set<> method directly, but you should design your data access logic such that you are not circumventing this guard rail other than, perhaps, in integration tests as needed.
For example, many of the details that would be helpful for selecting a contract such as author name or acceptance deadline are exposed throughout the aggregate in different classes. Yet accessing them to filter queries is overly complicated. It can be done but requires a lot of LINQ trickery and expertise. And that's just to filter the results. The results themselves need to be a string composed of appropriate highlights sufficient for the user to select the exact one that they're seeking. Listing 1 shows an example of the most efficient solution I could come up with to find a list of contracts based on the last name of any of the authors. Remember that the authors could vary from one version of the contract to another and, in this search, the domain experts specified that this filter should only find contracts with that author in the current version.
Listing 1: Using LINQ to find contracts against the aggregate is hard
public List<KeyAndDescription>
GetContractPickListForAuthorLastName(string lastname)
{
var currentVersions = _context.Contracts
.Where (c => c.Versions
.Any(v =>v.Authors
.Any(a =>a.Name.LastName.StartsWith(lastname))
)
).Select(c => new
{c.DateInitiated,
Current = c.Versions.Where(
v => v.Id == c.CurrentVersionId).FirstOrDefault()
}).ToList();
var picklist = currentVersions.Select(v =>
new SearchResult(v.Current.ContractId,
$"{v.DateInitiated.ToShortDateString()},
{v.Current.WorkingTitle},
{v.Current.Authors.FirstOrDefault().Name.FullName}"),
v.ContractNumber).ToList();
return picklist.OrderBy(pl => pl.Description).ToList();
}
As you can see from that one method in Listing 1, it's possible to build up layers of queries via the aggregate and its DbContext to support this. But for me, it was quite a hair-puller, especially to filter by name as I needed to drill into contract version, then each of its authors, and then the Name property (remember it's a value object), and even further in to the LastName property. In the end, this and the other queries I created worked well, although not perfectly, because I was only getting back the name of one of the authors. It was at the point when I was trying to determine how to also look for any co-authors that I finally decided the effort was just too much.
The aggregate was intentionally designed to represent a contract and its business logic. It exists to easily create and modify a contract while maintaining its invariants. However, that design has made the problem of search somewhat complicated. Even if you were to use specifications to define, combine, and execute queries for you (using the brilliant EF Specification from Steve Smith at https://github.com/ardalis/specification), the tiered nature of this aggregate makes defining and composing the various specifications on which you may want to search overly difficult.
Are You Working Too Hard?
This is when my ingrained habit of Domain-Driven Design thinking finally kicked in. Or perhaps the old Ben & Jerry's slogan “If it's not fun, why do it?” did. The fact that it was so hard to build up these queries is a big red flag. If you glean nothing else from my pain, at least remember this! As developers, it's habitual for us to set a goal and keep fighting every problem that comes our way until we achieve said goal. We slap more and more code onto the problem until it finally works. This is something of an ego problem for many of us and I'm happy to admit that it definitely is for me.
As developers, it's habitual for us to set a goal and keep fighting every problem that comes our way until we achieve said goal.
But DDD teaches us to take complex problems, break them apart into simpler problems, and then compose them as needed. I've drawn these lessons into many areas of my life, whether building software or trying to follow a complicated soup recipe.
Once I woke up from my ego trip, I realized that the filtering was just too hard to accomplish using LINQ against the aggregate. I also knew that I had a very appropriate tool at my disposal that's excellent at querying: the database. My eventual solution was to create a new simple model, a new DBContext, and some SQL Server objects (view, stored procedure, and a user-defined function). Let's see how that works.
What Filter Results Do Users Need to See?
The domain experts identified the key data that a user should see in order to help them find a particular contract. It should be a combination of the contract number, the names of all authors involved, the working title of the book, and the date that the contract negotiation was initiated. For example, this fake book: Contract start: 6/13/2023, “Learning Razor Tricks”, Author(s): Julie Lerman, Roland Guijt.
You can see that being built up in the concatenated string within the search results along with the ContractId and ContractNumber in the “way too hard” version in Listing 1. I have created a DTO class in my solution called SearchResults to easily capture that data.
What Filters Do Users Need?
The domain experts also shared a number of ways that their users look for contracts. They want to search by author name parts: first, last, or both names and they shouldn't need the full name to get a good result. For example, it's common that people can't remember if my last name has an “h” in it or not. They should be able search for authors with last name L or Le. Or the other common conundrum about my name: is it Julie or Julia? They can search for Jul.
Because every new contract version imposes a timeframe in which an author needs to respond, accepting or rejecting it, users may need to look for a contract whose response date is coming up soon.
During strategic design, discussions about searching for contracts revealed that there are two reasons for searching. One is to find a particular contract to examine it or modify its details. The other is simply for building reports. Reporting is a very different set of problems that's guaranteed to evolve and should (and will) be handled separately from this solution. During strategic design, the team was careful to keep the contract searches focused on those searches needed for working on contracts.
One cross-over idea from the reporting system is that a report will have a contract number on it, so a user with access to a contract number may want to search on that as well. I won't be implementing every one of these filters, but they can all follow a similar pattern.
Implementing a New Model and DbContext
We've already established that querying against the aggregate using the DbContext designed for persisting the aggregate is too hard. That's for contract management. For searches, I'll use the SearchResult DTO and a new SearchContext.
The SearchResult class is very simple. As described earlier, it has three properties: KeyValue, Description, and ContractNumber. The class is immutable and can only be set by way of its public constructor. The private parameterless ctor is there for EF to materialize query results.
public class SearchResult
{
public SearchResult(Guid key,
string description,string contractNumber)
{
KeyValue = key;
Description = description;
ContractNumber = contractNumber;
}
private SearchResult(){}
public Guid KeyValue { get; private set; }
public string? Description { get; private set; }
public string? ContractNumber { get; private set; }
}
The SearchContext class (see Listing 2) is read only (NoTracking) with the SearchResult configured as having no key.
Listing 2: The SearchContext class
public class SearchContext : DbContext
{
public SearchContext(DbContextOptions<SearchContext> options):base(options)
{
ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
}
public DbSet<SearchResult> SearchResults => Set<SearchResult>();
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<SearchResult>().HasNoKey();
}
}
But with these new classes, how are you to build LINQ queries? The answer is that you won't build any! Remember my earlier nod to the power of the database. You'll only be using the SearchContext to execute raw SQL and capture results into SearchResult objects. You'll see how I'm doing this a bit further on.
You may also have wondered how you'd be able to search within the Description property for names or dates. Again, you won't! The SearchResult is only there to capture data coming back from the raw SQL queries.
What About the Bounded Context and Database?
An important point to understand is that this SearchResult isn't any type of aggregate. It's a simple model and it lives in the same bounded context as the Contract aggregate. That also means that, by definition, it accesses the same database that the ContractContext interacts with. What do I mean when I say “by definition”? In DDD, you're guided to have different databases for each bounded context. But here, both the aggregate and the SearchResult model are part of one bounded context and therefore should be able to use a shared database.
Again, with this scenario, I have one DbContext for reading and writing and another that only reads. Because the second DbContext performs no writes and doesn't even perform LINQ queries, it has no bearing on the database schema. The primary DbContext, ContractContext, will be used with EF Core migrations to control the database schema. You should never use the SearchContext with migrations because EF Core would assume that the database has one single table with three columns and trash what was created by ContractContext.
As you are reading about having two different DbContexts in a single bounded context sharing the same database, you may be wondering about the case of multiple aggregates each with their own DbContext in this scenario. That's not relevant to this article, but I do want to address the question. If you have two aggregates, you need to find a balance where a single DbContext serves them both and serves and controls a single database. If achieving this is anything less than easy-peasy, that should raise a red flag, yet again, that something is probably wrong with your models or, more likely, that your determination that they belong in the same bounded context was in error.
What About Those Database Objects?
Here comes the fun part of the solution where it's time to put on your database hat or find someone in your org that wears one. Recall that I mentioned one view, one stored procedure, and one function. (Is anyone hearing a George Thorogood song now?) The stored procedure may seem complicated but for a database whiz, probably not. The hardest part will be formatting the SQL so that it's readable in this article!
If the sproc is complicated, does that raise a red flag and tell me I'm doing it wrong? To me, the answer is no. I'll be asking the database to do something it's very good at and my coded solution will be clean and easy to read. And from a DDD perspective, that means it will be easier to understand and maintain my code.
Based on the ContractContext, the database tables are structured so that the contract highlights are in a Contracts table and each version of the contract is in a Versions table. The Versions table also contains the data for the SpecificationSet value object, thanks to EF Core's Owned Entities mapping. Because there could be multiple authors (remember Author is also a value object) for a version, there's a third table, Version_Authors, that contains the author data. If you're curious about how my mappings and migrations came up with this schema, I go through that in detail in the above-mentioned Pluralsight course. I'm aware that a collection of value objects is seen as an anti-pattern. I thought long and hard before I decided to take this path. It's a decision made based on plenty of experience, so I'm comfortable with it. In fact, it's the first time I've ever designed an aggregate that has a collection of value objects as a property of the aggregate root.
Let's start with the view. Why do you need a view? The database schema is designed to persist the aggregate. Like the LINQ query, I'll have to dig through multiple tables to find the data I need to build the query for the filter and the results. By encapsulating the search details and output details into a single view, queries will be much simpler to build.
I thought it would be easier for you to comprehend the view in designer mode as it's displayed in Figure 3. You can see which fields I'm extracting from which tables and how those tables relate to one another. The SQL for the view is captured in a migration file in the solution that you can download. This gives me all of the fields I might want to search on and all of the fields I need to output.
The view lists all versions of each contract (Figure 4) and notes which ContractVersionId represents the current version of a particular contract. Note that for space consideration, the figure doesn't display the full GUID values.
Explicit Sprocs or One to Rule Them All?
I originally used different stored procedures to execute the needed queries. That was tidy but it led to complexity in the application because I had to have different methods and different calls. It worked and felt nicely organized.
There was a lot of duplication in these procedures. I removed some of it by encapsulating the creation of the description string into a function. Otherwise, the only differences were in the parameter lists and the WHERE statements in each procedure's subquery.
For example, to find contracts by author last name, there's a single parameter (@LastName) and the subquery is:
select currentversionid
from currentcontractversions
where left(LastName,len(trim(@LastName)))=trim(@LastName)
To find contracts by date initiated, the sproc takes two parameters, @initdatestart and @initdateend, with the subquery:
select currentversionid
from CurrentContractversions
where
cast(dateinitiated as date)>=@initdatestart)
and cast(dateinitiated as date)<=@initdateend
Adding filters meant adding a new stored procedure and then a new method in the search service to call that procedure. Lots of copy/pasting and the red flag came up again!
I considered dynamic queries (which would have meant embedding strings into the TSQL) and decided against that because - EWW - and that would introduce too many complications. However, I really wanted to make things simpler in the application and had to push the boundaries of my TSQL skills to come up with a single stored procedure. This may have been a simpler task for someone more adept at TSQL, but I did a lot of due diligence to ensure that I was making the best choice. I hope that research led me (and as I pass this on to you) to the best conclusion.
The stored procedure takes in all three parameters.
CREATE PROCEDURE GetContractsFlexTempTable
@LastName varchar(15),
@initdatestart varchar(20),
@initdateend varchar(20)
You can inspect the TSQL in the download to see the full details but I will highlight some of them here.
There's some trickery to deal with null dates, for example, if you're looking for a contract initiated after June 1 with no end date. I am pre-creating converting the incoming start and end dates to create values if they are null.
DECLARE @SDate DATETIME
DECLARE @EDate DATETIME
SET @SDate = ISNULL(@initdatestart, '19000101')
SET @EDate = ISNULL(@initdateend, GETDATE()+100)
And there's some more trickery to set up a temporary table in order to collect the key of any versions that match the filter.
select currentversionid INTO #ContractSubSet
FROM CurrentContractversions WHERE 1=2
Then, using a series of IF statements, I insert rows into the view using WHERE statements based on whether there are values in any of the three parameters. That way, I can combine Name and Date searches. I had to write each of those filters explicitly. Here's a bit of the procedure where I've determined that there is a lastname filter but no date filters.
IF @LastName IS NOT NULL
BEGIN
IF (@initdatestart is NULL and @initdateend is NULL) --only filter on lastname
INSERT INTO #ContractSubSet
SELECT currentversionid FROM currentcontractversions
WHERE left(LastName,len(trim(@LastName)))=trim(@LastName) ;
Finally, I execute one last query against that temporary table where I concatenate all of the info needed in the list. In other words, combining the date initiated, the working title, and, using an aggregate function, the names of any authors involved in that contract's current version. Again, I recommend perusing the TSQL if you are interested in how I implemented it all.
SELECT groupednames.contractId as KeyValue,[description],ContractNumber
FROM
(SELECT contractid,currentversionid, dbo.BuildContractHighlights(various data)
AS [description],ContractNumber
FROM CurrentContractversions
WHERE currentversionid IN (SELECT currentversionid FROM #ContractSubSet)
GROUP BY various data) groupednames
With this procedure in place, the database now has a single entry point - this one stored procedure that takes in my three parameters. And I can always expand the logic to add more parameters and filters. For some context, the current version of this sproc is 57 lines. It isn't a beast. It just felt like it when I had to figure out how to write it!
I've truly put all of the pain in the database into the view, stored procedure, and function. And, thanks to this, you'll see that the code in my app is simple, readable, and succinct.
Executing the Filters in the Application
Executing the stored procedure from the application is achieved with the help of a SearchParams class that encapsulates all of the possible filter values you may need. This can be expanded on as needed.
public class SearchParams
{
public SearchParams(string lastName, string startDate, string endDate)
{
LastName=lastName=="" ? null:lastName;
StartDate=startDate== "" ? null:startDate;
EndDate = endDate== "" ? null:endDate;
}
public string LastName { get; }
public string StartDate { get; }
public string EndDate { get; }
}
Your UI or API can populate this class and pass its values along to the stored procedure call. In my sample application, I have a simple set of Razor pages in an ASP.NET Core website. The user can enter filter details into the relevant text boxes - one for last name, one for start date, and one for end date - and then click a Filter button. The razor page constructs a SearchParams object from the form data and passes it on to a search service. There is also a Reset button that creates an empty SearchParams object. Here are the methods called when the buttons are clicked.
public async Task OnPostFilter()
{
var searchParams = new SearchParams(LastName, StartDate, EndDate);
ExecuteFilter(searchParams);
}
public async Task OnPostResetFilter()
{
var searchParams = new arams("", "", "");
ExecuteFilter(searchParams);
}
Both methods then pass their searchParams object to the ExecuteFilter method, also in the Razor page.
private async Task ExecuteFilter(SearchParams searchParams)
{
ContractHighlights = await _service.CallService(searchParams);
}
The service call is in my ContractSearchFlexService class shown in Listing 3. The original solution that used separate stored procs uses a ContractSearchService class, and you'll see in the demo code that I added the word “Flex” to the assets that target the GetContractsFlexTempTable stored procedure.
Listing 3: The ContractFlexSearchService class
public partial class ContractFlexSearchService
{
SearchContext _context;
static List<SearchResult> _results;
public ConractFlexSearchService(SearchContext context)
{
_context = context;
}
public List<SearchResult> SearchResults => results;
public async Task<List<SearchResult>> Service(SearchParams sP)
{
_results = _context.SearchResults.FromSqlInterpolated(
$"GetContractsFlexTempTable
{sP.LastName},{sP.StartDate},{sP.EndDate}").ToList();
return _results;
}
}
Figure 5 shows an example of the Razor app using the flexible search where I've applied a name and start date filter, resulting in two of the sample data contracts to be displayed.
Beauty in Simplicity
Take a moment to gaze upon the beauty and simplicity of the service class and its single method. Thanks to the SearchParams class and the stored procedure I created, I now have one single simple method. If I need to add more filters, I can do that by expanding the SearchParams class and the stored procedure. But the service won't need to change at all. The original solution allowed only one filter at a time and its service had separate methods for each filter as well as the “no filter” option. I did refactor it to allow a single point of entry to make it easy to use, but it's a bit complicated to maintain.
I owe a nod of thanks for some encouragement from a fellow .NET developer (David Henley) in my original GitHub repository for the Pluralsight course. His comment caused me to push myself to refactor the solution from the original (with its many stored procedures and many API calls), resulting in the handy SearchParams class and the super simple ContractSearchFlexService class. And, as you probably know, getting to a happy place with a big refactor really is fun!
