Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Bugs & Issues> TypedList selecting incorrect field
 

Pages: 1
Bugs & Issues
TypedList selecting incorrect field
Page:1/1 

  Print all messages in this thread  
Poster Message
bjacobs
User



Location:
Baton Rouge, LA
Joined on:
20-Aug-2008 17:24:31
Posted:
73 posts
# Posted on: 16-Jan-2018 21:17:56.  
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:
Code:

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
Code:

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()
Code:
        
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

Billy Jacobs  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37645 posts
# Posted on: 17-Jan-2018 10:36:41.  
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)



Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
bjacobs
User



Location:
Baton Rouge, LA
Joined on:
20-Aug-2008 17:24:31
Posted:
73 posts
# Posted on: 17-Jan-2018 17:37:20.  
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.
Billy Jacobs  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14531 posts
# Posted on: 17-Jan-2018 18:24:45.  
Please check whether the generated code reference the older libraries.

  Top
bjacobs
User



Location:
Baton Rouge, LA
Joined on:
20-Aug-2008 17:24:31
Posted:
73 posts
# Posted on: 17-Jan-2018 18:43:34.  
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?
Billy Jacobs  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37645 posts
# Posted on: 17-Jan-2018 20:50:06.  
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).



Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
bjacobs
User



Location:
Baton Rouge, LA
Joined on:
20-Aug-2008 17:24:31
Posted:
73 posts
# Posted on: 17-Jan-2018 20:55:21.  
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. Shocked

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

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

Thanks,

Billy


Billy Jacobs  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37645 posts
# Posted on: 17-Jan-2018 21:03:11.  
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 Wink)

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.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
bjacobs
User



Location:
Baton Rouge, LA
Joined on:
20-Aug-2008 17:24:31
Posted:
73 posts
# Posted on: 17-Jan-2018 21:15:33.  
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.
Billy Jacobs  Top
bjacobs
User



Location:
Baton Rouge, LA
Joined on:
20-Aug-2008 17:24:31
Posted:
73 posts
# Posted on: 17-Jan-2018 21:22:04.  
Quote:
generate the typedlist as a POCO with queryspec query


That worked. Regular Smiley


Billy Jacobs  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37645 posts
# Posted on: 18-Jan-2018 13:50:46.  
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... Dissapointed ) and therefore not an option...
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37645 posts
# Posted on: 18-Jan-2018 16:45:58.  
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.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
bjacobs
User



Location:
Baton Rouge, LA
Joined on:
20-Aug-2008 17:24:31
Posted:
73 posts
# Posted on: 18-Jan-2018 17:08:39.  
Good Deal. Regular Smiley
Billy Jacobs  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37645 posts
# Posted on: 18-Jan-2018 17:22:10.  
Fixed in 5.3.3 hotfix

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.