Add trigger support in EF Core.

[QianYC]

Background

We use EF Core in our web api service, and it works fine for most of the CRUD scenarios. However, due to the complexity of business logic, we also have many triggers and procedures in the database. EF Core doesn't have support for triggers and procedures, so we have to write sql scripts and run those scripts when deploying the service. This approach has the following pain points:

• Writing SQL scripts is bug-prone, especially when the business logic is complicated and you work in a large team where people skills are heterogeneous.
• Lack of logging. It's hard to log some useful traces in the SQL code.
• Poor accessibility. Given an entity class, it's hard to tell whether it has any related trigger or not. You have to search in your code base, but the fact is real business always has a giant repo, so the search process is also painful.

Solution

I propose we implement 2 types of trigger: in-memory trigger and persistent trigger. Here's the description of these 2 triggers.

In-memory Trigger

In-memory trigger doesn't necessarily exist in the database, it only lies in the DAO layer. We can achieve this by modifying the DbContext.SaveChanges() method, when saving an entity, the DbContext also searches for related triggers and execute them. In-memory trigger can support all kinds of database provider.

Persistent Trigger

Persistent trigger in the contrast, exists in the database. The EF Core generates corresponding DDL statements to create the triggers in the database just as it does for the Entity class. Persistent trigger only runs on database provider that supports triggers.

Summary

This solution can solve the above 3 paint points:

• By writing c# code instead of SQL code, wecan leverage the compiler to detect any compilation error.
• We can easily add logging by modifying the DbContext.SaveChanges() method.
• Now that wedefine the trigger as a c# class, the editor can show us the entity class's references, making it much more convenient to do code review.

[roji]

@QianYC it's very unlikely that EF could ever be able to replace writing SQL in database triggers. Trigger definitions use fully procedural language constructs, including flow control, exception handling; aside from these languages being very different across databases (full T-SQL on SQL Server, pl/pgsql on PostgreSQL...), EF simply cannot provide an entire .NET-based way which would be translatable to a full SQL script. If nothing else, .NET's LINQ expression tree support - which we use to translate LINQ querides - only supports (limited) expressions, and not statements or procedural constructs.

We do have #10770 to track better support for managing triggers in migrations; but you would still have to specify the actual SQL yourself.

What you call "in-memory trigger" can already implemented by overriding SaveChangse and including arbitrary logic in it. Is there something missing here that you're requesting?

[QianYC]

@roji Yeah I agree that the "in-memory trigger" can be implemented in customized DbContext. I'm just curious how did EF evolve so far - I mean, how does EF generate SQL statements e.g. creating tables, queries and so on, for different databases? You mentioned that different databases have different dialects so the above functionalities are also difficult to implement. I wonder if the trigger support is totally undoable or does it require lots of effort so EF team doesn't plan to do so? Thanks!

[roji]

The DDL syntax for creating and managing tables and columns is declarative, and very restricted; even if it indeed varies across database to a certain extent, the differences aren't big: CREATE TABLE looks overall pretty similar.

On the other hand, the procedural language used to define a stored procedure (or trigger) are completely different. That's a full-fledged programming language with variables, loops, exception handling and many other constructs. It simply is a completely different type of exercise.