How to get the foreign key in Entity Framework?

Question

Hello,

I am developing StudentApp in .NET 3.5 SP1 MVC Application.

I have two tables

1. Course
   CourseID, course_Name
2. Students
   studentID, student_Name, courseID(fk)

Now I made StudentApp.dbml which is having both table as entities.

As Foreign key will not be present in student entity, I can not display courseID in student model, more over i can not generate add, edit, list views.

So tell me how to display courseID(fk) in student & i also want course name instead.

And also dropdownbox showing course name & storing courseID in edit view .

Answer 1

Hi There,

Can you post your DBML? Also, DBML is used in LINQ to SQL (L2S) - EDMX is the mapping used in the ADO Entity Framework. Are you using LINQ to SQL or the Entity Framework (EF)?

No matter which one you are using - they both support Foreign Keys and you would get a property representing either side of the relationship - you don't need to do anything special (the Foreign Key must exist in the database, of course).

In EF, the foreign keys are called "navigtion properties" and they work a little differently to Foreign Keys in L2S. Nothing major, but updating them and "eager loading" are somewhat different.

Just drop the tables onto the map in the designer in Visual Studio (or generate using command line equivalents if you prefer).

Regarding Foreign Keys and Drop Down Lists (and other UI goodness) - I wrote a couple of blog entries on some approaches which might suit you. One part is located here and part two is located here.

Answer 2

If your using LINQ-to-SQL and created a DBML file in Visual Studio then the foreign keys can be listed through the Course property in the Student object (automatically generated so since it is a one-to-many relationship from Student). Sort of like this:

var studentCourseIds =
    from s in context.Students
    select s.Course.CourseID;

Since your goal is to find the coursename then it is already accessible with Student.Course.course_Name.

Answer 3

if you create the correct relationship in your SQL server database, then when you add the tables to your DBML designer, the relationships will be copied across also and your code will link up automatically.

Answer 4

I'm pretty sure you have to load the foreign reference for each entity. Since I have no idea how you've constructed your API, I'll have to give you a pseudocode'ish example, but I think this is what you need to do.

List<Students> studList = [your_db_facade].SelectStudents() // Or however you retrieve your students

foreach (Students singleStudent in studList)
    singleStudent.Context.CourseReference.Load() //CourseReference.Load() should be in the framework

Then you get the CourseID and name from the single student entity like

singleStudent.Course.CourseID
singleStudent.Course.course_Name

It could look slightly different for you, but I think the key to solving your problem is CourseReference.Load().