Linq Query Return zero rows how to make left join with linq

Mark Tabor

6y2.2k.NET

Hi i am facing a problem with my Linq query i have the following table

the issue is i am getting zero rows and when i inspect and bebug the query it says "Enumeration yielded no results"

below is my index method

public ActionResult Index()
{
ViewBag.PopulateGrades = _IEducation.PopulateGrade();
ViewBag.PopulatePrograms = _IEducation.PopulatePrograms();
ViewBag.PopulateStudents = _IEducation.PopulateStudents();
ViewBag.PopulateCourses = _IEducation.PopulateCourses();
ViewBag.PopulateModules = _IEducation.PopulateModules();
ViewBag.PopulateYear = _IYear.PopulateYears();
var StudentVSCourses = db.StudentCoursesAssigned.ToList();
ViewBag.Block_Id = new SelectList(db.Blocks, "Id", "Name");
ViewBag.Semester_Id = _ITeacherCoruses.PopulateSemsters();
using (DatabaseContext db = new DatabaseContext())
{
List Student_Assigned_courses = db.StudentCoursesAssigned.ToList();
List courses = db.Courses.ToList();
List Module = db.Moduels.ToList();
List year = db.Years.ToList();
List semesters = db.Semesters.ToList();
List Programs = db.Programs.ToList();
List students = db.Students.ToList();
List curr = db.Courses.ToList();
List grade = db.grades.ToList();
IEnumerable h = db.Courses.ToList();
var employeeRecord = from e in Student_Assigned_courses
join d in students on e.Student_id equals d.Student_Id into table1
from d in table1.ToList()
join i in courses on e.Course_Id equals i.Course_Id into table2
from i in table2.ToList()
join m in Module on e.Module_Id equals m.Id into table3
from m in table3.ToList()
join y in year on e.Year_Id equals y.Id into table4
from y in table4.ToList()
join p in Programs on e.Program_Id equals p.Id into table5
from p in table5.ToList()
join g in grade on e.Grade equals g.Id into table6
from g in table6.ToList()
join s in semesters on e.Semster_Id equals s.Semester_Id into table7
from s in table7.ToList()
select new StudentCoursesViewModel
{
studentAssignedCourses = e,
courses = i,
programs = p,
//curses=curr,
curses = h,
semester = s,
studnets = d,
Year = y,
Modules = m,
grade = g,
};
return View(employeeRecord.ToList());
}

if i comment the yellow highlighted section than it returns the rows , can someone tell me how to adjust the linq query so that it should return the rows no matter even if the semester is null.

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mark Tabor6y ago
Junaid that is my entire query
var test1 = (from c in db.StudentCoursesAssigned
join e in db.Years.DefaultIfEmpty()
on c.Year_Id equals e.Id
join cc in db.Courses.DefaultIfEmpty()
on c.Course_Id equals cc.Course_Id
join g in db.grades.DefaultIfEmpty()
on c.Grade equals g.Id
join m in db.Moduels.DefaultIfEmpty()
on c.Module_Id equals m.Id
join p in db.Programs.DefaultIfEmpty()
on c.Program_Id equals p.Id
join b in db.Blocks.DefaultIfEmpty()
on c.Block_Id equals b.Id
join s in db.Semesters.DefaultIfEmpty()
on c.Semster_Id equals s.Semester_Id
join ss in db.Students.DefaultIfEmpty()
on c.Student_id equals ss.Student_Id
select new
{
Student_Name=ss.Student_FName,
Course=cc.Course_Name,
Active=c.Active,
Course_start_date=c.Enrolment_Start,
Course_End_date=c.Enrolment_End,
Grade=g.Name,
Module=m.Name,
Program=p.Program_Title,
Year=e.Id,
Semester=s.Semester_Title,
blocks=b.Id

}).ToList();
Junaid Musla6y ago
Replace

join s in semesters on e.Semster_Id equals s.Semester_Id into table7
from s in table7.ToList()

With

join s in semesters on e.Semster_Id equals s.Semester_Id into table7
from s in table7.DefaultIfEmpty()
Mark Tabor6y ago
Jignesh Kumar I checked the link but i cannot re-write it correctly can you check>
Jignesh Kumar6y ago
Hi Mark,

Please use this link which has same sceanrio as yours,
https://www.mssqltips.com/sqlservertip/3169/understanding-linq-to-join-multiple-tables-where-null-match-are-expected/
Jignesh Kumar6y ago
Please refer this link, For all tables you do not require, https://www.tutlane.com/tutorial/linq/linq-left-outer-join#:~:text=In%20LINQ%2C%20LEFT%20JOIN%20or,and%20%22DefaultIfEmpty()%22%20method.
Mark Tabor6y ago
This same query works fine but in that we are writing select new StudentCoursesViewModel but my query does not work although these two queries are getting data from the same table , but one is using Ajax call to get the data

var employeeRecord = (from e in Student_Assigned_courses
join d in students on e.Student_id equals d.Student_Id into table1
from d in table1.DefaultIfEmpty()
join i in courses on e.Course_Id equals i.Course_Id into table2
from i in table2.DefaultIfEmpty()
join m in Module on e.Module_Id equals m.Id into table3
from m in table3.DefaultIfEmpty()
join y in year on e.Year_Id equals y.Id into table4
from y in table4.DefaultIfEmpty()
join p in Programs on e.Program_Id equals p.Id into table5
from p in table5.DefaultIfEmpty()
join g in grade on e.Grade equals g.Id into table6
from g in table6.DefaultIfEmpty()
join s in semesters on e.Semster_Id equals s.Semester_Id into table7
from s in table7.DefaultIfEmpty()
join b in blocks on e.Block_Id equals b.Id into table8
from b in table8.DefaultIfEmpty()
select new StudentCoursesViewModel
{
studentAssignedCourses = e,
courses = i,
programs = p,
//curses=curr,
curses = h,
semester = s,
blocks=b,
studnets = d,
Year = y,
Modules = m,
grade = g,
}).ToList();

Mark Tabor
Mark Tabor6y ago
Jignesh kumar now i changed the query and getting this error

Unable to create a constant value of type 'System.Char'. Only primitive types or enumeration types are supported in this context.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.NotSupportedException: Unable to create a constant value of type 'System.Char'. Only primitive types or enumeration types are supported in this context.
Source Error:
Line 37: Line 38: Line 39: var test1 = (from c in db.StudentCoursesAssigned Line 40: join e in db.Years.DefaultIfEmpty() Line 41: on c.Year_Id equals e.Id
Jignesh Kumar6y ago
Hi,

You have .DefaultIfEmpty() method at wrong place you need to use while table joing. I think you need to use like below
from s in table7.DefaultIfEmpty();

on those table you need to apply above where data is null and you want to perform left join.
Mark Tabor6y ago
Jignesh Kumar thanks for the reply i am running below linq statement but it give count as zero ,alhough i have three rows in to the table .

var test1 = (from c in db.StudentCoursesAssigned
join e in db.Years
on c.Year_Id equals e.Id
join cc in db.Courses
on c.Course_Id equals cc.Course_Id
join g in db.grades
on c.Grade equals g.Id
join m in db.Moduels
on c.Module_Id equals m.Id
join p in db.Programs
on c.Program_Id equals p.Id
join b in db.Blocks
on c.Block_Id equals b.Id
join s in db.Semesters
on c.Semster_Id equals s.Semester_Id
join ss in db.Students
on c.Student_id equals ss.Student_Id
select new
{
Student_Name=ss.Student_FName.DefaultIfEmpty(),
Course=cc.Course_Name.DefaultIfEmpty(),
Active=c.Active,
Course_start_date=c.Enrolment_Start,
Course_End_date=c.Enrolment_End,
Grade=g.Name.DefaultIfEmpty(),
Module=m.Name.DefaultIfEmpty(),
Program=p.Program_Title.DefaultIfEmpty(),
Year=e.Id,
Semester=s.Semester_Title.DefaultIfEmpty(),
blocks=b.Id,
}).DefaultIfEmpty();
---
When i check my query generated by linq and run it in SQL it still gives zero result
LINQ GENERATED QUERY
SELECT
[Filter1].[Student_id1] AS [Student_id],
[Filter1].[Student_FName] AS [Student_FName],
[Filter1].[Course_Name] AS [Course_Name],
[Filter1].[Active] AS [Active],
[Filter1].[Enrolment_Start] AS [Enrolment_Start],
[Filter1].[Enrolment_End] AS [Enrolment_End],
[Filter1].[Name1] AS [Name],
[Filter1].[Name2] AS [Name1],
[Filter1].[Program_Title] AS [Program_Title],
[Filter1].[Id1] AS [Id],
[Filter1].[Semester_Title] AS [Semester_Title],
[Filter1].[Block_Id1] AS [Block_Id]
FROM ( SELECT 1 AS X ) AS [SingleRowTable1]
LEFT JOIN (SELECT [Extent1].[Student_id] AS [Student_id1], [Extent1].[Active] AS [Active], [Extent1].[Block_Id] AS [Block_Id1], [Extent1].[Enrolment_Start] AS [Enrolment_Start], [Extent1].[Enrolment_End] AS [Enrolment_End], [Extent2].[Id] AS [Id1], [Extent3].[Course_Name] AS [Course_Name], [Extent4].[Name] AS [Name1], [Extent5].[Name] AS [Name2], [Extent6].[Program_Title] AS [Program_Title], [Extent7].[Semester_Title] AS [Semester_Title], [Extent8].[Student_FName] AS [Student_FName]
FROM [dbo].[Student_courses] AS [Extent1]
JOIN [dbo].[Years] AS [Extent2] ON [Extent1].[Year_Id] = [Extent2].[Id]
JOIN [dbo].[Courses] AS [Extent3] ON [Extent1].[Course_Id] = [Extent3].[Course_Id]
JOIN [dbo].[Grades] AS [Extent4] ON [Extent1].[Grade] = [Extent4].[Id]
JOIN [dbo].[Modules] AS [Extent5] ON [Extent1].[Module_Id] = [Extent5].[Id]
JOIN [dbo].[Programs] AS [Extent6] ON [Extent1].[Program_Id] = [Extent6].[Id]
JOIN [dbo].[Semesters] AS [Extent7] ON [Extent1].[Semster_Id] = [Extent7].[Semester_Id]
JOIN [dbo].[Students] AS [Extent8] ON [Extent1].[Student_id] = [Extent8].[Student_Id]
WHERE [Extent1].[Block_Id] IS NOT NULL ) AS [Filter1] ON 1 = 1
Nisha Regil6y ago
Please refer this link. It will be useful to you. It shows a perfect example of Left join in LINQ

https://dotnettutorials.net/lesson/left-outer-join-in-linq/
Jignesh Kumar6y ago
Hi,

You need to use .DefaultIfEmpty() method to get expected result,

Please refer : https://odetocode.com/blogs/scott/archive/2008/03/25/inner-outer-lets-all-join-together-with-linq.aspx

Linq Query Return zero rows how to make left join with linq

Jignesh Kumar I checked the link but i cannot re-write it correctly can you check>