Interception not working as expected with Entity Framework 6

Question

I'm trying to follow the interception example shown here to get it working with EF 6 but running into a problem with the function RewriteFullTextQuery as shown in Figure 1. The interception seems to work but it does not actually execute the logic in the for loop of the RewriteFullTextQuery method because the cmd.Parameters.Count is always zero. Furthermore the cmd.CommandText property seems to be displaying the correct SQL query which I take as another piece of evidence that the interception is working correctly.

Figure 1: RewriteFullTextQuery Code Excerpt

 public static void RewriteFullTextQuery(DbCommand cmd)
    {
        string text = cmd.CommandText;
        for (int i = 0; i < cmd.Parameters.Count; i++)
        {
            DbParameter parameter = cmd.Parameters[i];
            if (parameter.DbType.In(DbType.String, DbType.AnsiString, DbType.StringFixedLength, DbType.AnsiStringFixedLength))
            {

The RewriteFullTextQuery function is being called by the ReaderExecuting function shown in Figure 2 which gives it the command argument that is causing all the trouble.

Figure 2: ReaderExecuting Function

public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        RewriteFullTextQuery(command);
    }

Even though my code isn't exactly the same as the example, the interception seems to be working so it is making me wonder what conditions is it that will populate the command to have a Parameters.Count of more than zero?

bubi · Answer 1 · 2016-09-22T20:32:12.297

1

It works only if you pass the parameter to the query as a variable. If you use a literal EF won't use parameters.
I mean, this won't generate any parameter

context.Notes.Where(_ => _.NoteText == "CompareValue").Count();

This will

string compareValue = "CompareValue";
context.Notes.Where(_ => _.NoteText == compareValue).Count();

edited Sep 22 '16 at 20:32

answered Sep 22 '16 at 18:33

bubi

6,414
3
28
45

1

I found that blog with the answer yesterday and posted my answer just before I noticed you changed your answer to exactly what I found. I would like to accept my answer because there was a solution in the blog that helped me out but will upvote your answer to show appreciation for your help. – methon.dagger Sep 22 '16 at 20:51

methon.dagger · Accepted Answer · 2016-09-22T20:53:40.127

1

It turns out that it is because of the way Entity Framework generates the SQL. If you pass in a string literal as your search value to your LINQ statement it does not generate a SQL that makes use of a parameter. But if you pass in your search value as a variable, it will generate the SQL that utilizes a parameter. A solution (for dynamic queries) and more details can be found on this blog.

edited Sep 22 '16 at 20:53

answered Sep 22 '16 at 20:39

methon.dagger

505
9
28

Interception not working as expected with Entity Framework 6

2 Answers2