SqlExceptions occuring in linq query

Hi,

I'm encountering an sql exception when firing a linq query.

Linq qeury:

        using (var uow = Context.CreateUnitOfWork())
        {
            var periodeWerkgever = (from werkgeverField in uow.WgiImportFields
                                    join record in uow.WgiImportRecords on werkgeverField.WgiImportRecordId equals record.Id
                                    join periodeField in uow.WgiImportFields on record.Id equals periodeField.WgiImportRecordId
                                    join kop in uow.WgiImportKops on record.WgiImportKopId equals kop.Id
                                    where record.WgiImportKopId == 1 && record.Type == "Header" &&
                                        periodeField.Name == "Periode" &&
                                        werkgeverField.Name == "Werkgever"
                                    select new { Kop = kop, Periode = periodeField.ValueDateTime, WerkgeverID = werkgeverField.ValueString })
                         .FirstOrDefault();
        }

Generated SQL:

SELECT t0.* FROM ( SELECT t0.Id AS [t0.Id], t0.Name AS [t0.Name], t0.Type AS [t0.Type], t0.ValueDateTime AS [t0.ValueDateTime], t0.ValueNumber AS [t0.ValueNumber], t0.ValueString AS [t0.ValueString], t0.WgiImportRecordId AS [t0.WgiImportRecordId], t1.Id AS [t1.Id], t1.IsValid AS [t1.IsValid], t1.LineNumber AS [t1.LineNumber], t1.Type AS [t1.Type], t1.WgiImportKopId AS [t1.WgiImportKopId], t2.Id AS [t2.Id], t2.Name AS [t2.Name], t2.Type AS [t2.Type], t2.ValueDateTime AS [t2.ValueDateTime], t2.ValueNumber AS [t2.ValueNumber], t2.ValueString AS [t2.ValueString], t2.WgiImportRecordId AS [t2.WgiImportRecordId], t3.Id AS [t3.Id], t3.Name AS [t3.Name], t3.Type AS [t3.Type], t3.ValueDateTime AS [t3.ValueDateTime], t3.ValueNumber AS [t3.ValueNumber], t3.ValueString AS [t3.ValueString], t3.WgiImportRecordId AS [t3.WgiImportRecordId], t4.Id AS [t4.Id], t4.BatchGuid AS [t4.BatchGuid], t4.Code AS [t4.Code], t4.DatumTijd AS [t4.DatumTijd], t4.FileName AS [t4.FileName], t4.IsVerwerkt AS [t4.IsVerwerkt], t4.Periode AS [t4.Periode], t4.UserId AS [t4.UserId], t4.VerwerkingsDatum AS [t4.VerwerkingsDatum], ROWNUMBER() OVER(ORDER BY t0.Id) as RowNumber FROM WGIIMPORTFIELD t0 INNER JOIN WGIIMPORTRECORD t1 ON t0.WgiImportRecordId = t1.Id INNER JOIN WGIIMPORTFIELD t2 ON t1.Id = t2.WgiImportRecordId INNER JOIN WGIIMPORT_KOP t4 ON t1.WgiImportKopId = t4.Id WHERE (((t1.WgiImportKopId = 1 AND t1.Type = 'Header') AND t2.Name = 'Periode') AND t0.Name = 'Werkgever') ) t0 WHERE RowNumber <= 1

The exception message is:

The multi-part identifier "t3.Id" could not be bound. The multi-part identifier "t3.Name" could not be bound. ... etc

To illustrate I attach a test case. 1. Change the connection to the point to the database file (included in the solution) 2. Run the project en press the 'SQL Exception' button.

Regards, Arjan

Thanks for the repro project, this looks like a bug so I am digging into what the problem is here. I will post an update once we have tracked down the issue (hopefully with a fix we can push out to the nightlies).

Jeremy

Hi Jeremy,

Another query failing simularly is:

var leftFields = (from mutField in uow.WgiImportFields
                join standField in uow.WgiImportFields on mutField.Name equals standField.Name into standFields
                from standField in standFields.DefaultIfEmpty()
                where mutField.WgiImportRecordId == 1 && standField.WgiImportRecordId == 2
                select new
                {
                    Name = standField.Name,
                    ValueDateTime_New = mutField.ValueDateTime,
                    ValueDateTime_Old = standField.ValueDateTime,
                    ValueNumber_New = mutField.ValueNumber,
                    ValueNumber_Old = standField.ValueNumber,
                    ValueString_New = mutField.ValueString,
                    ValueString_Old = standField.ValueString,
                }).ToList();

I hope you can find the cause quickly.

Regards, Arjan

Hi Jeremy,

Any progress on the matter? This issue is becoming an impedement for the development of our project.

Regards, Arjan

Hi Arjan,

We have identified the problem which is a mapping issue in the LINQ provider and are still working on a fix. Unfortunately the nature of the change we need to make conflicts with other scenarios so we are looking at if we need to add in some type of option to identify the nature of what you are intending to do or if we can accomodate all the scenarios under a unified change.

I am hoping we should have this covered in the nightlies next week. In the meantime as a workaround you could look at using the standard LightSpeed querying API to express this rather than LINQ.

Jeremy

Hi Jeremy,

I have yet another linq query that fails. Here the exception is:

Message "The correlation name 't1' is specified multiple times in a FROM clause.".

The table WgiImportFields is added twice to the from clause. One time as left outer join and the second time as inner join.

Sql: SELECT t0.Id AS [t0.Id], t1.Name AS [t1.Name], t1.ValueDateTime AS [t1.ValueDateTime], t1.ValueNumber AS [t1.ValueNumber], t1.ValueString AS [t1.ValueString] FROM WGIIMPORTRECORD t0 LEFT OUTER JOIN (SELECT WGIIMPORTFIELD.Id, WGIIMPORTFIELD.Name, WGIIMPORTFIELD.Type, WGIIMPORTFIELD.ValueDateTime, WGIIMPORTFIELD.ValueNumber, WGIIMPORTFIELD.ValueString, WGIIMPORTFIELD.WgiImportRecordId FROM WGIIMPORTFIELD WHERE WGIIMPORTFIELD.Name = 'Dummy' ) t1 ON t0.Id = t1.WgiImportRecordId INNER JOIN WGIIMPORTFIELD t1 ON t0.Id = t1.WgiImportRecordId

    public void IncorrectSql()
    {
        using (var uow = Context.CreateUnitOfWork())
        {
            try
            {
                var leftFields = (from record in uow.WgiImportRecords
                                  join standField in uow.WgiImportFields.Where(wif => wif.Name == "Dummy") on record.Id equals standField.WgiImportRecordId into standFields
                                  from standField in standFields.DefaultIfEmpty()
                                  select new
                                  {
                                      RecordId = record.Id,
                                      Name = standField.Name,
                                      ValueDateTime = standField.ValueDateTime,
                                      ValueNumber = standField.ValueNumber,
                                      ValueString = standField.ValueString,
                                  }).ToList();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message);
            }
        }
    }

Has the Linq provider been refactored radically recently? I'm encountering incorrect sql in queries that IMHO are not very complex.

Regards, Arjan

Just a quick update to let you know we are still working on getting this resolved. We have decided to go with the approach of using a CompatibilityOption based switch on the LightSpeedContext to control this behaviour, as before Ill update you once we have this in the nightlies for you with more details.

In terms of the other query issue - thanks for the repro test, Ill have a look at this separately as it appears to be unrelated to the original issue.

Jeremy

Hi Jeremy,

Introducing a switch to fix bugs doesn't comfort me. I have some questions.

• What scenario's are conflicting with the fix for these sql exceptions?
• Will they be mutually exclusive?
• Aren't these exceptions bugs that need to be fixed in all scenario's?
• And lastly, do I need to turn on a switch to fix the bugs, or is it turned on by default?

Regards, Arjan

No I certainly agree its not ideal and we certainly intent to resolve this to cover all scenarios however given the nature of the problem its not likely we will be able to solve this in the short term as its not a trivial fix.

Currently there is a workaround available which is to write your query using the underlying querying API as the problem is in part of our LINQ providers translation code. The intention with providing a switch is to offer another workaround but one which would mean you can still use your existing LINQ query, the switch helps provide the currently missing context about how the aliasing needs to be managed to the LINQ provider which it can then use to make the correct translation.

If you are not comfortable with this Id rather not put it in but Im happy to do so to help you bridge the current issue. The switch would be something you can turn on/off as needed around execution of a specific query. It will not be on by default.

The conflicting scenarios are other cases of two instances of the primary table in the query being joined just under different circumstances than in your query.

Jeremy