Can I modify the sql generated by Entity Framework before it's executed?

Question

I have a multi-tenant database that returns vastly different numbers of rows depending on which tenant is being queried. Lately we are experiencing a parameter sniffing problem where Entity Framework (EF) queries executed against one tenant (TenantID = 1) take much longer than the same query against another tenant (TenantID = 2). I've done some research and determined that EF doesn't support query hints (see this question) that would allow me to force the query to recompile each time. Now I'm wondering if I can intercept the Sql query that is generated by EF and manually append "OPTION (OPTIMIZE FOR UNKNOWN)" before it is executed. Is this possible? Is EF pluggable such that I can modify the generated Sql before it is executed? Are there any examples of how to do this?

Answer 1

Have you tried working around the problem? You may be able to force EF to not use a param, e.g.:

var q = Context.Tenants.Where(t => t.TenantID == tenantId);

...will use a param, but:

var r = Context.Tenants.Where(t => t.TenantID == 1);

...won't, and I'll bet that:

var s = Context.Tenants.Where("it.TenantID = 1");

...won't, either.