LINQ Functions in C# with an SQLite Database
While writing a query to filter some data from a database using LINQ, I ran into an issue. My query ran correctly, and all my data was populating correctly in the browser. However, when I went to write tests, even the simplest test gave me this error.
The error is pretty simple: SQLite does not support the APPLY function that SQL supports. But our project doesn’t use SQLite. It turns out that due to limitations with our CI/CD pipeline, our test database needs to use SQLite, and LINQ was turning my code into an SQL query that uses the unsupported APPLY operation.
To see why, let’s look at how the data is formatted.
I have two tables:
Translation
ID
ENText
StateVariation
ID
TranslationID
ENText
StateName
A Translation shows us what data to display by default, and a StateVariation contains text that overrides the base translation.
We filter by a few different values on the Translation, but the one we’re worried about right now is the StateName field on the StateVariation object.
I want to get every Translation, and I also want every Translation to hold a list of all occurrences of StateVariation associated with it. In C#, this can be done with a LINQ query like this.
Some knowledge on join vs apply helps us understand what’s happening here.
A JOIN will combine rows of two tables based on a matching column. In this case, Translation.Id and Variation.TranslationId.
An APPLY performs a function on each row of data in a table.
When we take this query and turn it into an enumerable, LINQ decides how to execute it most efficiently. Mapping variations to the variations object happens on each row of data in our translations database. Because we perform this function on each row instead of simply joining the data, the resulting query will always have an APPLY operation, which means our tests for this function will always fail.
There are a few possible solutions for this.
Option 1: Pull the Data into Memory Before Filtering
When you turn a query into an enumerable, it’s pulled into memory. If we pull the Translations table and the StateVariations table into memory, we can continue performing our filtering with LINQ, with a higher degree of control over the execution.
The largest issue with this is, of course, that you’re potentially doing a lot of data manipulation in memory. This is inefficient and slow. This is why I’ve called out on line 2 that I want to do as much filtering as possible before I filter on StateVariations.
The benefit is that you can still run your tests against the exact same code that is running on production while keeping your code relatively easy to read and update.
Option 2: Write the Query Explicitly Using FromSqlRaw
The other way to control your SQL query is to write it yourself. If your filtering function needs to know what’s happening in multiple different tables, it’s going to be a huge strain on memory to pull all of them down at once. Fortunately for us, C# has FromSqlRaw. Below is an extremely basic query.
How simple! But sadly, it can’t stay this simple. It’s time to adjust our query to do a join on another table.
I want my final result to have a variable called ServiceFlag that is “1” when there is at least one Service associated with our Business and “0” otherwise.
We have to edit that super basic query above since we are creating a new table from our two tables.
This is a simple computation, but it’s already getting much more unwieldy and hard to read. This doesn’t even do any sorting, pagination, or filtering. Setting up any decently complicated filtering function is going to be time-intensive, and any future changes are going to need to be made in a way that doesn’t break existing filtering functionality.
While it will be more efficient when executing than Option 1, the improvements won’t be worth the added development time if you have a very small table.
Overall, neither of these solutions are ideal.
Option 1 is inefficient, and Option 2 is time-consuming to create and maintain. These options can be decent workarounds for this specific use case. However, for effective testing, it’s best to set up your tests to be the same as your development environment.
