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.

what you mean by did'nt worked, any exceptions or expected result did'nt came? — Ehsan Sajjad, Feb 01 '17 at 20:43

Yasemin çidem · Accepted Answer · 2017-02-01T21:52:59.800

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
                })

djangojazz · Answer 2 · 2017-02-01T21:01:18.237

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();
    }

Please take a look to my answer. I thought the same, but was not sure if it is an EF or LINQ problem. — Michael, Feb 01 '17 at 20:52
You are not getting the PROPERTIES off of your joined object I don't see in your example. You don't do 'new { thing = x, thing2 = y}'. You DO DO 'new { thing = x.PROPERTY, thing2 = y.PROPERTY}'. See updated example. — djangojazz, Feb 01 '17 at 21:00

How to join two tables with linq?

2 Answers2