DefaultIfEmpty with join in Where clause produces invalid SQL
Posted: Wed 11 Mar 2020 00:13
And another issue I have found in migrating from LinqToSQL to LinqConnect. This one looks like it can't cope with the "DefaultIfEmpty" syntax when there is also a join in the Where clause rather than . This is quite a large legacy code base and it would be a pain to have to go through every query that is like this and rewrite it to a different left join syntax which I am presuming would work.
The LinqConnect page says : "LinqConnect was developed closely to LINQ to SQL and retains full compatibility with it." but I have only just started going through our code and have already found four instances where it isn't , so I'm getting a bit concerned now about what else I might find.
This is the query (note that there were a bunch more columns in the actual query)
and this is the SQL that is created which is invalid
with the following error :
SqlException: No column name was specified for column 1 of 't2'.
for comparison, this is the valid SQL that LinqToSQL creates
The LinqConnect page says : "LinqConnect was developed closely to LINQ to SQL and retains full compatibility with it." but I have only just started going through our code and have already found four instances where it isn't , so I'm getting a bit concerned now about what else I might find.
This is the query (note that there were a bunch more columns in the actual query)
Code: Select all
var test = (from trans in sc.DB.PosTransaction.Where(p => p.TransDateTime >= model.StartDate
&& p.TransDateTime < model.EndDateNextDay)
from payment in sc.DB.PosTransPayment.Where(p => p.PosTransID == trans.PosTransID)
from loc in sc.DB.Location.Where(p => p.Fk_RetailLocation == trans.LocationID).DefaultIfEmpty()
select new
{
PosTransID = trans.PosTransID
});
Code: Select all
SELECT [t1].[PosTransID]
FROM [dbo].[PosTransaction] [t1]
CROSS APPLY (
SELECT NULL
FROM [dbo].[PosTransPayment] [t3]
WHERE [t3].[PosTransID] = [t1].[PosTransID]
) [t2]
LEFT OUTER JOIN [dbo].[Location] [t4] ON [t4].[fk_RetailLocation] = [t1].[LocationID]
WHERE ([t1].[TransDateTime] >= @p0) AND ([t1].[TransDateTime] < @p1)
SqlException: No column name was specified for column 1 of 't2'.
for comparison, this is the valid SQL that LinqToSQL creates
Code: Select all
SELECT [t0].[PosTransID]
FROM [dbo].[PosTransaction] AS [t0]
CROSS JOIN [dbo].[PosTransPayment] AS [t1]
LEFT OUTER JOIN [dbo].[Location] AS [t2] ON [t2].[fk_RetailLocation] = ([t0].[LocationID])
WHERE ([t0].[TransDateTime] >= @p0) AND ([t0].[TransDateTime] < @p1) AND ([t1].[PosTransID] = [t0].[PosTransID])