left outer join not returning null values
Posted: Thu 20 Jun 2013 15:49
The left outer join table(ILCSharedResourceDbSet) has a where clause that applies only for that particular table and my second where clause is what i need to apply on the whole resultant data.I need to get the rows even if the STATUS of my left outer join table is NULL. The OUTPUT of this query returns username of a class only who has status as not null from my left outer join table.I need all the username of a class who has status null and 0,3,4 but not who has status 1 & 2.Sorry of I'm not clear in my question
Code: Select all
var StudentsInfoBasedonTutorialID =( from user in context.aspnet_Users
join enroll in context.EnrollmentEntries
on user.UserId equals enroll.UserId
join enrollbridge in context.EnrollmentEntriesInLearningCarts
on enroll.EnrollmentEntryDatabaseID equals enrollbridge.EnrollmentEntryDatabaseID
join lc in context.LearningCarts
on enrollbridge.LearningCartsDatabaseId equals lc.LearningCartsDatabaseId
join ilcshare in context.ILCSharedResourceDbSet
.Where( o => o.OriginatorLearningCartsDatabaseId == lcOfInterest.LearningCartsDatabaseId && o.ILCResourceDatabaseID == resoucedbid )
on lc.LearningCartsDatabaseId equals ilcshare.RecipientLearningCartsDatabaseId
into shareusergroup from ilcshare in shareusergroup.DefaultIfEmpty()
select new
{
userid = user.UserId,
username = user.UserName,
learningcartid = lc.LearningCartsDatabaseId,
status = ilcshare.Status == null ? ilcshare.Status : 0
}).Where(o => o.status == 0 || o.status == 3 || o.status == 4 ).ToList();