0

I have the following classes:

public class Employee 
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Gender { get; set; }
    public DateTime JoinedCompany { get; set; }
    public ICollection<EmployeeSkill> EmployeesSkills { get; set; }
}

public class Skill 
{
    public int Id { get; set; }
    public string SkillName { get; set; }
}

public class EmployeeSkill 
{
    public int EmployeeId { get; set; }
    public Employee Employee { get; set; }

    public int SkillId { get; set; }
    public Skill Skill { get; set; }
}

I also have a DbContext class:

public DbSet<Employee> Employees { get; set; }
public DbSet<Skill> Skills { get; set; }
public DbSet<EmployeesSkill> EmployeeSkills { get; set; }

Now, I can get back the data with the following:

public async Task<IReadOnlyList<Employee>> GetEmployeesAsync () 
{
    return await _context.Employees
                         .Include (p => p.EmployeeSkills)
                         .Include (p => p.Skill)
                         .ToListAsync ();
}

which returns something like this:

[
  {
    "firstName": "Olivia",
    "lastName": "Lu",
    "gender": "F",
    "joinedCompany": "2015-03-31T00:00:00",
    "employeeSkills": [
      {
        "employeeId": 8,
        "skillId": 3,
        "skill": {
          "skillName": "Hadoop",
          "id": 3
        },
        "id": 1
      },
      {
        "employeeId": 8,
        "skillId": 4,
        "skill": {
          "skillName": "Spark",
          "id": 4
        },
        "id": 2
      }
    ],
    "id": 8
  }
]

I get really tripped up when using LINQ and I can never manage to find the right solution. I am trying to end up with the following:

[
  {
    "firstName": "Olivia",
    "lastName": "Lu",
    "gender": "F",
    "joinedCompany": "2015-03-31T00:00:00",
    "employeeSkills": [
      {
        "skillId": 3,
        "skillName": "Hadoop"
      },
      {
        "skillId": 4,
        "skillName": "Spark"
      }
    ],
    "id": 8
  }
]

How can I change the LINQ query as to get a result as per above?

Thanks for your help.

1
  • These aren't nested classes. Commented Aug 22, 2020 at 1:37

1 Answer 1

1

You're returning database model Entity Types directly from your query - so of course you're going to get the full structure of EmployeeSkill in the employeeSkills collection.

You need to define a new DTO type that represents the succinct representation of an EmployeeSkill and materialize that in your application code after you've already loaded the data (you can do this directly in the Linq query using Anonymous Types, but you can't export/expose Anonymous Types (and I don't think EF Core supports value-tuples yet) so define a proper DTO type to void future pain.

Anyway, do this:

class EmployeeDto
{
    [JsonProperty( "employeeId " )]
    public int      EmployeeId    { get; set; }
    [JsonProperty( "firstName" )]
    public string   FirstName     { get; set; }
    [JsonProperty( "lastName" )]
    public string   LastName      { get; set; }
    [JsonProperty( "gender" )]
    public string   Gender        { get; set; }
    [JsonProperty( "joinedCompany" )]
    public DateTime JoinedCompany { get; set; }

    [JsonProperty( "employeeSkills" )]
    public List<EmployeeSkillDto> EmployeesSkills { get; set; }
}

class EmployeeSkillDto
{
    [JsonProperty( "skillId" )]
    public Int32 SkillId { get; set; }

    [JsonProperty( "skillName" )]
    public String SkillName { get; set; }
}

public async Task<IReadOnlyList<EmployeeDto>> GetEmployeesAsync( CancellationToken cancellationToken = default ) {

    List<Employee> list = await _context.Employees
        .Include( p => p.EmployeeSkills )
        .Include( p => p.Skill )
        .ToListAsync( cancellationToken );

    // This part below is not async because it's done in-memory:
    List<EmployeeDto> dtoList = list
        .Select( e => new EmployeeDto()
        {
            EmployeeId = e.EmployeeId,
            FirstName = e.FirstName,
            LastName = e.LastName,
            /* etc */
            EmployeeSkills = e.EmployeeSkills
                .Select( s => new EmployeeSkillsDto()
                {
                    SkillId   = s.Skill.Id,
                    SkillName = s.Skill.Name
                } )
                .ToList()
        } )
        .ToList();

    return dtoList;
}

This might seem like dull, needless repetition (and I agree it is!) but it's necessary because a DTO and an Entity Class represent different things so you shouldn't use the same types to represent them. Fortunately there are tools to automate most of this, such as AutoMapper and VS extensions and T4 scripts that will generate "scaffolded" DTOs and other classes with the repetitive parts generated for you already.

Resources:

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.