How to write a query to get data from two tables in Entity Framework-CodePudding

I have these tables with these relations :

https://i.stack.imgur.com/xUbeu.png

And I wrote these codes :

public class TestData
{
    public int EmployeeId { get; set; }
    public string Email { get; set; }
    public string Password { get; set; }
    public string FullName { get; set; }
    public string Avatar { get; set; }
    public bool IsActive { get; set; }
    public List<int> Roles { get; set; }

}

   public TestData GetData(string email)
    {
        var employee = _CarRentalContext.Employees.SingleOrDefault(w => w.Email == email);
        List<int> Roles = _CarRentalContext.EmployeesRoles
            .Where(w => w.EmployeeId == employee.EmployeeId)
            .Select(s => s.RoleId).ToList();

        return new TestData()
        {
            EmployeeId = employee.EmployeeId,
            FullName=employee.FullName,
            Email=employee.Email,
            Password=employee.Password,
            IsActive=employee.IsActive,
            Avatar=employee.Avatar,
            Roles = Roles,
        };
    }

Now what is the best way to write this function? And if I want to get a list of RoleName instead of RoleId, what should this function look like?

CodePudding user response：

EF defines entities for tables. Your schema has a many-to-many EmployeeRoles table for the association between Employees and Roles, so the entities should look something like this:

public class Employee
{
    public int EmployeeId { get; set; }
    // ...
    public virtual ICollection<Role> Roles { get; set; } = new List<Role>();
    // or 
    // public virtual ICollection<EmployeeRole> EmployeeRoles { get; set; } = new List<EmployeeRole>();
}

If Employee doesn't expose a collection/list of either Role or EmployeeRole then your team needs to read up on using Navigation Properties for relationships. For nearly all relationships like this there is no need to have DbSets in the DbContext for the joining EmployeeRole entity. To populate a TestData DTO you just would need:

var testdata = _CarRentalContext.Employees
   .Where(w => w.Email == email)
   .Select(w => new TestData
   {
       EmployeeId = w.EmployeeId,
       FullName=w.FullName,
       Email=w.Email,
       Password=w.Password,
       IsActive=w.IsActive,
       Avatar=w.Avatar,
       Roles = w.Roles.Select(r => new RoleData
       {
           RoleId = r.RoleId,
           Name = r.Name
       }).ToList()
   }).SingleOrDefault();

If instead the Employee has a collection of EmployeeRoles then it's a little uglier, replacing the inner Roles= with :

       Roles = w.EmployeeRoles.Select(er => new RoleData
       {
           RoleId = er.Role.RoleId,
           Name = er.Role.Name
       }).ToList()

... to dive through the EmployeeRole to the Role.

Using Select like that is known as Projection and will let EF build a query to retrieve just the fields about the Employee and associated roles that you need to populate the details. Assuming you want both the ID and Name for each associated role, you would create a simple DTO (RoleData) and use Select within the Employee.Roles to populate from the Role entity/table.

CodePudding user response：

And if I want to get a list of RoleName instead of RoleId, what should this function look like?

.Select(s => s.Role.RoleName)