linq2db: Wrong SQL in Join Statement
See following joins:
[Test, NorthwindDataContext]
public void Test(string context)
{
using (var db = new NorthwindDB(context))
{
DataConnection.TurnTraceSwitchOn(TraceLevel.Verbose);
DataConnection.WriteTraceLine += (a, b) => { System.Diagnostics.Debug.WriteLine(a + " -> " + b); };
var jj = from o in db.Order
join c in db.Customer on o.CustomerID equals c.CustomerID into cg
from c in cg.DefaultIfEmpty().Take(1)
select new {o, c};
var res = jj.ToList();
var jj2 = from o in db.Order
join c in db.Customer.Take(1) on o.CustomerID equals c.CustomerID into cg
from c in cg.DefaultIfEmpty()
select new { o, c };
var res2 = jj2.ToList();
}
}
Both Querys create the same SQL, wich I think is not correct. Don’t know if the first one is possible in SQL.
Generated SQL:
DataConnection: -- NorthwindSqlite SQLite
SELECT
[o].[OrderID],
[o].[CustomerID],
[o].[EmployeeID],
[o].[OrderDate],
[o].[RequiredDate],
[o].[ShippedDate],
[o].[ShipVia],
[o].[Freight],
[o].[ShipName],
[o].[ShipAddress],
[o].[ShipCity],
[o].[ShipRegion],
[o].[ShipPostalCode],
[o].[ShipCountry],
[t1].[CustomerID1] as [CustomerID11],
[t1].[CompanyName] as [CompanyName1],
[t1].[ContactName] as [ContactName1],
[t1].[ContactTitle] as [ContactTitle1],
[t1].[Address] as [Address1],
[t1].[City] as [City1],
[t1].[Region] as [Region1],
[t1].[PostalCode] as [PostalCode1],
[t1].[Country] as [Country1],
[t1].[Phone] as [Phone1],
[t1].[Fax] as [Fax1]
FROM
[Orders] [o]
LEFT JOIN (
SELECT
[cg].[CustomerID] as [CustomerID1],
[cg].[CompanyName],
[cg].[ContactName],
[cg].[ContactTitle],
[cg].[Address],
[cg].[City],
[cg].[Region],
[cg].[PostalCode],
[cg].[Country],
[cg].[Phone],
[cg].[Fax]
FROM
[Customers] [cg]
LIMIT 1
) [t1] ON [o].[CustomerID] IS NULL AND [t1].[CustomerID1] IS NULL OR [o].[CustomerID] = [t1].[CustomerID1]
DataConnection: Execution time: 00:00:00.0135097
DataConnection: -- NorthwindSqlite SQLite
SELECT
[o].[OrderID],
[o].[CustomerID],
[o].[EmployeeID],
[o].[OrderDate],
[o].[RequiredDate],
[o].[ShippedDate],
[o].[ShipVia],
[o].[Freight],
[o].[ShipName],
[o].[ShipAddress],
[o].[ShipCity],
[o].[ShipRegion],
[o].[ShipPostalCode],
[o].[ShipCountry],
[cg].[CustomerID1] as [CustomerID11],
[cg].[CompanyName] as [CompanyName1],
[cg].[ContactName] as [ContactName1],
[cg].[ContactTitle] as [ContactTitle1],
[cg].[Address] as [Address1],
[cg].[City] as [City1],
[cg].[Region] as [Region1],
[cg].[PostalCode] as [PostalCode1],
[cg].[Country] as [Country1],
[cg].[Phone] as [Phone1],
[cg].[Fax] as [Fax1]
FROM
[Orders] [o]
LEFT JOIN (
SELECT
[t1].[CustomerID] as [CustomerID1],
[t1].[CompanyName],
[t1].[ContactName],
[t1].[ContactTitle],
[t1].[Address],
[t1].[City],
[t1].[Region],
[t1].[PostalCode],
[t1].[Country],
[t1].[Phone],
[t1].[Fax]
FROM
[Customers] [t1]
LIMIT 1
) [cg] ON [o].[CustomerID] IS NULL AND [cg].[CustomerID1] IS NULL OR [o].[CustomerID] = [cg].[CustomerID1]
DataConnection: Execution time: 00:00:00.0005012
About this issue
- Original URL
- State: closed
- Created 7 years ago
- Comments: 16 (16 by maintainers)
Commits related to this issue
- test for #676 — committed to jogibear9988/linq2db by jogibear9988 7 years ago
- test for #676 — committed to jogibear9988/linq2db by jogibear9988 7 years ago
- fix #676 * Fixed bug with OUTER/CROSS APPLY JOINS * Corrected exception message. — committed to linq2db/linq2db by sdanyliv 7 years ago
https://stackoverflow.com/questions/2043259/sql-server-how-to-join-to-first-row or https://stackoverflow.com/questions/15626493/left-join-only-first-row or https://stackoverflow.com/questions/19825142/sql-left-join-first-match-only