Relationship of two tables MVC C#

Question

How can I make a Lambda query, I have two Permission and Employee tables, where to inform the email of the Employee, he brings me the Employee’s permissions, through the email that I informed. In SQL gets like this:

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

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

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 in the following way:

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. Here are other examples and a brief documentation of how to load related entities: https://msdn.microsoft.com/en-us/data/jj574232.aspx

Notes:

a. It is necessary that you have a repository with both entities (Dbset).

b. It may be necessary/more appropriate to map the relationship of entities through the method DbContext.OnModelCreating. Example of how to map entities: http://www.entityframeworktutorial.net/code-first/configure-one-to-many-relationship-in-code-first.aspx

c. Query can get significantly large 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.