duplicate field in query records with Entity Framework on table without primary key

Question

duplicate field in query records with Entity Framework on table without primary key

Navigation

#1 by (0 votes)

0

Currently I have the need to connect an application to a table in a database (Oracle 11g) of other software. This table does not have a defined primary key.

My Entity is this.

public partial class Inacc {
    [Key, Column("ACCHIS", Order = 1)]        
    public int Historia { get; set; }

    [Key, Column("ACCNUM", Order = 2)]
    public int Numero { get; set; } 

    [Key, Column("ACCACC", Order = 3)]
    public int Accidente { get; set; }

    [Column("ACCFUO")]
    public string Fuente { get; set; }

    [Column("ACCDOO")]
    public int? Documento { get; set; }

    [Column("ACCFEC")]
    [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{yyyy-MM-dd}")]
    public DateTime? Fecha { get; set; }

    [Column("ACCIND")]
    public string Indice { get; set; }

    [Column("ACCEAD")]
    public string Sede { get; set; }

}

I search for HISTORY in my repository in this way

DbContext _connection = new DbContext();
DbSet<Inacc>  _dbSet = _connection.Set<Inacc>();
var inaccs = _dbSet.Where(m => m.Historia == 4353087).ToList();

The problem is that the result repeats the SOURCE and DOCUMENT fields in each of the registers

NOTE:

I have already done this type of interactions with other tables and I worked correctly.

I can not modify the table and add a primary key.

c# mvc oracle-11g entity-framework

asked by A. Monsalve 10.09.2018 в 22:51

source

1 answer

Parser Error - An error occurred during the parsing of a resource required to service this request Insert new records and edit existing SQL

score 0 · Accepted Answer

I found a way to solve it and was to create a separate repository with direct queries in sql (filling with a CASE the null fields of Document and Source with zero with DbSqlQuery , add this function in my main repository

public virtual IEnumerable<TEntity> GetWihtRawSql(string query, params object[] parameters)
    {
        object[] parameter = GenerateParameters(parameters);
        return _dbSet.SqlQuery(query, parameter).ToList();
    }

and my repository

    public class InaccRepository : CrosamRepository<Inacc>
    {
        public InaccRepository(CrosamConnection _connection, DefaultConnection context) : base(_connection, context)
        {

        }

        public IEnumerable<Inacc> GetAccidentes(int historia)
        {
            string sql = "select ACCHIS As Historia,  ACCNUM As Numero,  CASE  WHEN ACCFUO IS NULL THEN '0' ELSE ACCFUO END As Fuente," +
                "CASE  WHEN ACCDOO IS NULL THEN 0 ELSE ACCDOO END  As Documento,  " +
                "ACCACC As Accidente,  ACCFEC As Fecha,  ACCIND As Indice,  ACCEAD As Sede  " +
                "from INACC where ACCHIS = :historia";
            return GetWihtRawSql(sql, 
                new OracleParameter("historia", historia));
        }

        public IEnumerable<Inacc> GetAccidenteByHc(int his, int num, int acc)
        {
            string sql = "select ACCHIS As Historia,  ACCNUM As Numero,  CASE  WHEN ACCFUO IS NULL THEN '0' ELSE ACCFUO END As Fuente," +
                "CASE  WHEN ACCDOO IS NULL THEN 0 ELSE ACCDOO END  As Documento,  " +
                "ACCACC As Accidente,  ACCFEC As Fecha,  ACCIND As Indice,  ACCEAD As Sede  " +
                "from INACC where ACCHIS = :historia and ACCNUM = :num and ACCACC = :acc";
            return GetWihtRawSql(sql,
                new OracleParameter("historia", his),
                new OracleParameter("num", num),
                new OracleParameter("acc", acc));
        }

        public IEnumerable<Inacc> GetAccidenteByExt(string fue, int doc, string sed)
        {
            string sql = "select ACCHIS As Historia,  ACCNUM As Numero,  CASE  WHEN ACCFUO IS NULL THEN '0' ELSE ACCFUO END As Fuente," +
                "CASE  WHEN ACCDOO IS NULL THEN 0 ELSE ACCDOO END  As Documento,  " +
                "ACCACC As Accidente,  ACCFEC As Fecha,  ACCIND As Indice,  ACCEAD As Sede  " +
                "from INACC where ACCFUO = :fue and ACCDOO = :doc and ACCEAD = :sed";
            return GetWihtRawSql(sql,
                new OracleParameter("fue", fue),
                new OracleParameter("doc", doc),
                new OracleParameter("sed", sed));
        }
    }

Finally modify my entity the fields Document and source adding them as primary key

    public partial class Inacc {

    [Key, Column("ACCHIS", Order = 1)]        
    public int Historia { get; set; }

    [Key, Column("ACCNUM", Order = 2)]
    public int Numero { get; set; } 

    [Key, Column("ACCACC", Order = 3)]
    public int Accidente { get; set; }

    [Key, Column("ACCFUO", Order = 5)]
    public string Fuente { get; set; }

    [Key, Column("ACCDOO", Order = 4)]
    public int Documento { get; set; }

    [Column("ACCFEC")]
    [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{yyyy-MM-dd}")]
    public DateTime? Fecha { get; set; }

    [Column("ACCIND")]
    public string Indice { get; set; }

    [Column("ACCEAD")]
    public string Sede { get; set; }

}