Linq expression generates wrong SQL when performing multiple joins

Hi,

See the following linq expression:

var queryA = from ticket in uow.Tickets join status in uow.TicketStatuses on ticket.TicketStatusId equals status.Id select new TicketInfo { TicketId = ticket.Id, TicketStatus = status.Name, Title = ticket.Title, OrganisationId = ticket.OrganisationId, AssignedUserId = ticket.AssignedToUserId };

When I execute the query above, the SQL generated returns 5 columns in the SELECT statement (as expected).

var queryB = from ticket in uow.Tickets join submitter in uow.TicketSubmitters on ticket.Id equals submitter.TicketId join status in uow.TicketStatuses on ticket.TicketStatusId equals status.Id select new TicketInfo { TicketId = ticket.Id, TicketStatus = status.Name, Title = ticket.Title, OrganisationId = ticket.OrganisationId, AssignedUserId = ticket.AssignedToUserId, CustomerId = submitter.OpenedByUserId };

When I execute query B, the SQL generated returns all columns from all tables (Tickets, TicketSubmitters and TicketStatus). How can I enforce this query to return me only the 6 columns that I want?

The version of Mindscape.Lightspeed assembly is 4.0.887.18671.

Thanks, Christiano

I have noticed that if I replace my POCO class TicketInfo to an anonymous type, the select statement works as expected.

Is there a way for me to make this work with my class?

I suppose it would be silly to copy from the anonymous type to the DTO class.

Thanks, Christiano.

Yes I believe this is something we addressed quite a while ago in 4.0 where projections into known types would fall back into being handled client side and we have since added support to handle this. Can you try with a more recent build of either 4.0 or 5.0 to check that this resolves your issue.

Jeremy