I'm using Entity Framework 6 and DbContext
. I want to eager load records from the database for the purpose of using these records in the loop. I would like to explain with example (it's just example, please don't say me it is unrealistic in real world).
I have a DataGridView
which shows students list. In the Student
table, every record has a column favorite_subject_id
.
While showing each student record in DataGridView
, I want to find and display the subject name from the Subject
table with the student's favorite_subject_id
(let's say there is no database relationship between them. I know it should be in real world project. But it's just an example to clear my question.)
As first, I developed this concept as the following code:
foreach (DataGridViewRow row in gridView.Rows)
{
if (!row.IsNewRow)
{
var student = (Student)(row.DataBoundItem);
var favorite_subject = dbContext.Subjects.find(student.favorite_subject_id);
row.Cells[colFs.Index].Value = favorite_subject.subject_name.ToString();
}
}
But this code implementation will touch the database for every student records. It will damage the performance.
So I want to eager load the subjects records into the memory before loop. In the loop, I just want to search from these eager loaded records from the memory. The problem is that I don't know which is the right(best) way to eager load the records into the memory.
I wrote some code for this purpose. But I'm not sure whether it is right way to do.
var lstSubjects = dbContext.Subjects.ToList<Subject>();
foreach (DataGridViewRow row in gridView.Rows)
{
if (!row.IsNewRow)
{
var student = (Student)(row.DataBoundItem);
var favorite_subject= lstSubjects .Find(x => x.subject_id == student.favorite_subject_id);
row.Cells[colFs.Index].Value = favorite_subject.subject_name.ToString();
}
}
So the question is which is the best way to eager load the records from the database into the memory (by using DbContext
)?
You can use Include to do eager loading but this can be done only if you have database relationship between that tables.In that scenario you can use below code.
dbContext.Students.Include(x=>x.Subjects)
assuming you have DbSet Students corresponding to student table and and Subjects navigation property in Students dbset which denotes the relationship.
This will load all the students along with all the subjects linked to them.