CODEWITHSUNDEEP
c#entity-framework.net-coreentity-framework-6linq-to-entities
user2793090
user2793090

Reputation: 183

How can I get this entity with nested information using EF and linq

I have 3 tables that are related. Employee, Relative, RelationTypeCatalog. The relationship is IX_Relatives_EmployeeId and IX_Relatives_RelationTypeCatalogId.

Model How can I write this query with EF?

I'm trying to get the information from an employee, a list of relatives to that employee and what type of relationship they have. So far I've tried these options: 

public async Task<Employee> GetEmployee(int id)
{
//(1) tried this
return await _context.Employees
            .Include(emp=> emp.Relatives.Select(rel=> 
             rel.RelationTypeCatalog))
            .FirstOrDefaultAsync(emp=>emp.Id == id);


}
//(2) also tried this...
public async Task<Employee> GetEmployee(int id)
{
var  test = (from e in _context.Employees
                        join re in _context.Relatives
                        on e.Id equals re.EmployeeId
                        join t in _context.RelationTypes
                        on re.RelationTypeCatalogId equals t.Id
                        where e.Id == id
                        select e).FirstOrDefaultAsync();

            return await test;
}

This is how the query looks like with SQL

SELECT * 
from sagrha.employees 
inner join sagrha.relatives
on employees.Id = relatives.EmployeeId
inner join sagrha.relationtypes
on relatives.RelationTypeCatalogId= sagrha.relationtypes.Id;

I'm looking for a json result that will look like 

{
    "id": 1,
    "name": "Homero",
    "gender": "male",
    "relatives": [ 
    "Name":"Bart"
    "RelationType":"Child"
    ]
}

Upvotes: 0

Views: 213

Answers (1)

Mortaza Ghahremani
Mortaza Ghahremani

Reputation: 392

Try to use Include and Then Include like below:

return await _context.Employees
            .Include(emp=> emp.Relatives).ThenInclude(rel => rel.RelationTypeCatalog)
            .FirstOrDefaultAsync(emp=>emp.Id == id);

Upvotes: 2

Related Questions