Linq join statement

Question

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:

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

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?

Answer 1

Try this:

var query = from i in _imEntities.Issues
            join dept_r in _imEntities.Departments
            on i.Issued_to_dept equals dept_r.Dept_ID
            join dept_i in _imEntities.Departments
            on i.Issued_to_dept equals dept_i.Dept_ID
            where i.State == 1 
            select new {
                i.ID_No, 
                i.Issue_Date, 
                Raised_By = dept_r.Dept_Name,
                Issued_To = dept_i.Dept_Name, 
                Details = i.Details
            };

Answer 2

It's not clear what you are trying to achieve. But you definitely trying to apply where filter on single name string (also predicate syntax is not correct). Here is query which conditionally returns Dept_Name in Raised_By and Issued_To properties:

var query = 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 = (i.Raised_by_Dept == dept.Dept_ID) ? dept.Dept_Name : null,
                Issued_To = (i.Issued_to_dept == dept.Dept_ID) ? dept.Dept_Name : null, 
                Details = i.Details
            };