What are the differences in performance when using querys with EF vs ADO

Question

What is the difference in performance when using the basic query methods SqlQuery<TElement> and ExecuteSqlCommand of Entityframework in relation to directly using the ADO.NET?

If there is considerable difference in performance, this is due to the data processing performed in the application by Entityframework before accessing the database or the Entityframework also causes impacts on the database?

Using ADO.NET to perform a select

using (SqlConnection connection = new SqlConnection("connectionString"))
{
    using (SqlCommand command = new SqlCommand("SELECT * FROM TABLE", connection))
    {
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            ///...
        }
    }
}

Using Entity Framework to perform a select with Sqlquery

context.Database.SqlQuery<Table>("SELECT * FROM TABLE");

Using ADO.NET to carry out an Insert statement

using (SqlConnection connection = new SqlConnection("connectionString"))
{
    using (SqlCommand command = new SqlCommand("INSERT INTO TABLE VALUES ('foo', 'ba', GETDATE())", connection))
    {
        int records = command.ExecuteNonQuery();        
    }
}

Using Entityframework to perform an Insert statement with Executesqlcommand

context.Database.ExecuteSqlCommand("INSERT INTO TABLE VALUES ('foo', 'ba', GETDATE())");

Answer 1

There is a old comparison shown the performance of some Orms with raw access. The Entity Framework improved greatly from there to here, especially EF Core, but there is still a overhead natural in it. One more benchmark.

Obviously, these comparisons depend a lot on what is being done, what strategy and specific settings are being used. Even though everyone wants a magic piece of information about which is faster, you can’t say anything about performance without doing specific tests in real-life situations.

The way used in EF code is unusual, in the background it is hardly using what EF has to offer, it does not use it as a full ORM, which can be a good and equalize performance. With equal strategies there won’t be much difference in performance, at least to assemble the query which is one of the costs that weigh in the performance.

When you choose to write your own darlings SQL will be the ones that determine the bulk of the performance. It is very common for the programmer to be able to write darlings better that a ORM can write from a C expression. Again, it will vary from case to case.

For those who opt for EF, an optimization strategy is certainly to abandon its normal syntax that abstracts SQL and use a query own. Of course this situation can prevent one of the advantages of the ORM which is to allow database abstraction (something that in general the person does not need but it makes premature generalization)

If it is to always use like this, it probably has little advantage in using EF, it is better to use a simpler ORM like the Dapper already mentioned in link above or even the pure ADO.NET.

Obviously other things can make a difference depending on the context applied. We don’t even know how the EF is set to say anything. Even knowing it will still be difficult to make definitive statements.

There’ll always be a question: you are having performance problems or just want it to be faster which is already fast enough?