0

I have a question about joining same entity using LINQ.

Department entity:

public class Department
{
    public int DepartmentId { get; set; }
    [MaxLength(250), Column(TypeName = "varchar")]
    public string Description { get; set; }
    public int? ParentDepartmentId { get; set; }
    public DateTime CreatedDate { get; set; }
    public DateTime UpdatedDate { get; set; }
    public virtual ICollection<DepartmentUser> GetDepartmentUsers { get; set; }

    public Department()
    {
        CreatedDate = DateTime.Now;
        UpdatedDate = DateTime.Now;
    }
}

There is a relationship between ParentDepartmentId field of Department entitiy and DepartmantId field of same entity. I want to get Departments with Description field of ParentDepartment with a LINQ query. I run the following code but it returned zero Departments although there are 3 Departments in database.

using (var context = new AttendanceDBContext())
{
    var departments =
        from d in context.Departments
        join dd in context.Departments on d.ParentDepartmentId equals dd.DepartmentId
        select new DepartmentDTO
        {
            DepartmentId = d.DepartmentId,
            Description = d.Description,
            ParentDepartment = dd.Description,
            UserCount = d.GetDepartmentUsers.Count(),
            CreatedDate = d.CreatedDate,
            UpdatedDate = d.UpdatedDate
        };
    return departments.ToList();
}

DepartmentDTO entity

public class DepartmentDTO
{
    public int DepartmentId { get; set; }
    public string Description { get; set; }
    public string ParentDepartment { get; set; }
    public int UserCount { get; set; }
    public DateTime CreatedDate { get; set; }
    public DateTime UpdatedDate { get; set; }
}
Community
  • 1
  • 1
yasinkzl
  • 1

2 Answers2

0

I guess there is something to do with the fact that your're trying to join with Int and a Ìnt?, 2 types that you can't really deal with together like that. I guess this answer to a similar problem will help you. This is from the MSDN:

In a join clause, if only one of the comparison keys is a nullable value type, you can cast the other to a nullable type in the query expression. In the following example, assume that EmployeeID is a column that contains values of type int?:

void TestMethod(Northwind db)
{
    var query =
        from o in db.Orders
        join e in db.Employees
            on o.EmployeeID equals (int?)e.EmployeeID
        select new { o.OrderID, e.FirstName };
}

You can read the full explanation here here

Platypus
  • 321
  • 1
  • 4
  • 17
  • `int?` just means that there's a department that has no parent department. – JohnyL Jan 07 '20 at 14:50
  • Please note that when comparing both, Linq struggles, I'm perfectly aware of what you said. i'm gonna edit my answer for further explaining. – Platypus Jan 07 '20 at 15:04
  • JohnyL your thought is true for int? – yasinkzl Jan 08 '20 at 05:23
  • Yes as you can see in the example from MSDN, one is `int` and one is `int?`. The 2 are actually not the same and if you already tried to do implicit cast with those 2 you should have seen that it's not that easy. Comparing is usually ok but assigning not quite. – Platypus Jan 08 '20 at 07:15
0

Left outer join solved my problem.

I changed Linq query as the following.

    public List<DepartmentDTO> ListDetail()
    {
        using (var context = new AttendanceDBContext())
        {
            var departments = from d in context.Departments
                              join dd in context.Departments on d.ParentDepartmentId equals dd.DepartmentId into ddd
                              from dddd in ddd.DefaultIfEmpty()
                              select new DepartmentDTO
                              {
                                  DepartmentId = d.DepartmentId,
                                  Description = d.Description,
                                  ParentDepartment = dddd.Description,
                                  UserCount = d.GetDepartmentUsers.Count(),
                                  CreatedDate = d.CreatedDate,
                                  UpdatedDate = d.UpdatedDate
                              };
            return departments.ToList();
        }
    }

Query result showed in image

yasinkzl
  • 1