Entity Framework - Related Entity Perfomance

Question

i have an query to load related entity for an specific entity, but it´s take a 5 seconds to load data, what happened?

var rows = clientes.Select(c => new
        {
            c.Id,
            c.Nome,
            Telefone = String.Format("(0{0}) {1}", c.DDD, c.Telefone),
            c.Email,
            Veiculo = (from v in c.Veiculos select new { v.Id, v.Modelo, v.Chassi }),
        })
    .Skip(pageNumber > 1 ? qtdRows * (pageNumber - 1) : 0)
    .Take(qtdRows)
    .ToArray();

As an aside, I think you need an OrderBy clause for the Skip to reliably return the expected subset of entities. — Ian Nelson, Feb 17 '14 at 14:08
Well your `Veiculo` select is pulling down the *entire* table for *every* record, my guess is that's the problem. — James, Feb 17 '14 at 14:11
Is it first query to database in runtime? If so, you should mind the "warm up" problem of Entity Framework http://stackoverflow.com/questions/13250679/how-to-warm-up-entity-framework-when-does-it-get-cold — oxfn, Feb 17 '14 at 14:15
It's probably because it's an outer join. Or you should add indexes in the database. — Gert Arnold, Feb 17 '14 at 23:39

score 2 · Answer 1 · answered Feb 17 '14 at 14:20

2

It seems that you join two entities but not using filter to get Veiculos of current Clientes.

May be you should use something like

var rows = clientes.Select(c => new
        {
            c.Id,
            c.Nome,
            Telefone = String.Format("(0{0}) {1}", c.DDD, c.Telefone),
            c.Email,
            Veiculo = (from v in c.Veiculos *where v.ClientId == c.Id* select new { v.Id, v.Modelo, v.Chassi }),
        })
    .Skip(pageNumber > 1 ? qtdRows * (pageNumber - 1) : 0)
    .Take(qtdRows)
    .ToArray();

But, more consistent method is to add navigation property Veiculo to entity Client and lay joining of tables on Entity Framework.

answered Feb 17 '14 at 14:20

Kirill Bestemyanov

11,946
2
24
38

With where it´s down time from 5.60s to 4.68s, in the Client entity i have an navigation property like public virtual IList Veiculos { get; set; } – Leandro de Souza Feb 17 '14 at 14:38
I´m using Repository Pattern with NInject(DI), first, i´m getting the clientes, IList clientes = (from c in _clienteRepository.Get(c => c.CodigoDealer == dealer.Codigo).Include(v => v.Veiculos) select c).Include(b=>b.Beneficios).ToList();, if remove this line (from v in c.Veiculos *where v.ClientId == c.Id* select new { v.Id, v.Modelo, v.Chassi }), it´s very fast! – Leandro de Souza Feb 17 '14 at 14:53
The explicit `where` doesn't make sense here, `c.Veiculos` is a navigation property therefore the `where` is implied. – James Feb 17 '14 at 15:45
exact, in the original query i did´t wrote, but it´s strange, the query was optimized with where clause, so i don´t known why with navigation property the query is so slow... – Leandro de Souza Feb 17 '14 at 18:26
Could you check what sql requests is posted to Sql server and post it in your question? Use sql profiler. – Kirill Bestemyanov Feb 18 '14 at 06:27
I´m using VS2010 Professional Edition, so sql profiler isn't avaiable, but i did the traced with EFProf and see details... SELECT [Extent1].[ID_VEICULO] AS [ID_VEICULO], [Extent1].[ID_CLIENTE] AS [ID_CLIENTE], [Extent1].[Modelo] AS [Modelo], [Extent1].[Chassi] AS [Chassi] FROM [dbo].[TB_IR_VEICULO] AS [Extent1] WHERE [Extent1].[ID_CLIENTE] = 10 /* @EntityKeyValue1 */ And one alert that say, Select N + 1 is a data access anti-pattern... – Leandro de Souza Feb 18 '14 at 13:55

Entity Framework - Related Entity Perfomance

1 Answers1