Data search with Entity Framework

Question

Data search with Entity Framework

Navigation

#1 by (3 votes)
#2 by (3 votes)

3

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 basically know three ways:

1st form:

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

2nd 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, along with the name of each form, a response that the most appropriate way to achieve a large volume of data taking into account the RAM usage , processing and the query sent to the bank (because sometimes Entity builds querys a lot for simple instructions that end up consuming more processing of the database server).

c# .net entity-framework

asked by anonymous 17.02.2016 / 12:34

2 answers

Socket- does not receive the message completely How to open another Activity from a Navigation Drawer?

score 3 · 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 a Raw SQL only in situations where gaining a few "ms" results in a large gain, that is, in the few queries ("~ 5%") that account for "~ 95%" of the program. / p>

Form 1 basically works as a syntactic sugar of form 3, it will only present a small overhead at compile time, but 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, or some complex or primitive type ... a contour 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 completely separate language, and the same differs in some important points of TSQL and PLSQL.

link

score 3 · Answer 2

Entity Framework is a "robust" Framework that treats various factors for you, such as Lazy Loading, Detection of Changes, Proxies, among others. If you disable these features you will have a great performance gain.

For example, your disabling query would look 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 Search-only Frameworks, such as Dapper . That would give you a great performance boost, even more with the outdated version of EF.

And finally, if you want the fastest (and most labor-intensive) you can opt for pure ADO.

I'll leave some reference links for a better understanding.