ASP.NET Core - How to convert SQL Server statement to Entity Framework Core

Question

I have this SQL Server query which works and gives the desired result in SQL Server:

SQL Server query:

SELECT
  s.RegNumber,
  s.AdmissionDate,
  c.CourseName
FROM student AS s
JOIN student_course AS sc
  ON s.id = sc.StudentId
JOIN course AS c
  ON c.id = sc.CourseId
WHERE c.Id = '67A21699-DFE6-4CB6-96B6-E009FD389596';

StudentCourses:

public class StudentCourses
{
    public Guid Id { get; set; }
    public Guid StudentId { get; set; }
    public Guid CourseId { get; set; }
}

However, when I tried to turn it to ASP.NET Core-6 Entity Framework as shown below:

var sc = Context.StudentCourses
                .Where(x => x.CourseId == '67A21699-DFE6-4CB6-96B6-E009FD389596')
                .Include(x => x.Student)
                .Include(x => x.Course);
return sc;

It gives me student details as null.

How do I get this sorted out?

Thanks.

sc is an IQueryable. You need to call .ToListAsync to get the actual results. — Dai
– Dai, Commented Jul 24, 2022 at 6:39
x.CourseId == new Guid('67A21699-DFE6-4CB6-96B6-E009FD389596') — Charlieface
– Charlieface, Commented Jul 24, 2022 at 6:51
Are entity relationships properly modelled in your EF context? Typically, for many-to-many relationship the auxiliary table maintaining that relationship would not be represented as an entity. — n0rd
– n0rd, Commented Jul 24, 2022 at 7:20

Felix · Accepted Answer · 2022-07-24 18:50:03Z

In order to understand how to do that, you need to go back to basics

You need to study how to build a model in EF. There are many tutorials on the web; and SO discourages opinions; so I will just put a link to official Microsoft documentation. You will end up with something like this:

public class Student
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public ICollection<Course> Courses { get; set; }
}

public class Course
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public ICollection<Student> Students { get; set; }
}

Note that there is no class StudentCourses!

You need to study the implementation of many-to-many relationship in Entity Framework 6. Again, you should start at official Microsoft documentation; but it is a complex topic - so you probably need to spend more time on some tutorials. In DbContext you will have something like this:

modelBuilder.Entity<Students>()
            .HasMany(p => p.Courses)
            .WithMany(p => p.Students)

Once you have basic understanding of that, writing a LINQ statement is quite straightforward. Remember, you are NOT converting SQL to LINQ!

var sc = _context.Courses
    .Where(x => x.CourseId == '67A21699-DFE6-4CB6-96B6-E009FD389596')
    .Select(c => new { 
        c.CourseId,
        c.Name,
        c.Students
    });

Also note - that whenever you use Include(), it's "code smell"; it means you need to review your design, and maybe study some more tutorials.

Collectives™ on Stack Overflow

ASP.NET Core - How to convert SQL Server statement to Entity Framework Core

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related