Entity Framework and SQL Server OUTPUT clause

Question

I'd like to use SQL OUTPUT clause to keep history of the records on my database while I'm using Entity Framework. To achieve this, EF needs to generate the following example for a DELETE statement.

Delete From table1
output deleted.*, 'user name', getdate() into table1_hist
Where field = 1234;

The table table1_hist has the same columns as table1, with the addition of two columns to store the name of the user who did the action and when it happened. However, EF doesn't seem to have a way to support this SQL Server's clause, so I'm lost on how to implement that.

I looked at EF's source code, and the DELETE command is create inside a internal static method (GenerateDeleteSql in System.Data.Entity.SqlServer.SqlGen.DmlSqlGenerator class), so I can't extend the class to add the behavior I want. It looks like I'll have to rewrite the SQL Server provider based on the existing code, but that is something I'd like to avoid...

So, my question is if there's another option to do this (an extension, for example) or do I have to rewrite this provider?

Thank you.

Another option is to override DbContext.SaveChanges and intercept the deletes there. Changing code in an open source project is always very tricky. You will keep adapting future releases because I don't think it's going to be accepted as a supported feature in the code base. — Gert Arnold
– Gert Arnold, Commented Sep 17, 2013 at 6:57

podiluska · Accepted Answer · 2013-09-16 14:41:32Z

2

Have you considered either

Using Stored Procedures to encapsulate your data logic
A delete trigger to capture the data
Change Data Capture (Enterprise edition only)
not actually deleting the data - merely setting a flag in the data to mark it as deleted.

edited Sep 16, 2013 at 14:41

answered Sep 16, 2013 at 14:32

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fabio Over a year ago

Thanks for the answer. I'm analyzing it along with Gert's comment, to decide the best approach for me. I'll keep in touch

Collectives™ on Stack Overflow

Entity Framework and SQL Server OUTPUT clause

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related