Light Speed 3 Linq Joins generates cross joins

 Hi,

I have been playing with the lightspeed 3 beta. Below is a linq query and the generated sql. You will notice that the first 3 joins were generated as a cross join ignoring the join condition. Is this a bug or am I missing something. Also notice that the select clause is bringing back all the table data instead of the specific columns I selected. This could potentially harm perfromance. Is there plans to fix this as well. Thanks.

 

 

 

 

 

 

 

var

 

 

perms = (from u in

uow.Users

 

 

join rm in uow.UserRoleMaps on u.Id equals

rm.UserId

 

 

join approle in uow.ApplicationRoles on rm.RoleId equals

approle.Id

 

 

join contsec in uow.ContentTabsSecurities on approle.Id equals

contsec.RoleId

 

 

join conttab in uow.ContentTabs on contsec.ContentTabId equals

conttab.Id

 

 

where

u.LoginName == authenticatedUserName

 

 

select new

{ Permission = contsec.Permission, Value = conttab.Value });

 

In the debbugger I looked at the sql this query generated and it was the following.

SELECT

t0.Id AS [t0.Id],

t0.Address AS [t0.Address],

t0.Business AS [t0.Business],

t0.CheckApprovalLimit AS [t0.CheckApprovalLimit],

t0.City AS [t0.City],

t0.Email AS [t0.Email],

t0.FirstName AS [t0.FirstName],

t0.IsActive AS [t0.IsActive],

t0.IsCheckProcessor AS [t0.IsCheckProcessor],

t0.IsSupervisor AS [t0.IsSupervisor],

t0.LastName AS [t0.LastName],

t0.LoginName AS [t0.LoginName],

t0.MiddleName AS [t0.MiddleName],

t0.OpenForAssignment AS [t0.OpenForAssignment],

t0.Password AS [t0.Password],

t0.PermitRights AS [t0.PermitRights],

t0.Phone AS [t0.Phone],

t0.State AS [t0.State],

t0.SupervisorId AS [t0.SupervisorId],

t0.TaxSsnPermission AS [t0.TaxSsnPermission],

t0.Title AS [t0.Title],

t0.Zip AS [t0.Zip],

t1.Id AS [t1.Id],

t1.RoleId AS [t1.RoleId],

t1.UserId AS [t1.UserId],

t2.Id AS [t2.Id],

t2.Name AS [t2.Name],

t3.Id AS [t3.Id],

t3.ContentTabId AS [t3.ContentTabId],

t3.Permission AS [t3.Permission],

t3.RoleId AS [t3.RoleId],

t4.Id AS [t4.Id],

t4.ContentUrl AS [t4.ContentUrl],

t4.DisplayName AS [t4.DisplayName],

t4.HelpUrl AS [t4.HelpUrl],

t4.IsMenu AS [t4.IsMenu],

t4.Seq AS [t4.Seq],

t4.TabGroup AS [t4.TabGroup],

t4.ToolTip AS [t4.ToolTip],

t4.Value AS [t4.Value]

FROM

Users t0

CROSS JOIN

ApplicationRoles t2

CROSS JOIN

ContentTabsSecurity t3

CROSS JOIN

ContentTabs t4

INNER JOIN

UserRoleMap t1

ON

t0.Id = t1.UserId

WHERE

t0.LoginName = 'mylogin'

 

 

 

 

 

 

Thanks for reporting this.  Generating cross instead of inner joins is a known bug which we are working on.  We're not sure whether we'll be able to address the "select everything" issue in 3.0: currently we handle projections on the server (i.e. select only required columns) for non-join scenarios, but for joins we have to perform the projection client-side.  We are looking into improving this as part of the cross join fix but we can't commit to it at this stage.  We'll post an update when we know the outcome.

Hi Ivan,

I guess this is also reported here http://www.mindscape.co.nz/forums/Thread.aspx?ThreadID=2518, so not to cross-post I'll ask here:

Could you maybe give us some more light on this joins issue? We were switching to LS 3 primarily because of LINQ joins support. And as we're waiting for the answer to this issue reported a week ago, one wonders that something is potentially "wrong" (sorry for a harsh word, my english vocabulary is poor) in your engine regarding approach to complex joins (in the sense of what does a user expect how should LINQ join queries end up as SQL queries).

The question being asked is because, as we are in the later stages of the project, it looks to me like we should still stay away from LS joins for some time and stick with views.

Thanks

Hi Mark,

The issue relates to some restrictions that were previously in place which meant that a join would be checked that it was being made against the entity type of the query. When translating queries in from the LINQ provider this is not always the case (such as the example queries where you have a longer chain of joined tables) and we need to carry through the join criteria so we can maintain these as inner joins rather than them ending up as cross joins. We are currently working on updating this and they should be pushed out via an update beta within the next few days.

 

Jeremy

Hi Jeremy,

Thank you for your answer.

Please also note, mentioned in that other thread, that when you add a group by to those queries, the generated SQL falls apart.

Hi guys,

We will be pushing up a new installer to the store today which includes this fix. Let us know once you have had a chance to get it installed.

As a heads up, there is still more work going on in this area around optimizing the queries which get generated (e.g. a query may end up with redundant criteria between the join and where clause) but we wanted to get the immediate update out for you asap.

 

Jeremy

The updated build is now in the store on your account page - please let us know how you find it.

John-Daniel Trask