How do I load related entities from a database view using entity framework?

Question

I have been struggling with a "one to many" relationship regarding a database view.

Im using entity framework and I have this "simplified" model:

A "Course" entity. A "Student" entity.

A Course can have many Students.

The following code works fine:

public class Course
{
    public int Id { get; set; }
    public virtual List<Student> Students { get; set; }
}
public class Sudent
{
    public int CourseId { get; set; }
    public virtual Course Course { get; set; }
}


private List<Course> GetCourses()
{
    List<Course> courses;
    using (var db = context ?? new MyContext())
    {
        courses = (from course in db.Courses select course)
                    .Include(l => l.Students)
                    .ToList();
    }
}

Here is my MyContext:

public class MyContext : DbContext
{
    protected MyContext()
    {}
    public virtual DbSet<Course> Courses { get; set; }
    public virtual DbSet<OpenCourse> OpenCourses { get; set; }
}

I have a view "OpenCourses" that gets all courses that are open.

public class OpenCourse
{
    public int Id { get; set; }
    public virtual List<Student> Students { get; set; }
}

private List<OpenCourse> GetOpenCourses()
{
    List<Course> courses;
    using (var db = context ?? new MyContext())
    {
        courses = (from course in db.OpenCourses select course)
                    .Include(l => l.Students)
                    .ToList();
    }
}

When I run it I get the following inner-exception: "Invalid column name 'OpenCourse_Id'."

I get that I somehow have to associate the OpenCourse-view with many Students. But how?

Prefereably I would like to do this in my method and my lambda expression.

This association is usually done within the database context object. Are you able to post the code for `MyContext`? — cmcquillan, May 09 '16 at 13:53
Yes. I have seen that. But I have not succeeded. Any ideas on how to how to? — Gradde, May 09 '16 at 14:51

score 0 · Answer 1 · edited May 23 '17 at 11:59

0

You don't have a relation property, from Student to OpenCourse.

You can add it like this.

public class Student
{
    public int CourseId { get; set; }
    public int OpenCourseId { get; set; }
    public virtual Course Course { get; set; }
}

But at this time, I think you need to review your domain design.

Also check this for n to m relations. https://stackoverflow.com/a/8927380/465536

edited May 23 '17 at 11:59

Community

1
1

answered May 09 '16 at 14:20

R Quijano

1,301
9
10

hmm.. I do not want to add the property OpenCourseId to the entity Student for the reason to get my view to work. – Gradde May 09 '16 at 14:34
I know that my domain design is realy bad in my example. In real life I do not have these entities. Its much more complex. But thats not the point. The point is: If i have a database view that returns the same colums as a table, how can I get the relations to work when i refer to that view using entity framework? Specifically in my example: One to many. – Gradde May 09 '16 at 15:21

score 0 · Accepted Answer · answered May 09 '16 at 15:47

You are going to need to override the OnModelCreating method of the MyContext object. Entity Framework has some other ways to define relationships on the model itself, but this is unlikely to work with your current setup (multiple entities having a collection of students).

I am assuming that your OpenCourses view is actually a wrapper around your Courses table. This means I am assuming that Student.CourseId is also your foreign key to OpenCourses.

With that in mind, this is how OnModelCreating might look

public class MyContext : DbContext
{
    protected MyContext()
    {}
    public virtual DbSet<Course> Courses { get; set; }
    public virtual DbSet<OpenCourse> OpenCourses { get; set; }
}

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<OpenCourse>()
        .HasMany<Student>(p => p.Students)
        .WithRequired()
        .HasForeignKey(p => p.CourseId);

    base.OnModelCreating(modelBuilder);
}

Works beautifully. Straight out of the box! Thanks. – Gradde May 09 '16 at 16:19 — Gradde, May 09 '16 at 16:19

How do I load related entities from a database view using entity framework?

2 Answers2