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

Question

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.

I have edited your title. Please see, "Should questions include “tags” in their titles?", where the consensus is "no, they should not". — John Saunders
– John Saunders, Commented Mar 25, 2014 at 17:36

JC Ford · Accepted Answer · 2014-03-25 17:46:43Z

2

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.

answered Mar 25, 2014 at 17:46

JC Ford

7,0963 gold badges27 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Peter Smith Over a year ago

Just a question: does the First() not cause the data to load?

JC Ford Over a year ago

You're calling First() against the Notations and Courses DbSets, but within those queries you're trying to access the Course and Notation properties of the Certification entities you retrieved outside the loop. The null exception is coming from trying to access v.Course.CourseId and v.Notation.NotationId when v.Course and v.Notation are null.

BLo Over a year ago

The thing is, v.Course is not null! Because i retrieve them inside the loop : foreach retrieve them. Lazy loading is already enabled, thanks to the virtual properties in my classes (iirc). Thats the actual problem, everything is loaded fine, except for notation. see: dropbox.com/s/86aa1w9zjvurjv5/certstate.png

BLo Over a year ago

Tested adding Configuration.LazyLoadingEnabled = true; As i thought, it was already on, didnt change anything.

JC Ford Over a year ago

Looking at your question again...if lazy loading is on then the Notation and Course references should be loaded with the related Notation and Course entities when you access them. So why are you trying to explicitly query for them at all? You should be able to just omit the from c in db.Courses ... and from n in db.Notations lines and access the properties of v.Notation and v.Course directly: v.Course.CourseName

|

Peter Smith · Accepted Answer · 2014-03-25 17:48:03Z

1

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

answered Mar 25, 2014 at 17:48

Peter Smith

5,5529 gold badges54 silver badges80 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related