Entity Framework + LINQ slow speed vs String Query Speed?
I inherited a C # / ASP.NET MVC / Entity Framework project with some slowness. There isn't much data in the DB, but the calls .Include()
were causing the slowdown.
However, I found something very strange. I have a 2k row table with numbers (5 columns). I have indexes on the columns that I am looking for.
By doing:
_entities.MyTable.Where(x=> x.Id1 == 4 && x.Id2 == 5).First()
it takes 1800ms on my development machine.
However, when I do this:
_entities.MyTable.Where("it.Id1 = 4 and it.Id2 = 5").First()
it takes 10ms.
What's the deal? I don't understand why the LINQ expression would be so slow.
source to share
Could it be that EF has to generate the SQL to make the where clause in the first example, and in the second the SQL is much easier to generate since it can just plug in the already provided SQL?
Ive found EF to be very slow at generating queries, in this case it seems unlikely as it is a fairly simple query in both cases.
Have you tried compiling the first query and running it multiple times to check that the execution time includes actually running the SQL and not just creating it?
source to share