Hi,
I was just stumbling upon some rather strange Linq inefficiency where
columns are duplicated.
I wrote the following simple query (based against the model used in
NHibernate for Linq/ProjectionsTest.cs):
[Test]
public void ProjectMultipleConditionals()
{
var query = (from user in db.Users
orderby user.Id
select new
{
user.Id,
RoleId = user.Role != null ? user.Role.Id :
new int?(),
RoleName = user.Role != null ?
user.Role.Name: null
})
.ToList();
}
One would expect an sql containing just three columns (or maybe four if
role.Id is selected for each condition)
Upon inspecting the generated SQL, I see the following:
select user0_.UserId as col_0_0_,
role1_.Id as col_1_0_,
user0_.RoleId as col_2_0_,
role1_.Id as col_3_0_,
role1_.Name as col_4_0_,
role1_.Id as Id39_0_,
role1_.Id as Id39_1_,
role1_.Name as Name39_0_,
role1_.IsActive as IsActive39_0_,
role1_.EntityId as EntityId39_0_,
role1_.ParentId as ParentId39_0_,
role1_.Name as Name39_1_,
role1_.IsActive as IsActive39_1_,
role1_.EntityId as EntityId39_1_,
role1_.ParentId as ParentId39_1_
from Users user0_
left outer join Roles role1_
on user0_.RoleId = role1_.Id
order by user0_.UserId asc
It seems as if simply accessing the user.Role for comparison gets
translated to a select * from Role - and that twice!
And in my (more complicated use case) where I have 4 checks, and the
referenced table has 20 columns, I suddenly end up selecting 80 additional
columns.
You can easily work around that by adding a nullable RoleId property
(mapped as readonly), just don't know if someone is aware of that behavior
and if that is the recommended fix.
Btw when using the nullable RoleId property four times for checks, you get
4 selects to the same roleId column. Shouldn't that translate to an sql
comparison?
best regards,
Peter
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/nhusers.
For more options, visit https://groups.google.com/d/optout.
