How to have multiple one-to-many relations between two entities using Entity Framework Code First

Question

Below is a simple approach to save relational database records which is working perfectly fine. I have doubt on one scenario. Before that i need to know the way i am approaching has any difficulties if the database complexity increases. Any better, efficient but simple approach?

ONE to ONE:

tb_student // store student details
id, name, country_id (country_id foriegnkey set with id of tb_country)

tb_country // store all available countries
id, name

[Table("tb_student")]
public class Student
{    
    [Key]
    public int id { get; set; }
    public string name { get; set; }
    public Country country { get; set; }
}
[Table("tb_country")]
public class Country
{    
    [Key]
    public int id { get; set; }
    public string name { get; set; }
}

with

student come as parameter or create new student
Country _country = // we have selected country

StudentModelContext sdb = new StudentModelContext();
student.country = _country;
sdb.students.Add(student);
sdb.SaveChanges();

ONE to MANY:

tb_student // store student details
id, name

tb_typesubject // store all available subjects
id, name

tb_subject // store student - subject relation
id, student_id, subjecttypeid

[Table("tb_student")]
public class Student
{    
    [Key]
    public int id { get; set; }
    public string name { get; set; }
    public List<Subject> subjects { get; set; }
} 
[Table("tb_typesubject")]
public class TypeSubject
{    
    [Key]
    public int id { get; set; }
    public string name { get; set; }
} 
[Table("tb_subject")]
public class Subject
{    
    [Key]
    public int id { get; set; }
    public int subjecttypeid { get; set; }
    // we dont have to create student_id here
}

with

student come as parameter or create new student
TypeSubject _subjType1 = // we have selected subject list
TypeSubject _subjType2 = // we have selected subject list

Subject _subject1 = new Subject();
_subject1.subjecttypeid = _subjType1.id;
Subject _subject2 = new Subject();
_subject2.subjecttypeid = _subjType2.id;

StudentModelContext sdb = new StudentModelContext();
student.subjects = new List<Subject>;
student.subjects.add(_subject1);
student.subjects.add(_subject2);
sdb.students.Add(student);
sdb.SaveChanges();

This works perfectly. And i am very glad. We can load all values by

Student stud = sd.students.Find(1);
stud.Entry(stud).Collection(s => s.subjects).Load();

If student can give fees by installment for each subject

for (int i = 0; i < stud.subjects.Count; i++)
    sd.Entry(stud.subjects[i]).Collection(f => f.fees).Load();

My doubt is how to design following scenerio:

There will be review for each student which is send by another student. How to do this for class like:

[Table("tb_student")]
public class Student
{    
    [Key]
    public int id { get; set; }
    public string name { get; set; }
    public List<Review> reviews { get; set; }
} 
[Table("tb_review")]
public class Review
{    
    [Key]
    public int id { get; set; }
    public string message { get; set; }
    public int student_id { get; set; } // review of which student
    public Student reviewer { get; set; } // whom send the review
}

any help ?

Maximilien · Accepted Answer · 2014-08-19T09:00:54.187

2

Try to add 2 Students in your Review class, for example:

[Table("tb_review")]
public class Review
{    
    [Key]
    public int id { get; set; }
    public string message { get; set; }
    public Student student{ get; set; } // review of which student
    public Student reviewer{ get; set; } // whom send the review
}

And your Student class should be like this:

[Table("tb_student")]
public class Student
{    
    [Key]
    public int id { get; set; }
    public string name { get; set; }
    [InverseProperty("student")]
    public List<Review> reviewAbout{ get; set; }
    [InverseProperty("reviewer")]
    public List<Review> reviewBy{ get; set; }
}

edited Aug 19 '14 at 09:00

answered Aug 18 '14 at 15:24

Maximilien

221
2
10

thanks for you answer. If the review list is set [NotMapped] we can save this. Review list should be there on student because a student will get many reviews, right ? How we can load the review list for a student ? and also load reviewer ? – abduIntegral Aug 19 '14 at 06:44
You're right, you need something else in EF called InverseProperty data annotation. I edited my answer. – Maximilien Aug 19 '14 at 09:04
thanks for help. it worked :-) it would be a great help to others too if you accept my edit for you answer – abduIntegral Aug 20 '14 at 08:26
i have edited your "this" answer with some changes and details about how to fetch results. it is shown that some need to accept to display it and i think its you. thats why asked – abduIntegral Aug 22 '14 at 09:48
I'm not an expert in StackOverflow but it's weird I don't see anything. Have a look [here](http://stackoverflow.com/help/privileges/edit) – Maximilien Aug 22 '14 at 13:01
me too not an expert here. any way leave it if you are not alerted. may be it have to be done by someone other than us. when i edited (thinking may be that will help people like me) and saved its appearing after some time when refreshed edit is not there. i though someone need to approve that and it was you because the answer is yours. thanks again for your time – abduIntegral Aug 22 '14 at 16:34

score 1 · Answer 2 · answered Aug 20 '14 at 08:33

namespace MvcApplication4.Models
{
 [Table("tb_book")]
public class Book
{
    [Key]
    public int ID { get; set; }
    public string Title { get; set; }

    [InverseProperty("Books")]
    public Author Author { get; set; }
}
[Table("tb_author")]
public class Author
{
    [Key]
    public int ID { get; set; }
    public string Name { get; set; }

    [InverseProperty("Author")]
    public ICollection<Book> Books { get; set; }
}
[DbConfigurationType(typeof(MySql.Data.Entity.MySqlEFConfiguration))]
public class StudentModelContext : DbContext
{
    public DbSet<Book> Books { get; set; }
    public DbSet<Author> Authors { get; set; }
}
}

Table structure

CREATE TABLE `tb_book` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Title` varchar(45) DEFAULT NULL,
`Author_ID` int(11) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `ab_idx` (`Author_ID`),
CONSTRAINT `ab` FOREIGN KEY (`Author_ID`) REFERENCES `tb_author` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;


CREATE TABLE `tb_author` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(45) DEFAULT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

How to have multiple one-to-many relations between two entities using Entity Framework Code First

2 Answers2

Linked