1

I am a beginner with EF and MVC, so, I have been using the Database first approach. I am facing a problem which is, I have in my database a table called Subjects, and another one called Prerequisites. The Idea is that each subject is supposed to have one or more prerequisite. When I created the ADO.NET Entity Data Model, it mapped those two tables into only one table -Subjects- but with two navigation properties both referencing the Subjects table. I need to create a controller to manage assigning prerequisites to subjects. So, I thought I could have just like my database, a separate table called Prerequisites which I can use as the Model Class to my controller. I don't know how to do this. please help if you can.

Here is the Generated Model for the Subjects table

enter image description here

Here is the diagram for the Subjects and Prerequisites tables

enter image description here

Kenchi
  • 191
  • 1
  • 3
  • 18

1 Answers1

1

In your controller, if you have a Subject in a variable called s, you can go s.tblSubject1 to get all the Subjects that are the Prerequisites. Similarly if you go s.tblSubjects you'll get the list of Subjects that this Subject is a prerequisite for. It may be the other way round, you could rename your relationships in the model better so you know which is which.

TDP
  • 1,141
  • 1
  • 9
  • 24
  • Okay, now what will happen if I try to assign a new prerequisite to a subject? how will the data be inserted into the Prerequisites table? – Kenchi Mar 13 '17 at 13:21
  • If you add a new Subject into the List of Prerequisites that you've returned for a Subject, and tell EF to save the changes, it should do the insert for you... You can use SQL Server Profiler to see what EF is doing under the covers, very useful. – TDP Mar 13 '17 at 13:34
  • Okay, Thank you very much. I will try it and mark your answer if it worked. Thanks again – Kenchi Mar 13 '17 at 13:52
  • I am sorry, can you please tell me how to write the equivalent of this sql query in Comprehension Syntax? select Sc from tblSubject where Sc not in (Select Prerequisite from tblPrerequisite where Sc = @subject) and Sc != @subject using the navigation properties – Kenchi Mar 13 '17 at 14:12
  • I prefer Lambda expressions... [SO question: Query vs Lambda](http://stackoverflow.com/questions/16185514/linq-query-or-lambda-expression) – TDP Mar 13 '17 at 14:19
  • 1
    Something along the lines of: `var notYetAssigned = dbContext.Subjects.Where(x => subject.tblSubjects.Any(y => x.Sc != y.Sc)).ToList();` – TDP Mar 13 '17 at 14:49
  • It throws an exception when trying to pass that list to a view stating the following: Unable to create a constant value of type 'GFIS.Models.Subject'. Only primitive types or enumeration types are supported in this context. and the SQL that the profiler is showing only retrieves subjects that are actually a prerequisite to the subject I am providing – Kenchi Mar 13 '17 at 15:21
  • I'm happy you've got as far as you have, but you should ask new questions now. Good luck and enjoy the journey. – TDP Mar 13 '17 at 15:35
  • Thank you so much for taking the time to answer my question :) – Kenchi Mar 13 '17 at 16:25