Hello!

I ran into a problem with LINQ where the generated SQL seems to be wrong. I saw another post (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=13735&HighLight=1) with a similar problem but the proposed solution there didn't make it for me.

My data model is (see attached jpg):

---------    1  *   -------------------   *  1  ------------
| Video | ------->| VideoCategory |<---------| Category |
---------             -------------------                 -------------

Video has many Categories and is connected through an intermediary table VideoCategory.

I want to get all the Categories that are NOT connected to a specific Video. I try to run the following LINQ:

                var allCat = from c1 in linqMetaData.Category
                             select c1;

                var videoCat = from c in linqMetaData.Category
                               join vc in linqMetaData.VideoCategory on c.CategoryId equals vc.CategoryId
                               join v in linqMetaData.Video on vc.VideoId equals v.VideoId
                               where v.VideoId == 3
                               select c;

                var diff = allCat.Except(videoCat);

The above code is valid LINQ and I am able to retrieve the correct data from the database if I'm using e.g. LinqPad to execute LINQ directly in the database. However, when I try to run this through LLBLGen I get the following exception:

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException was unhandled Message="An exception was caught during the execution of a retrieval query: The multi-part identifier \"LPLA_2.CategoryId\" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."

The generated SQL:

SELECT DISTINCT [LPLA_1].[CategoryId], [LPLA_1].[Name], [LPLA_1].[Description] FROM [WeBeFree].[dbo].[Category] [LPLA_1]  WHERE ( ( ( NOT (  EXISTS (SELECT [LPA_L1].[CategoryId] FROM (SELECT [LPA_L2].[CategoryId], [LPA_L2].[Name], [LPA_L2].[Description] FROM (( [WeBeFree].[dbo].[Category] [LPA_L2]  INNER JOIN [WeBeFree].[dbo].[VideoCategory] [LPA_L3]  ON  [LPA_L2].[CategoryId] = [LPA_L3].[CategoryId]) INNER JOIN [WeBeFree].[dbo].[Video] [LPA_L4]  ON  [LPA_L3].[VideoId] = [LPA_L4].[VideoId]) WHERE ( ( ( [LPA_L4].[VideoId] = 3)))) [LPA_L1] WHERE ( [LPLA_2].[CategoryId] = [LPA_L1].[CategoryId]))))))

The generated SQL contains an error. At the very end if I change

[LPLA_2].[CategoryId]

to be

[LPLA_1].[CategoryId] 

everything works and I get the correct result.

I downloaded the latest build today July 7th and regenerated everything but it doesn't help. Any ideas, work arounds or fixes?

Thank you for your time.

You're using the very latest build of the runtime libraries? Please post the runtime libraries version as stated in the guidelines post in this forum (so not the .net version but the build date )

Oops, sorry for not informing about the versions.

Just to make sure I got everything clean I uninstalled LLBLGen and downloaded the latest build (once again and tried again but still with no success.

The libraries I use:

SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll file version 2.6.8.624 product version 2.6.08.0624

SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll file version 2.6.8.627 product version 2.6.08.0627

I would have expected to see a version number similar to 2.6.8.707 as you put up a new build today but maybe I'm wrong on this one.

The above two dll:s are the only ones I reference in my project but my DataDBSpecific project also references "SD.LLBLGen.Pro.DQE.SqlServer.NET20.dll" with file version 2.6.8.612.

Do I have wrong versions?

I'm not aware of us distributing a new build today?

If the latest build still gives this error, we'll look into it a.s.a.p.. 'Except' is a special code path, so it might be the alias error is in that code path. Expect a fix tomorrow (tuesday). If we can't reproduce it, we'll report back to you for a repro case, though I think we have enough information to be able to reproduce it.

Reproduced (same crash)

[Test]
public void GetAllEmployeesWhoHaventFiledOrderForCustomerUsingExceptOnNestedQuery()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);

        var allEmployees = from e in metaData.Employee select e;

        var employeesCustomer = from e in metaData.Employee
                                join o in metaData.Order on e.EmployeeId equals o.EmployeeId
                                join c in metaData.Customer on o.CustomerId equals c.CustomerId
                                where c.CustomerId == "CHOPS"
                                select e;
        var diff = allEmployees.Except(employeesCustomer);

        int count = 0;
        int[] expectedValues = new int[] { 2, 5, 8, 9 };
        foreach(var v in diff)
        {
            count++;
            Assert.IsTrue(expectedValues.Contains(v.EmployeeId));
        }
        Assert.AreEqual(4, count);
    }
}

Looking into it.

Fixed it. It was a mistake in the Except/Intersect handler, where in the case of the source being a query (as it is in your case) the correlation filter referred to the complete source (with the select) however the filter was added to that source, so it could never refer to that query (as it was part of it )

Our tests on Except used various kinds of entity sources, but not full queries hence we missed this.

Wow, thanks a lot - it works. You guys rock. Your response time and service mindedness is excellent.

You've got a star in my book! Great job!

rockman wrote:

Wow, thanks a lot - it works. You guys rock. Your response time and service mindedness is excellent.

You've got a star in my book! Great job!

Glad it works, Rockman!