How to load child collection in linq2Db?

1.9k Views Asked by At

I have created below tables in my sql.

create table Department
(
    Id uniqueidentifier primary key not null,
    Name nvarchar(255),
    IsActive bit
)

create table Employee
(
    Id uniqueidentifier primary key not null,
    Name nvarchar(255),
    Age int,
    Address nvarchar(255),
    Email varchar(max),
    Dob datetime,
    DeptId uniqueidentifier,
    IsActive bit,
    Foreign key (DeptId) references Department(Id)
)

In employee table, "DeptId" is foreign key. I have written the code to fetch all the employees with its department.

The problem here is I am getting the employees with null departments.
I remember when using EF core, I did something like this:

 var employees = db.Employees.Include(e => e.Department);

The above code returns employees with it departments but when using linq2Db, How do I write the code to fetch employees with departments ?

Here is my code:
Employee.cs

    public class Employee
    {
        [PrimaryKey]
        public Guid Id { get; set; }
        public string Name { get; set; }
        public int? Age { get; set; }
        public string Address { get; set; }
        public string Email { get; set; }
        public DateTime? Dob { get; set; }
        public Guid? DeptId { get; set; }
        public bool? IsActive { get; set; }

        public virtual Department Department { get; set; }
    }

Department.cs

    public class Department
    {
        public Department()
        {
            this.Employees = new HashSet<Employee>();
        }

        [PrimaryKey]
        public System.Guid Id { get; set; }
        public string Name { get; set; }
        public bool? IsActive { get; set; }

        [Association(ThisKey = nameof(Department.Id), OtherKey = nameof(Employee.DeptId))]
        public virtual ICollection<Employee> Employees { get; set; }
    }

SampleDbContext.cs

    public class SampleDbContext : DataConnection
    {
        public SampleDbContext(LinqToDbConnectionOptions<SampleDbContext> options)
            : base(options)
        {

        }

        public ITable<Employee> Employees => GetTable<Employee>();
        public ITable<Department> Departments => GetTable<Department>();
    }

EmployeeController.cs

    [Route("api/[controller]")]
    [ApiController]
    public class EmployeeController : ControllerBase
    {
        private readonly SampleDbContext _sampleDbContext;

        public EmployeeController(SampleDbContext sampleDbContext)
        {
            _sampleDbContext = sampleDbContext;
        }

        // GET: api/<EmployeeController>
        [HttpGet]
        public async Task<Employee[]> Get()
        {
            return await _sampleDbContext.Employees.ToArrayAsync();
        }

    }

Can anybody help me on this ?
Thanks !!

0

There are 0 best solutions below