We have a view that is very slow when you don’t use a filter on the name field. Everywhere in our stored procedures and (sub)queries we have this applied.

In our web applicatie we use Entity Framework. The business layer required the name filter, but the result always times out. The query looks like below.

-- simplified for clarity
SELECT [Extent1].[Name] as [Name]
FROM view as [Extent1]
WHERE [Extent1].[Name] = @p__linq__0

This is case of bad parameter sniffing. More about this in the References at the end of this post.

Reading this answer on stackoverflow made me look into the custom query execution against the Database object of your Context. And that solved the puzzle with a custom method instead of the generated IDbSet properties.

public IQueryable<viewRecordType> GetViewRecordsWhereNameIs(
string name) {
   // Danger! Sql injection possible
   var query = string.Format(
      "SELECT * FROM view WHERE name = '{0}'",
      name);
   return Database.SqlQuery<viewRecordType>(query).AsQueryable();
}

Please note the warning. Sql injection is possible when you don’t use parameters!

References

Entity Framework Performance and What You Can Do About It
How to Confuse the SQL Server Query Optimizer