Build Version: 5.5 (5.5.0) RTM, Build Date: 02-Nov-2018 Project: Adapter project targetting .NET 4.5.2 Database: MS Sql on 2016

Apologies in advance if there is an obvious link to this in the documentation, I searched around for a bit and was unable to find anything specific.

We don't currently leverage the derived model classes in our application, but we're looking towards implementing a data access web API and need representative DTO objects for each entity. From my initial reading, it looks like these classes would be a good fit for this purpose.

Our end goal is to:

• Have a derived class for each entity in our database.
• Be able to translate from entity to model, and from model to entity
• If the entity definition is updated when we run the sync relational data action, the derived model class should be updated as well. Are derived models a good way to achieve the desired functionality? What can we accomplish out of the box from the above list, and what would be the best practice approach for generating these models? Should there be a derived model for each entity, or can we group them all in one derived model?

Thanks!

mprothme wrote:

Apologies in advance if there is an obvious link to this in the documentation, I searched around for a bit and was unable to find anything specific.

You are right, all the information about DTOs Derived Model with LLBLGen Pro is here.

mprothme wrote:

Are derived models a good way to achieve the desired functionality?

Yes, they are there exactly for that: implement a DTO layer that you can use in your stateless applications (f.i: APIs).

mprothme wrote:

What can we accomplish out of the box from the above list, and what would be the best practice approach for generating these models?

a) Derived class for each entity: you can do that. As it's a derived model, you should create the DTO for the entities you want (and their related entities as related objects in the DTO)

b) Translate from entity to model and viceversa: yes. Please check the documentation link above.

c) Update the DTO Derived Model when the Entity Model definition is updated in LLBLGen Designer: yes, this just works like that.

mprothme wrote:

Should there be a derived model for each entity, or can we group them all in one derived model?

A Derived Model is a set of elements that have certain use or certain meaning in a context. So that's totally up to you. Each Derived Model will generate two .net projects: Model, and Persistence (helper which will help you in translating from DTO to entities and viceversa. So consider this when making that decision. I don't think you need to write a Derived Model for each entity. The usual way is to have just one Derived Model.

Maybe you did confuse terms. Derived Model is the Set of Elements, and there are different kind of Derived Models (DTOs, MongoDB objects, DocumentDB, etc). When you create a Derived Model you can add elements to it, you can add a Derived Model Element for each entity if you want. However the usual way is that you build graphs based on how your DTO should look like. For instance, you can add an Order DTO that will have inside, a subelement that represent the Customer information, Order Item collection, and so on.

I Hope that clarifies things to you

Thanks that's really helpful! I have a few other questions:

One thing I notice from the link you posted is that the methods for persisting a DTO to the database only persist the root element. I take this to mean that if I had a company DTO with related employee DTOs I wouldn't be able to recursively save/update the company and the employees into the database.

Is that correct? And if so:

• Are there any plans to implement such functionality in the future?
• Is there a good way to achieve this functionality manually today?

Next question, we currently use the Adapter project, and that gives us the ability to optionally prefetch entities across relationships. It seems like when you add elements to your model you have to explicitly tell the designer what relationships you want on each element.

• Is there a way to have the designer generate each model with all of its relationships in a similar way entity navigators are built, and then in code specify how deep or what paths you want to fetch similar to how prefetching works? Or do we have to specify all of this at design time?

Lastly, we currently use LINQ to SQL in a lot of our code. I was reading over the LINQ to SQL section, specifically the limitations around inheritance and fetching derived subtypes.

• Is this indicating that you can't use LINQ to SQL to load elements with derived sub-elements? (ie, I couldn't use LINQ to SQL to load a company DTO with a list of employee DTOs)

Thanks again!

mprothme wrote:

Thanks that's really helpful! I have a few other questions:

One thing I notice from the link you posted is that the methods for persisting a DTO to the database only persist the root element. I take this to mean that if I had a company DTO with related employee DTOs I wouldn't be able to recursively save/update the company and the employees into the database.

Is that correct?

Yes.

the reason for this is that the DTO's are a projection of entity elements, but due to denormalization can lack essential fields. For DTOs the reference is enough for 'the relationship' between e.g. company and the contained employee objects. For persistence into the DB the contained Employee derived DTO has to have the PK field as well.

And if so:

• Are there any plans to implement such functionality in the future?
• Is there a good way to achieve this functionality manually today?

No there aren't plans to implement this in the future as it's not really possible to do in a generic way: only if the contained DTOs are 1:1 projections of related entities, but that's precisely a restriction that's not present for DTOs. For instance, Order DTOs which contain a Customer DTO with just the Company name and the address is perfectly fine but it's not possible to persist the customer.

If you have an Employee DTO as well, you'll see that the type of the contained employee DTO inside Company isn't the same type as the Employee DTO root element. this is by design. However you could copy the persistence code for Employee DTO and use that as a base to persist the contained employee DTO objects: all the persistence code does is fetch the related entity object, set the fields from the DTO and persist the entity again.

Next question, we currently use the Adapter project, and that gives us the ability to optionally prefetch entities across relationships. It seems like when you add elements to your model you have to explicitly tell the designer what relationships you want on each element.

• Is there a way to have the designer generate each model with all of its relationships in a similar way entity navigators are built, and then in code specify how deep or what paths you want to fetch similar to how prefetching works? Or do we have to specify all of this at design time?

For DTOs you don't need to specify any prefetch paths when fetching them. So in your company DTO with contained Employee DTOs, and you want to fetch company DTO instances, just fetch these and the framework will fetch the contained employee DTOs as well, you don't need to specify prefetch paths to fetch these. If you need company DTOs without contained employee DTOs, you need to create a new company DTO without the contained employee DTOs. This is fine and by design, it gives you the flexibility to shape the DTO graphs for the specific purposes they're used in instead of the model. So if you use company in e.g. 3 different use cases, it's perfectly fine to create 3 different company DTOs, one for each use case, each with different fields and contained DTOs.

Lastly, we currently use LINQ to SQL in a lot of our code. I was reading over the LINQ to SQL section, specifically the limitations around inheritance and fetching derived subtypes.

• Is this indicating that you can't use LINQ to SQL to load elements with derived sub-elements? (ie, I couldn't use LINQ to SQL to load a company DTO with a list of employee DTOs)

correct. The main reason is that most ORMs (ours too, but due to the way our framework works internally we can do what's required here) determine what end type to materialize based on the data in the row in the resultset, however the projections to DTOs require the class instance to project into is first created and then the data is placed into it.

In general, inheritance and DTOs is in theory a nice idea and we added it for completeness, but in practice inheritance and DTOs is a no-go: avoid it.

Thanks again for the reply!

Just to clarify regards to my last LINQ to SQL question, because I'm still a bit unsure if I asked the right question.

I'm not asking about inheritance and derived classes in terms of type inheritance, ie I have a class called Person and a class called employee that inherits from Person, thus gaining its fields and methods. I'm asking about the ability to load an element that contains a nested element or elements corresponding to a database relationship.

Say I have a Company table and an employee table, and the employee table has an FK relationship to Company. In my model, I would expect to have a company DTO that had a list of employee DTO values. Is it possible to use LLBLGen Pro and LINQ to SQL to load the company DTO and its list of employee DTO values?

If that answer is a no, are there plans to support this in the future, potentially by leveraging multiple queries in the same way that prefetching works in LINQ to SQL today?

I know my organization at least wouldn't have any problems with this, even if you had to bring back potentially more data than was necessary and do the subquery projections in memory, only because most of our code leverages LINQ to SQL and if possible we'd like to not have to switch to a different method of querying to get this particular case to work.

Thanks!

mprothme wrote:

Say I have a Company table and an employee table, and the employee table has an FK relationship to Company. In my model, I would expect to have a company DTO that had a list of employee DTO values. Is it possible to use LLBLGen Pro and LINQ to SQL to load the company DTO and its list of employee DTO values?

This would be ok, as you are not fetching related set of subtypes, but only entities that inherit from some root element.

mprothme wrote:

I know my organization at least wouldn't have any problems with this, even if you had to bring back potentially more data than was necessary and do the subquery projections in memory, only because most of our code leverages LINQ to SQL and if possible we'd like to not have to switch to a different method of querying to get this particular case to work.

I would recommend you to migrate to LLBLGen Runtime Framework as it's more versatile and powerful. It's worth the effort IMHO.

Thanks for the replies again. We'll definitely take your recommendation into consideration!