5.3 (5.3.1) RTM - TRIAL Build Date 03-Nov-2017 Runtime Version 5.3.2 Adapter SQL Server 2014 Code is Generated as .Net Standard 2.0

Inheritance Hierarchies: LandParcel - Id - Name

Subdivision : LandParcel - Id

Lot : LandParcel - Id

ColorOptionSelection - Id - LotId (Navigator Lot) - SubdivisionId (Navigator Subdivision)

ColorOptionSelectionAppointment - Id - ColorOptionSelectionId (Navigator ColorOptionSelection) - AppointmentDate

I created a typed list from ColorOptionSelectionAppointment selecting the following: ColorOptionSelectionAppointment.Id as Id ColorOptionSelectionAppointment.ColorOptionSelectionId as ColorOptionSelectionId ColorOptionSelectionAppointment.LotId as LotId ColorOptionSelectionAppointment.SubdivisionId as SubdivisionId Lot.Name as LotName Subdivision.Name as SubdivisionName

NOTE: I only renamed Lot.Name and Subdivision.Name since they have a common supertype and I need the name from each of them.

I generate the code and use it in the following way:

var colorOptionAppointmentSummaries = new LinqMetaData(new DataAccessAdapter()).GetColorOptionAppointmentSummaryTypedListTypedList();
return colorOptionAppointmentSummaries.ToList(); 

There are 2 queries produced when this code executes. The first one selects every field in all tables and the SubdivisionName (F9_111) appears.

The second one selects the same value ([LPA_L1].[F9_11]) for LotName and SubdivisionName.

When I look at both queries produced by LLBLGen it does all of the joins and table aliasing correctly.

The problem is that the second query selects the same aliased field for both LotName and SubdivisionName which is ultimately the LotName.

The second query appears to be what is used to map the results back to the entity and has the incorrect name (Has [LPA_L1].[F9_11] for both LotName and SubdivisionName) instead.

Here is the sql produced for both queries. [LPA_L1].[F9_11] is selected for both the LotName and SubdivisionName

Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LPA_L5].[AppointmentDate], [LPA_L5].[ColorOptionSelectionId], [LPA_L5].[CreatedBy], [LPA_L5].[CreateTime], [LPA_L5].[Id], [LPA_L5].[LastUpdateTime], [LPA_L5].[LastUpdateUser], @p2 AS [LPFA_17], [LPA_L3].[CreatedBy] AS [F19_0], [LPA_L3].[CreateTime] AS [F19_1], [LPA_L3].[Id] AS [F19_2], [LPA_L3].[LastUpdateTime] AS [F19_3], [LPA_L3].[LastUpdateUser] AS [F19_4], [LPA_L4].[Id] AS [F3_5], [LPA_L4].[LotId] AS [F3_6], [LPA_L4].[SubdivisionId] AS [F3_7], @p4 AS [LPFA_18], [LPA_L6].[Acre] AS [F9_0], [LPA_L6].[Active] AS [F9_1], [LPA_L6].[AddressId] AS [F9_2], [LPA_L6].[CreatedBy] AS [F9_3], [LPA_L6].[CreateTime] AS [F9_4], [LPA_L6].[Deleted] AS [F9_5], [LPA_L6].[FloodZoneComments] AS [F9_6], [LPA_L6].[FloodZoneId] AS [F9_7], [LPA_L6].[Id] AS [F9_8], [LPA_L6].[LastUpdateTime] AS [F9_9], [LPA_L6].[LastUpdateUser] AS [F9_10], [LPA_L6].[Name] AS [F9_11], [LPA_L6].[RegionId] AS [F9_12], [LPA_L6].[SortIndex] AS [F9_13], [LPA_L7].[Id] AS [F11_14], [LPA_L7].[Block] AS [F11_15], [LPA_L7].[FilingId] AS [F11_16], [LPA_L7].[Letter] AS [F11_17], [LPA_L7].[SubdivisionId] AS [F11_18], @p6 AS [LPFA_19], [LPA_L8].[Acre] AS [F9_00], [LPA_L8].[Active] AS [F9_112], [LPA_L8].[AddressId] AS [F9_23], [LPA_L8].[CreatedBy] AS [F9_34], [LPA_L8].[CreateTime] AS [F9_45], [LPA_L8].[Deleted] AS [F9_56], [LPA_L8].[FloodZoneComments] AS [F9_67], [LPA_L8].[FloodZoneId] AS [F9_78], [LPA_L8].[Id] AS [F9_89], [LPA_L8].[LastUpdateTime] AS [F9_910], [LPA_L8].[LastUpdateUser] AS [F9_1011], [LPA_L8].[Name] AS [F9_1112], [LPA_L8].[RegionId] AS [F9_1213], [LPA_L8].[SortIndex] AS [F9_1314], [LPA_L9].[Id] AS [F16_14], [LPA_L9].[AlternateApplicationMappingId] AS [F16_15], [LPA_L9].[PartList] AS [F16_16], [LPA_L9].[TotalLots] AS [F16_17], @p8 AS [LPFA_20] FROM (((((([JAB.Structures].[dbo].[WorkflowItem] [LPA_L3] INNER JOIN [JAB.Structures].[dbo].[ColorOptionSelection] [LPA_L4] ON [LPA_L3].[Id]=[LPA_L4].[Id]) INNER JOIN [JAB.Structures].[dbo].[ColorOptionSelectionAppointment] [LPA_L5] ON [LPA_L4].[Id] = [LPA_L5].[ColorOptionSelectionId]) INNER JOIN [JAB.Structures].[dbo].[Lot] [LPA_L7] ON [LPA_L4].[LotId] = [LPA_L7].[Id]) INNER JOIN [JAB.Structures].[dbo].[LandParcel] [LPA_L6] ON [LPA_L6].[Id]=[LPA_L7].[Id]) INNER JOIN [JAB.Structures].[dbo].[Subdivision] [LPA_L9] ON [LPA_L4].[SubdivisionId] = [LPA_L9].[Id]) INNER JOIN [JAB.Structures].[dbo].[LandParcel] [LPA_L8] ON [LPA_L8].[Id]=[LPA_L9].[Id]) WHERE ( ( ( [LPA_L4].[Id] IS NOT NULL) OR ( [LPA_L7].[Id] IS NOT NULL) OR ( [LPA_L9].[Id] IS NOT NULL)))
    Parameter: @p2 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @p4 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @p6 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @p8 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query: 
    Query: SELECT [LPA_L1].[AppointmentDate], [LPA_L1].[Id], [LPA_L1].[ColorOptionSelectionId], [LPA_L1].[LastUpdateTime], [LPA_L1].[F9_11], [LPA_L1].[F3_6], [LPA_L1].[F3_7], [LPA_L1].[F9_11] FROM (SELECT [LPA_L5].[AppointmentDate], [LPA_L5].[ColorOptionSelectionId], [LPA_L5].[CreatedBy], [LPA_L5].[CreateTime], [LPA_L5].[Id], [LPA_L5].[LastUpdateTime], [LPA_L5].[LastUpdateUser], @p2 AS [LPFA_17], [LPA_L3].[CreatedBy] AS [F19_0], [LPA_L3].[CreateTime] AS [F19_1], [LPA_L3].[Id] AS [F19_2], [LPA_L3].[LastUpdateTime] AS [F19_3], [LPA_L3].[LastUpdateUser] AS [F19_4], [LPA_L4].[Id] AS [F3_5], [LPA_L4].[LotId] AS [F3_6], [LPA_L4].[SubdivisionId] AS [F3_7], @p4 AS [LPFA_18], [LPA_L6].[Acre] AS [F9_0], [LPA_L6].[Active] AS [F9_1], [LPA_L6].[AddressId] AS [F9_2], [LPA_L6].[CreatedBy] AS [F9_3], [LPA_L6].[CreateTime] AS [F9_4], [LPA_L6].[Deleted] AS [F9_5], [LPA_L6].[FloodZoneComments] AS [F9_6], [LPA_L6].[FloodZoneId] AS [F9_7], [LPA_L6].[Id] AS [F9_8], [LPA_L6].[LastUpdateTime] AS [F9_9], [LPA_L6].[LastUpdateUser] AS [F9_10], [LPA_L6].[Name] AS [F9_11], [LPA_L6].[RegionId] AS [F9_12], [LPA_L6].[SortIndex] AS [F9_13], [LPA_L7].[Id] AS [F11_14], [LPA_L7].[Block] AS [F11_15], [LPA_L7].[FilingId] AS [F11_16], [LPA_L7].[Letter] AS [F11_17], [LPA_L7].[SubdivisionId] AS [F11_18], @p6 AS [LPFA_19], [LPA_L8].[Acre] AS [F9_00], [LPA_L8].[Active] AS [F9_112], [LPA_L8].[AddressId] AS [F9_23], [LPA_L8].[CreatedBy] AS [F9_34], [LPA_L8].[CreateTime] AS [F9_45], [LPA_L8].[Deleted] AS [F9_56], [LPA_L8].[FloodZoneComments] AS [F9_67], [LPA_L8].[FloodZoneId] AS [F9_78], [LPA_L8].[Id] AS [F9_89], [LPA_L8].[LastUpdateTime] AS [F9_910], [LPA_L8].[LastUpdateUser] AS [F9_1011], [LPA_L8].[Name] AS [F9_1112], [LPA_L8].[RegionId] AS [F9_1213], [LPA_L8].[SortIndex] AS [F9_1314], [LPA_L9].[Id] AS [F16_14], [LPA_L9].[AlternateApplicationMappingId] AS [F16_15], [LPA_L9].[PartList] AS [F16_16], [LPA_L9].[TotalLots] AS [F16_17], @p8 AS [LPFA_20] FROM (((((([JAB.Structures].[dbo].[WorkflowItem] [LPA_L3] INNER JOIN [JAB.Structures].[dbo].[ColorOptionSelection] [LPA_L4] ON [LPA_L3].[Id]=[LPA_L4].[Id]) INNER JOIN [JAB.Structures].[dbo].[ColorOptionSelectionAppointment] [LPA_L5] ON [LPA_L4].[Id] = [LPA_L5].[ColorOptionSelectionId]) INNER JOIN [JAB.Structures].[dbo].[Lot] [LPA_L7] ON [LPA_L4].[LotId] = [LPA_L7].[Id]) INNER JOIN [JAB.Structures].[dbo].[LandParcel] [LPA_L6] ON [LPA_L6].[Id]=[LPA_L7].[Id]) INNER JOIN [JAB.Structures].[dbo].[Subdivision] [LPA_L9] ON [LPA_L4].[SubdivisionId] = [LPA_L9].[Id]) INNER JOIN [JAB.Structures].[dbo].[LandParcel] [LPA_L8] ON [LPA_L8].[Id]=[LPA_L9].[Id]) WHERE ( ( ( [LPA_L4].[Id] IS NOT NULL) OR ( [LPA_L7].[Id] IS NOT NULL) OR ( [LPA_L9].[Id] IS NOT NULL)))) [LPA_L1]
    Parameter: @p2 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @p4 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @p6 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @p8 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.

// I need to rename this but Here is the genered method GetColorOptionAppointmentSummaryTypedListTypedList()

        
public IQueryable<JAB.Structures.TypedListClasses.ColorOptionAppointmentSummaryTypedListRow> GetColorOptionAppointmentSummaryTypedListTypedList()
        {
            var current0 = this.ColorOptionSelection;
            var current1 = from colorOptionSelection in current0
                           join colorOptionSelectionAppointment in this.ColorOptionSelectionAppointment on colorOptionSelection.Id equals colorOptionSelectionAppointment.ColorOptionSelectionId
                           join lot in this.Lot on colorOptionSelection.LotId equals lot.Id
                           join subdivision in this.Subdivision on colorOptionSelection.SubdivisionId equals subdivision.Id
                           select new {colorOptionSelectionAppointment, colorOptionSelection, lot, subdivision };
            return current1.Select(v=>new JAB.Structures.TypedListClasses.ColorOptionAppointmentSummaryTypedListRow() { AppointmentDate = v.colorOptionSelectionAppointment.AppointmentDate, Id = v.colorOptionSelectionAppointment.Id, ColorOptionSelectionId = v.colorOptionSelectionAppointment.ColorOptionSelectionId, LastUpdateTime = v.colorOptionSelectionAppointment.LastUpdateTime, LotName = v.lot.Name, LotId = v.colorOptionSelection.LotId, SubdivisionId = v.colorOptionSelection.SubdivisionId, SubdivisionName = v.subdivision.Name });
        }

As an aside. Why is the sql for the first query produced that selects all fields from all tables?

Thanks,

Billy Jacobs

The issue sounds familiar and was first corrected in 5.3.2: https://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=24546 (this is a similar issue). You're certain you're not using v5.3.1 ? (and a trial should have ended by now?)

It generates 2 queries but it executes just 1, the first is a wrapped query in the second. The first one is this projection: select new {colorOptionSelectionAppointment, colorOptionSelection, lot, subdivision };

as this states that all fields of all entities involved should be selected, it simply does that. Then it projects that set out to the real fields you wanted to fetch. The reason it's done this way is that it's otherwise very hard to generate the Linq query code. It's not optimized away at the linq level as that would require a lot of tree visits which could make executing the query slow, and as the DB will optimize it anyway, we leave it as is. (Linq to sql has code which optimizes these kind of queries, which can lead to slow queries)

Hi Otis,

I am using 5.3.1 designer/code generator. I am using the 5.3.2 runtime.

I started a trial about 13 days ago with 5.3. My 5.1 licenses expired a short while back and I am about to upgrade to the latest version but have not done so just yet. I will be purchasing new licenses this week.

Version Details: In Help/About I see 5.3 (5.3.1) RTM - TRIAL 17 days remaining In my VS Project, when I right click on SD.LLBLGen.Pro.ORMSupportClasses (5.3.2) and look at properties the version says 5.3.2.

Please check whether the generated code reference the older libraries.

The 2 generated .Net projects under Dependencies/NuGet reference 5.3.2 and no updates are available in NuGet Package Manager. Is that what you mean?

Then we need a repro case as we can't reproduce it here with the tests we have (as the tests we have work fine with the exact same scenario you are using).

Ok. I will get one to you. Just as an FYI, I purchased the licenses and downloaded the latest version and regenerated and retested and get the same results. Hopefully it is not something stupid on my part.

It may not be until tomorrow or early Friday when I send it. I have a demo to prepare for tomorrow.

I have temporarily worked around it by writing a view and creating a typed list from the view.

Thanks,

Billy

In cases like this, you can also generate the typedlist as a POCO with queryspec query, which likely will work fine. (and it's slightly faster too )

I'll try with the model you gave at the start of the thread, I overlooked that one, it could be used as a repro I guess.

Ok. I will try that in a bit and see. I have not used the querySpec feature before although I have read about it.

Just let me know if you need me to send the repro project and I can. I will let you know if the querySpec works.

generate the typedlist as a POCO with queryspec query

That worked.

These fields:

ColorOptionSelectionAppointment.LotId as LotId ColorOptionSelectionAppointment.SubdivisionId as SubdivisionId

aren't your described model. I assume you meant ColorOptionSelection.LotId etc. as those are in the query?

(I can reproduce it with your example model and LotId/Subdivisionid from ColorOptionSelection.)

Simplifying the generated query isn't going to work if e.g. left/right joins are present, as temporary join results are then required (linq... ) and therefore not an option...

We've simplified the queries being generated and in most cases a separate projection is no longer needed. We went for this solution because it would lead to faster simpler queries and still the same resultsets and also it would fix this issue. (normally people wouldn't write linq queries like this).

If all tests succeed it's in 5.3.3 hotfix.

Good Deal.

Fixed in 5.3.3 hotfix