Derived Model output ignores subtype fields

Posts   
 
    
KDL
User
Posts: 24
Joined: 25-Mar-2010
# Posted on: 02-Feb-2023 02:07:45   

LLBLGen version 5.9.4 (also tried in 5.10 beta with the same results) Adapter .NET 6 C# DTO Class Model SQL Server 2019 SD.LLBLGen.Pro.DQE.SqlServer (5.9.4-hotfix-20230109) SD.LLBLGen.Pro.ORMSupportClasses (5.9.4-hotfix-20230109)

I'm trying to create a Derived Element DTO for 5 entities, 4 of which participate in Target-Per-Entity hierarchies (please see attached screenshot). InvoiceBase has a subtype of Invoice, while ReceivedItem has a subtype of DamagedItem.

The Derived Element DTO output should include 3 ReceivedItems (which it does), but the first ReceivedItem object should be of type DamagedItem and include the additional subtype field (which it does not). It was selected in the LLBLGen designer, and the data exists. Additionally, the ORM Profiler confirmed that no query was executed for the subtype (see below).

I have reproduced the problem in a simple database and LLBLGen project (which I can provide if helpful). My object model is similar in structure to the "Detailed Example" in the documentation (https://www.llblgen.com/Documentation/5.9/Designer/Concepts/DerivedModels.htm).

This is the final (of 3) queries LLBLGen executed to populate the ReceivedItem -- it seems to be missing a left join to the DamagedItem table:

SELECT [LPA__1].[InvoiceItemID] AS [InvoiceItemId],
       [LPA__1].[ItemID]        AS [ItemId],
       [LPA__1].[TestReceived]
FROM   [LlblgenTest].[dbo].[ReceivedItem] [LPA__1]
WHERE  (([LPA__1].[InvoiceItemID] IN (@p1, @p2, @p3))) 

Code to return the DTO :

var q = qf.Invoice
     .ProjectToInvoiceDto(qf);

Let me know if you need any additional details. And thanks very much for your help!

Attachments
Filename File size Added on Approval
llblgenExample.png 57,062 02-Feb-2023 02:08.09 Approved
Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 02-Feb-2023 06:29:00   

Could you please attach the repro LLBLGen proj file and the repro sol. and database?

KDL
User
Posts: 24
Joined: 25-Mar-2010
# Posted on: 02-Feb-2023 09:45:17   

Walaa wrote:

Could you please attach the repro LLBLGen proj file and the repro sol. and database?

Thanks, I've attached the items:

  • VS solution (WebApplication2)
  • LlblgenTest.llblgenproj
  • LlblgenTest.dacpac (SQL Server DB)

Thanks!

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 02-Feb-2023 09:46:21   

Be aware that DTO's on entities with inheritance is not recommended. It 'works' but the projections are slow and cumbersome. This is explained in the documentation: https://www.llblgen.com/Documentation/5.10/Derived%20Models/dto_llblgen.htm

The projections for entities with inheritance are only supported when you use Linq (see page above), they're not supported for QuerySpec (and your code suggest you use QuerySpec). But in all honesty, the end result will likely cause slow queries on your database.

(You'll see in the generated projection code that the inheritance related projection is pretty big and will issue more than 1 query)

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 02-Feb-2023 10:17:45   

So in short: change the code to use a Linq query.

Frans Bouma | Lead developer LLBLGen Pro
KDL
User
Posts: 24
Joined: 25-Mar-2010
# Posted on: 02-Feb-2023 22:09:13   

Otis wrote:

So in short: change the code to use a Linq query.

Thanks for your help and the additional information about inheritance with DTOs. As you suggested, I changed the code to Linq which now produces the correct results.

            using var adapter = new DataAccessAdapter();
            var metaData = new LinqMetaData(adapter);
            var q = (from i in metaData.Invoice
                     select i)
                     .ProjectToInvoiceDto();

            return q.ToList();

In case it helps someone else, I also had to add the Microsoft.AspNetCore.Mvc.NewtonsoftJson nuget package to my Web API project for the subtype field to be included in the JSON output. I couldn't get System.Text.Json to include it.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 02-Feb-2023 22:27:48   

Glad it's sorted simple_smile System.Text.Json is indeed a bit limited (it's fast, but doesn't do cyclic references very well IIRC). This forum system for that reason also uses newtonsoft.json for dto's simple_smile

Frans Bouma | Lead developer LLBLGen Pro