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.

.Net Core Multiple Get Methods with the Action Method Selector Attribute

Full source code available here.

In .Net Core Web Api it is not possible for the routing mechanism to distinguish between the following action methods.

public string GetSomething(int id, int something)
and
public string GetAnother(int id, int another)

But with the help of an ActionMethodSelectorAttribute we can tell the methods apart.

Step 1
Add a new class called QueryStringConstraint and inherit from ActionMethodSelectorAttribute.

public class QueryStringConstraint : ActionMethodSelectorAttribute
{
	public override bool IsValidForRequest(RouteContext routeContext, ActionDescriptor action)
	{
		IList<ParameterDescriptor> methodParameters = action.Parameters;

		ICollection<string> queryStringKeys = routeContext.HttpContext.Request.Query.Keys.Select(q => q.ToLower()).ToList();
		IList<string> methodParamNames = methodParameters.Select(mp => mp.Name.ToLower()).ToList();

		foreach (var methodParameter in methodParameters)
		{
			if (methodParameter.ParameterType.Name.Contains("Nullable")) 
			{
				//check if the query string has a parameter that is not in the method params
				foreach(var q in queryStringKeys)
				{
					if (methodParamNames.All(mp => mp != q))
					{
						return false;
					}
				}

				if(queryStringKeys.All(q => q != methodParameter.Name.ToLower()))
				{
					continue;
				}
			}
			else if (queryStringKeys.All(q => q != methodParameter.Name.ToLower()))
			{
				return false;
			}
		}
		return true;
	}
}

Step 2
Add the QueryStringConstraint to the action methods that need to be distinguished by query string parameters.

[QueryStringConstraint] 
public string GetAnother(int id, int another)
{
	return $"GetAnother {id} {another}";
}

// http://localhost:62922/api/values/?id=1&something=22
[QueryStringConstraint] 
public string GetSomething(int id, int something)
{
	return $"GetSomething {id} {something}";
} 

Full source code available here.

.NET Core Web Api Routing

Full source code available here.

Routing in .NET Core Web Api (1.1 and 2) is a little different than in earlier versions.

I’ve written about this a few times, you can find those posts here.

In this post I’m going to show some examples of routing that might help you if you are having problems, especially if you want to have multiple GET methods. In a later article I will show how to use the action method selector to choose between actions methods with the same signature.

The code and comments are fairly self explanatory.

[HttpGet("api/Person")]
public class PersonController : Controller
{
	// GET: api/Person
	[HttpGet]
	public IEnumerable<string> Get()
	{
		return new string[] { "Dave Smith", "Edward Temple" };
	}

	// http://localhost:27624/api/person/1
	[HttpGet("{id}")]
	public string Get(int id)
	{
		return $"Get by id:{id} - Dave Smith";
	}

	// http://localhost:62689/api/person/ByNumber/5
	[HttpGet("ByNumber/{number}")]
	public string GetByNumber(int number)
	{
		return $"Get by number:{number} - Tom Davis";
	}
	// http://localhost:62689/api/person/ByFirstName/Steve
	[HttpGet("ByFirstName")]
	public string GetByFristName(string firstName)
	{
		return $"Get by first name - {firstName} Smith";
	}

	// http://localhost:62689/api/person/ByLastName?lastname=Smith
	[HttpGet("ByLastName")]
	public string GetByLastName(string lastName)
	{
		return $"Get by last name - Dave {lastName}";
	}

	// http://localhost:62689/api/person/ByFirstAndLastName?firstname=Steve&lastname=Smith
	[HttpGet("ByFirstAndLastName")]
	public string GetByFirstAndLastName(string firstName, string lastName)
	{
		return $"Get by first and last name - {firstName} {lastName}";
	}
}

Here are some more examples with attribute routing and a query model.

[HttpGet("api/accounts/{AccountId}")]
public class AccountsController : Controller
{
	// http://localhost:62689/api/accounts/11
	public IActionResult Get(int accountId)
	{
		return Ok(accountId);
	}

	// http://localhost:62689/api/accounts/?accountId=11
	// http://localhost:62689/api/accounts/?accountId=11&AccountName=dave
	// http://localhost:62689/api/accounts/?accountId=11&AccountName=dave&managerid=15
	[HttpGet("/api/accounts/")]
	public IActionResult Get(QueryModel queryModel)
	{
		return Ok(queryModel);
	}

	// http://localhost:62689/api/accounts/11/manager/22
	[HttpGet("Manager/{ManagerId}")]
	public IActionResult Get(int accountId, int managerId)
	{
		return Ok($"accountId:{accountId}, managerId:{managerId}");
	}

	// http://localhost:62689/api/accounts/11/manager/22/role/33
	[HttpGet("/api/accounts/{AccountId}/Manager/{ManagerId}/Role/{RoleId}")]
	public IActionResult Get(int accountId, int managerId, int roleId)
	{
		return Ok($"accountId:{accountId}, managerId:{managerId}, roleId:{roleId}");
	}
}

public class QueryModel
{
	public int AccountId { get; set; }
	public string AccountName { get; set; }
	public int ManagerId { get; set; }
}

Full source code available here.

Fluent Validation in ASP.NET Core

Full source code available here.

I have written about Fluent Validation a couple of times. There is a new library available for .Net Core.

How to return to validation messages back to the caller is not immediately obvious. You might see the validators running, but the responses are missing! Here is how to solve that problem.

Step 1

Firstly add the package to your Web Api project.
Install-Package FluentValidation.AspNetCore.
If you are going to keep your validators in the Web Api project that is all you need to add.

But if you put the validators in a different project you need to add the FluentValidation package to that projecty
Install-Package FluentValidation.

Step 2

In startup.cs add –

public void ConfigureServices(IServiceCollection services)
{
    services.AddMvc(options =>
        {
            options.Filters.Add(typeof(ValidateModelAttribute));
        })
        .AddFluentValidation(fv => fv.RegisterValidatorsFromAssemblyContaining<PersonModelValidator>());
}

Note the line options.Filters.Add(typeof(ValidateModelAttribute)), we need to add that filter.

Step 3

Add the validation filter –

public class ValidateModelAttribute : ActionFilterAttribute
{	
    public override void OnActionExecuting(ActionExecutingContext context)	
    {	
        if (!context.ModelState.IsValid)	
        {	
            context.Result = new BadRequestObjectResult(context.ModelState);
        }
    }
}
Step 4

Add a model and a validator.

public class PersonModel
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

public class PersonModelValidator : AbstractValidator<PersonModel>
{
    public PersonModelValidator()
    {
        RuleFor(p => p.FirstName).NotEmpty();
        RuleFor(p => p.LastName).Length(5);
    }
}

Full source code available here.

Returning default values from a failed web request with Polly Fallbacks

Full source code available here.

In previous posts on Polly I showed how to use a simple retry, and a retry with a delegate that is called before the request is retried.

In this post I will show how use a Polly fallback policy, this allows a “fallback” or default value to be returned when both the request and retries fail.

The retry policy is similar to ones I have used before.

_httpRetrytPolicy = Policy.HandleResult<HttpResponseMessage>(
         r => r.StatusCode == HttpStatusCode.InternalServerError)
        .WaitAndRetryAsync(2, retryAttempt => TimeSpan.FromSeconds(retryAttempt));

The fallback policy returns a HttpResponseMessage with some cached values.

_httpRequestFallbackPolicy = Policy.HandleResult<HttpResponseMessage>(
         r => r.StatusCode == HttpStatusCode.InternalServerError)
         .FallbackAsync(new HttpResponseMessage(HttpStatusCode.OK)
         {
             Content = new ObjectContent(_cachedList.GetType(), _cachedList, new JsonMediaTypeFormatter())
         });

Using the polices is almost the same as in my earlier posts, the httpClient.GetAsync is executed by the retry policy. The retry policy is executed by the fallback policy. The Polly documentation refers to this as a “‘Russian-Doll’ or ‘onion-skin-layers’ model”.

HttpResponseMessage httpResponse =  
   await _httpRequestFallbackPolicy.ExecuteAsync( () 
   =>  _httpRetrytPolicy.ExecuteAsync(()
   =>  httpClient.GetAsync(requestEndpoint)));

The above call inside a call can also be done with a Polly policy wrap, I will demonstrate that in later post.

If the request succeeds the first time, the response is assigned to httpResponse. If the request fails, the retry policy kicks in and retries up to three times. If all of those fail, the fallback policy returns a httpResponse containing the cached list.

Full source code available here.

Re-authorization and onRetry with Polly

Full source code available here.

In a previous post I showed how to use Polly to make a simple call, retrying in the event of failure. In another I showed how to let a request fail and process the response.

In this post I will show a retry where a delegate is called as part of the retry. A common use case for this is reauthorizing after an Unauthorized response.

The difference between this policy and the ones in my previous posts is very small.

public class ValuesController : ApiController
{
    readonly RetryPolicy<HttpResponseMessage> _httpRequestPolicy;
    private HttpClient _httpClient;
    public ValuesController()
    {
        _httpRequestPolicy = Policy.HandleResult<HttpResponseMessage>(
                r => r.StatusCode == HttpStatusCode.InternalServerError ||
                     r.StatusCode == HttpStatusCode.Unauthorized)
            .WaitAndRetryAsync(3, retryAttempt => TimeSpan.FromSeconds(retryAttempt), onRetry: (response, timespan) =>
            {
                if (response.Result.StatusCode == HttpStatusCode.Unauthorized)
                {
                    PerformReauthorization();
                }
            });
    }

The onRetry delegate is the new part –

onRetry: (response, timespan) =>
{
	if (response.Result.StatusCode == HttpStatusCode.Unauthorized)
	{
		PerformReauthorization();
	}
}

In my PerformReauthorization I create a new HttpClient and pass it the new authorization code. For simplicity I am passing a code in the cookie of my request, you could of course use Basic Authentication or any another technique.

private void PerformReauthorization()
{
    // here you would get a new token, call some other service, etc.
    _httpClient = GetHttpClient("GoodAuthCode"); // pass in the good auth token
}

The important thing to note is that my HttpClient instance is class level, meaning it can be accessed from PerformReauthorization.

The call to the web service remains the same.

HttpResponseMessage httpResponse = await _httpRequestPolicy.ExecuteAsync(() => _httpClient.GetAsync(requestEndpoint))

I’ll post of fallbacks soon, they allow a default value to be returned in the event of repeated failures.

For completeness here is GetHttpClient.

private HttpClient GetHttpClient(string authCookieValue)
{
    var cookieContainer = new CookieContainer();
    var handler = new HttpClientHandler() {CookieContainer = cookieContainer};
    cookieContainer.Add(new Uri("http://localhost"),new Cookie("Auth", authCookieValue));
    _httpClient = new HttpClient(handler);
    _httpClient.BaseAddress = new Uri(@"http://localhost:2629/api/");

    _httpClient.DefaultRequestHeaders.Accept.Clear();
    _httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
    return _httpClient;
}

Full source code available here.