DefaultIfEmpty with join in Where clause produces invalid SQL

[Paul_Ibis]

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)

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
                        });

and this is the SQL that is created which is invalid

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)

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

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])

[Paul_Ibis]

This will work if the linq query is changed to

Code: Select all

var test = (from trans in sc.DB.PosTransaction.Where(p => p.TransDateTime >= model.StartDate
                                                     && p.TransDateTime < model.EndDateNextDay)
                        join payment in sc.DB.PosTransPayment on trans.PosTransID equals payment.PosTransID
                        from loc in sc.DB.Location.Where(p => p.Fk_RetailLocation == trans.LocationID).DefaultIfEmpty()
                        select new 
                        {
                            PosTransID = trans.PosTransID
                        });

which produces the valid SQL

Code: Select all

SELECT [t1].[PosTransID]
FROM [dbo].[PosTransaction] [t1]
INNER JOIN [dbo].[PosTransPayment] [t2] ON [t1].[PosTransID] = [t2].[PosTransID]
LEFT OUTER JOIN [dbo].[Location] [t3] ON [t3].[fk_RetailLocation] = [t1].[LocationID]
WHERE ([t1].[TransDateTime] >= @p0) AND ([t1].[TransDateTime] < @p1)

However, it would be good if the original code as posted worked so we wouldn't have to go through the whole code base and re-write each similar query. I just had a quick look through the code base and there would be around 100 queries that would need to be re-written.

[Shalex]

We have reproduced the bug and are working on a solution. We will notify you about the fix.