I want to translate my query to entity framework fluet api but it's giving errors

Malik Humza Yunas 1 Reputation point
2021-07-10T14:29:26.823+00:00

Hi all,

I am trying to implement a query in entity framework core asp.net core 5 using fluent api instead of linq.

This is what i want to accomplish

SELECT *
FROM tblCourseAllocations as allocation
LEFT JOIN tblSemesters as semester ON semester.Semester_Name = allocation.Semester_Name
AND
semester.Department_Code = allocation.Department_Code
AND
allocation.Program_Code = semester.Program_Code
AND
semester.Program_Session = allocation.Program_Session
AND
semester.Section_Name = allocation.Section_Name
LEFT JOIN tblCourses as Courses ON allocation.Course_Code = Courses.Course_Code

WHERE allocation.Employee_Code = '108020'
AND
allocation.Semester_Name = 'Spring2021'

and my c# code to do this is

_context.TblCourseAllocations

            .Include(ca => ca.Department)
            .Include(ca => ca.Department.TblCourses)
            .Include(ca => ca.Department.TblPrograms)
            .Include(ca => ca.Department.TblSemesters.Where(s => s.SemesterName.Equals(ca.SemesterName)))
            .Where(ca => ca.EmployeeCode.Equals(employee.EmployeeCode) 
                         & ca.SemesterName.Equals(semester) 
                         & ca.PaymentStatus.Equals("Regular/Unpaid")
            )
            .AsSplitQuery()
           .AsEnumerable().OrderBy(d => d.ProgramSession)

When i put first filter on semesters it throws me an error that page can't be loaded and you can evaluate client side this query.

Any help will be highly appreciated. Simply want to know how to apply filters on included properties in fluent api.

Regards,

Developer technologies | .NET | Entity Framework Core
Developer technologies | ASP.NET | ASP.NET Core
SQL Server | Other
Developer technologies | C#
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Michael Taylor 60,326 Reputation points
    2021-07-10T16:42:11.077+00:00

    The Include method is just for listing the related types/tables to load as part of the query. This sets up the JOIN used in the query. To actually filter on the table you would use a subsequent where clause. However you don't need a where clause here. The ON portion of the JOIN in SQL translates to the relationship defined in EF. In your EF configuration for the table you will have specified that Department has a one-to-one relationship with Semester and you would have specified that the relationship is between the names. This allows EF to properly join the tables.

    .Include(ca => ca.Department)   // JOIN Department ON <defined in EF configuration>
    .Include(ca => ca.Department.TblCourses)  //JOIN Courses ON <defined in EF configuration>
    .Include(ca => ca.Department.TblPrograms) //JOIN Programs ON <defined in EF configuration>
    .Include(ca => ca.Department.TblSemesters)  //JOIN Semesters ON <defined in EF configuration>
     .Where(ca => ca.EmployeeCode.Equals(employee.EmployeeCode)  //WHERE EmployeeCode = ....
                               & ca.SemesterName.Equals(semester)                     // AND SemesterName = ...
                               & ca.PaymentStatus.Equals("Regular/Unpaid")        // AND PaymentStatus = "Regular/Unpaid"
                  )
     .AsSplitQuery()  //Have no idea what this is doing
     .OrderBy(d => d.ProgramSession  // ORDER BY ProgramSession
    

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.