Using an mdf file database with Entity Framework Core 2 in Visual Studio 2017

Full source code available here.

If you want to play around with Entity Framework it can be a little frustrating to create a complex database with a lot of sample data.
Instead, you could download the Northwind database from Microsoft, it has plenty of tables, a bunch of views and handful of stored procs. More than enough to try out many of the features of EF.

Now, all you have to is figure out how to get access to the db from your application, and it’s not as simple as it could be. For some reason unknown to me, Microsoft keeps changing the name of their localdb.

So here’s how to do it in Visual Studio 2017.

Step 1
Download the Northwind database – you can get it here https://www.microsoft.com/en-us/download/details.aspx?id=23654
Install it, it will create some files in c:\SQL Server 2000 Sample Databases.

Step 2
Copy Northwind.mdf and Northwind.ldf to your user directory, something like c:\users\bryan. The Northwind files have to be in that directory to work with the connection string below.

Step 3
Now in Visual Studio use the following connection string.

"Data Source=(LocalDB)\\MSSQLLocalDB;DataBase=Northwind;Integrated Security=True;Connect Timeout=30"

My sample application is a .NET Core Web Api. In startup.cs, add the following –

public void ConfigureServices(IServiceCollection services)
{
	services.AddDbContext<NorthwindContext>(options =>
		options.UseSqlServer("Data Source=(LocalDB)\\MSSQLLocalDB;DataBase=Northwind;Integrated Security=True;Connect Timeout=30"));
	services.AddMvc();}

If you want to place the database files elsewhere in your filesystem, add an absolute filepath to the connection string.

Step 4
Add an Order.cs class, this represents the orders table from the Northwind database. You can of course add more classes to represent other tables in the Northwind database.

public class Order
{
	public int OrderId { get; set; }
	public string CustomerID { get; set; }
	public int EmployeeID { get; set; }
	public DateTime OrderDate { get; set; }
	// etc.
}

Step 5
Add a NorthwindContext class that inherits from DbContext and add the Order DbSet.

public class NorthwindContext : DbContext
{
	public NorthwindContext(DbContextOptions options) : base(options) { }
	public DbSet<Order> Orders { get; set; }
}

Step 6
In the controller, add the NorthwindContext to the constructor.

public OrdersController(NorthwindContext northwindContext)
{
	_northwindContext = northwindContext;
}

Step 7
And finally, use the context in the Get method.

[HttpGet("{orderId}")]
public async Task<IActionResult> Get(int orderId)
{
	var order = await _northwindContext.Orders.Where(o => o.OrderId == orderId).SingleOrDefaultAsync();
	return Ok(order);
}

Full source code available here.

Performing a WHERE IN with Entity Framework or on a List

WHERE IN is a very useful and commonly used feature of SQL, it looks like this –

SELECT * FROM ORDER WHERE OrderId IN (10248, 10249, 10250, 10251)

The will return up to four rows of data, showing just the orders that have an OrderId in the list you passed to the select statement.

You can do the same with Entity Framework by using the Contains predicate with a Where.

First you need to put the OrderIds you are looking for in some sort of enumerable.

IEnumerable ordersToFind = new List { 10248, 10249, 10250, 10251 };

Then you use Where on the Orders DbSet and check the orderIds against the list you just created.

var orders = _northwindContext.Orders.Where(o => ordersToFind.Contains(o.OrderId));

That’s all there is too it. You don’t have to use this on just Entity Framework, it also works on simple lists, arrays, enumerables, etc.

Using the HttpClientInterception to Test Methods That Use a HttpClient

Full source code available here.

In my previous post I showed a way of testing a controller that uses a HttpClient.

I had to mock the HttpMessageHandler pass that to the HttpClient and set a bunch of properties. It works well, but is a bit long winded.

I received a comment from a reader who suggested that I try the JustEat.HttpClientInterception library. It allows you to setup responses to specified requests, and pass these to a HttpClient. Then the HttpClient is passed to the controller.

Here is how the test method looks –

[Fact]
public async Task GetTest()
{
    //Arrange
    List<int> myList = new List<int>() {1, 2, 3, 4, 5};
    
    // setup the interceptor
    HttpRequestInterceptionBuilder builder = new HttpRequestInterceptionBuilder()
        .ForHost("localhost.something.com")
        .ForPath("/v1/numbers")
        .WithJsonContent(myList);
    
    // create the HttpClient from the builder
    // and setup the HttpClientBaseAddress
    HttpClient client = new HttpClientInterceptorOptions()
        .Register(builder).CreateHttpClient("http://localhost.something.com/v1/");

    ValuesController controller = new ValuesController(client);

    //Act
    IActionResult result = await controller.Get();

    //Assert
    OkObjectResult resultObject = result as OkObjectResult;
    Assert.NotNull(resultObject);

    List<int> numbers = resultObject.Value as List<int>;
    Assert.Equal(5, numbers.Count);
}

Briefly, here is the constructor of the values controller. It takes the HttpClient as a parameter, usually passed by dependency injection.

public class ValuesController : Controller
{
    readonly IAsyncPolicy<HttpResponseMessage> _httpRetryPolicy;
    private readonly HttpClient _httpClient;

    public ValuesController(HttpClient httpClient)
    {
        _httpClient = httpClient;
    }
	//snip..

Full source code available here.

Unit Testing a Method That Uses HttpClient

Full source code available here.

In this post I’m going to show you how test an action method of controller that uses a HttpClient. When performing this test you want to isolate just the code of the action method for testing, you want to remove the dependency on the HttpClient. I hoped it would be simple, that there would be an IHttpClient, but there is not.

Instead I mocked the HttpMessageHandler, then pass it to the constructor of the HttpClient. The HttpMessageHandler is set to return a list of numbers in response to any request.

The constructor of the controller takes the HttpClient as a parameter, usually passed by dependency injection.

public class ValuesController : Controller
{
    readonly IAsyncPolicy<HttpResponseMessage> _httpRetryPolicy;
    private readonly HttpClient _httpClient;

    public ValuesController(HttpClient httpClient)
    {
        _httpClient = httpClient;
    }
	//snip..

In the test class I mock the HttpMessageHandler, set its SendAsync method to return OK and a list of numbers in response to any request. I then pass the mocked HttpMessageHandler to the constructor of the HttpClient.

Now the HttpClient will return the list of numbers I expect and I my test just tests the code of the action method.

The rest of the test is written as normal.

[Fact]
public async Task GetTest()
{
    //Arrange
    string numberJson= JsonConvert.SerializeObject(new List<int>() { 1, 2, 3, 4, 5 });

    var httpMessageHandler = new Mock<HttpMessageHandler>();
    httpMessageHandler.Protected()
        .Setup<Task<HttpResponseMessage>>("SendAsync", ItExpr.IsAny<HttpRequestMessage>(),
            ItExpr.IsAny<CancellationToken>())
        .Returns(Task.FromResult(new HttpResponseMessage
        {
            StatusCode = HttpStatusCode.OK,
            Content = new StringContent(numberJson, Encoding.UTF8, "application/json"),
        }));

    HttpClient httpClient = new HttpClient(httpMessageHandler.Object);
    httpClient.BaseAddress = new Uri(@"http://localhost:63781/v1/");
    httpClient.DefaultRequestHeaders.Accept.Clear();
    httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

    ValuesController controller = new ValuesController(httpClient);

    //Act
    IActionResult result = await controller.Get();

    //Assert
    OkObjectResult resultObject = result as OkObjectResult;
    Assert.NotNull(resultObject);

    List<int> numbers = resultObject.Value as List<int>;
    Assert.Equal(5, numbers.Count);
}

Full source code available here.

Using the Polly Timeout when making a Http Request

Full source code available here.

When making remote service requests the remote side will sometimes take longer than acceptable to respond. You have a few choices in handling this.

1. Put up with it, but this means you’re stuck waiting for some unknown period and hope you get a response.
2. Wait for the local HttpClient to timeout.
3. Fire off more requests expecting one of them to respond quickly. But now you have multiple requests open holding resources on your side.

Polly offers another approach. The Polly Timeout Policy allows you to specify how long a request should take to respond and if it doesn’t respond in the time period you specify, a cancellation token is used to release held resources.
The Timeout policy can be combined with a retry policy to fire off another request as soon as the timeout occurs.

This is great for GET requests, but be careful with anything that has side effects, search for idempotent if you don’t know what I mean.

Code
I define two policies, the first is a simple Retry Policy that is set to retry three times. The other is a Timeout Policy that is set to react if no response is received within one second.

readonly RetryPolicy<HttpResponseMessage> _httpRetryPolicy;
readonly TimeoutPolicy _timeoutPolicy;

public CatalogController()
{
	_httpRetryPolicy =
		Policy.HandleResult<HttpResponseMessage>(r => !r.IsSuccessStatusCode)
			.Or<TimeoutRejectedException>()
			.RetryAsync(3);

	_timeoutPolicy = Policy.TimeoutAsync(1);
}

Next I chain (or wrap) these policies around a http request executed by HttpClient.

HttpResponseMessage response = await 
	_httpRetryPolicy.ExecuteAsync(() =>
		_timeoutPolicy.ExecuteAsync(async token => 
			await httpClient.GetAsync(requestEndpoint, token), CancellationToken.None));

Line 4 makes the request to the remote service using the HttpClient.
Line 3, executes the timeout policy, if the http client does NOT respond with 1 second the timeout policy will throw a TimeoutRejectedExcetion.
Line 2, the retry policy condition will trigger when a TimeoutRejectedException occurs, and a retry will be performed.
Line 1, sets the return value from the _httpRetryPolicy to the HttpResponse.

Execution
1. The HttpClient calls the remote inventory service which has been written to take 10 seconds to respond for the first three requests, the fourth replies promptly.
2. For the first three requests, after 1 second no response is received from the remote controller, so the retry policy throws a TimeoutRejectedException and cancels the request by the HttpClient.
3. The retry policy condition is triggered and the a retry is executed.

This sequence repeats itself two more times.

4. Finally, on the fourth attempt the request succeeds in less than a second and the a response is returned.

Conclusion
Timeout lets you preempt a slow response and when combined with a retry policy it allows you to send another request quickly.

Full source code available here.

Unit testing Entity Framework Core Stored Procedures

Full source code available here.

Entity Framework Core has made unit testing CRUD functions much easier, see here for an example of using the In Memory Database, it allows you to search, add, remove and update rows.

But the in memory database doesn’t support execution of stored procedures in any way. I’ve seen people suggest that execution of a stored procedure is an integration test, but I don’t agree with that. If testing a method that does CRUD is a unit test, then testing a method that calls a stored procedure must also be unit a test.

An anonymous colleague of mine came up with this technique.

The first thing to do is add a class that implements IAsyncEnumerable and IQueryable.

public class SpAsyncEnumerableQueryable<T> : IAsyncEnumerable<T>, IQueryable<T>
{
	private IAsyncEnumerable<T> _spItems;
	public Expression Expression => throw new NotImplementedException();
	public Type ElementType => throw new NotImplementedException();
	public IQueryProvider Provider => throw new NotImplementedException();

	public SpAsyncEnumerableQueryable(params T[] spItems)
	{
		_spItems = AsyncEnumerable.ToAsyncEnumerable(spItems);
	}

	public IEnumerator<T> GetEnumerator()
	{
		return _spItems.ToEnumerable().GetEnumerator();
	}

	IEnumerator IEnumerable.GetEnumerator()
	{
		return GetEnumerator();
	}

	IAsyncEnumerator<T> IAsyncEnumerable<T>.GetEnumerator()
	{
		return _spItems.GetEnumerator();
	}
}

Then add an extension class for DbSet, it’s DbSet because it has the method FromSql that calls stored procedures. MockFromSql takes a SpAsyncEnumerableQueryable of items that the stored proc will return.

public static class DbSetExtensions
{
	public static DbSet<T> MockFromSql<T>(this DbSet<T> dbSet, SpAsyncEnumerableQueryable<T> spItems) where T : class
	{
		var queryProviderMock = new Mock<IQueryProvider>();
		queryProviderMock.Setup(p => p.CreateQuery<T>(It.IsAny<MethodCallExpression>()))
			.Returns<MethodCallExpression>(x =>
			{
				return spItems;
			});

		var dbSetMock = new Mock<DbSet<T>>();
		dbSetMock.As<IQueryable<T>>()
			.SetupGet(q => q.Provider)
			.Returns(() =>
			{
				return queryProviderMock.Object;
			});

		dbSetMock.As<IQueryable<T>>()
			.Setup(q => q.Expression)
			.Returns(Expression.Constant(dbSetMock.Object));
		return dbSetMock.Object;
	}
}

Finally, the unit test looks like this.

[Fact]
public async Task Get_Results()
{
	//Arrange 
	var products = new SpAsyncEnumerableQueryable<Product>(new Product()
	{
		ProductId = Guid.NewGuid(),
		ProductName = "Some name",
		Size = 1,
		Value = 2
	}, new Product()
	{
		ProductId = Guid.NewGuid(),
		ProductName = "Some other name",
		Size = 3,
		Value = 4
	});

	var productContextOptions = new DbContextOptionsBuilder<ProductContext>()
		.UseInMemoryDatabase(databaseName: "Get results")
		.Options;

	ProductContext productContext = new ProductContext(productContextOptions);

	productContext.Products = productContext.Products.MockFromSql(products);

	ProductsController controller = new ProductsController(productContext);

	//Act
	IActionResult actionResult = await controller.Get();

	//Assert
	OkObjectResult okObjectResult = actionResult as OkObjectResult;
	Assert.NotNull(okObjectResult);
	List<Product> productResponse = okObjectResult.Value as List<Product>;

	Assert.Equal(2, productResponse.Count);
}

Full source code available here.

Reusing HttpClient with Dependency Injection

Full source code available here.

If you are using HttpClient to make requests for you, you might have come across some articles discussing how to reuse HttpClient. They strongly advocate for using a single HttpClient for as many requests as possible, i.e. not creating a new HttpClient for every request.
Not having to create/dispose of the HttpClient for every request should improve the performance of you application. One estimate states that every time you instantiate a HttpClient takes 35ms.

In this article I will show you how to use dependency injection to reuse the HttpClient in .Net Core, but the same principle applies in Framework 4.x applications.

The one advantage of creating a new HttpClient for every request is that you don’t need to worry about the DNS record of an endpoint changing during the lifetime of the application, this is common if you are swapping staging and production instances during a deployment. But this is easyly(ish) handled by the ServicePointManger.

Adding HttpClient to the DI Container
In Startup.cs add the below lines.

public void ConfigureServices(IServiceCollection services)
{
    Uri endPointA = new Uri("http://localhost:58919/"); // this is the endpoint HttpClient will hit
    HttpClient httpClient = new HttpClient()
    {
        BaseAddress = endPointA,
    };

    ServicePointManager.FindServicePoint(endPointA).ConnectionLeaseTimeout = 60000; // sixty seconds

    services.AddSingleton<HttpClient>(httpClient); // note the singleton
    services.AddMvc();
}

This approach is ideal if you have a limited number of endpoints and you know them at application startup. If you don’t know then endpoints at startup you can add the call to ServicePointManager where you HttpClient requests occur.

Using the HttpClient
Now I have the HttpClient registered with the Dependency Injection container, let’s take a look at the controller that uses it to make a request. See the inline comments.

public class ValuesController : Controller
{
    private readonly HttpClient _httpClient; // declare a HttpClient

    public ValuesController(HttpClient httpClient) // this is the singelton instance of HttpClient
    {
        _httpClient = httpClient; // assign it to the local HttpClient
    }

    // GET api/values
    [HttpGet]
    public async Task<IActionResult> Get()
    {
        string requestEndpoint = "api/products";

        HttpResponseMessage httpResponse = await _httpClient.GetAsync(requestEndpoint); // make request
        List<Product> products = await httpResponse.Content.ReasAsJsonAsync<List<Product>>();
        return Ok(products);
    }
}

Full source code available here.

HttpContent ReadAsAsync with .NET Core 2

Full source code available here.

If you are used to using HttpContent.ReadAsAsync you might be surprised to learn that it is missing from .NET Core 2. You can try adding Microsoft.AspNet.WebApi.Client but you might get warnings or errors.

At some point Microsoft will come out with an updated NuGet package, but in the meantime here is a work around.

At this extension method to your code.

using Newtonsoft.Json;
using System.Net.Http;
using System.Threading.Tasks;

namespace ReadAsAsyncCore
{
    public static class HttpContentExtensions
    {
        public static async Task<T> ReadAsJsonAsync<T>(this HttpContent content)
        {
            string json = await content.ReadAsStringAsync();
            T value = JsonConvert.DeserializeObject<T>(json);
            return value;
        }
    }
}

And use like this.

HttpResponseMessage httpResponse = await httpClient.GetAsync(requestEndpoint);
List<Product> products = await httpResponse.Content.ReadAsJsonAsync<List<Product>>();

Full source code available here.

Entity Framework Core 2 Unit Testing in .NET Core 2

Full source code available here.

Unit testing Entity Framework used to be quite a chore, but over the past few years it has become significantly easier.

In this post I’m going to show you how to use the InMemory database with named instances.

The ProductsController is fairly standard, it takes a DbContext in the constructor which is instantiated by dependency injection (see the Startup.cs in the source code if you don’t know how to do this).

public ProductsController(ProductsContext context)
{
    _productsContext = context;
}

The action methods use the context to query the database and return responses to the callers. From the response status code the caller knows if there is also content (products) in the response.

[HttpGet]
public async Task<IActionResult> Get()
{
    var products = await _productsContext.Products.ToListAsync();

    if (!products.Any())
    {
        return NotFound();
    }
    return Ok(products);
}

Testing
In the unit test project, (see here if you need help creating this), I’ve added a ProductsControllerTests class.

In my Get test I created an instance of a DbContextOptions by using the DbContextOptionsBuilder. On the builder I specify that I want an InMemoryDatabase and give it a unique name for this test – this is important, if the name is not unique across all tests or if it is left blank you will end up with a database shared by more than one test.

In the Arrange portion of the test I also instantiate the ProductsContext; then instantiate a few products, add them to the context; don’t forget to save the changes; finally create an instance of the of ProductsController.

Here is the full Arrange portion of the test.

// Arrange
var options = new DbContextOptionsBuilder<ProductsContext>()
.UseInMemoryDatabase(databaseName: "Get test")
.Options;

ProductsContext productsContext = new ProductsContext(options);
Product product01 = new Product { Name = "first", ProductId = 1, Sku = "abc" };
Product product02 = new Product { Name = "second", ProductId = 2, Sku = "def" };

productsContext.Products.Add(product01);
productsContext.Products.Add(product02);
await productsContext.SaveChangesAsync();
ProductsController controller = new ProductsController(productsContext);

Next I Act, just calling the controller’s Get method. The controller already has the context and the get method will query it just like a real database.

// Act
var actionResult = await controller.Get();

Then I Assert. 

// Assert
var okObjectResult = actionResult as OkObjectResult;

List<Product> products = okObjectResult.Value as List<Product>;

Assert.Equal(2, products.Count());

Note the as OkObjectResult type, I know it's that type because that is what I return from the ProductsController.

I assign the value of the okObjectResult to a List.Assert that there are two entries in the list.

Testing NotFound - 404
In the ProductsController I also have a Get that takes a productId as an int and returns a product.
You should also test the scenario that no entries in the database match the search. In this case I am returning a NotFound, you may choose to return something else like an OK with nothing in it. In the case of a list, some people like to return an empty list; there is much debate on this topic and I have no interest in adding my two cents.

To test the NotFound response, the Act and Assert change a little. I call the GET method with an id that I know will return nothing.

// Act
var actionResult = await controller.Get(999);

// Assert
var notFoundResult = actionResult as NotFoundResult;

Assert.Equal(404, notFoundResult.StatusCode);

Full source code available here.

Unit Testing .NET Core 2 Web Api

Full source code available here.

Unit testing Web API controllers in .NET Core 2 is very easy.

I have very simple GET and POST methods.

[Route("api/[controller]")]
public class ValuesController : Controller
{
    [HttpGet]
    public async Task<IActionResult> Get()
    {
        // imagine some db logic
        List<string> values = new List<string>() { "value1", "value2" };
        return Ok(values);
    }

    [HttpPost]
    public async Task<IActionResult> Post([FromBody]string value)
    {
        // imagine some db logic
        return Created("", value);
    }
}

Add an xUnit Test Project to your solution.

In the test project add a ValuesControllerTests class.

Add a method to test the ValuesController.Get like this –

[Fact]
public async Task TestGet()
{
    // Arrange
    var controller = new ValuesController();

    // Act
    IActionResult actionResult = await controller.Get();

    // Assert
    Assert.NotNull(actionResult);

    OkObjectResult result = actionResult as OkObjectResult;

    Assert.NotNull(result);

    List<string> messages = result.Value as List<string>;

    Assert.Equal(2, messages.Count);
    Assert.Equal("value1", messages[0]);
    Assert.Equal("value2", messages[1]);
}

Note here how I expect the result to be an OkObjectResult

OkObjectResult result = actionResult as OkObjectResult;

And here where I cast the result.Value as List, the type I sent from the controller. No deserializing from json, strings or byte arrays needed!

List<string> messages = result.Value as List<string>;

Now it is simple to perform the appropriate asserts.

Here is another example, this time testing the ValuesController.Post().

[Fact]
public async Task TestPost()
{
    // Arrange
    var controller = new ValuesController();

    // Act
    IActionResult actionResult = await controller.Post("Some value");

    // Assert
    Assert.NotNull(actionResult);
    CreatedResult result = actionResult as CreatedResult;

    Assert.NotNull(result);
    Assert.Equal(201, result.StatusCode);
} 

You can also test a result for NotFoundResult, OkResult, UnauthorizedResult, etc.

Full source code available here.