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!

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

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!

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)

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

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.

Glad it's sorted 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