{"action":"opened","issue":{"url":"https://api.github.com/repos/volkanceylan/Serenity/issues/3624","repository_url":"https://api.github.com/repos/volkanceylan/Serenity","labels_url":"https://api.github.com/repos/volkanceylan/Serenity/issues/3624/labels{/name}","comments_url":"https://api.github.com/repos/volkanceylan/Serenity/issues/3624/comments","events_url":"https://api.github.com/repos/volkanceylan/Serenity/issues/3624/events","html_url":"https://github.com/volkanceylan/Serenity/issues/3624","id":330639740,"node_id":"MDU6SXNzdWUzMzA2Mzk3NDA=","number":3624,"title":"Problem with SqlQuery Join and Where","user":{"login":"Estrusco","id":16304015,"node_id":"MDQ6VXNlcjE2MzA0MDE1","avatar_url":"https://avatars0.githubusercontent.com/u/16304015?v=4","gravatar_id":"","url":"https://api.github.com/users/Estrusco","html_url":"https://github.com/Estrusco","followers_url":"https://api.github.com/users/Estrusco/followers","following_url":"https://api.github.com/users/Estrusco/following{/other_user}","gists_url":"https://api.github.com/users/Estrusco/gists{/gist_id}","starred_url":"https://api.github.com/users/Estrusco/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Estrusco/subscriptions","organizations_url":"https://api.github.com/users/Estrusco/orgs","repos_url":"https://api.github.com/users/Estrusco/repos","events_url":"https://api.github.com/users/Estrusco/events{/privacy}","received_events_url":"https://api.github.com/users/Estrusco/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2018-06-08T12:31:40Z","updated_at":"2018-06-08T12:31:40Z","closed_at":null,"author_association":"CONTRIBUTOR","body":"Hi all.\r\n\r\nIn repository I want to add two (or more) joins to the Query, but I get an unexpected result.\r\nIf I write \r\n\r\n```cs\r\nprotected override void OnBeforeExecuteQuery()\r\n{\r\n    base.OnBeforeExecuteQuery();\r\n    \r\n    int myTestValueVariable = 1234;\r\n\r\n    Query.LeftJoin(\"[dbo].[Table1]\", new Alias(\"tab1\"), new Criteria(\"T0\", \"[ID]\") == new Criteria(\"tab1\", \"[ID]\"))\r\n         .Where(\"tab1.MyField = (select top 1 tab1.[IDValue] from [dbo].[Table1] where tab1.[IDValue] = \" + myTestValueVariable + \")\");\r\n}\r\n```\r\n\r\nI get the correct result: \r\n\r\n```SQL\r\nSELECT TOP 100 \r\nT0.[ID] AS [ID],\r\nT0.[CustomerID] AS [CustomerID],\r\nT0.[CompanyName] AS [CompanyName],\r\nT0.[ContactName] AS [ContactName],\r\nT0.[ContactTitle] AS [ContactTitle],\r\nT0.[Address] AS [Address],\r\nT0.[City] AS [City],\r\nT0.[Region] AS [Region],\r\nT0.[PostalCode] AS [PostalCode],\r\nT0.[Country] AS [Country],\r\nT0.[Phone] AS [Phone],\r\nT0.[Fax] AS [Fax],\r\nT0.[LastContactDate] AS [LastContactDate],\r\nT0.[LastContactedBy] AS [LastContactedBy],\r\nT0.[Email] AS [Email],\r\nT0.[SendBulletin] AS [SendBulletin] \r\nFROM [Customers] T0 \r\nLEFT JOIN [dbo].[Table1] tab1 ON (T0.[ID] = tab1.[ID]) \r\nWHERE tab1.MyField = (select top 1 tab1.[IDValue] from [dbo].[Table1] where tab1.[IDValue] = 1234 \r\nORDER BY T0.[CompanyName], T0.[ID];\r\nSELECT count(*)  \r\nFROM [Customers] T0 \r\nLEFT JOIN [dbo].[Table1] tab1 ON (T0.[ID] = tab1.[ID]) \r\nWHERE tab1.MyField = (select top 1 tab1.[IDValue] from [dbo].[Table1] where tab1.[IDValue] = 1234\r\n```\r\n\r\nBut if I add another Join\r\n\r\n```cs\r\n    Query.LeftJoin(\"[dbo].[Table1]\", new Alias(\"tab1\"), new Criteria(\"T0\", \"[ID]\") == new Criteria(\"tab1\", \"[ID]\"))\r\n     .Where(\"tab1.MyField = (select top 1 tab1.[IDValue] from [dbo].[Table1] where tab1.[IDValue] = 1234\" + myTestValueVariable + \")\");\r\n    Query.LeftJoin(\"[dbo].[Table2]\", new Alias(\"tab2\"), new Criteria(\"T0\", \"[ID]\") == new Criteria(\"tab2\", \"[ID]\"))\r\n     .Where(\"tab2.MyField = (select top 1 tab2.[IDValue] from [dbo].[Table2] where tab2.[IDValue] = 1234\" + myTestValueVariable + \")\");\r\n```\r\n\r\nI get this query:\r\n\r\n```SQL\r\nSELECT TOP 100 \r\nT0.[ID] AS [ID],\r\nT0.[CustomerID] AS [CustomerID],\r\nT0.[CompanyName] AS [CompanyName],\r\nT0.[ContactName] AS [ContactName],\r\nT0.[ContactTitle] AS [ContactTitle],\r\nT0.[Address] AS [Address],\r\nT0.[City] AS [City],\r\nT0.[Region] AS [Region],\r\nT0.[PostalCode] AS [PostalCode],\r\nT0.[Country] AS [Country],\r\nT0.[Phone] AS [Phone],\r\nT0.[Fax] AS [Fax],\r\nT0.[LastContactDate] AS [LastContactDate],\r\nT0.[LastContactedBy] AS [LastContactedBy],\r\nT0.[Email] AS [Email],\r\nT0.[SendBulletin] AS [SendBulletin] \r\nFROM [Customers] T0 \r\nLEFT JOIN [dbo].[Table1] tab1 ON (T0.[ID] = tab1.[ID]) \r\nLEFT JOIN [dbo].[Table2] tab2 ON (T0.[ID] = tab2.[ID]) \r\nWHERE tab1.MyField = (select top 1 tab1.[IDValue] from [dbo].[Table1] where tab1.[IDValue] = 1234 AND tab2.MyField = (select top 1 tab2.[IDValue] from [dbo].[Table2] where tab2.[IDValue] = 1234 \r\nORDER BY T0.[CompanyName], T0.[ID];\r\nSELECT count(*)  \r\nFROM [Customers] T0 \r\nLEFT JOIN [dbo].[Table1] tab1 ON (T0.[ID] = tab1.[ID]) \r\nLEFT JOIN [dbo].[Table2] tab2 ON (T0.[ID] = tab2.[ID]) \r\nWHERE tab1.MyField = (select top 1 tab1.[IDValue] from [dbo].[Table1] where tab1.[IDValue] = 1234 AND tab2.MyField = (select top 1 tab2.[IDValue] from [dbo].[Table2] where tab2.[IDValue] = 1234\r\n```\r\n\r\nThe where conditions are merged and inserted in second left join.\r\nAnyone have any suggest?\r\n\r\n"}}
{"actor":{"display_login":"Estrusco"}}