How to populate a code column for query with repeated values

Navigation
1

I have a query where I have multiple data return from each user. But when I perform a search, I have the returned data as a return.

I know that this repetition of the data occurs because I do not contain a single data (not repeated) in this query, as a primary key , since the basis for this query is a View created in my DataBase .

I know this, because if I add this line in my query (where I create a Codigo field with a single count for each row) I can return the data without repeating. 

 ROW_NUMBER() OVER(ORDER BY Nome desc ) AS 'Codigo'

Problem: When adding the row in my View (SQL) the query does not repeat the data, but a query that takes 4 seconds to accomplish, adding this row to the same query takes 8 minutes.

Is there a way to return this data without repeating the code, or another way to treat my View (SQL)?

My controller that does this query looks like this: 

public ActionResult Dependente()
{
   var dependente =
        dependenteRepository.Dependentes.Where(r => r.CdMatricula == matricula && r.SqContrato == contrato).ToList();
    return View(dependente);
}

My model looks like this: 

public class Dependente
    {
        public double NRCPF { get; set; }
        public string NmPessoa { get; set; }
        public string Nome_dependente { get; set; }
        public string DsGrauDependencia { get; set; }
        public int CdMatricula { get; set; }
        public Int16 SqContrato { get; set; }

        [DataType(DataType.Date)]
        [DisplayFormat(DataFormatString = "{0:dd/MM/yyyy}", ApplyFormatInEditMode = true)]
        public DateTime? Nascimento { get; set; }
    }
    
asked by anonymous 13.03.2015 / 12:45

1 answer

1

You do not need to number the rows by SQL. You can do this by Controller , by converting each dependent object to an anonymous object: 

public ActionResult Dependente()
{
    var i = 1;
    var dependentes =
        dependenteRepository.Dependentes
            .Where(r => r.CdMatricula == matricula && r.SqContrato == contrato)
            .Select(d => new { 
                Codigo = 0,
                NRCPF = d.NRCPF,
                NmPessoa = d.NmPessoa,
                Nome_dependente = d.Nome_dependente,
                DsGrauDependencia = d.DsGrauDependencia,
                CdMatricula = d.CdMatricula,
                SqContrato = d.SqContrato,
                Nascimento = d.Nascimento
             })
            .ToList();

    foreach (var dependente in dependentes) 
    {
        dependente.Codigo = i++;
    }

    return View(dependentes);
}

View needs to treat or treat the anonymous object as dynamic or receive a list of ViewModels . For example: 

@model IEnumerable<dynamic>

Or else: 

@model IEnumerable<DependenteViewModel>

Then you need to set the ViewModel : 

public class DependenteViewModel
{
    public int Codigo { get; set; }
    public double NRCPF { get; set; }
    public string NmPessoa { get; set; }
    public string Nome_dependente { get; set; }
    public string DsGrauDependencia { get; set; }
    public int CdMatricula { get; set; }
    public Int16 SqContrato { get; set; }

    [DataType(DataType.Date)]
    [DisplayFormat(DataFormatString = "{0:dd/MM/yyyy}", ApplyFormatInEditMode = true)]
    public DateTime? Nascimento { get; set; }
}

Finally, Controller looks like this: 

public ActionResult Dependente()
{
    var i = 1;
    var dependentes =
        dependenteRepository.Dependentes
            .Where(r => r.CdMatricula == matricula && r.SqContrato == contrato)
            .Select(d => new DependenteViewModel { 
                Codigo = 0,
                NRCPF = d.NRCPF,
                NmPessoa = d.NmPessoa,
                Nome_dependente = d.Nome_dependente,
                DsGrauDependencia = d.DsGrauDependencia,
                CdMatricula = d.CdMatricula,
                SqContrato = d.SqContrato,
                Nascimento = d.Nascimento
             })
            .ToList();

    foreach (var dependente in dependentes) 
    {
        dependente.Codigo = i++;
    }

    return View(dependentes);
}
    
13.03.2015 / 15:03
Responsive Screens Add Maven to a Web Application Project