locked
Enforce constraints using EF Core? RRS feed

  • Question

  • I have an ASP.NET Core application using Entity Framework Core.  The app can write records to the database successfully.  The database has many data-consistency constraints built into the tables and I would like to enforce those at the application level also against input records sent from a client for writing to the database.  Having the application write a record that violates one of the constraints just produces HTTP status code 500, Internal Server Failure, which obviously is not very informative.  It would be possible of course to write code in the application that checks for all the constraints but that's major duplication of effort.  Is there a way to have EF Core retrieve the check constraints in a manner that they can be used directly without re-implementing them in the code?  I haven't been able to find anything about this but I know EF Core has some knowledge of constraints in its model classes.

    Monday, May 6, 2019 7:55 PM

Answers

  • The database has many data-consistency constraints built into the tables and I would like to enforce those at the application level also against input records sent from a client for writing to the database. 

    IMHO, it should not be that developer needs to do some kind of constraint validation.

    I haven't been able to find anything about this but I know EF Core has some knowledge of constraints in its model classes.

    The developer should know what the constrains are,  and through code persistence of objects/records to the database based on the known constraints,  the constrains are adhered to for persistence. This is DBA 101 that any developer should know about the basics of database administration using a relational database such as MS SQL Server or any other relational database that is being used,  becuase EF is only adhering to  the constraints that have been created at the table level for tables in the database.

    This is the problem with the migration stuff and an ORM doing the basic DBA stuff for the developer when the developer should know where the buck stops is at the database and not what is happening with what the ORM is doing acting like a DBA tool.  

    Having the application write a record that violates one of the constraints just produces HTTP status code 500, Internal Server Failure, which obviously is not very informative.

    What it really means is that a .NET exception was thrown, there is no error handling in the code, the exception was swallowed by the Web server and the Web server through the HTTP 500 error.

     The bottom line is that you need global exception handling in the code where in code that throws an exception in the MVC project or any project the MVC references  is caught by the GEH and logged so that you can review the exception. It also means you don't need a try/catch anywhere in in code, becuase the GEH catches all unhandled exceptions.

    I am using Serilog. 

    It works the same for all 2.x Core solutions

    https://ondrejbalas.com/using-serilog-with-asp-net-core-2-0/

    https://scottsauber.com/2017/04/03/adding-global-error-handling-and-logging-in-asp-net-core/

    GEH with logging in MVC Core 2.1.

    using Microsoft.AspNetCore;
    using Microsoft.AspNetCore.Hosting;
    using Serilog;
    
    namespace ProgMgmntCore2UserIdentity
    {
        public class Program
        {
            public static void Main(string[] args)
            {
                CreateWebHostBuilder(args).Build().Run();
            }
    
            public static IWebHostBuilder CreateWebHostBuilder(string[] args) =>
                WebHost.CreateDefaultBuilder(args)
                    .UseStartup<Startup>()
                    .UseSerilog();
        }
    }

    Follow Serilog setup and where the setup for  ExceptionHandle is being used to point to ErrorController

    using System;
    using System.IO;
    using Microsoft.AspNetCore.Builder;
    using Microsoft.AspNetCore.Hosting;
    using Microsoft.AspNetCore.Http;
    using Microsoft.AspNetCore.Identity;
    using Microsoft.AspNetCore.Mvc;
    using Microsoft.EntityFrameworkCore;
    using Microsoft.Extensions.Configuration;
    using Microsoft.Extensions.DependencyInjection;
    using Microsoft.Extensions.FileProviders;
    using Microsoft.Extensions.Logging;
    using ProgMgmntCore2UserIdentity.Data;
    using ProgMgmntCore2UserIdentity.Models;
    using ProgMgmntCore2UserIdentity.WebApi;
    using Serilog;
    
    namespace ProgMgmntCore2UserIdentity
    {
        public class Startup
        {
            public Startup(IConfiguration configuration)
            {
                Configuration = configuration;
                Log.Logger = new LoggerConfiguration().ReadFrom.Configuration(configuration).CreateLogger();
            }
    
            public IConfiguration Configuration { get; }
    
            // This method gets called by the runtime. Use this method to add services to the container.
            public void ConfigureServices(IServiceCollection services)
            {
                services.Configure<CookiePolicyOptions>(options =>
                {
                    // This lambda determines whether user consent for non-essential cookies is needed for a given request.
                    options.CheckConsentNeeded = context => true;
                    options.MinimumSameSitePolicy = SameSiteMode.None;
                });
    
                services.AddDbContext<ApplicationDbContext>(options =>
                    options.UseSqlServer(
                        Configuration.GetConnectionString("DefaultConnection")));
                services.AddDefaultIdentity<IdentityUser>()
                    .AddEntityFrameworkStores<ApplicationDbContext>();
    
    
                //Password Strength Setting
                services.Configure<IdentityOptions>(options =>
                {
                    // Password settings
                    options.Password.RequireDigit = true;
                    options.Password.RequiredLength = 8;
                    options.Password.RequireNonAlphanumeric = false;
                    options.Password.RequireUppercase = true;
                    options.Password.RequireLowercase = false;
                    options.Password.RequiredUniqueChars = 6;
    
                    // Lockout settings
                    options.Lockout.DefaultLockoutTimeSpan = TimeSpan.FromMinutes(30);
                    options.Lockout.MaxFailedAccessAttempts = 10;
                    options.Lockout.AllowedForNewUsers = true;
    
                    // User settings
                    options.User.RequireUniqueEmail = true;
                });
    
                //Seting the Account Login page
                services.ConfigureApplicationCookie(options =>
                {
                    // Cookie settings
                    options.Cookie.HttpOnly = true;
                    options.ExpireTimeSpan = TimeSpan.FromMinutes(30);
                    options.LoginPath = "/Account/Login"; // If the LoginPath is not set here, ASP.NET Core will default to /Account/Login
                    options.LogoutPath = "/Account/Logout"; // If the LogoutPath is not set here, ASP.NET Core will default to /Account/Logout
                    options.AccessDeniedPath = "/Account/AccessDenied"; // If the AccessDeniedPath is not set here, ASP.NET Core will default to /Account/AccessDenied
                    options.SlidingExpiration = true;
                });
    
                services.AddSingleton<IFileProvider>(
                    new PhysicalFileProvider(
                        Path.Combine(Directory.GetCurrentDirectory(), "wwwroot")));
    
                //Models
                services.AddTransient<IProjectModel, ProjectModel>();
                services.AddTransient<ITaskModel, TaskModel>();
                services.AddTransient<IModelHelper, ModelHelper>();
                services.AddTransient<ICacheModel, CacheModel>();
    
                //WebApis
                services.AddTransient<IWebApi, WebApi.WebApi>();
    
                services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);
            }
    
            // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
            public void Configure(IApplicationBuilder app, IHostingEnvironment env, IServiceProvider services, ILoggerFactory loggerFactory)
            {
                loggerFactory.AddSerilog();
    
                app.UseExceptionHandler("/Error/Error");
               
                app.UseStaticFiles();
    
                app.UseAuthentication();
                
                app.UseMvc(routes =>
                {
                    routes.MapRoute(
                        name: "default",
                        template: "{controller=Home}/{action=Index}/{id?}");
                });
            }
        }
    }
    appsettings.json
    {
      "ConnectionStrings": {
        "DefaultConnection": "Server=DESKTOP-A86QNRQ\\SQLEXPRESS;Database=InventoryDB;User Id=SA;Password=xxxxxxxxx"
      },
      "Serilog": {
        "Using": ["Serilog.Sinks.Console"],
        "MinimumLevel": "Error",
        "WriteTo": [
          { "Name": "Console" },
          {
            "Name": "RollingFile",
            "Args": {
              "pathFormat": "c:\\logs\\log-{Date}.txt",
              "outputTemplate": "{Timestamp:yyyy-MM-dd HH:mm:ss.fff zzz} [{Level}] {Message}{NewLine}{Exception}"
            }
          }
        ],
        "Enrich": ["FromLogContext", "WithMachineName", "WithThreadId"],
        "Properties": {
          "Application": "My Application"
        },
        "Logging": {
          "LogLevel": {
            "Default": "Error"
          }
        },
        "AllowedHosts": "*"
      }
    }

    using System;
    using System.Diagnostics;
    using Microsoft.AspNetCore.Diagnostics;
    using Microsoft.AspNetCore.Mvc;
    using Microsoft.Extensions.Logging;
    using ProgMgmntCore2UserIdentity.Models;
    
    namespace ProgMgmntCore2UserIdentity.Controllers
    {
        public class ErrorController : Controller
        {
            private readonly ILogger _logger;
    
            public ErrorController(ILogger<ErrorController> logger)
            {
                _logger = logger;
            }
    
            public IActionResult Index()
            {
                return View();
            }
    
            [ResponseCache(Duration = 0, Location = ResponseCacheLocation.None, NoStore = true)]
            public IActionResult Error()
            {
                var exceptionFeature = HttpContext.Features.Get<IExceptionHandlerPathFeature>();
    
                // Get which route the exception occurred at
    
                string routeWhereExceptionOccurred = exceptionFeature.Path;
    
                // Get the exception that occurred
    
                var requestid = "";
    
                requestid = Activity.Current?.Id != null ? Activity.Current?.Id : HttpContext.TraceIdentifier;
                    
                Exception exceptionThatOccurred = exceptionFeature.Error;
    
                _logger.LogError("Request Id: " + requestid + " " + routeWhereExceptionOccurred + " " + exceptionThatOccurred);
    
                return View(new ErrorViewModel { RequestId = Activity.Current?.Id ?? HttpContext.TraceIdentifier });
            }
        }
    }

    namespace ProgMgmntCore2UserIdentity.Models
    {
        public class ErrorViewModel
        {
            public string RequestId { get; set; }
    
            public bool ShowRequestId => !string.IsNullOrEmpty(RequestId);
        }
    }

    Views/Shared/Error.cshtml changed.

    @model ProgMgmntCore2UserIdentity.Models.ErrorViewModel
    @{
        ViewData["Title"] = "Error";
    }
    
    <h1 class="text-danger">Error.</h1>
    <h2 class="text-danger">An error occurred while processing your request.</h2>
    
    @if (Model.ShowRequestId)
    {
        <p>
            <strong>Request ID:</strong> <code>@Model.RequestId</code>
        </p>
    }
    
    <<h2>Oh, No!</h2>
    
     <div class="alert alert-danger">
         Something bad happened and we have error monitors in place that will let
         us know that we have something to fix.
         If you want to contact us, refer to the
         following error id: <strong>@Model.RequestId</strong>
         
     </div>

     






    • Edited by DA924x Tuesday, May 7, 2019 8:08 PM
    • Marked as answer by John Boncek Wednesday, June 5, 2019 7:42 PM
    Monday, May 6, 2019 11:35 PM

All replies

  • The database has many data-consistency constraints built into the tables and I would like to enforce those at the application level also against input records sent from a client for writing to the database. 

    IMHO, it should not be that developer needs to do some kind of constraint validation.

    I haven't been able to find anything about this but I know EF Core has some knowledge of constraints in its model classes.

    The developer should know what the constrains are,  and through code persistence of objects/records to the database based on the known constraints,  the constrains are adhered to for persistence. This is DBA 101 that any developer should know about the basics of database administration using a relational database such as MS SQL Server or any other relational database that is being used,  becuase EF is only adhering to  the constraints that have been created at the table level for tables in the database.

    This is the problem with the migration stuff and an ORM doing the basic DBA stuff for the developer when the developer should know where the buck stops is at the database and not what is happening with what the ORM is doing acting like a DBA tool.  

    Having the application write a record that violates one of the constraints just produces HTTP status code 500, Internal Server Failure, which obviously is not very informative.

    What it really means is that a .NET exception was thrown, there is no error handling in the code, the exception was swallowed by the Web server and the Web server through the HTTP 500 error.

     The bottom line is that you need global exception handling in the code where in code that throws an exception in the MVC project or any project the MVC references  is caught by the GEH and logged so that you can review the exception. It also means you don't need a try/catch anywhere in in code, becuase the GEH catches all unhandled exceptions.

    I am using Serilog. 

    It works the same for all 2.x Core solutions

    https://ondrejbalas.com/using-serilog-with-asp-net-core-2-0/

    https://scottsauber.com/2017/04/03/adding-global-error-handling-and-logging-in-asp-net-core/

    GEH with logging in MVC Core 2.1.

    using Microsoft.AspNetCore;
    using Microsoft.AspNetCore.Hosting;
    using Serilog;
    
    namespace ProgMgmntCore2UserIdentity
    {
        public class Program
        {
            public static void Main(string[] args)
            {
                CreateWebHostBuilder(args).Build().Run();
            }
    
            public static IWebHostBuilder CreateWebHostBuilder(string[] args) =>
                WebHost.CreateDefaultBuilder(args)
                    .UseStartup<Startup>()
                    .UseSerilog();
        }
    }

    Follow Serilog setup and where the setup for  ExceptionHandle is being used to point to ErrorController

    using System;
    using System.IO;
    using Microsoft.AspNetCore.Builder;
    using Microsoft.AspNetCore.Hosting;
    using Microsoft.AspNetCore.Http;
    using Microsoft.AspNetCore.Identity;
    using Microsoft.AspNetCore.Mvc;
    using Microsoft.EntityFrameworkCore;
    using Microsoft.Extensions.Configuration;
    using Microsoft.Extensions.DependencyInjection;
    using Microsoft.Extensions.FileProviders;
    using Microsoft.Extensions.Logging;
    using ProgMgmntCore2UserIdentity.Data;
    using ProgMgmntCore2UserIdentity.Models;
    using ProgMgmntCore2UserIdentity.WebApi;
    using Serilog;
    
    namespace ProgMgmntCore2UserIdentity
    {
        public class Startup
        {
            public Startup(IConfiguration configuration)
            {
                Configuration = configuration;
                Log.Logger = new LoggerConfiguration().ReadFrom.Configuration(configuration).CreateLogger();
            }
    
            public IConfiguration Configuration { get; }
    
            // This method gets called by the runtime. Use this method to add services to the container.
            public void ConfigureServices(IServiceCollection services)
            {
                services.Configure<CookiePolicyOptions>(options =>
                {
                    // This lambda determines whether user consent for non-essential cookies is needed for a given request.
                    options.CheckConsentNeeded = context => true;
                    options.MinimumSameSitePolicy = SameSiteMode.None;
                });
    
                services.AddDbContext<ApplicationDbContext>(options =>
                    options.UseSqlServer(
                        Configuration.GetConnectionString("DefaultConnection")));
                services.AddDefaultIdentity<IdentityUser>()
                    .AddEntityFrameworkStores<ApplicationDbContext>();
    
    
                //Password Strength Setting
                services.Configure<IdentityOptions>(options =>
                {
                    // Password settings
                    options.Password.RequireDigit = true;
                    options.Password.RequiredLength = 8;
                    options.Password.RequireNonAlphanumeric = false;
                    options.Password.RequireUppercase = true;
                    options.Password.RequireLowercase = false;
                    options.Password.RequiredUniqueChars = 6;
    
                    // Lockout settings
                    options.Lockout.DefaultLockoutTimeSpan = TimeSpan.FromMinutes(30);
                    options.Lockout.MaxFailedAccessAttempts = 10;
                    options.Lockout.AllowedForNewUsers = true;
    
                    // User settings
                    options.User.RequireUniqueEmail = true;
                });
    
                //Seting the Account Login page
                services.ConfigureApplicationCookie(options =>
                {
                    // Cookie settings
                    options.Cookie.HttpOnly = true;
                    options.ExpireTimeSpan = TimeSpan.FromMinutes(30);
                    options.LoginPath = "/Account/Login"; // If the LoginPath is not set here, ASP.NET Core will default to /Account/Login
                    options.LogoutPath = "/Account/Logout"; // If the LogoutPath is not set here, ASP.NET Core will default to /Account/Logout
                    options.AccessDeniedPath = "/Account/AccessDenied"; // If the AccessDeniedPath is not set here, ASP.NET Core will default to /Account/AccessDenied
                    options.SlidingExpiration = true;
                });
    
                services.AddSingleton<IFileProvider>(
                    new PhysicalFileProvider(
                        Path.Combine(Directory.GetCurrentDirectory(), "wwwroot")));
    
                //Models
                services.AddTransient<IProjectModel, ProjectModel>();
                services.AddTransient<ITaskModel, TaskModel>();
                services.AddTransient<IModelHelper, ModelHelper>();
                services.AddTransient<ICacheModel, CacheModel>();
    
                //WebApis
                services.AddTransient<IWebApi, WebApi.WebApi>();
    
                services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);
            }
    
            // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
            public void Configure(IApplicationBuilder app, IHostingEnvironment env, IServiceProvider services, ILoggerFactory loggerFactory)
            {
                loggerFactory.AddSerilog();
    
                app.UseExceptionHandler("/Error/Error");
               
                app.UseStaticFiles();
    
                app.UseAuthentication();
                
                app.UseMvc(routes =>
                {
                    routes.MapRoute(
                        name: "default",
                        template: "{controller=Home}/{action=Index}/{id?}");
                });
            }
        }
    }
    appsettings.json
    {
      "ConnectionStrings": {
        "DefaultConnection": "Server=DESKTOP-A86QNRQ\\SQLEXPRESS;Database=InventoryDB;User Id=SA;Password=xxxxxxxxx"
      },
      "Serilog": {
        "Using": ["Serilog.Sinks.Console"],
        "MinimumLevel": "Error",
        "WriteTo": [
          { "Name": "Console" },
          {
            "Name": "RollingFile",
            "Args": {
              "pathFormat": "c:\\logs\\log-{Date}.txt",
              "outputTemplate": "{Timestamp:yyyy-MM-dd HH:mm:ss.fff zzz} [{Level}] {Message}{NewLine}{Exception}"
            }
          }
        ],
        "Enrich": ["FromLogContext", "WithMachineName", "WithThreadId"],
        "Properties": {
          "Application": "My Application"
        },
        "Logging": {
          "LogLevel": {
            "Default": "Error"
          }
        },
        "AllowedHosts": "*"
      }
    }

    using System;
    using System.Diagnostics;
    using Microsoft.AspNetCore.Diagnostics;
    using Microsoft.AspNetCore.Mvc;
    using Microsoft.Extensions.Logging;
    using ProgMgmntCore2UserIdentity.Models;
    
    namespace ProgMgmntCore2UserIdentity.Controllers
    {
        public class ErrorController : Controller
        {
            private readonly ILogger _logger;
    
            public ErrorController(ILogger<ErrorController> logger)
            {
                _logger = logger;
            }
    
            public IActionResult Index()
            {
                return View();
            }
    
            [ResponseCache(Duration = 0, Location = ResponseCacheLocation.None, NoStore = true)]
            public IActionResult Error()
            {
                var exceptionFeature = HttpContext.Features.Get<IExceptionHandlerPathFeature>();
    
                // Get which route the exception occurred at
    
                string routeWhereExceptionOccurred = exceptionFeature.Path;
    
                // Get the exception that occurred
    
                var requestid = "";
    
                requestid = Activity.Current?.Id != null ? Activity.Current?.Id : HttpContext.TraceIdentifier;
                    
                Exception exceptionThatOccurred = exceptionFeature.Error;
    
                _logger.LogError("Request Id: " + requestid + " " + routeWhereExceptionOccurred + " " + exceptionThatOccurred);
    
                return View(new ErrorViewModel { RequestId = Activity.Current?.Id ?? HttpContext.TraceIdentifier });
            }
        }
    }

    namespace ProgMgmntCore2UserIdentity.Models
    {
        public class ErrorViewModel
        {
            public string RequestId { get; set; }
    
            public bool ShowRequestId => !string.IsNullOrEmpty(RequestId);
        }
    }

    Views/Shared/Error.cshtml changed.

    @model ProgMgmntCore2UserIdentity.Models.ErrorViewModel
    @{
        ViewData["Title"] = "Error";
    }
    
    <h1 class="text-danger">Error.</h1>
    <h2 class="text-danger">An error occurred while processing your request.</h2>
    
    @if (Model.ShowRequestId)
    {
        <p>
            <strong>Request ID:</strong> <code>@Model.RequestId</code>
        </p>
    }
    
    <<h2>Oh, No!</h2>
    
     <div class="alert alert-danger">
         Something bad happened and we have error monitors in place that will let
         us know that we have something to fix.
         If you want to contact us, refer to the
         following error id: <strong>@Model.RequestId</strong>
         
     </div>

     






    • Edited by DA924x Tuesday, May 7, 2019 8:08 PM
    • Marked as answer by John Boncek Wednesday, June 5, 2019 7:42 PM
    Monday, May 6, 2019 11:35 PM
  • My app is in ASP.NET Core 2.0 MVC, which I perhaps should have been more specific on.  Thanks for your help but I haven't gotten much further.

    Following your posting and the one by Scott Sauber you referred to, as well as numerous other blogs and tutorials, I've tried to enable global exception handling in my app but the handler never gets control.  Here's one version of what I've tried adding.

    To Startup.Configure:

                app.UseExceptionHandler("/Home/Error");

    A new class HomeController, which begins like this:

        public class HomeController : Controller
        {
            public IActionResult Error()
            {
                var ExceptionFeature = HttpContext.Features.Get<IExceptionHandlerPathFeature>();
    ...

    Is there something else I need to do to hook this up to actually get called when an exception occurs?  The sources I've read don't say so but are mostly targeted at ASP.NET Core 2.2.  Referring to some (less common) content about 2.0, here is another attempt:

    To Startup.Configure:

                app.UseExceptionHandler("/Error");

    A new class ErrorController, which begins like this:

        [Route("[controller]")]
        public class ErrorController : Controller
        {
            [Route("")]
            public IActionResult Get()
            {
    ...

    Neither of these ever gets control when an exception occurs.  What am I missing?

    As may be painfully obvious, I am very new to all this.  Again, thanks for your help.

    Friday, May 24, 2019 3:51 PM
  •     app.UseExceptionHandler("/Home/Error");

    It should be Error/Error pointing to the 'Errorcontroller' and the action method 'Error' in the Errorcontroller. 

    You should be following the link I gave you to the 'T' along with some adjustments I made in the code I presented in the Error() action method.

    Friday, May 24, 2019 9:41 PM