How to modify query in EF Core 2.0, before it goes to the SQL Server?

Question

I have this need for Persian language, where a query should first be sanitized/normalized from the point of characters.

Persian "ی" should be used instead of Arabic "ي".

In EF 6, I had an interceptor that would implement IDbCommandInterceptor and would change the raw SQL query using this simple line of code:

        command.CommandText = command.CommandText.SafePersianEncode();

This was hugely beneficial, reducing cost, increasing consistency, boosting quality, and preventing data cleansing requirements.

As we're migrating to EF Core 2.0, we realized that interceptors are not there, and it seems that they won't be implemented in future versions too.

Since this is a very legitimate requirement, and the capability to intercept a flow is a well-known pattern (like Angular interceptors to modify ALL HTTP requests/responses in one place), and from the point of architecture it helps managing cross-cutting concerns in a neat way, what should we do now?

Is there a global way (or anyway at all) that we can modify RAW SQL query before it goes to SQL Server in EF Core 2.0?

Answer 1

take a look at this

https://blogs.msdn.microsoft.com/dotnet/2016/09/29/implementing-seeding-custom-conventions-and-interceptors-in-ef-core-1-0/

you can override save changes to detect,modify changes before saving to database

Example:

 public override int SaveChanges(bool acceptAllChangesOnSuccess)
{
    ChangeTracker.DetectChanges();

    foreach (var entry in ChangeTracker.Entries().Where(e => e.State == EntityState.Added))
    {
        //modify entry.Entity here
    }

    ChangeTracker.AutoDetectChangesEnabled = false;
    var result = base.SaveChanges(acceptAllChangesOnSuccess);
    ChangeTracker.AutoDetectChangesEnabled = true;

    return result;
}

EDIT: Simple example to change all modified string properties

public override int SaveChanges(bool acceptAllChangesOnSuccess)
        {
            ChangeTracker.DetectChanges();

            foreach (var entry in ChangeTracker.Entries()
                .Where(e => e.State == EntityState.Added ||
                     e.State == EntityState.Modified))
            {
                //modify entry.Entity here
                foreach (var prop in entry.Properties)
                {
                    if ((entry.State == EntityState.Added ||
                prop.IsModified) && prop.OriginalValue is string)
                        prop.CurrentValue = prop.CurrentValue + "edited";
                }
            }

            ChangeTracker.AutoDetectChangesEnabled = false;
            var result = base.SaveChanges(acceptAllChangesOnSuccess);
            ChangeTracker.AutoDetectChangesEnabled = true;

            return result;
        }

this example will change any string property on add or edit to xxedited and save to database you can easily build custom rules here based on old value ,property type or accessing the entity itself. and so on