SQL Server - Validate Column In Every Insert

c# entity-framework-core sql-server-2012

Question

I am writing a .net core 2.2 C#, code-first, api with a SQL Server backend. It was just requested to make the api a multi-tenant api. So, I added TenantId (INT NOT NULL) to all DB tables, QueryFilters, etc... Everything is working as expected.

I want to ensure I did not and no one in the future will forget to add the TenantId on all inserts. TenantId will always be a value greater than zero. Is there a way in SQL Server to ensure that every insert on every table has a TenantId value that is greater than zero?

1
0
1/9/2019 3:31:45 PM

Accepted Answer

First, it seems that TenantId should be a ForeignKey to the table Tenant. If yes then it definitely cannot be zero or less, else :

Add CHECK Constraint on the column

CREATE TABLE your_table (
    TenantId INT NOT NULL CHECK(TenantId > 0)
    ...
)

And if column is NOT NULL then it should be in every insert else exception will throw

Cannot insert the value NULL into column 'TenantId', table 'dbo.your_table'; column does not allow nulls. INSERT fails.

Then, in your C# code use Data Annotations attribute

[Range(1, Int32.MaxValue)]

or create own like: [MinValue(1)]

To "ensure that every insert on every table has a TenantId value that is greater than zero" you can also use constructor with private setter

public class Model
{
   public Model(int name, int tenantId)
   {
      Name = name;
      if(tenantId <= 0)
      {
          throw new Exception();
      }
      TenantId = tenantId;
   }

   public string Name { get; private set; }
   public int TenantId { get; private set; }
}

or something like this property in your model

public int TenantId {
    get {
        return _tenantId;
    }
    set {
        if (value <= 0) throw new Exception();
        _tenantId = value;
    }
}
1
1/9/2019 4:14:12 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow