CODEWITHSUNDEEP
c#linqentity-framework
BLo
BLo

Reputation: 13

SQL query shows good database values, but LINQ to Entity framework brings a null value from nowhere

I'm having a really strange problem here, and i dont have any clue why.

I'm supposed to make small localdb console app in C#. The goal is to enter persons (teachers, actually) in the DB, with a certain amount of information.

I have a few classes, but 2 of them are important here: Certification and Notation. Certifications are, well, certifications of the professors.

The code for these classes is this:

class Certification
{
    public int CertificationID { get; set; }
    public virtual Teacher Teacher { get; set; }
    public virtual Course Course { get; set; }
    public string CertificationName { get; set; }
    public virtual Notation Notation { get; set; }    
}    
class Notation
{
    public int NotationID {get;set;}
    public string Note {get;set;}
}

Nothing too dangerous. Through migrations i made my database, and they look like they should:

Certification:

CertificationID (PK)
CertificationName
Course_CourseID (FK to another class, course)
Notation_NotationID (FK to notations)
Teacher_TeacherID (FK to the teachers)

Notations:

NotationID (PK)
Note

My program allows me to add teachers, with all the informations i need, and for example, their certifications. Here, i made some dummy teacher, with a dummy certification. If i call SELECT * FROM Certification , i get exactly what i should get, a single line like this:

CertificationID = 6
CertificationName = placeholder
Course_CourseID = 13
Notation_NotationID = 12
Teacher_TeacherID = 5

Everything is correct in this. CourseID links to an actual course in the database, NotationID in an actual note, and Teacher to an actual teacher too. Everything is fine!

Now, i just want to show the certifications of our teacher:

var certifs = from c in db.Certifications where c.Teacher.TeacherID == item.TeacherID select c;

foreach(var v in certifs )
{
    var course = (from c in db.Courses where c.CourseID == v.Course.CourseID select c).First();
    var note = (from n in db.Notations where n.NotationID == v.Notation.NotationID select n.NotationID).First();

    Console.WriteLine("Name: " + v.CertificationName + ", related to the " + course.CourseName + " course, with a note of " + note);

    Console.WriteLine("");
}

And it doesn't work. When my foreach loop starts, my first item in the loop doesn't have any reference to a notation. Everything else is fine: the foreign keys for the course and the teachers are here, and valid, but for the notation, i only get a null value. So my certification item looks more like:

CertificationID = 6
CertificationName = placeholder
Course_CourseID = 13
Notation_NotationID = null
Teacher_TeacherID = 5

Basically, if i do a SQL Query, my row in the database is perfectly fine, but calling it through the entity framework (and LINQ) returns a null value for the notation. (which throws an exception when calling var note etc....

Does anybody have an idea about this? I'm really stuck on this.

I'm sorry if my English isn't good enough. If you guys need more information, just ask.

Anwsered by JC:

Lazy loading isnt working properly. Eager loading solves the problem.

Upvotes: 1

Views: 757

Answers (2)

Peter Smith
Peter Smith

Reputation: 5550

In your line

var note = (from n in db.Notations 
where n.NotationID == v.Notation.NotationID 
select n.NotationID).First();

you are selecting n.NotationID only which would return an integer only. Trying changing the select to select n

Upvotes: 1

JC Ford
JC Ford

Reputation: 7066

The problem is you aren't populating your navigation properties when you retrieve the Certification entities. Then you try to access them and they're null.

You either need to make sure lazy loading is turned on:

Configuration.LazyLoadingEnabled = true; //In your DbContext's constructor

in which case just accessing the Course and Notification references should cause them to be populated in separate database transactions...

...or you need to employ eager loading when querying against the DbSet:

var certifs = from c in db.Certifications.Include(c=>c.Course).Include(c=>c.Notation) where ...

Which will cause Course and Notation to be loaded at the same time Certifications is loaded all in one database transaction.

Upvotes: 2

Related Questions