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?

When you say `I just can't get the dept_Names into the Raised_By and Issued_To fields`, do you mean you are receiving an exception, error or they are blank? — Ryan Gates, Jan 31 '13 at 21:25
Originally the Issues table stores the dept_ID under Raised_By and Issued_To. Where Raised_By is the department that raises an issue and Issued_To is the department that the issue is "Issued To". I really want a query that joins the department and issue table and can show me the department name in a grid and not the dept_id. — user2030579, Jan 31 '13 at 22:01
When I say "I just can't get the dept_Names into the Raised_By and Issued_To fields", I mean that I can't get a logic using the linq query to show the corresponding department names for each department id stored under the respective fields... — user2030579, Jan 31 '13 at 22:02
I realize that you tagged your question with `linq`, but if you want a conceptual table that joins the department and issue table a view may suit you better than linq. — Ryan Gates, Jan 31 '13 at 22:13

score 1 · Answer 1 · answered Jan 31 '13 at 21:27

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

The query returns the Issued_To department names but not the Raised_By. The Raised_By is shown as null. i.Raised_by_Dept field is an int? (nullable int), could that be the cause of it? — user2030579, Jan 31 '13 at 22:06
@user2030579 yes, I'm selecting `null` if `Raised_by_Dept` of current issue not equal to `Dept_ID` of joined department. — Sergey Berezovskiy, Jan 31 '13 at 22:31

score 1 · Accepted Answer · answered Jan 31 '13 at 21:31

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

Thank you soooo much!! This is exactly what I was looking for. It's just that the dept_r was set to i.Issued_To but I changed it to i.Raised_By (minor stuff cuz I understood the query). U are genius. — user2030579, Jan 31 '13 at 22:20

Linq join statement

2 Answers2

Linked