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?
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;
}
}