CategorySQL

How to reset the entities state on a Entity Framework Db Context

I had two bad days. Those days wasted chasing a stupid bug. I had a test class with 4 test cases on my infrastructure layer. If executed one by one, they pass. If the whole suite was executed, only the first one was passing.

At the end I found out that it was due to the Entity Framework Core db Context tracking the state of the entities. More or less. 
In a nutshell, every time a call to SaveChanges() fails,  the subsequent call on the same instance of the db context will retry the operations. 

So let’s say your code is making an INSERT with bad data and fails. Maybe you catch that and then you do another write operation reusing the db context instance.

Well that will fail too. Miserably.

Maybe it’s more correct to say that the second call will look for changes on the entities and will try to commit them. Which is basically the standard and expected behaviour.

Since usually db context instances are created for every request this might not be a big issue.

However, in case you are writing tests using XUnit Fixtures, the instance is created once per test class and reused for all the tests in that class. So in this case it might affect test results.

A potential solution is to reset the state of the changed entities, something like this:

Another option is to avoid entirely reusing the db context and generating a new one from scratch.
In my code the db context was registered on the DI container and injected as dependency. I changed the consumer classes to use a Factory instead and that fixed the tests too 🙂

SQL: generate a report with dynamic columns by month

Hi all! This time I’ll try to dig a little bit into the fabulous realm of SQL dynamic queries 😀

The need: I had to generate a report showing some counts divided by month, basically the columns represent the months and the user has the possibility to pick a date range.

For example, imagine that you want to select the number of the orders placed by all the customers by month.

Using the standard Northwind database as reference, the first thing to do is to generate a list of months along with the relative start and end days:

The next step is “quite” easy: all we have to do is to generate a string containing a main query that picks all the customers and have many sub-queries,  one for each month, SELECTING the count of the orders.

As you may see, most of the work is done with lines 5-10 where we use the COALESCE function to concatenate the sub-queries created using the #dates temp table. Note that each query will contain WHERE clause that filters the Order by Customer.

On lines 12-13 we create the final query to be executed, and finally, on line 16 we ask sp_executesql  to run our code.

Here’s a screenshot of the results:

dynamic sql query variable column names

dynamic sql query variable column names

 

Don’t forget to DROP the #dates table! 😀

SQLCE: How to execute complex scripts

For one of the side-projects I am working on, I needed a way to execute long SQL scripts to create some reports. I started the project using SQLCE because I didn’t wanted to bother with a full installation of a SQL server (even the Express one), so I wrote all the code exploiting EntityFramework 6 and the SQL Server Compact & SQLite Toolbox. I didn’t used SqlLite because at the time EF6 didn’t had support for it (was added in February 2014, see here).

However, one of the drawbacks is the lack of support of some SQL commands, for example the syntax “SELECT … INTO … FROM”. Also, I had complex scripts that used the GO command to separate the blocks. In order to make these work, I wrote a very simple routine that splits the .sqlce file by line, searches for each GO and executes the query till that point. You can find the code here on GitHub 🙂

© 2018 Davide Guida

Theme by Anders NorenUp ↑