How to change entry values for DbContext.SaveChanges()

Question

We are trying to implement a multi tenant architecture in our Web API based application. We are using RLS in SQL Server, and Subscription_Id is, what is given to each subscriber. We have set the default value for Subscription_Id in SQL Server, so while I am calling db.SaveChanges(), I just want to ignore the Subscription_Id going to the SQL Server from the API.
I tried setting the value of Subscription_Id in the SaveChanges() override method but got stuck here.

public override int SaveChanges()
{
    var objectType = selectedEntity.CurrentValues.ToObject();
    Guid value = new Guid("54E720FC-616B-44C6-8485-5F2185FD7B4C");
    PropertyInfo propertyInfo = 
    objectType.GetType().GetProperty("Subscription_Id");  


    ChangeTracker.Entries().FirstOrDefault()
       .CurrentValues.ToObject().GetType()
       .GetProperty("Subscription_Id")
       .SetValue(objectType, Convert.ChangeType(value, propertyInfo.PropertyType), null);

    return base.SaveChanges();
}

Why you want to change or override Subscription_Id ? It's a primary/unique key I think. — Basanta Matia, Jun 30 '17 at 06:13
No the subscription id is for subscriber so all the data related to the subscriber is saved along with subscriber id — Gunashekar, Jun 30 '17 at 06:45
Why you want to set the value of subscriptionId in the savechanges() ?? — Basanta Matia, Jun 30 '17 at 06:47
because i am using row level security in sql and i have set subscription id to check there and set the default value of the subscriptionid as the current context value , but the entity frame work is sending the subscriptionid as null , I just want to ignore the subscription id or to send the value on savechanges as all the tables will have that column — Gunashekar, Jun 30 '17 at 06:52

Diana · Accepted Answer · 2017-07-02T15:09:44.280

My advice is that you shouldn't modify your SaveChanges() code for this.

A recommended way of using RLS is making the TenantId columns transparent to your EF model and your code, so you don't need to define Tenant ID or navigation properties in your entities. This way you don't need to change your SaveChanges() code, or to explicitly manage and set Subscription_Id values anywhere in your code other that when opening the DB connection.

What you need to do is manually setting a default value constraint in the Subscription_Id columns in your database, with a default value based on the current session Subscription_Id parameter. The value will be set when inserting the records, and implicitly used to filter any subsequent queries and commands at database level.

In case of a new column:

ALTER TABLE SomeEntityTable ADD Subscription_Id nvarchar(128)
    DEFAULT CAST(SESSION_CONTEXT(N'UserId') AS nvarchar(128))

In case of an existing column:

ALTER TABLE SomeEntityTable 
    ADD DEFAULT CAST(SESSION_CONTEXT(N'UserId') AS nvarchar(128)
    FOR Subscription_Id

If the column had a previous different DEFAULT value it would be good to also delete its associated obsolete DEFAULT constraint. More info about updating default values in existing columns can be found here.

These columns should not be included in your model. You should not have properties for them in your entity classes. If you are using Database First you should make sure you exclude/ignore these columns when updating your model from your database.

How to do this if you are using EF Code First: you can manually include AlterColumn (or CreateColumn) instructions in a code migration after you generate it with Add-Migration. Do it for every entity table:

public override void Up()
{
    AlterColumn("dbo.SomeEntityTable", "Subscription_Id", 
        c => c.String(
            nullable: false, 
            maxLength: 128,
            defaultValueSql: "CAST(SESSION_CONTEXT(N'UserId') AS nvarchar(128))"));
}

(It would be good to add also a Down() method removing the column.)

Warning: Be careful when running this migration if you already have existing records in the tables with an empty Subscription_Id column value (or if you are adding a new Subscription_Id column to a table that already have records). The empty column will be filled with the value of the Subscription_Id in the connection that is executing the migration, which probably will be wrong (you probably don't want all the existing records to be associated to that specific subscription). In that case you may want to include explicit UPDATE instructions with the right Subscription_Id values in your Up() method, with the Sql() method. Something like this:

Sql("UPDATE SomeEntitiesTable SET Subscription_Id= '19bc9b0d-28dd-4510-bd5e-d6b6d445f511' WHERE Id IN (1, 2, 5)");

With Code First you should also remove the Subscription_Id properties from your model classes. If you can't, at least add explicit Ignore() instructions in your configuration code for the Subscription_Id columns, you don't want them in your EF mappings.

Note: I'm assuming here that you created a RLS policy in your DB that uses UserId parameter in SESSION_CONTEXT, and that your application code is setting that value when opening the DB connection, via a DbConnectionInterceptor or something similar.

This page contains more info.

"These columns should not be included in your model. You should not have properties for them in your entity classes. If you are using Database First you should make sure you exclude/ignore these columns when updating your model from your database. " How do i exclude the columns in data base first EF . I have already set the subscription id default value — Gunashekar, Jul 05 '17 at 06:43
I'm not quite familiar with Database First. From the comments [in this post](https://stackoverflow.com/questions/38445897/how-to-exclude-certain-columns-in-ef6-using-database-first) it seems that you can just remove the properties from the class model in the edmx and they will not be regenerated the next time you update your model, as the columns are still in the store model, so EF doesn't consider them as new columns to add to the class model. — Diana, Jul 05 '17 at 19:22

How to change entry values for DbContext.SaveChanges()

1 Answers1