LINQ To Entity - Unique Left Outer Join Scenerio

I am trying to write the following Left Outer Join scenerio in Linq to Entity syntax and I can't for the life of me figure out exactly how to pull it off... Here is the working SQL that I am trying to ultimately achieve:

SELECT * FROM Students s LEFT JOIN ParentStudents ps ON ps.StudentId = s.StudentId AND ps.ParentId = '6D279F72-2623-459F-B701-5C77C52BA52F'

WHERE s.TenantId = 3 AND s.FamilyId = '28833312-46eb-4a54-9132-8a7c8037cec5'

The part highlighted in Bold is where I fall down ... I want Students to return regardless if there are any ParentStudent records in the database.

Here is my latest LINQ to Entity code that does not work:

    public ICollection<ParentStudentListing> GetParentStudents(Guid FamilyId, Guid ParentId)
    {
        var query = from s in DataContext.Students
                    from ps in s.ParentStudents.DefaultIfEmpty()
                    where s.TenantId == CurrentUser.TenantId && s.FamilyId == FamilyId && ps.ParentId == ParentId
                    select new ParentStudentListing { StudentId = s.StudentId, FirstName = s.FirstName, MiddleName = s.MiddleName, LastName = s.LastName, RelationshipId = ps.RelationshipId, ParentStudentId = ps.ParentStudentId, isAllowedToPickUp = ps.isAllowedToPickUp, isEmergency = ps.isEmergency, isLiveIn = ps.isLiveIn, ParentId = ps.ParentId };

        return query.ToList();
    }

This code does not bring back students unless there are ParentStudent records in the database which is not the intended result. I want to bring back students regardless if there are ParentStudent records, but if there are ParentStudent records I want those joined up with the Student records...

Thank You!

Answers


Here is my first attempt at a LINQ join:

var query = from s in DataContext.Students
            join ps in s.ParentStudents on ps.ParentId equals s.ParentId into ps
            from ps in ps.DefaultIfEmpty()
            where s.TenantId == CurrentUser.TenantId && s.FamilyId == FamilyId                
            select new ParentStudentListing { StudentId = s.StudentId, FirstName = s.FirstName, MiddleName = s.MiddleName, LastName = s.LastName, RelationshipId = ps.RelationshipId, ParentStudentId = ps.ParentStudentId, isAllowedToPickUp = ps.isAllowedToPickUp, isEmergency = ps.isEmergency, isLiveIn = ps.isLiveIn, ParentId = ps.ParentId };

Referenced from here.


Thanks Joe for the added help .... That didn't quite get me there, but I did finally get it running. Here is the working code:

    public ICollection<ParentStudentListing> GetParentStudents(Guid FamilyId, Guid ParentId)
    {
        var query = from s in DataContext.Students
                    join ps in DataContext.ParentStudents
                          on new { s.StudentId, ParentId = ParentId }
                      equals new { ps.StudentId, ps.ParentId } into ps_join
                    from ps in ps_join.DefaultIfEmpty()
                    where s.TenantId == CurrentUser.TenantId && s.FamilyId == FamilyId
                    select new ParentStudentListing { StudentId = s.StudentId, FirstName = s.FirstName, MiddleName = s.MiddleName, LastName = s.LastName, RelationshipId = ps.RelationshipId, ParentStudentId = ps.ParentStudentId, isAllowedToPickUp = ps.isAllowedToPickUp, isEmergency = ps.isEmergency, isLiveIn = ps.isLiveIn, ParentId = ps.ParentId };

        return query.ToList();
    }

Need Your Help

Do I need to download a specific js library to use highcharts-ng directive or is it already embedded in highcharts.js

angularjs highcharts angular-ui-bootstrap highcharts-ng

I am trying to use highcharts with angular. now I wanted to use ready made directive highcharts-ng but nothing is showing up. I am suspecting that I need to download/use external library although I...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.