Database Set up for Asp.NET API with SQL Server and Entity Framework



  1. API Route Versioning
  2. Configuration Management
  3. Secret Management
  4. Monitoring
  5. Database
  6. Documentation
  7. CORS
  8. Request Validation
  9. Global Exception Handling
  10. URL Rewriting
  11. Deploy .NET API to Azure App Service



  • Replaceable: you’d be able to replace your ORM at anytime in future with minimal/no change to the rest of your application
  • Scalable: to allow your code-base scale/grow in size as you add more and more tables/entities while maintaining a predictable code organisation
  • Performant: to have patterns in place to create an acceptable level of query performance.

End Game


  • NonQuery
  • Procedure
  • Query
  • QueryScalar
  • TableValuedFunction

Unit of Work


1. Start Up

  • Configure ConnectionString: which in this case I am reading it from the secret store. Refer to my post here on how to Manage Secrets in Asp.Net 5.0.
  • Lazy Loading: Lazy loading is configured so you would fetch what you need from your database in terms of related entities as opposed to fetching unwanted records.
  • Sensitive Data Logging: Which you would enable it only in Dev environments.
  • Enable Service Provider Caching: This will enable the caching of internal service providers, and it should only be Disabled in Dev/Test environments for testing purposes, which otherwise will have considerable negative impact on performance.
  • AppLoggerFactory: can be configured to log warnings coming out of Entity Framework, which will cover shortly.

2. DbContext

3. Entities

public virtual ICollection<Feature> Features { get; set; }

4. Migrations

dotnet-ef migrations --startup-project ..\Api\Api.csproj add add-new-migration --verbose
  • You can waste a log of time here! Make sure when you run the above command, you are cmd is at the root of DataAccess layer, with DBContext file right beside it.
  • Also make sure you have the — verbose flag enabled, as it highlights the underlying issues. If you don’t include that flag, you can go round round round with a vague generic error without knowing what the actual problem is.
  • Make sure you include the — startup-project flag, as it points to the API which is consuming the database context. Without that, it will fail as well.
dotnet-ef database --startup-project ..\Api\Api.csproj update
dotnet-ef database --startup-project ..\Api\Api.csproj update specificMigrationName

Automated Database Update

5. Dependency Injection

services.AddTransient<IRespository<Guid, DataAccess.Models.Customer>, CustomerRepository>();

Pellerex Foundation: For Your Next Enterprise Software



Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store