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,

Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
701 questions
ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,234 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,870 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,364 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Michael Taylor 49,166 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