Data Search with Entity Framework

Question

I have a project in Asp.Net MVC where I need to work with a large volume of data. To get them from the Database (Microsoft SQL Server) using Entity Framework 4 I know basically three ways:

first form:

var resultado = from p in db.Pessoa
                where p.DataNasc > x
                select new { Id = p.PessoaId, Nome = p.Nome };

second form:

var cmd = string.format(@"SELECT p.PessoaId, p.Nome
                          FROM Pessoa p
                          WHERE p.DataNasc > {0}", x);

var objectContext = ((IObjectContextAdapter)db).ObjectContext;
var resultado = objectContext.ExecuteStoreQuery<PessoaCustom>(cmd).ToList<PessoaCustom>();

3rd form:

var resultado = db.Pessoa.Where(p => p.DataNasc > x).Select(p => new {Id = p.PessoaId, Nome = p.Nome});

I would like, together with the name of each form, an answer that address what is the most appropriate way to obtain a large volume of data taking into account the memory usage RAM, processing and the query sent to the bank (because sometimes the Entity builds querys a lot complex for simple instructions that end up consuming more database server processing).

Answer 1

Jedais, I see no point in trying to compare Raw SQL (form 2) and Linq (Query - form 1, Method - form 2).

I would use Raw SQL only in situations where winning a few "ms" results in a big gain, that is, in those few queries ("~5%") that account for "~95%" of the program.

Form 1 basically works as a syntactic sugar of the form 3, this will present only a small overhead at the time of the compilation, however both are identical.

Form 2, I advise you to use:

var query = @"SELECT PessoaId, Nome FROM Pessoa WHERE DataNasc > @p0"
var resultado = from p in db.Pessoa.SqlQuery<PessoaModel>(query, x).ToList();

The problem in this way is that it is not possible to work with dynamic types, so you will need to return the Entity itself (default), or some kind of complex or primitive... an outline solution would be to use a Dynamic type like Expandoobject.

Finally, we have a solution that is the middle ground, Entity SQL... in this case I advise you to read the following material, since Entoty SQL is a language completely apart, and the same differs in some important points of TSQL and PLSQL.

https://msdn.microsoft.com/library/bb399560(v=vs.100). aspx

Answer 2

The Entity Framework is a "robust" framework that deals with several factors for you, such as Lazy Loading, Change Detection, Proxies, among others. If you disable these features you will get a great performance gain.

For example, your disabling query would be like this:

var db = new dbContext();
db.Configuration.AutoDetectChangesEnabled = false;//verifica mudanças no objeto
db.Configuration.EnsureTransactionsForFunctionsAndCommands = false;//define o valor que determina se as funções e comandos SQL deve sempre ser executada em uma transação. EF6
db.Configuration.LazyLoadingEnabled = false;//Desabilita o LazyLoading
db.Configuration.ProxyCreationEnabled = false;//Desabilita o proxy
db.Configuration.UseDatabaseNullSemantics = false;
db.Configuration.ValidateOnSaveEnabled = false;

var resultado = db.Pessoa.AsNoTracking().Where(p => p.DataNasc > x).Select(p => new {Id = p.PessoaId, Nome = p.Nome});

Another factor that influences is the version you are using. the EF today is already in version 6, you are using the 4. It has been improved in many ways, performance was one of them.

Another option would be to use other Frameworks only for searches, such as Dapper. This would give you a great performance gain, even more with the outdated version of EF.

And finally, if you want the fastest (and most laborious) way you can opt for pure ADO.

I’ll leave some links for better understanding.

• Improving Performance with EF
• 10 Tips for better performance with EF
• EF Performance Optimization
• Good Pratices with EF