Question

I couldn t convert the following left join SQL to linq:

select  Students.StudentID, StudentAddresses.state
from Students left join Studentaddresses on (Students.StudentID = Studentaddresses.StudentID and StudentAddresses.Active=1)
where (StudentAddresses.Rank =1  or StudentAddresses.StudentID is null)
and Students.StudentID =3

学生可以在学生演讲桌上拥有零记录或多份记录,但只有一份记录可以活跃,排名一。

我能够做一个左边会,为正常局势工作。但是,如果学生在学生桌上有两张不活跃的记录,我不知道如何使学生记录在最后结果中只出现过一次。谁能提供帮助?

Answer 1

使用<代码>代号()

var LeftJoin = (from student in Students
                join address in (from address1 in StudentAddresses where address.Active select address1) 
                on student.StudentID equals address.StudentId
                into JoinedStudentAddress
                from joined in JoinedStudentAddress.DefaultIfEmpty()
                select new                          
                {
                    StudentID = student.StudentID,
                    State = joined != null ? joined.State : null
                }).Distinct();

Alternate syntax

 var LeftJoin = Students.GroupJoin( StudentAddress.Where( a => a.Active ),
                                    s => s.StudentID,
                                    a => a.StudentID,
                                    (s,a) => new { Student = s, Addresses = a } )
                        .SelectMany( j = > j.Addresses.DefaultIfEmpty()
                                     (s,a) => new {
                                                 StudentID = s.Student.StudentID,
                                                 State = a != null ? a.State : null
                                    })
                        .Distinct();

Answer 2

This would be the SQL statement converted to linq:

var q = from student in Students
        from adress in Studentaddresses.Where(x => student.StudentID == x.StudentID && x.Active).DefaultIfEmpty()
        where (adress.Rank == 1 || adress.StudentID == null) && student.StudentID == 3
        select new                          
        {
           StudentID = student.StudentID,
           State = adress.state
        };

友情链接