CODEWITHSUNDEEP
sqlsql-serverlinq
DotNetBeginner
DotNetBeginner

Reputation: 439

linq with right and left join

We are incorporating entity framework in my application. I have converted lot of simple stored procedures into linq queries but this one is giving me trouble and I am not sure where I am going wrong. My brain has turned into jelly and I cannot make it work. I will really appreciate your help so please bear with me.

My SQL is:

SELECT ...
FROM tblProjectAssignment 
INNER JOIN tblProjectAssignmentCollection 
   ON tblProjectAssignment.AssignmentID = tblProjectAssignmentCollection.AssignmentID 
RIGHT OUTER JOIN tblCEQRPhaseIIUsers 
   ON tblProjectAssignment.UserID = tblCEQRPhaseIIUsers.UserID 
LEFT OUTER JOIN tblCEQRAccessUserRole 
   ON tblCEQRPhaseIIUsers.UserRoleTypeID = tblCEQRAccessUserRole.UserRoleTypeID

I need help converting the above to a linq query. I know DefaultIfEmpty() is used for outer joins, but the results that I get when I run the query are incorrect. So, what I need is tblCEQRPhaseIIUsers to get all results independent of the corresponding results in other table. The below query is behaving as all tables has inner joins.

var query = from PATable in db.tblProjectAssignments
            from PACTable in db.tblProjectAssignmentCollections.Where(la => la.AssignmentID == PATable.AssignmentID)
            from UserTable in db.tblCEQRPhaseIIUsers.Where(la => la.UserID == PATable.UserID).DefaultIfEmpty()
            from UserRoleTable in db.tblCEQRAccessUserRoles.Where(la => la.UserRoleTypeID == UserTable.UserRoleTypeID).DefaultIfEmpty()
            where (UserTable.FirstName.ToLower().Contains(search.FirstName.Trim().ToLower()) || search.FirstName == null)
            where (UserTable.LastName.ToLower().Contains(search.LastName.Trim().ToLower()) || search.LastName == null)
            where (PACTable.CEQRNumber.ToLower().Contains(search.CEQRNumber.Trim().ToLower()) || search.CEQRNumber == null)
            where (PACTable.ProjectName.ToLower().Contains(search.ProjectName.Trim().ToLower()) || search.ProjectName == null)
            where (PATable.IsActive == true)
            where (UserTable.IsActive == true)
            select new
            {
                AssignmentID = PATable.AssignmentID,
                UserID = PATable.UserID,
                FirstName = UserTable.FirstName,
                MiddleName = UserTable.MiddleName ?? string.Empty,
                LastName = UserTable.LastName,
                UserRole = UserRoleTable.UserRoleName,
                CEQRNumber = PACTable.CEQRNumber,
                ProjectName = PACTable.ProjectName,
                CollectionID = PACTable.CollectionID,
                EmailAddress = UserTable.EmailAddress,
                AssignmentIsActive = PACTable.IsActive
            };

Upvotes: 1

Views: 51

Answers (1)

Aducci
Aducci

Reputation: 26644

The problem is you can't do right joins in linq. The good news is you can rewrite your query to avoid using the right join. You do this by rearranging the tables and using left joins instead.

Currently you are doing this:

select ...
from [MainTable] m
inner join [InnerTable] i on m.Num = i.Num
right join [RightTable] r on m.Num = r.Num
left join [LeftTable] l on r.Num = l.Num

That should be equivalent to this:

select... 
from [RightTable] r 
left join [LeftTable] l on r.Num = l.Num
left join [MainTable] m on m.Num = r.Num
left join [InnerTable] i on m.Num = i.Num

Now you have a query without right joins, you should be able to write the linq query

Upvotes: 1

Related Questions