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

Malik Humza Yunas 1

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,

1 answer

Michael Taylor 47,966 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
Please sign in to rate this answer.
Malik Humza Yunas 1 Reputation point

2021-07-10T17:48:55.47+00:00

HI sir , Thanks for comment actually let me know that as in laravel we define extension methods of models and then we can even access those methods on run time. But here in asp.net mvc is there any way to define accessor ? I want to access join semester table with many filter operations as in my query. But when i try to do in eloquent it is not happening should i try to define more relationship in dbcontext ?

Michael Taylor 47,966 Reputation points

2021-07-10T19:01:21.303+00:00

The DbContext relationships determine the join condition on tables and are handled automatically by EF. So database relationships are handled at the context level when you're defining your entities. Everything else goes in where clauses and it doesn't matter whether your where is on root entities or children.

To be honest your DB structure doesn't make sense to me. I don't know why you'd have a relationship between departments and semesters. I definitely don't understand why you'd ever use a semester name as the foreign key relationship over using the PK of the semester table. What I'd expect is that a department (say CompSci) would have zero or more courses linked from course (e.g Programming 1) back to the department PK it is in. A semester (e.g. Fall 2021) would then have zero or more courses within it. That doesn't appear to match your EF model so I really question the EF model you have defined.

But assuming you have properly set up your EF relationships such that departments have zero or more semesters then you'd have defined a relationship on your Department entity that says a department has zero or more semesters and told EF to match that information by using the semester name. This handles all the joins for you. The only thing Include does is it tells EF to load the corresponding table data (eager loading) instead of waiting until you access it (lazy loading).

Not sure about your extension method question but you can use extension methods to simplify this if you want.

Michael Taylor 47,966 Reputation points

2021-07-10T19:06:12.29+00:00

For example,

public static class MyExtensions { //Just sememsters public static IQueryable<Department> IncludeSemesters ( this IQueryable<Department> source ) { return source.Include(x => x.TblSemesters); } //All children public static IQueryable<Department> IncludeChildren ( this IQueryable<Department> source ) { return source.IncludeSemesters() .Include(x => x.TblPrograms); } }

Simplifies queries but can be dangerous if used improperly.

var currentSemesterDepartments = ca.Departments.IncludeChildren().Where(x => x.TlbSemesters.Contains("Fall 2021"));

Just guessing on your EF structure though. Department wouldn't have a semester but it would have a list of semesters and so you search that using the where clause to filter them down. But I suspect your query isn't going to perform well so you need to be careful.

Malik Humza Yunas 1 Reputation point

2021-07-10T20:54:42.147+00:00

Hi sir maybe I explained wrong. Please see the query i posted above in sql format. My table is courseallocations and that is mapped to department , programs , course and semesters if I let EF join courseallocations automatically to semesters then as per semester name there are many many records in semesters table. I know this all has been designed in very abnormal and wrong way of db but this was designed by third party developer who was just training and now there are lots of data in that and i designed new application but still can't go away from that structure because of too many flaws.

Regards,

Michael Taylor 47,966 Reputation points

2021-07-10T21:56:54.99+00:00

The joins in your original query are going to be completely managed by your EF configuration. This has nothing to do with your where clauses or LINQs query. Again, the only thing Include does is tell EF to add a join to the dependent table (eager loading) instead of waiting until it needs the data (lazy loading). It doesn't change anything about how that join is done.

Remove the where clause inside your Include call. It is not needed if your EF model is defined correctly. Assuming the remainder of your LINQ query clauses are correct then you should get what you want.

Michael Taylor 47,966 Reputation points

2021-07-10T21:57:04.987+00:00

Just guessing at your EF model here.

public class CourseAllocation { public Semester Semester { get; set; } public string Semester_Name { get; set; } public string Department_Code { get; set; } public string Program_Session { get; set; } public string Program_Code { get; set; } public string Course_Code { get; set; } public Course Course { get; set; } public string Employee_Code { get; set; } } public class Semester { public string Semester_Name { get; set; } public string Department_Code { get; set; } public string Program_Session { get; set; } public string Program_Code { get; set; } } public class Course { public string Course_Code { get; set; } }

I prefer fluent configurations and I think it makes it easier here.

modelBuilder.Entity<CourseAllocation>() .HasRequired(x => x.Semester) .WithMany() .HasForeignKey(x => new { x.Semester_Name, x.Department_Code, x.Program_Code, x.Program_Session }); modelBuilder.Entity<CourseAllocation>() .HasRequired(x => x.Course) .WithMany() .HasForeignKey(x => x.Course_Code);

Michael Taylor 47,966 Reputation points

2021-07-10T22:00:29.537+00:00

The EF configuration handles all the join logic so you don't need it anymore. Just guessing but a course allocation is 1:1 on semester and course. But a single semester/course can have any # of allocations.

//Breaking this up so you can more easily see what is going on... //Get all allocations and eager load semester and courses IQueryable<CourseAllocation> query = context.CourseAllocations.Include(x => x.Semester) .Include(x => x.Course) //Filter to the semester and employee query = query.Where(x => x.Employee_Code = "108020" && x.Semester_Name = "Spring2021"); //Execute var results = query.ToList();

Malik Humza Yunas 1 Reputation point

2021-07-10T22:12:01.807+00:00

Sir,

I really appreciate your help but actually somehow the 3rd party developer did really very wrong. CourseAllocation table has semestername as foregin key so to get semester we need to take department code from course allocation table and then semester name , program code, program session , program section.

Malik Humza Yunas 1 Reputation point

2021-07-10T22:12:10.867+00:00

Some how i have figured it out using LINQ.

var myquery = (from allocation in _context.TblCourseAllocations
join department in _context.TblDepartments on allocation.DepartmentCode equals department.DepartmentCode
join course in _context.TblCourses on allocation.CourseCode equals course.CourseCode
join semesterc in _context.TblSemesters on
new
{
allocation.SemesterName,
allocation.DepartmentCode,
allocation.ProgramCode,
allocation.ProgramSession,
allocation.SectionName
}
equals
new
{
semesterc.SemesterName,
semesterc.DepartmentCode,
semesterc.ProgramCode,
semesterc.ProgramSession,
semesterc.SectionName
}
join program in _context.TblPrograms on allocation.ProgramCode equals program.ProgramCode
where allocation.EmployeeCode.Equals(employee.EmployeeCode) & allocation.SemesterName.Equals(semester) & allocation.PaymentStatus.Equals("Regular/Unpaid")
select new AllocationObject(allocation,course, department,semesterc,program));

Can we do this USING fluent API ? I really like that way.

Malik Humza Yunas 1 Reputation point

2021-07-10T22:16:57.177+00:00

My department is as followed

public partial class TblDepartment
{
[DisplayName("Department Name")]
public string DepartmentName { get; set; }
[DisplayName("Department Description")]
public string DepartmentDescription { get; set; }

public string DepartmentCode { get; set; } [DisplayName("Faculty Name")] public int FacultyId { get; set; } public virtual TblFaculty Faculty { get; set; } public virtual ICollection<TblCourseAllocation> CourseAllocations { get; set; }

Malik Humza Yunas 1 Reputation point

2021-07-10T22:17:57.98+00:00

My model for CourseAllocation is as followed

public partial class TblCourseAllocation
{
public string ProgramCode { get; set; }
public string DepartmentCode { get; set; }
public string ProgramSession { get; set; }
public string SectionName { get; set; }
public string SemesterName { get; set; }
public string EmployeeCode { get; set; }
public string CourseCode { get; set; }
public string StartTime { get; set; }
public string EndTime { get; set; }
public string LectureDay { get; set; }
public int AllocationId { get; set; }
public string PaymentStatus { get; set; }
public string Finalize { get; set; }
public int? CreditHours { get; set; }
public string Description { get; set; }
public string OldCourseCode { get; set; }
public string OldEmployeeCode { get; set; }
public int StartLectureNo { get; set; }
public int EndLectureNo { get; set; }
public DateTime? AllocatedAt { get; set; }
public string Note { get; set; }
public TblEmployee Employee { get; set; }
public TblCourse TblCourse { get; set; }
public TblDepartment Department { get; set; }

public TblProgram Program { get; set; } }

Michael Taylor 47,966 Reputation points

2021-07-11T00:27:23.89+00:00

Yes you can do that using LINQ extension method Join but you don't have to. It is a waste of resources if EF is properly configured. EF already handles the joins so you don't need any of them. That is basically the whole point of EF. The relationship between tables is not something your code (outside EF configuration) needs to code against. Unless you're doing joins that are not defined by the DB then you should not need LINQ joins at all.

Is your DBcontext properly configured to represent the table relationships as I had shown earlier?

Michael Taylor 47,966 Reputation points

2021-07-11T00:28:26.783+00:00

Yes that lines up with what I said it probably looked like. Did you properly configure your EF context to represent the relationships? If you did then all you need is the small query I gave earlier where you use the where method to select just the data you care about.

Michael Taylor 47,966 Reputation points

2021-07-11T00:30:39.44+00:00

Assuming that semestername is the PK on the semester table then this is exactly what they should have done. Dependent tables have a FK to the parent table containing the related data. This is how relational databases work. EF is coded to handle this automatically. All you have to do is defined the column(s) in the dependent table that make up the FK to the parent table. That is what I demoed in my code block. As I mentioned there I find the fluent configuration for these kinds of things to be cleaner than trying to use attributes.

Please try the query I gave against your existing data model and see what happens. Report back on any issues you run across.
Sign in to comment