>> Hi everyone, welcome to
another episode of Visual Studio Toolbox.
I'm your co-host, Leslie Richardson.
>> I'm your co-host, Robert Green.
>> I'm your guest Phil Japikse.
>> Awesome, and welcome to another edition
of automated testing on Visual Studio Toolbox,
part of the mini series that we're still presenting.
We've been talking about a lot of
different parts of automated testing.
We just wrapped up three episodes all about Mac in particular,
as well as a little bit of a dive into Azure DevOps,
and some GitHub Actions,
which is pretty sweet.
This time around we're going to be talking
about integration testing as
it relates to stuff such as EF Core and SQL Server.
>> Correct. I want to really stress that we are
talking about integration tests here,
which are not unit tests.
We had this discussion,
I think a couple episodes ago.
>> What's the difference just as a reflection?
>> The difference, this is not the academic definition,
this is Phil's definition,
a unit test is you're testing an isolated unit of work.
In integration test you're testing multiple pieces.
Whether that's two different pieces of code that
you're actually running the code as opposed to mocking out.
You go into a database,
you're hitting a web service.
There's something else that your code is integrated with.
So it's not isolated.
One of the downsides of integration testing your data access layer
is it's time-consuming.
We talked about how to tie into your CI/CD pipeline,
but you don't want
your integration test to always run every time we
check in because it's going to be
a monumental drain on your resources.
By resources I mean your SQL Azure and those types of things.
But you still want to have those tests around it.
Now what we do in real life is we do
two different types of
integration tests or tests for the data access layer.
We do true integration tests.
We're actually testing all the way down to the database.
We're adding records, we're deleting records,
we're updating records, we're getting records.
But what we run every time we check-in are the mocked tests.
We talked about mocking in depth
and actually it was a sample repo.
We've mocked a data access layer out for our unit test,
but for integration test we're actually hitting the database.
>> Just a follow-up to that and maybe a prelude to this.
You can mock so that you're not hitting the actual database.
You could also use something like local SQL Server
or even SQL Lite and duplicate,
have a sample database that you're actually talking to.
What would be the benefit of mocking over that?
is that a reasonable thing to
do without having to mock the data layer?
>> Well, I think anything that
moves the ball forward for you is reasonable.
I'm not going to sit here and say it's not
reasonable. It is reasonable.
If it helps your team progress
and become more automated, it's reasonable.
One of the downsides if you're using
SQL Server and you decide instead testing against SQL Lite,
you're really not testing the exact same platform.
Something that works in SQL Lite might not work in SQL Server
and then you have a false positive or vice versa.
The other thing that EF core has,
and correct me if I'm wrong,
but I think the EF Core team is actually
talking less and less
about an in-memory database implementation so you can swap it out.
I played with it,
but I never use it in memory database provider
because it's the same thing.
I am writing code against SQL Server.
If I'm testing against the in-memory database provider,
how do I know that you're going to behave exactly the same?
If you're testing code that requires data,
but you don't want to test the data layer
you mock out the data layer.
That's just what you do.
>> Okay.
>> As a prelude to this episode,
Robert and I did a 15 episode series on EF Core in 2020.
If you haven't seen that,
you need to stop this and go watch it now.
No, seriously, we're not going to go into the mechanics.
>> There's a ton of resources on EF core.
Those are among them.
>> Yes. Those are the best.
What else would you go to?
No, I'm just kidding. The doctor really good as well.
>> Assume people know we have core,
and assume people know SQL Server
and we're just going to focus on the testing part.
>> We're going to cover a couple of things.
We'll going to cover some practices you
have to include to make this work effectively.
We're also going to expand on our xUnit knowledge.
We're going to talk about some setup mechanics that we need to do.
Let's start off with
a data initialization and easy for me to say,
getting the data in the database.
Here I've got some sampled data.
I only have two tables.
If you read any of my books,
this is based on the AutoLot,
so it's very car focused.
We have cars and makes and that's all we have.
We simplify the database.
Then I have this code in the initializer where I
can clear out the data and then I can see the data.
What we want to do,
and we talked about this at length earlier,
is we want to make sure that in the setup,
the test setup that we have the data that we're expecting.
In the cleanup, we reset the data to how it was when we came in.
I have one method that we can call and it's
simply clear and seed database.
Now what this does is it just
deletes all the data, adds all the data.
It's a very small record set.
It's very, very fast.
If you're testing against the local to your point earlier,
you could just as efficiently drop the database and recreate it.
But if your database is in Azure is a bad thing.
Because you can drop it programmatically,
but creating its pretty hard to do in Azure, put near impossible.
Here we have just as method and all it does is go
through and add records,
clear the records, add the records.
Let's go back to our tests and we're using a class fixture.
The class fixture is of type Ensure AutoLotDatabaseTestFixture.
Like I said before, I'm terrible at naming things.
But if we look in here,
what I'm doing is I'm getting a configuration so I'm
using the ASP.NET Core and.NET Core configuration subsystem.
I've got in app settings.JSON file,
I'm not going to go into depth about how that work,
but it allows me through a configuration file,
change what the connection string is.
There's no secrets here, I'm going into local DB.
Then if you have access to my machine,
there's my password and my test. It's complicated.
>> Excellent password.
>> I've never seen you using that one.
>> No, because usually there's a one on the end.
>> With an exclamation mark.
>> Yeah.
>> Very creative. No one is going to crack that.
>> In my ensure fixture here,
my constructor which runs before the fixture runs.
I am getting a configuration.
I'm creating an instance of the context,
and then I'm clearing and receiving the database.
Now this does the clearing reseed
also create a database if needed,
as long as I'm not running against Azure.
When we look at our tests,
creating a new instance of our repo.
Then we're going to start calling tests against it.
What I'm testing here is I'm not testing EF Core.
I have to believe that EF Core worked and I do.
I'm not saying that starkly,
but I'm not testing the framework.
>> You are not testing
your individual methods that make calls and do things.
You've already tested those.
>> No. This is actually
testing the individual methods on the repos,
not the methods and the controllers,
but on the repository.
I use a modified repository pattern.
All my data access goes through a repository class.
It allows me to encapsulate logic.
I'm coding to an interface so I can inject that in.
My line of business code,
all it knows is it says car repo,
get all and then car show up.
It doesn't have to know that it's EF Core or anything else,
just happens to be.
This is testing a data access layer.
I'm actually testing the repository.
>> Is that integration testing,
testing the data access layer?
>> This is integration testing.
>> Integration testing is
basically unit testing the data access layer.
Is that in this characterization?
>> Well, unit testing, again,
if we're looking at the academic definitions,
a unit test is only testing a unit of work.
A unit of work is isolated to the smallest functioning piece,
which is a method.
I'm actually bringing in additional outside forces.
It's my C## code,
my EF Core code,
and the database itself.
You were right where you said unit testing.
It's testing a data access layer,
that's one type of integration testing.
If I'm calling a web service,
I'm calling a web page,
I'm calling something else.
Those are all file system,
for example. All integration testing.
I just said don't test for this is showing how to test EF Core.
But I'm also just trying to show how to test with data.
These are pretty simple.
I am running a query.
I'm checking what the query string is.
I'm putting, remember that output helper that we have an xUnit.
This is going to actually show what the query is that's executed.
Then I'm asserting that
my expected count is correct. We'll run this.
We're not going to run all of the gets
because the gets are pretty rudimentary.
It worked. Of course,
I have no idea which one it is now and all of them.
>> I wish the Test Explorer would redirect you to
the file in question if you're just trying to
quickly return to the task.
>> Any upcoming episode on unit testing tools, Visual Studio.
>> You dropped back.
>> There's my query, so I can see
what the query was when it was executed.
What's more important to me in
my testing is doing things like inserting a record,
because now I'm checking that
my repository is taking care of things like foreign keys.
Do those things exists everything else but also
don't want to sully the database.
What I have is in
this test framework and it will be in the download
or in the GitHub repo is I
have a helper method called ExecuteInATransaction.
I don't have to keep writing the same thing over and over again.
If we look at my Base Test method, I'll stop.
What I am doing here is I'm creating an execution strategy.
In execution strategy,
we talk about this and of course,
but just as a brief reminder.
If you have an execution strategy in play, which I do,
I've got the SQL Server retry execution strategy turned on,
then you have to run your transaction in an execution strategy,
so that's what this boilerplate code is.
Then here's my using transaction,
I am passing in a delegate,
that's my action and then I automatically roll back.
Now in your production code,
obviously you wouldn't automatically
roll back if everything worked,
you commit and rollback.
But since I am only testing my insert,
I don't want to actually do an insert, so I roll back,
so I'm leaving it in the state that it was when I found it.
Then here is my local function,
which becomes my delegate that pass into the executed transaction.
Update works the same way,
so I have code here and you notice by asserts are in here.
I am getting the first color out of the database,
I happen to know what color it is.
I know it's a black car,
I change it to white I save changes and
then create a second context.
Now this is important when you're doing testing with EF Core.
I already have car one or the car in context one,
it's already in the change tracker.
For me to test against any changes to
that is moot because it's already tracking it.
If I really want to test what happened in the database,
I need to create a new context,
call into the database and see what values
are to make sure they actually persisted.
Something new that you can do and in recent versions of IE Core,
I could do this now,
which I think would be more efficient.
That's a fairly recent features I can clear
the change tracker and then I can use the same context.
There'll be more efficient because creating
context has some overhead.
Remove works the same way.
Here's the one thing that I definitely wanted to show,
and it's showing a Concurrency Exception.
If you go back to our episodes or any documentation,
you can get automatic currency checking within
EF Core by having a real version and this was SQL Server.
We always turn it on and so what
happens is if you are working with an object,
so you pull customer out of a database,
I pull customer database so
the raw version value for both of our customers is the same,
it is automatically handled by SQL Server.
You make a change and save it,
the raw version gets updated.
Now in a database primary key of raw version of 1,2,3,
it's making a number of different format.
I have primary key of one,
but row version is 1,1,11.
I go to save it what happens is
z of course changes the way it saves,
it says instead of just updates where ID equals
one and row version equals,
in my case, 1,1,11.
Well, that record doesn't exist.
So what SQL Server has got nothing to do so that's fine,
not an exceptional thing,
I'm not raising a problem,
I'm just going to say no records returned.
The change tracker says, of course,
I should have had one record changed,
I'm getting zero,
I'm going to throw my hands up and punt.
That gives us this exception right here,
which is a DbUpdateConcurrency Exception.
This is important because
how do you handle the concurrency exception.
All EF Core does is say,
there's a problem, you as
a developer have to determine what to do with that problem.
You could do the easy thing and say,
sorry Phil, Robert beat you to it, try again,
or you could go as far as
look at what I change versus what you change,
and then try and apply
some logic to so we change different fields.
We're going to go ahead and get Roberts car,.
>> Say you can change the database,
I could change the database, you'd changed first.
I change it after I win, last in-wins.
>> Well, that's someone's is typically not a good idea.
>> Because?
>> Because it's a principle of least surprises and broken,
just over your data and you don't know it.
>> What's the difference if I
change a wreck and you come in three hours later and change it?
>> If you come in three hours later and change
it you are seeing the data as it
is and then changing it with
the knowledge of what the state of the object is.
If we're pulling it back and I see that your title
is developer and I update your salary for a developer salary,
but you actually go in there with
your Flintstones title and
you change it to senior developer because you just got a raise,
and now I have written his salary to be a developer
because I didn't know he was a senior developer
because I didn't know there was a difference in a data.
If I come in three hours later,
I've pulled the data,
so I have a fresh copy of data and go, oh,
he's a senior developer,
that means salary y not x.
We want to really make sure we don't
violate the principle of loops surprise.
>> We digress,.
>> Well, this the first
time this series we've digressed, isn't it?
>> Now we're digressing again.
>> I guess the whole alias thing was a bit of a digression.
Let me show you the test helper through and through.
One thing that I like to do is I use a configuration subsystem.
Then this sets the base path to be the current directory,
I have a Json file.
Now this is important if
you're watching a hump, you are watching a hump.
If you have an app settings.json in
one of your tests projects and you're using Azure DevOps,
I don't know about GitHub Actions
because it might have the same problem,
but if you have appsettings.json in your test project,
you have app settings Json in
your web application or your SQL Server or whatever,
it might be the one from your test project that actually gets
overwritten or it might be
the one that you really want to keep that gets overwritten.
We've actually had failures because we had
the same name appsettings.development.json
or appsettings.production.json. What I do is I always add what I'm
doing if it's not the main line application.
Because I'm creating the configuration
subsystem here basically from
scratch using the framework and then I can name whatever want.
I always have a helper method it says go get me the context
based on the whole configuration system.
Then here is the GetSecondContext,
and GetSecond Context is what allows me to do
that concurrency checking and to make sure things got updated.
Then just to show you in the base test class,
I have executed the transaction.
We talked about this,
but you can also execute in this shared transaction.
This means contexts one isn't a transaction,
context two is in the same transaction.
I haven't committed, that changes yet so if
I wasn't in the same transaction
contexts two wouldn't see the changes,
wouldn't be able to verify it.
You can share transactions between DbContext
and that is as simple as setting the transaction,
using the transaction coming from the existing transaction.
Any questions? Again, just safety tip do not plug these into your CICD pipeline,
because you're going to be testing.
We've got hundreds of tests that hit the database.
We do not want to as running every time we check in.
It's going to drive up your costs,
as well as your performance.
All right.
>> Sweet. We are in the home stretch of our series,
so closing out with the next episode.
What are we going to do to wrap things up?
>> Remember automated UI testing.
>> Awesome, looking forward to UI testing,
so stay tuned for that.
We are on the final episode,
so don't miss it and until next time, happy coding.
[MUSIC]