Does raw SQL run faster than the equivalent LINQ methods?

Question

I have this method that returns the Count of rows with the given search criteria:

public int HistoryCount(Guid id, string tableName)
{
    int sqlCount = context.Database.SqlQuery<int>("SELECT COUNT(*) FROM 
        COMMON.TbHistoryLog WHERE ObjectId = '" + id + "' 
        AND TableName = '" + tableName + "'").First();
    FuncHistory = x => x.ObjectId == id && x.TableName == tableName;
    return AuditHelper.HistoryCount(context.TbHistoryLog, FuncHistory);
}

and this is AuditHelper.HistoryCount method:

public static int HistoryCount<TSet>(DbSet<TSet> set, Func<TSet, bool> predict) where TSet : class
{
    var auditCount = set.Count(predict);
    return auditCount;
}

I was running into long querying time when executing AuditHelper.HistoryCount method, and then I tried to run the same query (I guess) using raw SQL, which returned the result immediately.

Is there something wrong with my implementation or raw SQL is faster than the equivalent LINQ methods?

Why don't you run a profiler to check the generated query. That way you can compare the raw sql with the query generated by EF. — Harsh, Aug 10 '17 at 12:43
The raw SQL and LINQ queries you've shown are not equivalent though. The LINQ query most likely translates to parameterized SQL, while the sample SQL query is using embedded constant values. If you convert it to using parameters, you'd probably get the same execution time as LINQ, because what you are explaining sounds like parameter sniffing problem. — Ivan Stoev, Aug 10 '17 at 12:43
By the way, if the `tableName` variable comes from a user input, you are not protected against SQL Injection. — fharreau, Aug 10 '17 at 12:59
How is performance if you use `context.TbHistoryLog.Where(x => x.ObjectId == id && x.TableName == tableName).Count()` instead? — mjwills, Aug 10 '17 at 13:22

score 3 · Accepted Answer · answered Aug 10 '17 at 18:06

Because you are using Count(Func<>) in your helper function, you are calling Enumerable.Count() which means you are pulling all records from the database and then processing the predicate and count locally. You need to use Expression<Func<>> to use Queryable.Count() that will be processed by the SQL server:

public static int HistoryCount<TSet>(DbSet<TSet> set, Expression<Func<TSet, bool>> predict) where TSet : class {
    var auditCount = set.Count(predict);
    return auditCount;
}

Then code it like so:

return AuditHelper.HistoryCount(context.TbHistoryLog, x => x.ObjectId == id && x.TableName == tableName);

Or declare FuncHistory to be Expression:

Expression<Func<TBHistoryLog, bool>> FuncHistory = x => x.ObjectId == id && x.TableName == tableName;

Does raw SQL run faster than the equivalent LINQ methods?

1 Answers1