title: Linq join statement
body: <p>I am trying to select from multiple tables in an entity model. But there are two columns I would like to select and it's just not working out. The LINQ statement I have is:</p>
  
  <pre><code>var searchResult = from i in _imEntities.Issues
                     join dept in _imEntities.Departments
                     on i.Issued_to_dept equals dept.Dept_ID
                     where i.State == 1
                     select new {
                         i.ID_No,
                         i.Issue_Date,
                         Raised_By = dept.Dept_Name
                                         .Where(i.Raised_by_Dept == dept.Dept_ID),
                         Issued_To = dept.Dept_Name
                                         .Where(i.Issued_to_dept == dept.Dept_ID),
                         Details = i.Details
                     };
  </code></pre>
  
  <p>The column names are all correct, but I just can't get the dept_Names into the Raised_By and Issued_To fields. Is there another way to execute this?</p>