0

I'm using a dbcontext linq query:

var list = context.MyTable.Where(x => x.IsValid).ToList();

The SqlProfiler shows this Sql Query:

SELECT * FROM [MyTable] WHERE IsValid = 1

The problem is that in this table I'm using a lot of sql indexes, and by default it uses the wrong index and query is taking a very long time. I need to add the index I have in the table into the query.

In other words how to get this query from linq?

SELECT * FROM [MyTable] WITH(INDEX(PK_MyIndexName)) WHERE IsValid = 1
Jon Egerton
  • 40,401
  • 11
  • 97
  • 129
user3018896
  • 99
  • 1
  • 1
  • 7
  • There is [this](http://stackoverflow.com/q/26761827/613130) one, using interceptors – xanatos Jun 30 '15 at 07:42
  • 3
    I'd rather want to find out what makes SS ignore this index and try fix that (maybe by `CREATE STATISTICS`). If you forget about adding this hint you could get into trouble if the query shape changes later. – Gert Arnold Jun 30 '15 at 09:20
  • 2
    SQL Server is probably capable of planning such a simple query. Maybe a scan really is the better choice here. sp_updatestats and retry. How many rows does the table have and how many are matched by IsValid = 1? – usr Jun 30 '15 at 09:23

0 Answers0