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
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.
For example,
Simplifies queries but can be dangerous if used improperly.
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.
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,
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.Just guessing at your EF model here.
I prefer fluent configurations and I think it makes it easier here.
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.
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.
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.
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; }
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; }
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?
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.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
Activity