LINQ query not translated correctly to the database

Question

I'm working on a MVC Web Site and as i'm newbie in mvc i'm facing some issues. This one is realy weird and have been bothering me for a while. I'm using LINQ to make my queries and some of then were realy, realy slow.

Then i opened SQL Server Profiler to look for the queries on a closer way. I just noted that my LINQ queries are being sent to the database without the where clause and that is making then to take a long time to be executed.

Now i'll post relevant part of code to see if u guys can get something wrong with that.

Model

[Table("tblSolicitacao")]
public partial class ChamadosViewModel
{
    [Key]        
    public int Nsu { get; set; }

    [Required(ErrorMessage = "*")]
    public int IdAutor { get; set; }

    [Required(ErrorMessage = "*")]
    [DisplayName("Assunto")]
    public string NomeDaSolicitacao { get; set; }

    [Required(ErrorMessage = "*")]
    [DisplayName("Descrição")]        
    public string Descricao { get; set; }

    [Required(ErrorMessage = "*")]
    [DisplayName("Grupo")]
    public string Grupo { get; set; }

    [Required(ErrorMessage = "*")]
    [DisplayName("Atividade")]
    public string Atividade { get; set; }

    [Required(ErrorMessage = "*")]
    [DisplayName("Prioridade")]
    public string Prioridade { get; set; }

    [Required(ErrorMessage = "*")]
    [DataType(DataType.DateTime, ErrorMessage = "Formato de Data Inválido")]        
    public Nullable<System.DateTime> DataPretendidaPeloAutor { get; set; }
    public Nullable<bool> AcompanharViaEmail { get; set; }
    public Nullable<int> IdAnalistaDesignado { get; set; }
    public string Complexidade { get; set; }
    public Nullable<System.DateTime> DataPrevistaPeloAnalista { get; set; }
    public Nullable<int> SubordinacaoDeSolicitacao { get; set; }
    public string COD_FORNECEDOR { get; set; }
    public Nullable<decimal> TOLERANCIA { get; set; }
    public string DESC_COND_PGTO { get; set; }
    public string FILIAL_FATURAR { get; set; }
    public string FILIAL_ENTREGA { get; set; }
    public string FILIAL_COBRANCA { get; set; }
    public string MOTIVO_COMPRA { get; set; }
    public Nullable<int> TIPO_SOLICITACAO { get; set; }
    public string FORMA_PGTO { get; set; }
    public string MOTIVOCHAMADO { get; set; }
    public Nullable<int> TEMPORESOLUCAO { get; set; }
    public string CodigoRateioFilial { get; set; }
    public string CodigoRateioCentroCusto { get; set; }
    public Nullable<System.DateTime> DataSolicitacao { get; set; }

    [NotMapped]
    public string NomeAutor { get; set; }
    [NotMapped]
    public string NomeAnalista { get; set; }

    public IEnumerable<genericoGruposViewModel> Grupos { get; set; }
    [NotMapped]
    public List<ListItem> Transitos { get; set; }
    public List<string> Complexidades { get; set; }
    public IEnumerable<genericoGrupoAtividadeViewModel> Atividades { get; set; }
    public IEnumerable<genericoPrioridadeViewModel> Prioridades { get; set; }
    public IEnumerable<ColaboradorViewModel> Usuarios { get; set; }

    [NotMapped]
    IEnumerable<ColaboradorViewModel> AnalistasDesignados { get; set; }
}

Controller

    ChamadosViewModel ChamadoDetalhe = new ChamadoDetalhe();

    ChamadoDetalhe = _dbHelpDesk.Chamados
                .ToList()
                .Where(x => x.Nsu == NumeroChamado)
                .Select(x => new ChamadosViewModel
                        {
                            Nsu = x.Nsu,
                            IdAutor = x.IdAutor,
                            NomeDaSolicitacao = x.NomeDaSolicitacao,
                            Descricao = x.Descricao,
                            Grupo = x.Grupo,
                            Atividade = x.Atividade,
                            Prioridade = x.Prioridade,
                            DataPretendidaPeloAutor = x.DataPretendidaPeloAutor,
                            AcompanharViaEmail = x.AcompanharViaEmail,
                            IdAnalistaDesignado = x.IdAnalistaDesignado,
                            Complexidade = x.Complexidade,
                            DataPrevistaPeloAnalista = x.DataPrevistaPeloAnalista,
                            SubordinacaoDeSolicitacao = x.SubordinacaoDeSolicitacao,
                            COD_FORNECEDOR = x.COD_FORNECEDOR,
                            TOLERANCIA = x.TOLERANCIA,
                            DESC_COND_PGTO = x.DESC_COND_PGTO,
                            FILIAL_FATURAR = x.FILIAL_FATURAR,
                            FILIAL_ENTREGA = x.FILIAL_ENTREGA,
                            FILIAL_COBRANCA = x.FILIAL_COBRANCA,
                            MOTIVO_COMPRA = x.MOTIVO_COMPRA,
                            TIPO_SOLICITACAO = x.TIPO_SOLICITACAO,
                            FORMA_PGTO = x.FORMA_PGTO,
                            MOTIVOCHAMADO = x.MOTIVOCHAMADO,
                            TEMPORESOLUCAO = x.TEMPORESOLUCAO,
                            CodigoRateioFilial = x.CodigoRateioFilial,
                            CodigoRateioCentroCusto = x.CodigoRateioCentroCusto,
                            DataSolicitacao = x.DataSolicitacao
                        }
                    )
                    .Single()

and thats the select that went to the database

    SELECT 
        [Extent1].[Nsu] AS [Nsu], 
        [Extent1].[IdAutor] AS [IdAutor], 
        [Extent1].[NomeDaSolicitacao] AS [NomeDaSolicitacao], 
        [Extent1].[Descricao] AS [Descricao], 
        [Extent1].[Grupo] AS [Grupo], 
        [Extent1].[Atividade] AS [Atividade], 
        [Extent1].[Prioridade] AS [Prioridade], 
        [Extent1].[DataPretendidaPeloAutor] AS [DataPretendidaPeloAutor], 
        [Extent1].[AcompanharViaEmail] AS [AcompanharViaEmail], 
        [Extent1].[IdAnalistaDesignado] AS [IdAnalistaDesignado], 
        [Extent1].[Complexidade] AS [Complexidade], 
        [Extent1].[DataPrevistaPeloAnalista] AS [DataPrevistaPeloAnalista], 
        [Extent1].[SubordinacaoDeSolicitacao] AS [SubordinacaoDeSolicitacao], 
        [Extent1].[COD_FORNECEDOR] AS [COD_FORNECEDOR], 
        [Extent1].[TOLERANCIA] AS [TOLERANCIA], 
        [Extent1].[DESC_COND_PGTO] AS [DESC_COND_PGTO], 
        [Extent1].[FILIAL_FATURAR] AS [FILIAL_FATURAR], 
        [Extent1].[FILIAL_ENTREGA] AS [FILIAL_ENTREGA], 
        [Extent1].[FILIAL_COBRANCA] AS [FILIAL_COBRANCA], 
        [Extent1].[MOTIVO_COMPRA] AS [MOTIVO_COMPRA], 
        [Extent1].[TIPO_SOLICITACAO] AS [TIPO_SOLICITACAO], 
        [Extent1].[FORMA_PGTO] AS [FORMA_PGTO], 
        [Extent1].[MOTIVOCHAMADO] AS [MOTIVOCHAMADO], 
        [Extent1].[TEMPORESOLUCAO] AS [TEMPORESOLUCAO], 
        [Extent1].[CodigoRateioFilial] AS [CodigoRateioFilial], 
        [Extent1].[CodigoRateioCentroCusto] AS [CodigoRateioCentroCusto], 
        [Extent1].[DataSolicitacao] AS [DataSolicitacao]
    FROM [dbo].[tblSolicitacao] AS [Extent1]

Do you guys can imagine a reason for this where clause to have been omited ? Maybe something on my model class ?

Thanks for the support.

Answer 1

You used .ToList() before the .Where(.... .ToList() will grab the whole table in your situation. Simply remove that from your query and you should be good.

Answer 2

The ToList call brings the entire result set into memory and then you are performing the where, try the following:

dbHelpDesk.Chamados
            .Where(x => x.Nsu == NumeroChamado)
            .ToList()
            .Select(x => new ChamadosViewModel ...