Relationship of two tables MVC C #

-1

How can I make a Lambda query, I have two Permission and Employee tables, where to inform the email of the Employee, it brings me the permissions of the Employee, through the email that I reported. In SQL looks like this:

Select Funcionario.Email, Permissao.Nome'Nome permissao' 
from Funcionario, Permissao 
where Funcionario.PermissaoId = Permissao.PermissaoId

Use Entity Framework, I have this method, and precise returns the name of the employee's permission.

public override string[] GetRolesForUser(string username) {
    FuncionarioRepositorio _funcionarioRepositorio = new FuncionarioRepositorio(); 
    string sRoles = _funcionarioRepositorio.BuscarTodos().Where(c => c.Email == username).FirstOrDefault().ToString(); 
    string[] retorno = { sRoles }; return retorno; 
}
    
asked by anonymous 23.09.2016 / 19:56

1 answer

1

Assuming you use the Entity Framework and you have the following classes:

public class Permissao
{
    [Key]
    public int PermissaoId { get; set; }

    public string Name { get; set; }

    public virtual ICollection<Funcionario> Funcionarios { get; set; }
}

public class Funcionario
{
    public int Id { get; set; }

    public string Email { get; set; }

    public int PermissaoId { get; set; }

    [ForeignKey("PermissaoId")]
    public Permissao Permissao { get; set; }
}

public class Repositorio : DbContext
{
    public DbSet<Permissao> Permissoes { get; set; }

    public DbSet<Funcionario> Funcionarios { get; set; }
}

You can query as follows:

using (var db = new Repositorio())
{
    Funcionario funcionario = db.Funcionarios
        .Include(p => p.Permissao)
        .Where(c => c.Email == "[email protected]")
        .FirstOrDefault();

    Console.WriteLine(funcionario.Permissao.Name); // nome da permissão
}

I used the Core version of EF in this example, the principle remains the same for Entity Framework 6.x. In this link you have other examples and a brief documentation on how to load related entities: link

Notes:

a. It is necessary that you have a repository with the two entities (DbSet).

b. It may be necessary / more appropriate to map the entities relationship through the DbContext.OnModelCreating method. Example of how to map entities: link

c. The Query can get significantly larger with the number of fields in the tables, so you can include .Select at the end of the search expression if you find it necessary.

    
23.09.2016 / 21:12