1

We are using Entity Framework in my project. In one of the requirement I need to query data from multiple tables using left outer joins along with conditions. Here is the SQL script. Can someone provide the lambda expression for this?

SELECT  
    e.EmployeeId, e.EmployeeFirstName, e.EmployeeLastName,
    s.SkillId, s.SkillName,
    c.CertificateId, c.CertificateName, c.ExpiryDate
FROM    
    [dbo].[Employee] AS e
LEFT OUTER JOIN 
    [dbo].[EmployeeSkill] AS s ON e.EmployeeId = s.EmployeeId 
                               AND s.IsActiveSkill = 1
LEFT OUTER JOIN 
    [dbo].[EmployeeCertification] AS c ON e.EmployeeId = c.EmployeeId  
                                       AND c.IsActiveCertification = 1  
                                       AND c.ExpiryDate < GETUTCDATE() + 30
WHERE   
    e.DepartmentId = 1
    AND e.IsActiveEmployee = 1
Murthy
  • 11
  • 2
  • see if you teach yourself from looking at this post: https://stackoverflow.com/questions/17142151/linq-to-sql-multiple-tables-left-outer-join?rq=1 – jazb Oct 11 '18 at 04:42
  • Possible duplicate of [LINQ to SQL multiple tables left outer join](https://stackoverflow.com/questions/17142151/linq-to-sql-multiple-tables-left-outer-join) – vesan Oct 11 '18 at 05:06
  • @John, In the post you referring, though we are doing LEFT OUTER JOIN on dc_tpatient_bookingm it will convert as inner join for this table as I see there is a condition in where clause on m.enteredon – Murthy Oct 11 '18 at 05:30
  • Basically I want the additional filters should apply on respective table in left outer join. So I can fetch only active skills from skill table and only active certificates and where expiry date is in next 30 days from employee certification table. – Murthy Oct 11 '18 at 05:40

1 Answers1

0

You can use GroupJoin. It has same functionality as left outer join in SQL.

gopal
  • 11
  • 2
  • When I do GroupJoin and run the SQL profile I could see all the columns querying from database instead of the columns specified in Lambda Expression. If we are querying by table wise, it's querying only specified columns and by doing group join is working perfect. But for this approach we need to write lot of code. I'm looking for only one statement, like our SQL query , which should query only selected columns from database. – Murthy Oct 11 '18 at 06:52