How to join two tables with linq?

Question

I am trying to join two of my tables with linq based on an id, so far unseccesfully.

Here is how my models look :

  public class WorkRole
    {

        public int WorkRoleId { get; set; }
        public string RoleName { get; set; }
        public string RoleDescription { get; set; }
        public int CompanyId { get; set; }
        public virtual Company Company { get; set; }
        public virtual ICollection<WorkRolesUsersDetails> WorkRolesUsersDetails { get; set; }
    }



public class WorkRolesUsersDetails
    {

        public int WRUDId { get; set; }

        public int? WorkRoleId { get; set; }


        public string UserDetailsId { get; set; }


        public virtual WorkRole WorkRole { get; set; }

        public virtual UserDetails UserDetails { get; set; }

        public DateTime FocusStart { get; set; }
        public DateTime FocusEnd { get; set; }

        public bool isActive { get; set; }
    }

I am trying to get in one view WorkRoleId, RoleName, RoleDescription and CompanyId from the first table and UserDetailsId, FocusStart, FocusEnd and isActive from the second table.

The farthest i got with my ideas was :

var query = db.WorkRoles.Join(db.WorkRolesUsersDetails,x => x.WorkRoleId,y => y.WorkRoleId,(x, y) => new { wr = x, wrud = y });  

But sadly, it didn't work. I just don't know enough linq and couldn't get much out of other questions/answers here. Please, help.

Answer 1

Code for joining 2 tables is:

var list = db.WorkRoles.
                Join(db.WorkRolesUsersDetails,
                o => o.WorkRoleId, od => od.WorkRoleId,
                (o, od) => new
                {
                    WorkRoleId= o.WorkRoleId
                    RoleName= o.RoleName,
                    RoleDescription= o.RoleDescription,
                    CompanyId= o.CompanyId,
                    WRUDId= od.WRUDId,
                    UserDetailsId= od.UserDetailsId,
                    FocusStart=od.FocusStart,
                    FocusEnd=od.FocusEnd
                })

Answer 2

If you are using EF may I suggest the Includes statement it works wonders. IF you have a foreign key assigned. It basically gets the other data with it.

static void Main(string[] args)
{
  using (var context = new TesterEntities())
  {
    var peopleOrders = context.tePerson.Include("teOrder").First(p => p.PersonId == 1).teOrder.ToList();
    peopleOrders.ForEach(x => Console.WriteLine($"{x.OrderId} {x.Description}"));
  }
}

Combining manually without navigation context option.

public class Student
  {
    public int StudentID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }

    public List<StudentTestScore> Scores { get; set; }
  }

  public class StudentTestScore
  {
    public int StudentID { get; set; }
    public int Score { get; set; }
  }

  class Program
  {

    static void Main(string[] args)
    {
      var students = new List<Student>
        {
        new Student { StudentID = 1, FirstName = "Brett", LastName = "X" },
        new Student { StudentID = 2, FirstName = "John", LastName = "X" }
        };
      var grades = new List<StudentTestScore> { new StudentTestScore { StudentID = 1, Score = 98 } };

      var combined = students.Join(grades, x => x.StudentID, y => y.StudentID,
                (x, y) => new
                {
                  Student = $"{x.FirstName} {x.LastName}",
                  Grade = y.Score
                }).ToList();

      combined.ForEach(x => Console.WriteLine($"{x.Student} {x.Grade}"));

      Console.ReadLine();
    }