Search code examples
c#nhibernateentity-framework-6lazy-loading

Why NHibernate is not sending extra select query in one-to-many during updating child?


I am quite new to NHibernate and I made simple app for testing NHibernate and Entity Framework performance. I noticed some problem with updating child in one-to-many relationship. During updating child Entity Framework is sending extra select query to get children. That seems ok because of lazy loading. But NHibernate is not sending extra select query to get children even when lazy loading is enabled. Here comes my question. Why is it not sending extra select query? Why doesn't need it? Thanks for help.

NHibernate code for updating:

var students = nHibernateSQLServerSession.Query<Student>().Where(s => s.Grades.Count > 0).Take(records).ToList();
using (nHibernateSQLServerSession.BeginTransaction())
{
    foreach (var student in students)
    {
        student.Grades.ElementAt(rand.Next(student.Grades.Count)).Score = rand.Next(7);
    }
    nHibernateSQLServerSession.GetCurrentTransaction().Commit();
}

Entity Framework code for updating:

using (var db = new ORMTesterContext("name=ORMTesterSQLServer"))
{
    students = db.Students.Where(s => s.Grades.Count > 0).Take(records).ToList();
    foreach (var student in students)
    {
        student.Grades.ElementAt(rand.Next(student.Grades.Count)).Score = rand.Next(7);
    }
    db.SaveChanges();
}

Relationship mapping in NHibernate:
Students

<set name="Grades" inverse="true">
    <key column="StudentId" />
    <one-to-many class="NHibernate.DataAccess.Entities.Grades.Grade, NHibernate.DataAccess" />
</set>

Grades

<many-to-one name="Student" class="NHibernate.DataAccess.Entities.Students.Student, NHibernate.DataAccess" column="StudentId" cascade="delete" />

Executed queries:
NHibernate:

exec sp_executesql 
N'select TOP (@p0) student0_.Id as id1_4_, student0_.Name as name2_4_, student0_.Surname as surname3_4_ 
from Students student0_ 
where (select (count(*)) 
    from Grades grades1_ 
    where student0_.Id=grades1_.StudentId)>@p1',N'@p0 int,@p1 int',@p0=1,@p1=0
exec sp_executesql 
N'UPDATE Grades 
SET Score = @p0, StudentId = @p1, TeacherId = @p2 
WHERE Id = @p3',N'@p0 int,@p1 int,@p2 int,@p3 int',@p0=1,@p1=78761,@p2=601,@p3=1202

Entity Framework:

SELECT TOP (1) 
    [Project1].[Id] AS [Id], 
    [Project1].[Name] AS [Name], 
    [Project1].[Surname] AS [Surname]
    FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Name] AS [Name], 
        [Extent1].[Surname] AS [Surname], 
        (SELECT 
            COUNT(1) AS [A1]
            FROM [dbo].[Grades] AS [Extent2]
            WHERE [Extent1].[Id] = [Extent2].[StudentId]) AS [C1]
        FROM [dbo].[Students] AS [Extent1]
    )  AS [Project1]
    WHERE [Project1].[C1] > 0
exec sp_executesql N'SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Score] AS [Score], 
    [Extent1].[StudentId] AS [StudentId], 
    [Extent1].[TeacherId] AS [TeacherId]
    FROM [dbo].[Grades] AS [Extent1]
    WHERE [Extent1].[StudentId] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=78761
exec sp_executesql N'UPDATE [dbo].[Grades]
SET [Score] = @0
WHERE ([Id] = @1)
',N'@0 int,@1 int',@0=6,@1=1201

Solution

  • Ok, my mistake. I completely forgot about NHibernate cache. The grade was already cached in first query and was checking queries for next attempts that's why I didn't notice extra query on first attempt.