How to fill a code column for reference with repeated values

Question

I have a query where I have returned several data of each user. However, when performing a search, I have as return the repeated data.

I know that this repetition of the data happens because I do not contain a single data (that does not repeat) in this query, as a primary key, as the basis for this consultation is a View maid in my DataBase.

I know this because if I add this line in my query (where I create a field Codigo 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 line in my View (SQL) the query does not repeat the data, but a query that takes 4 seconds to perform, when adding this line the same query takes 8 minutes.

There is a way to return this data without repeating by code, or another way to treat my View (SQL)?

My controller that makes this query is like this:

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

My model is that way:

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; }
    }

Answer 1

You do not need to number the rows by SQL. You can do this by yourself Controller, converting each dependent object into 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);
}

To View need or treat as dynamic the object anonymous or receive a list of Viewmodels. For example:

@model IEnumerable<dynamic>

Or else:

@model IEnumerable<DependenteViewModel>

Then you need to define 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, the Controller gets 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);
}