CODEWITHSUNDEEP
c#linqsql-server-2005
TRS
TRS

Reputation: 11

Combine two rows of data using LINQ/SQL

I have a situation where my query returns the following two rows:

UserName  ID  Designation   RoleID

shd.1234   3        1          2

shd.1234   3        1          5

I am able to display these results in a Jqgrid using LINQ.

However I want to display it as under in a single row:(if RoleID is 5, display it in another column in the same row.)

UserName  ID  Designation   RoleID    AdditionalRoleID

shd.1234   3        1          2            5

My current query is something like this:

empDetails = (from u in ObjectContext.USERS
              join ed in ObjectContext.USERS_EMPLOYEE_DETAILS on u.UserID equals ed.UserID
              join r in ObjectContext.ROLES_FOR_USERS on u.UserID equals r.UserID
              join ro in ObjectContext.ROLES on r.RoleID equals ro.RoleID
              where (r.HospitalID == Context.CurrentUser.HIdentity.HospitalID)
              where(r.RoleID!= 4)
              select new Models.AdminModelSettings.EmployeeDetailsForGivenHospital
              {
                  UserName = u.UserName,
                  EmployeeId = ed.ID,
                  EmployeeDesignation = ed.Designation,
                  RoleID = r.RoleID,
                  RoleName = r.RoleID == 1 || r.RoleID == 2 || r.RoleID == 3 ? ro.RoleName : null,
                  AdditionalRole = r.RoleID == 5  ? ro.RoleName : null
              }).ToList();

I would please like to know how this can be done in SQL/LINQ.

Upvotes: 1

Views: 853

Answers (1)

Doctor Jones
Doctor Jones

Reputation: 21664

UPDATE: I've improved the code so it will do all of the work in SQL instead of returning the results and then performing the grouping.

This should do the trick!

The key part is where we order the rows by RoleID and then group the rows by ID. It assumes that you only have a Role and an Additional Role, i.e. it won't select an AdditionalRole2 or AdditionalRole3, etc...

var empDetails = from u in ObjectContext.USERS 
                 join ed in ObjectContext.USERS_EMPLOYEE_DETAILS on u.UserID equals ed.UserID 
                 join r in ObjectContext.ROLES_FOR_USERS on u.UserID equals r.UserID 
                 join ro in ObjectContext.ROLES on r.RoleID equals ro.RoleID 
                 where (r.HospitalID == Context.CurrentUser.HIdentity.HospitalID) 
                 where(r.RoleID!= 4) 
                 select new Models.AdminModelSettings.EmployeeDetailsForGivenHospital 
                 { 
                     UserName = u.UserName, 
                     EmployeeId = ed.ID, 
                     EmployeeDesignation = ed.Designation, 
                     RoleID = r.RoleID, 
                     RoleName = r.RoleID == 1 || r.RoleID == 2 || r.RoleID == 3 ? ro.RoleName : null
                 });

var roleIDs = new List<int> { 1, 2, 3 };

//group our results and order the group by the role id
var temp = empDetails.GroupBy(row => row.ID).Select(g => new { First = g.FirstOrDefault(r => roleIDs.Contains(r.RoleID)), Last = g.FirstOrDefault(r => r.RoleID == 5) });

//select the data into the shape that we want
var query = temp.Select(result => new Models.AdminModelSettings.EmployeeDetailsForGivenHospital
{
    UserName = (result.First ?? result.Last).UserName,
    EmployeeId = (result.First ?? result.Last).ID,
    EmployeeDesignation = (result.First ?? result.Last).Designation,
    RoleID = (result.First == null) ? (int?)null : result.First.RoleID,
    AdditionalRoleID = (result.Last == null) ? (int?)null : result.Last.RoleID
});

Upvotes: 1

Related Questions