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