1

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 !!

Glenn singh
  • 233
  • 1
  • 6
  • 18
  • See following for sample : https://github.com/linq2db/linq2db/blob/master/Tests/Linq/Samples/JoinOperatorTests.cs – jdweng Oct 16 '20 at 15:37
  • I just checked and found a query selector there. Did you mean to say that . Include () in EF performs inner join behind the scene ? – Glenn singh Oct 16 '20 at 15:52
  • Yes. The linq query is translated to a database query. – jdweng Oct 16 '20 at 15:59
  • 1
    Alright!! But does linq2db has anything equivalent to .Include() ? Is there any better way to write this code ? – Glenn singh Oct 16 '20 at 17:44
  • 1
    Yes, it does: https://stackoverflow.com/questions/63150864/linq2db-effective-way-to-query-hierarchy-of-objects – Svyatoslav Danyliv Oct 17 '20 at 09:06
  • I tried this "_sampleDbContext.Employees.LoadWith(d => d.Department).ToArrayAsync();" and now it is giving me an error called "LinqToDBException: Member 'd.Department' is not an association.". What is wrong I am doing here ? – Glenn singh Oct 17 '20 at 14:06
  • In the link I've described that Association attribute is needed. – Svyatoslav Danyliv Oct 18 '20 at 15:58
  • Yes I have added the Association. In "Department.cs" I have added this "[Association(ThisKey = nameof(Department.Id), OtherKey = nameof(Employee.DeptId))]" on public ICollection Employees { get; set; }. But still the same error "LinqToDBException: Member 'dep.Department' is not an association." In my controller, I am doing this " _sampleDbContext.Employees.LoadWith(dep => dep.Department).ToArrayAsync();" . Do you think that something wrong is happening here ? – Glenn singh Oct 19 '20 at 05:12
  • Better to create issue on GitHub. I'll check what is wrong. – Svyatoslav Danyliv Oct 19 '20 at 10:41
  • Edit your question and add Association attributes. – Svyatoslav Danyliv Oct 20 '20 at 13:33
  • Here is my public repo : https://github.com/Ashwani44/Linq2DbDemo – Glenn singh Oct 21 '20 at 04:53
  • I updated my question and added that Association attribute. – Glenn singh Oct 22 '20 at 07:48

0 Answers0