View Mapping with Entity Framework

Asked

Viewed 426 times

2

I am doubtful how to map a View created in Oracle Bank 11G with Fluent-API.

Here is an excerpt from the view with alias:

tran.id_transporte AS "ID Navio",
tran.ds_nome_transporte AS "Navio",
pemb.id_porto AS "ID Porto Embarque",
pemb.ds_porto AS "Porto Embarque",
temb.id_terminal AS "ID Terminal",

In the mapping I did as follows:

        public VWProgramacaoEmbarque_Mapping()
    {
        this.HasKey(t => t.IdTerminal);
        this.ToTable("VW_PROGRAMACAO");

        this.Property(t => t.IdNavio).HasColumnName("ID_TRANSPORTE");
        this.Property(t => t.Navio).HasColumnName("DS_NOME_TRANSPORTE");
        this.Property(t => t.IdPortoEmbarque).HasColumnName("ID_PORTO");
        this.Property(t => t.PortoEmbarque).HasColumnName("DS_PORTO");
        this.Property(t => t.IdTerminal).HasColumnName("ID_TERMINAL");
    }

In Oracle I can perform the query of a column with alias.

select distinct("ID Navio") from vw_programacao;

In my application I am performing the consultation as follows:

var idNavios = Context.VWProgramacao.Select(e => e.IdNavio).Distinct();

But generates the following error:

{"ORA-00904: "Extent1 ". " ID_TRANSPORTE ": invalid identifier"}

I believe it is because of my mapping that I am wrong or maybe it is because of the alias of my view.

Any idea what it might be? Where I’m going wrong?

c# database entity-framework-6 fluent-api
  • 1

    If you’re exposing the view with these aliases, you don’t have the ID_TRANSPORTE column, but ID Ship (Be very careful with these spaces, I wouldn’t recommend doing that). And replace to the correct value in all these properties.

  • @Renatoafonso This view was created by another sector where I work, in this case in my application the name of my column is ... Hascolumnname("SHIP ID"); ?

  • 1

    Yes. In these lines of code, you are associating your object’s property with the table column /view. In this case, your view has as column names those alias.

  • @Renatoafonso I understood, I will change and do the test again.

  • Do not map view with EF but tables.. you can make a select that returns everything you need with them.

  • @Renatoafonso I withdrew my answer to prioritize yours, if you are interested to answer a solution based on what I said, I will accept, because it worked as you said.

  • @samuelrvg, I have already put as an answer. Thank you

Show 2 more comments

2 answers

2


If you’re exposing the view with these aliases, you don’t have the ID_TRANSPORTE column, but ID Ship (Be very careful with these spaces, I wouldn’t recommend doing that). And replace to the correct value in all these properties.

1

This mistake happens because you sweat VIEW does not have the column ID_TRANSPORTE, Maybe by the time you created it you’ve put:

See that you put this COLUMN as "Ship ID"

Tran.id_transport AS "Ship ID"

So when mapping to the Entity, you would need to name the "ALIAS" and not the COLUMN itself, or if you remove the nicknames there yes you can name the COLUMNS.

For example, your mapping should look like this:

YOUR VIEW

tran.id_transporte AS "ID Navio",
tran.ds_nome_transporte AS "Navio",
pemb.id_porto AS "ID Porto Embarque",
pemb.ds_porto AS "Porto Embarque",
temb.id_terminal AS "ID Terminal",

YOUR MAPPING WOULD HAVE TO STAY THAT WAY

public VWProgramacaoEmbarque_Mapping()
{
    this.HasKey(t => t.IdTerminal);
    this.ToTable("VW_PROGRAMACAO");

    this.Property(t => t.IdNavio).HasColumnName("ID Navio");
    this.Property(t => t.Navio).HasColumnName("Navio");
    this.Property(t => t.IdPortoEmbarque).HasColumnName("ID Porto Embarque");
    this.Property(t => t.PortoEmbarque).HasColumnName("Porto Embarque");
    this.Property(t => t.IdTerminal).HasColumnName("ID Terminal");
}

You would need to use the "ALIAS", if you are going to map using the names of the COLUMNS simply do not need the Nicknames.

Browser other questions tagged c# database entity-framework-6 fluent-api

You are not signed in. Login or sign up in order to post.