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
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.