How to intercept a SELECT query

Question

I am exploring Entity Framework 7 and I would like to know if there is a way to intercept a "SELECT" query. Every time an entity is created, updated or deleted I stamp the entity with the current date and time.

SELECT * 
FROM MyTable
WHERE DeletedOn IS NOT NULL

I would like all my SELECT queries to exclude deleted data (see WHERE clause above). Is there a way to do that using Entity Framework 7?

I would use soft deletes in stead, i.e. a bit field `Deleted`. If it is set to true, the update datetime will show when this happened. See https://github.com/rowanmiller/Demo-TechEd2014 for a complex, but complete solution. — Gert Arnold, Jul 24 '15 at 23:04
And of course they didn't implement command interceptors yet in EF 7. I'm starting to hate this half-baked version even if its still a pre-release. Still, I think it's better to follow the standard soft delete approach, but I don't know how to intercept EF7 commands (yet). — Gert Arnold, Jul 24 '15 at 23:17
The where clause you provided would only result in any data that has been deleted, unless I am reading it incorrectly? Did you mean to use Is Null? — DDiVita, Jul 27 '15 at 16:02
Does this answer your question? [Filter all queries (trying to achieve soft delete)](https://stackoverflow.com/questions/45096799/filter-all-queries-trying-to-achieve-soft-delete) — Roland Pihlakas, Apr 28 '20 at 18:33

DDiVita · Answer 1 · 2015-07-27T15:59:08.193

3

I am not sure what your underlying infrastructure looks like and if you have any abstraction between your application and Entity Framework. Let's assume you are working with DbSet<T> you could write an extension method to exclude data that has been deleted.

        public class BaseEntity
        {
                public DateTime? DeletedOn { get; set; }
        }
        public static class EfExtensions
            {
                public static IQueryable<T> ExcludeDeleted<T>(this IDbSet<T> dbSet) 
                    where T : BaseEntity
                {
                    return dbSet.Where(e => e.DeletedOn == null);
                }
            }
      //Usage
      context.Set<BaseEntity>().ExcludeDeleted().Where(...additional where clause).

edited Jul 27 '15 at 15:59

answered Jul 25 '15 at 00:52

DDiVita

4,225
5
63
117

That's not really what I am asking for. I asked for a way to always include DeletedOn == Null in my queries to prevent soft deleted row to show up in the results. – Martin Jul 27 '15 at 14:55
@Martin, I misunderstood what you were asking. Instead of intercepting the SQL, why not include that filter in your underlying infrastructure. Have you had a look at using the Specification Pattern? What does your infrastructure look like? Do you have any extra abstraction between EF and your application? – DDiVita Jul 27 '15 at 15:35
@Martin, I updated my answer with a possible solution. – DDiVita Jul 27 '15 at 15:50

score 0 · Answer 2 · answered May 30 '18 at 08:53

I have somewhat same issue. I'm trying to intercept read queries like; select, where etc in order to look into the returned result set. In EF Core you don't have an equivalent to override SaveChanges for read queries, unfortunately.

You can however, still i Entity Framework Core, hook into commandExecuting and commandExecuted, by using

 var listener = _context.GetService<DiagnosticSource>();
(listener as DiagnosticListener).SubscribeWithAdapter(new CommandListener());

and creating a class with following two methods

   public class CommandListener
    {
        [DiagnosticName("Microsoft.EntityFrameworkCore.Database.Command.CommandExecuting")]
        public void OnCommandExecuting(DbCommand command, DbCommandMethod executeMethod, Guid commandId, Guid connectionId, bool async, DateTimeOffset startTime)
        {
            //do stuff. 
        }

        [DiagnosticName("Microsoft.EntityFrameworkCore.Database.Command.CommandExecuted")]
        public void OnCommandExecuted(object result, bool async)
        {
           //do stuff. 
        }
    }

However these are high lewel interceptors and hence you won't be able to view the returned result set (making it useless in your case).

I recommend two things, first go to and cast a vote on the implementation of "Hooks to intercept and modify queries on the fly at high and low level" at: https://data.uservoice.com/forums/72025-entity-framework-core-feature-suggestions/suggestions/1051569-hooks-to-intercept-and-modify-queries-on-the-fly-a

Second you can use PostSharp (a commercial product) by using interceptors like; LocationInterceptionAspect on properties or OnMethodBoundaryAspect for methods.

How to intercept a SELECT query

2 Answers2