Changed select to
select new { user.Id, user.Role }
Gives as SQL:
select user0_.UserId as col_0_0_,
role1_.Id as col_1_0_,
role1_.Id as Id39_,
role1_.Name as Name39_,
role1_.IsActive as IsActive39_,
role1_.EntityId as EntityId39_,
role1_.ParentId as ParentId39_
from Users user0_
left outer join Roles role1_
on user0_.RoleId = role1_.Id
order by user0_.UserId asc
Role.Id is duplicate???
Tried commenting out the order - no change.
Simplified to query to just include user.id and role.id:
select user0_.UserId as col_0_0_,
user0_.RoleId as col_1_0_
from Users user0_
left outer join Roles role1_
on user0_.RoleId = role1_.Id
So yes, it seems as if using a complex type leads to duplication: the id of
the foreign key is included plus all other columns (incl the id again).
When using a column in a conditions multiple times, this column gets also
duplicated (but I guess that's because the Linq parser does no distinct on
columns - but that's just a minor issue)
/Peter
Am Montag, 5. Oktober 2015 16:14:05 UTC+2 schrieb Ricardo Peres:
>
> At first it seems to be that it is ignoring the "select new ..." part, or,
> better, moving it to LINQ to Objects (after the query is run). That might
> happen because it is a "complex" expression.
> Can you try it with just "select new { user.Id, user.Role }"?
>
> RP
>
> On Monday, October 5, 2015 at 11:43:54 AM UTC+1, PeSo wrote:
>>
>> 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.
