Problem with . Include() in Entity Framework(Postgres)

Question

I’ve been having a problem with Entityframework, it’s as follows:

When I use the ORM(.Include) string to give a Join in tables with foreign keys, it automatically changes the name of the keys in the automatically generated string and ends up not finding in the database, follows the models and the string:

[Table("usuarios", Schema = "public")]
public partial class Usuario
{
    [Key]
    public Guid id { get; set; }
    public Guid nivel_id { get; set; }
    public Guid empresa_id { get; set; }
    public string nome { get; set; }
    public string email { get; set; }
    public string senha { get; set; }
    public DateTime datavencimento { get; set; }
    public bool ativo { get; set; }

    public virtual Nivel nivel { get; set; }
    public virtual Empresa empresa { get; set; }

And in the controller I use the following string:

public ActionResult Index()
    {
        var usuarios = db.Usuario
                        .Include(n => n.nivel)
                        .Include(e => e.empresa)
                        .ToList();

        return View(usuarios);
    }

Ae in debug mode, the EF select looks like this, notice at the end of the instruction, it changes the name nivel_id to nivel_id1, because this occurring, is that it is some kind of "conflict" with the Postgres' file?:

SELECT 1 AS "C1", "Extent1"."id", "Extent1"."nivel_id", "Extent1"."empresa_id", "Extent1"."nome", "Extent1"."email", "Extent1"."senha", "Extent1"."datavencimento", "Extent1"."ativo", "Extent2"."id" AS "id1", "Extent2"."nome" AS "nome1", "Extent3"."id" AS "id2", "Extent3"."cnpj", "Extent3"."razaosocial", "Extent3"."nomefantasia", "Extent3"."datafundacao", "Extent3"."contato", "Extent3"."cep", "Extent3"."ibge", "Extent3"."tipologradouro", "Extent3"."logradouro", "Extent3"."numero", "Extent3"."complemento", "Extent3"."bairro", "Extent3"."municipio", "Extent3"."estado", "Extent3"."site", "Extent3"."situacao" FROM "public"."usuarios" AS "Extent1" LEFT OUTER JOIN "public"."niveis" AS "Extent2" ON "**Extent1"."nivel_id1" = "Extent2"."id" LEFT OUTER JOIN "public"."empresas" AS "Extent3" ON "Extent1"."empresa_id1" = "Extent3"."id"

I would like to know why he does this and what way he has to correct without changing the existing database or model.

If anyone can help me, I’d appreciate it! VLW

Answer 1

The elegant way to solve it is by forcing the attribute [ForeignKey] in the Model:

[Table("usuarios", Schema = "public")]
public partial class Usuario
{
    [Key]
    public Guid id { get; set; }
    public Guid nivel_id { get; set; }
    public Guid empresa_id { get; set; }
    public string nome { get; set; }
    public string email { get; set; }
    public string senha { get; set; }
    public DateTime datavencimento { get; set; }
    public bool ativo { get; set; }

    [ForeignKey("nivel_id")]
    public virtual Nivel nivel { get; set; }
    [ForeignKey("empresa_id")]
    public virtual Empresa empresa { get; set; }
}

This is because the name of your foreign keys is a little outside of the Entity Framework naming convention, which does not use the "_" (underline).