Recommendation for populating models in v5

Posts   
 
    
jovball
User
Posts: 434
Joined: 23-Jan-2005
# Posted on: 28-Jun-2016 22:23:05   

I'm trying to understand the new features in v5 and looking for current recommendations/approaches on populating a class/model/DTO.

Given this as a example for the model:



public class CustomerOrderModel
    {
        public Int32 OrderId { get; set; }
        public String CustomerId { get; set; }
        public Nullable<Int32> EmployeeId { get; set; }
        public Nullable<DateTime> OrderDate { get; set; }
        public Nullable<DateTime> RequiredDate { get; set; }
        public Nullable<DateTime> ShippedDate { get; set; }
        public Nullable<Int32> ShipVia { get; set; }
        public Nullable<Decimal> Freight { get; set; }
        public String ShipCity { get; set; }
        public String ShipRegion { get; set; }
        public String ShipCountry { get; set; }
        public String CompanyID { get; set; }
        public String CompanyName { get; set; }
        public String SalesRepLastName { get; set; }
        public String SalesRepFirstName { get; set; }
    }

And this as the SQL:


SELECT 
    o.OrderID
    ,o.OrderDate
    ,o.RequiredDate
    ,o.ShippedDate
    ,c.CustomerID
    ,c.CompanyName
    ,o.EmployeeId
    ,e.LastName As SalesRepLastName
    ,e.FirstName As SalesRepFirstName
    ,o.ShipVia
    ,o.Freight
    ,o.ShipCity 
    ,o.ShipRegion
    ,o.ShipCountry  
FROM
    dbo.Customers c
    INNER JOIN dbo.Orders o
    ON c.CustomerID = o.CustomerID
    INNER JOIN dbo.Employees e
    ON e.EmployeeID = o.EmployeeID

In v4 and previous, I would've created an entity based on a SQL database view. I could've either used the entity directly or populated a model using some type of mapping code or mapper tool (AutoMapper/Mapster, etc.).

What would be some good approaches for doing this? I'm interested in reducing the amount of initial coding as well as what works for future maintenance.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 29-Jun-2016 08:10:21   

Well, now you can define derived models on top of the entity model and target a different framework in each derived model. You can use the derived models to produce hierarchical projected, denormalized sets and e.g. store them in document databases or use them as DTOs in MVC models or exposed as a service to client-side frameworks. Support for several document databases (MongoDB, RavenDB and a generic document DB) has been added, as well as readonly / readwrite DTO projections. (more info...).

In the case you exposed, you could still map a DB view to an entity and define a derived model on top of that.

David Elizondo | LLBLGen Support Team
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 19-Dec-2017 03:31:24   

Old thread I know but I've got some questions on the subject.

For a LINQ-Head like me the designer provides two options for generating DTOs from entities: **Derived Models **and Typed Lists.

A comparision:

  • **Derived Models ** Typed Lists
  • New in V5 Been there for a while
  • Currently LINQ only LINQ, QuerySpec, DataTable
  • Supports updates back to entity Entity to DTO only
  • Single Entity to DTO as well as IQueryable IQueryable to DTOs only
  • Put in a separate VS project from entities In same VS project as entities
  • Can filter by any related field Can only filter by fields in the DTO
  • Select only SelectMany (i.e can have multiple rows per entity)
  • Nice UX Not so nice UXsimple_smile What other differences are there? The only advantage that Typed Lists has that I can see is that it does SelectMany otherwise Derived Models are the way to go - thoughts?
Jeremy Thomas
daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 19-Dec-2017 07:23:27   

IMHO, TypedLists are great. However now with DerivelModels I prefer to use DTOs, as they are more flexible, organic and standard across layers. They are more intuitive for navigation and they are best serialized for the json libraries out there.

David Elizondo | LLBLGen Support Team
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39570
Joined: 17-Aug-2003
# Posted on: 19-Dec-2017 10:05:52   

Typed lists are flat lists, so a joined set. Derived model elements are hierarchies and not flat lists. This is a fundamental difference which IMHO already makes the rest of the comparisons rather moot wink

If you however denormalize all fields in a Derived model element to make it a flat list, then it's quite similar indeed.

Frans Bouma | Lead developer LLBLGen Pro
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 21-Dec-2017 05:11:57   

Ha I indeed left out the most fundamental difference.

Though what I didn't state was that I was only considering options for creating a flat list DTO. Given my flat list focus the main difference seems that Derived Models are limited to only denormalizing fields in entities with X-to-1 relationships with the Root Entity but fields in entities in a 1-to-many relationships with the Root Entity can't be denormalized. Any plans to offer that for Derived Models? Or will we always have to use Typed Lists for that?

Jeremy Thomas
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39570
Joined: 17-Aug-2003
# Posted on: 21-Dec-2017 10:27:08   

Indeed denormalizing fields in DTOs is only for fields accessible over 1:1/m:1 relationships. This is by design as the way the DTOs are fetched is using a query which is potentially a hierarchical query (nested queries in projection). A typed list is always a joined set. The DTO approach has the advantage that it doesn't give you duplicate rows which can be less efficient in some scenarios (e.g. skewed 1:n relationships with many related elements per parent) but has the disadvantage that denormalizing a field over an 1:n relationship is potentially very wasteful as the related query is a nested query and you'll get the problem where you have to filter child rows on parent and child as a set which has its limits (parameters).

So each has its pros/cons, where DTOs are mainly designed to be used hierarchical, like document graphs, where you denormalize fields when needed. Typed lists are designed to be just as flat lists and never as hierarchies. The API for typedlists was designed way earlier than the one for DTOs and the API for typedlists is meant to be used side-by-side with entities, they're part of the entity model and typedlists are seen as 'projections on the entity model' in the same scope. DTO models however are projections of models on top of the entity model and projections of these are outside the scope of the entity model.

In practice, if you create a flat list from DTOs then it doesn't look that much different (except perhaps the API), but through its core design to be hierarchical, it has a limitation with what you can do if you want them to appear as flat lists.

Frans Bouma | Lead developer LLBLGen Pro
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 08-Jan-2018 04:55:17   

Otis wrote:

In practice, if you create a flat list from DTOs then it doesn't look that much different (except perhaps the API), but through its core design to be hierarchical, it has a limitation with what you can do if you want them to appear as flat lists.

In providing data for our reports (we use Microsoft.ReportViewer to render our reports) we often require completely flat lists i.e. one row per child row resulting in duplicate parent data.

Currently we hand code those report objects and the LINQ projections on to them. Seems to me we could start using typed lists for that. (if we buy LLBLGen licenses for everyonestuck_out_tongue_winking_eye ) The main thing stopping us doing that is that the typed list LINQ API is not as flexible as the Derived model API. Particularly that since you can only filter after a projection then you can only filter on fields present in the typedlist. If the projection was separated from datasource we could filter the datasource before the projection.

One solution would be to change this generated method:

        /// <summary>Gets the query to fetch the typed list Risk.HazardGridModel</summary>
        /// <returns>IQueryable</returns>
        public IQueryable<AQD.Model.TypedListClasses.HazardGridModelRow> GetHazardGridModelTypedList()
        {
            var current0 = this.Hazard;

to

    /// <summary>Gets the query to fetch the typed list Risk.HazardGridModel</summary>
    /// <returns>IQueryable</returns>
    public IQueryable<HazardGridModelRow> GetHazardGridModelTypedList(IQueryable<HazardEntity> current0)
    {
      current0 = current0 ?? this.Hazard;
      var current1 = from risk_Hazard in current0

i.e., We can override this.Hazard with a passed in IQueryable<HazardEntity> with arbitrary filters on it.

If you follow what I'm saying.

Jeremy Thomas
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39570
Joined: 17-Aug-2003
# Posted on: 09-Jan-2018 14:57:27   

Ok, and how do you like to obtain the initial queryable (without the projection) ? As I understand you correctly, you'd like to get 2 methods: 1 which produces the IQueryable<T> with just the joins, and 1 which receives that IQueryable<T> and projects it to the typedlist class, am I correct ?

The method as we have it now can't go, that'll break a lot of code. What can be done is make it call 2 methods, (one to create the joins, and one to project the joins) so you have the choice. Problem with that is that it leads to 2 extra methods per typed list and we're currently working on reducing the amount of generated code wink

Frans Bouma | Lead developer LLBLGen Pro
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 10-Jan-2018 05:11:45   

Otis wrote:

Ok, and how do you like to obtain the initial queryable (without the projection) ? As I understand you correctly, you'd like to get 2 methods: 1 which produces the IQueryable<T> with just the joins, and 1 which receives that IQueryable<T> and projects it to the typedlist class, am I correct ?

No - though that would work for me toosmile .

No I'm sugesting just adding a parameter to the existing method, so from my example above, being able to call

LinqMetaData.GetHazardGridModelTypedList(LinqMetaData.Hazard.Where(h=h.State="SomeState"))

I substitute the root queryable BEFORE all the joins.

Being able to put in code between the joined IQueryable and the projection would probably be handy but that's a bigger change, for the reasons you pointed out.

For reference

        /// <summary>Gets the query to fetch the typed list Risk.HazardGridModel</summary>
        /// <returns>IQueryable</returns>
        public IQueryable<AQD.Model.TypedListClasses.HazardGridModelRow> GetHazardGridModelTypedList()
        {
            var current0 = this.Hazard;

// I want to come in here

            var current1 = from risk_Hazard in current0
                           join general_OrganisationStructurePortal in this.OrganisationStructurePortal on risk_Hazard.BusinessUnitAffectedID equals general_OrganisationStructurePortal.OrgItemID into joinresult_1
                           from general_OrganisationStructurePortal in joinresult_1.DefaultIfEmpty()
                           join general_StaffMember in this.StaffMember on risk_Hazard.EnteredByID equals general_StaffMember.StaffMemberID into joinresult_2
                           from general_StaffMember in joinresult_2.DefaultIfEmpty()
                           join lastReviewedBy in this.StaffMember on risk_Hazard.LastReviewedByID equals lastReviewedBy.StaffMemberID into joinresult_3
                           from lastReviewedBy in joinresult_3.DefaultIfEmpty()
                           join locationDescriptor in this.HazardRiskDescriptor on risk_Hazard.HazardLocationID equals locationDescriptor.ItemID into joinresult_4
                           from locationDescriptor in joinresult_4.DefaultIfEmpty()
                           join risk_HazardRiskDescriptor in this.HazardRiskDescriptor on risk_Hazard.ClassificationID equals risk_HazardRiskDescriptor.ItemID into joinresult_5
                           from risk_HazardRiskDescriptor in joinresult_5.DefaultIfEmpty()
                           join risk_HazardStatus in this.HazardStatus on risk_Hazard.HazardStatusID equals risk_HazardStatus.HazardStatusID into joinresult_6
                           from risk_HazardStatus in joinresult_6.DefaultIfEmpty()
                           select new {general_OrganisationStructurePortal, risk_Hazard, general_StaffMember, lastReviewedBy, locationDescriptor, risk_HazardRiskDescriptor, risk_HazardStatus };

// Not here

            return current1.Select(v=>new AQD.Model.TypedListClasses.HazardGridModelRow() { EnteredBy = v.general_StaffMember.StaffMemberName, DateIdentified = v.risk_Hazard.DateIdentified, EnteredOn = v.risk_Hazard.EnteredOn, HazardNo = v.risk_Hazard.HazardNo, HazardNoSortable = v.risk_Hazard.HazardNoSortable, Category = v.risk_HazardRiskDescriptor.ItemText, HazardTitle = v.risk_Hazard.HazardTitle, AffectedBusinessUnit = v.general_OrganisationStructurePortal.OrgItemDescription, Location = v.locationDescriptor.ItemText, HazardStatus = v.risk_HazardStatus.HazardStatus, NextReviewDate = v.risk_Hazard.NextReviewDate, BusinessUnitAffectedID = v.risk_Hazard.BusinessUnitAffectedID, LastReviewedBy = v.lastReviewedBy.StaffMemberName, LastReviewedDate = v.risk_Hazard.LastReviewedDate });
        }
Jeremy Thomas
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39570
Joined: 17-Aug-2003
# Posted on: 10-Jan-2018 10:25:15   

Ok, though that limits the amount of filtering you can apply, e.g. in your example you can only filter on Hazard, not on risk_Hazard for example. But it could be an option indeed: an optional parameter which is by default null and if not null, will be assigned to current0 instead of the default one.

Frans Bouma | Lead developer LLBLGen Pro
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 11-Jan-2018 11:30:33   

Otis wrote:

Ok, though that limits the amount of filtering you can apply, e.g. in your example you can only filter on Hazard, not on risk_Hazard for example.

?Not following you - risk_Hazard is the variable for Hazard.

Jeremy Thomas
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39570
Joined: 17-Aug-2003
# Posted on: 11-Jan-2018 18:24:20   

My bad, I meant one of the joined entities, like StaffMember. I picked a random name in the join list and didn't look closely enough simple_smile

Frans Bouma | Lead developer LLBLGen Pro
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 12-Jan-2018 04:13:25   

Otis wrote:

My bad, I meant one of the joined entities, like StaffMember. I picked a random name in the join list and didn't look closely enough simple_smile

Funnily that example does work - in LinqPad

GetHazardGridModelTypedList(Hazard.Where(h=>h.StaffMember.StaffMemberID==136))
SELECT [LPA_L1].[StaffMemberName]   AS [EnteredBy],
       [LPA_L1].[DateIdentified],
       [LPA_L1].[EnteredOn],
       [LPA_L1].[HazardNo],
       [LPA_L1].[HazardNoSortable],
       [LPA_L1].[ItemText7]       AS [Category],
       [LPA_L1].[HazardTitle],
       [LPA_L1].[OrgItemDescription] AS [AffectedBusinessUnit],
       [LPA_L1].[BusinessUnitAffectedID],
       [LPA_L1].[ItemText]         AS [Location],
       [LPA_L1].[HazardStatus],
       [LPA_L1].[NextReviewDate],
       CASE
         WHEN CASE
                WHEN ([LPA_L1].[NextReviewDate] < '2018-01-12T16:07:18' /* @p1 */) THEN 1
                ELSE 0
              END = 1 THEN 'zoverdue' /* @p3 */
         ELSE
             CASE
               WHEN CASE
                      WHEN (([LPA_L1].[NextReviewDate] < '2018-01-13T16:07:18' /* @p4 */)
                             OR ([LPA_L1].[NextReviewDate] < '2018-01-19T16:07:18' /* @p5 */)) THEN 1
                      ELSE 0
                    END = 1 THEN 'wwarntwo' /* @p7 */
               ELSE
                   CASE
                     WHEN CASE
                            WHEN (([LPA_L1].[NextReviewDate] < '2018-01-20T16:07:18' /* @p8 */)
                                   OR ([LPA_L1].[NextReviewDate] < '2018-01-26T16:07:18' /* @p9 */)) THEN 1
                            ELSE 0
                          END = 1 THEN 'awarnone' /* @p11 */
                     ELSE '' /* @p13 */
                   END
             END
       END                         AS [Overdue],
       [LPA_L1].[StaffMemberName32]  AS [LastReviewedBy],
       [LPA_L1].[LastReviewedDate]
FROM   (SELECT [LPA_L4].[Default_]                  AS [Default],
               [LPA_L4].[Item_Level_Num]              AS [ItemLevelNum],
               [LPA_L4].[Organisation_Code]        AS [OrganisationCode],
               [LPA_L4].[Org_Item_Description]      AS [OrgItemDescription],
               [LPA_L4].[Org_Item_ID]                AS [OrgItemID],
               [LPA_L4].[Org_Item_Level]              AS [OrgItemLevel],
               [LPA_L4].[Org_Item_Parent]            AS [OrgItemParent],
               [LPA_L4].[Org_Item_Sequence]        AS [OrgItemSequence],
               [LPA_L4].[Org_Item_Status]            AS [OrgItemStatus],
               [LPA_L4].[Party_ID]                  AS [PartyID],
               1 /* @p15 */                                AS [LPFA_41],
               [LPA_L3].[Business_Unit_Affected_ID]   AS [BusinessUnitAffectedID],
               [LPA_L3].[Classification_ID]        AS [ClassificationID],
               [LPA_L3].[Date_Identified]            AS [DateIdentified],
               [LPA_L3].[Entered_By_ID]            AS [EnteredByID],
               [LPA_L3].[Entered_On]                  AS [EnteredOn],
               [LPA_L3].[Feedback],
               [LPA_L3].[Feedback_By_ID]              AS [FeedbackByID],
               [LPA_L3].[Feedback_Entered_On]        AS [FeedbackEnteredOn],
               [LPA_L3].[Hazard_Location_ID]          AS [HazardLocationID],
               [LPA_L3].[Hazard_No]                AS [HazardNo],
               [LPA_L3].[Hazard_No_Sortable]          AS [HazardNoSortable],
               [LPA_L3].[Hazard_Statement]          AS [HazardStatement],
               [LPA_L3].[Hazard_Status_ID]          AS [HazardStatusID],
               [LPA_L3].[Hazard_Title]              AS [HazardTitle],
               [LPA_L3].[Identified_By_ID]          AS [IdentifiedByID],
               [LPA_L3].[Last_Reviewed_By_ID]        AS [LastReviewedByID],
               [LPA_L3].[Last_Reviewed_Date]          AS [LastReviewedDate],
               [LPA_L3].[Next_Review_Date]          AS [NextReviewDate],
               [LPA_L3].[Potential_Current_ID]      AS [PotentialCurrentID],
               [LPA_L3].[Publish],
               [LPA_L3].[WR_Number]                AS [WRNumber],
               [LPA_L3].[WR_Period]                AS [WRPeriod],
               [LPA_L3].[WR_Type]                    AS [WRType],
               1 /* @p17 */                                AS [LPFA_42],
               [LPA_L5].[Computer_Username]        AS [ComputerUsername],
               [LPA_L5].[Created_On]                  AS [CreatedOn],
               [LPA_L5].[Daily_Login_Fails]        AS [DailyLoginFails],
               [LPA_L5].[Department_ID]            AS [DepartmentID],
               [LPA_L5].[Email_Address]            AS [EmailAddress],
               [LPA_L5].[Enforce_Password_Expiration] AS [EnforcePasswordExpiration],
               [LPA_L5].[Enforce_Password_Policy]    AS [EnforcePasswordPolicy],
               [LPA_L5].[Externally_Managed]          AS [ExternallyManaged],
               [LPA_L5].[External_StaffMember_Ref]  AS [ExternalStaffMemberRef],
               [LPA_L5].[Fax_Number]                  AS [FaxNumber],
               [LPA_L5].[Last_LoggedOn]            AS [LastLoggedOn],
               [LPA_L5].[Last_Login_Fail_On]          AS [LastLoginFailOn],
               [LPA_L5].[Locked],
               [LPA_L5].[Locked_On]                AS [LockedOn],
               [LPA_L5].[Party_ID]                  AS [PartyID0],
               [LPA_L5].[Password],
               [LPA_L5].[Password_Last_Changed_On]  AS [PasswordLastChangedOn],
               [LPA_L5].[Phone_Number]              AS [PhoneNumber],
               [LPA_L5].[Portal_Password_Hash]      AS [PortalPasswordHash],
               [LPA_L5].[Position],
               [LPA_L5].[Require_Password_Change]    AS [RequirePasswordChange],
               [LPA_L5].[Staff_Member_ID]            AS [StaffMemberID],
               [LPA_L5].[Staff_Member_Name]        AS [StaffMemberName],
               [LPA_L5].[Status],
               1 /* @p19 */                                AS [LPFA_43],
               [LPA_L6].[Adequate],
               [LPA_L6].[Descriptor_Subject_ID]    AS [DescriptorSubjectID],
               [LPA_L6].[Group_Sequence]              AS [GroupSequence],
               [LPA_L6].[Item_ID]                    AS [ItemID],
               [LPA_L6].[Item_Level]                  AS [ItemLevel],
               [LPA_L6].[Item_Status]                AS [ItemStatus],
               [LPA_L6].[Item_Text]                AS [ItemText],
               [LPA_L6].[Parent_Item_ID]              AS [ParentID],
               1 /* @p21 */                                AS [LPFA_44],
               [LPA_L7].[Adequate]                  AS [Adequate1],
               [LPA_L7].[Descriptor_Subject_ID]    AS [DescriptorSubjectID2],
               [LPA_L7].[Group_Sequence]              AS [GroupSequence3],
               [LPA_L7].[Item_ID]                    AS [ItemID4],
               [LPA_L7].[Item_Level]                  AS [ItemLevel5],
               [LPA_L7].[Item_Status]                AS [ItemStatus6],
               [LPA_L7].[Item_Text]                AS [ItemText7],
               [LPA_L7].[Parent_Item_ID]              AS [ParentID8],
               1 /* @p23 */                                AS [LPFA_45],
               [LPA_L8].[Hazard_Status]            AS [HazardStatus],
               [LPA_L8].[Hazard_Status_ID]          AS [HazardStatusID9],
               [LPA_L8].[Seq_No]                      AS [SeqNo],
               1 /* @p25 */                                AS [LPFA_46],
               [LPA_L9].[Computer_Username]        AS [ComputerUsername10],
               [LPA_L9].[Created_On]                  AS [CreatedOn11],
               [LPA_L9].[Daily_Login_Fails]        AS [DailyLoginFails12],
               [LPA_L9].[Department_ID]            AS [DepartmentID13],
               [LPA_L9].[Email_Address]            AS [EmailAddress14],
               [LPA_L9].[Enforce_Password_Expiration] AS [EnforcePasswordExpiration15],
               [LPA_L9].[Enforce_Password_Policy]    AS [EnforcePasswordPolicy16],
               [LPA_L9].[Externally_Managed]          AS [ExternallyManaged17],
               [LPA_L9].[External_StaffMember_Ref]  AS [ExternalStaffMemberRef18],
               [LPA_L9].[Fax_Number]                  AS [FaxNumber19],
               [LPA_L9].[Last_LoggedOn]            AS [LastLoggedOn20],
               [LPA_L9].[Last_Login_Fail_On]          AS [LastLoginFailOn21],
               [LPA_L9].[Locked]                      AS [Locked22],
               [LPA_L9].[Locked_On]                AS [LockedOn23],
               [LPA_L9].[Party_ID]                  AS [PartyID24],
               [LPA_L9].[Password]                  AS [Password25],
               [LPA_L9].[Password_Last_Changed_On]  AS [PasswordLastChangedOn26],
               [LPA_L9].[Phone_Number]              AS [PhoneNumber27],
               [LPA_L9].[Portal_Password_Hash]      AS [PortalPasswordHash28],
               [LPA_L9].[Position]                  AS [Position29],
               [LPA_L9].[Require_Password_Change]    AS [RequirePasswordChange30],
               [LPA_L9].[Staff_Member_ID]            AS [StaffMemberID31],
               [LPA_L9].[Staff_Member_Name]        AS [StaffMemberName32],
               [LPA_L9].[Status]                      AS [Status33],
               1 /* @p27 */                                AS [LPFA_47]
        FROM   ((((((([AQD].[rm_Hazard] [LPA_L3]
                      LEFT JOIN [AQD].[gn_Organisation_Structure_Port] [LPA_L4]
                          ON [LPA_L3].[Business_Unit_Affected_ID] = [LPA_L4].[Org_Item_ID])
                     LEFT JOIN [AQD].[gn_Staff_Member] [LPA_L5]
                         ON [LPA_L3].[Entered_By_ID] = [LPA_L5].[Staff_Member_ID])
                    LEFT JOIN [AQD].[rm_Hazard_Risk_Descriptors] [LPA_L6]
                        ON [LPA_L3].[Hazard_Location_ID] = [LPA_L6].[Item_ID])
                   LEFT JOIN [AQD].[rm_Hazard_Risk_Descriptors] [LPA_L7]
                       ON [LPA_L3].[Classification_ID] = [LPA_L7].[Item_ID])
                  LEFT JOIN [AQD].[rm_Hazard_Status] [LPA_L8]
                      ON [LPA_L3].[Hazard_Status_ID] = [LPA_L8].[Hazard_Status_ID])
                 LEFT JOIN [AQD].[gn_Staff_Member] [LPA_L9]
                     ON [LPA_L3].[Last_Reviewed_By_ID] = [LPA_L9].[Staff_Member_ID])
                INNER JOIN [AQD].[gn_Staff_Member] [LPA_L10]
                    ON [LPA_L10].[Staff_Member_ID] = [LPA_L3].[Entered_By_ID])
        WHERE  (([LPA_L10].[Staff_Member_ID] = 136 /* @p28 */))) [LPA_L1]

It's twice joining on [Entered_By_ID] = [Staff_Member_ID] - one for filter and one for projection. Returns the right results in this instance but the duplicate join is not a pattern I would bet the house on.wink

Jeremy Thomas
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39570
Joined: 17-Aug-2003
# Posted on: 13-Jan-2018 09:37:28   

Indeed, hence I think calling a method which produces the join and then apply the filter might work better.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39570
Joined: 17-Aug-2003
# Posted on: 18-Jan-2018 17:24:29   

Due to a fix we had to make (to v5.3.3) we'll generate the typedlist queries simpler now, without the required immediate projection with entities. This solves a problem with inheritance and makes the generated queries much simpler and thus faster. https://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=140065&ThreadID=24601

So this idea won't make it, I'm afraid.

Frans Bouma | Lead developer LLBLGen Pro
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 25-Jan-2018 03:14:14   

Otis wrote:

Due to a fix we had to make (to v5.3.3) we'll generate the typedlist queries simpler now, without the required immediate projection with entities.

So this idea won't make it, I'm afraid.

Is the other option of 'an optional parameter which is by default null and if not null, will be assigned to current0 instead of the default one.' still on the table?

Jeremy Thomas
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39570
Joined: 17-Aug-2003
# Posted on: 25-Jan-2018 11:05:26   

There wasn't anything specific finalized in this discussion about that? If something's passed in, the whole join stack is then still performed, so you can only filter on the root entity, that's what you want?

Frans Bouma | Lead developer LLBLGen Pro
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 25-Jan-2018 11:34:03   

Otis wrote:

If something's passed in, the whole join stack is then still performed, so you can only filter on the root entity, that's what you want?

Yeah that's enough just about all the time.

Jeremy Thomas
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39570
Joined: 17-Aug-2003
# Posted on: 26-Jan-2018 11:22:01   

Thanks, will see what we can do.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39570
Joined: 17-Aug-2003
# Posted on: 20-Feb-2018 14:55:00   

Implemented in v5.4.

Normal:


// linq
[Test]
public void TypedListAsPocoLinqQueryTest4()
{
    using(var adapter = new DataAccessAdapter())
    {
        var metaData = new LinqMetaData(adapter);
        var q = from x in metaData.GetSingleEntityPocoLinqTypedList()
                where x.CompanyName.StartsWith("S")
                select x;

        var results = ((ILLBLGenProQuery)q).Execute<List<SingleEntityPocoLinqRow>>();
        Assert.AreEqual(7, results.Count);
    }
}

// sql
SELECT [LPA_L1].[CustomerId],
       [LPA_L1].[CompanyName],
       [LPA_L1].[ContactName]
FROM   (SELECT [LPLA_1].[CustomerID] AS [CustomerId],
               [LPLA_1].[CompanyName],
               [LPLA_1].[ContactName]
        FROM   [Northwind].[dbo].[Customers] [LPLA_1]) [LPA_L1]
WHERE  (((([LPA_L1].[CompanyName] LIKE 'S%' /* @p1 */))))

New:


// linq
        

[Test]
public void TypedListAsPocoLinqQueryTest5()
{
    using(var adapter = new DataAccessAdapter())
    {
        var metaData = new LinqMetaData(adapter);
        var q = from x in metaData.GetSingleEntityPocoLinqTypedList(metaData.Customer.Where(c=>c.CompanyName.StartsWith("S")))
                select x;

        var results = ((ILLBLGenProQuery)q).Execute<List<SingleEntityPocoLinqRow>>();
        Assert.AreEqual(7, results.Count);
    }
}

// sql
SELECT [LPLA_1].[CustomerID] AS [CustomerId],
       [LPLA_1].[CompanyName],
       [LPLA_1].[ContactName]
FROM   [Northwind].[dbo].[Customers] [LPLA_1]
WHERE  ((((([LPLA_1].[CompanyName] LIKE 'S%' /* @p1 */)))))

As the predicate is now inside the projection, the query isn't wrapped and therefore easier to parse by the DB as well (doesn't matter much, but still).

The same will also be supported for the fetch methods for poco typed lists in QuerySpec.

Frans Bouma | Lead developer LLBLGen Pro
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 21-Feb-2018 11:24:30   

Nice!

Jeremy Thomas