Hi,

I was wondering if it is possible to design complex entities using LLBLGen instead of entities that exactly match the table layout? As an example let's assume We have a Customers table in DB that holds the following columns:

FirstName LastName Street PhoneNumber

Is it possible design the following domain model using LLBLGen designer and generate the code using the adapter template:

public class Customer { public string FirstName {...} public string LastName {...} public ContactInfo ContactInfo {...} }

public class ContactInfo { public string Street {...} public string PhoneNumber {...} }

Best regards, Robert Wilczynski.

Almost everything is possible with LLBLGenPro

You need in the designer add Customer, then add it again and rename with "CustomerInfo" Edit both Customer and CustomerInfo and Add/Remove the fields you want to map at Entities.

Please read LLBLGenPro Help - Using the designer - Adding and editing entities - (especially) Fields mapped on database fields sub tab

Hope helpful

David,

Thanks for your suggestions. I have read the section of the manual you've mentioned but haven's seen anything that would relate to my problem.

I have created a Customer class and a ContactInfo class but how am I supposed to create a property of CustomerInfo type in a Customer class using the designer?

Best regards, Robert Wilczynski.

You can create a 1:1 relation between Customer (PK side) and CustomerInfo (FK side) at the designer. This will create a property like Customer.CustomerInfo in your generated code.

To achieve that read LLBLGenPro Help - Using the designer - Adding custom relations

David,

Thanks so much for all the help. I'm starting to believe LLBLGen is capable of almost anything Any idea how such design affects the performance? I am guessing it's not going to just select all fields and then place the values into correct objects but will follow the general approach when dealing with relations (sql JOIN on Customers table using CustomerId key)? Also, when using adapter template I assume I will need to specify prefetch paths to get all the data?

Robert.

Hi Robert,

Any idea how such design affects the performance? I am guessing it's not going to just select all fields and then place the values into correct objects but will follow the general approach when dealing with relations (sql JOIN on Customers table using CustomerId key)?

As a matter of fact, LLBLGenPro will generate two queries in that case, something like:

Generated Sql query: 
    Query: SELECT [Northwind].[dbo].[Customers].[CustomerID] AS [CustomerId], [Northwind].[dbo].[Customers].[CompanyName], [Northwind].[dbo].[Customers].[ContactName], [Northwind].[dbo].[Customers].[ContactTitle] FROM [Northwind].[dbo].[Customers]  WHERE ( ( [Northwind].[dbo].[Customers].[CustomerID] = @CustomerId1))
    Parameter: @CustomerId1 : StringFixedLength. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "ALFKI".

Generated Sql query: 
    Query: SELECT [Northwind].[dbo].[Customers].[CustomerID] AS [CustomerId], [Northwind].[dbo].[Customers].[Address], [Northwind].[dbo].[Customers].[City], [Northwind].[dbo].[Customers].[Region], [Northwind].[dbo].[Customers].[PostalCode], [Northwind].[dbo].[Customers].[Country], [Northwind].[dbo].[Customers].[Phone], [Northwind].[dbo].[Customers].[Fax] FROM [Northwind].[dbo].[Customers]  WHERE ( ( ( [Northwind].[dbo].[Customers].[CustomerID] = @CustomerId1)))
    Parameter: @CustomerId1 : StringFixedLength. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "ALFKI".

For large collection sets you can use DataAccessAdapter.ParameterisedPrefetchPathThreshold for increse performance (Read LLBLGenProHelp - Using generated code - Prefetch Paths).

Here is why LLBLGenPro won't make JOINS in this case: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7097&StartAtMessage=0&#39232

Also, when using adapter template I assume I will need to specify prefetch paths to get all the data?

Yes, you will, something like:

void RetrieveCustomerAndCustomerInfo()
{
    CustomerEntity customer = new CustomerEntity("ALFKI");
    PrefetchPath2 path = new PrefetchPath2((int) EntityType.CustomerEntity);
    path.Add(CustomerEntity.PrefetchPathCustomerInfo);          

    using (DataAccessAdapter adapter = new DataAccessAdapter())
    {
        adapter.FetchEntity(customer, path);
    }
}

Just a follow up question:

Are there any plans to address the issue of complex object oriented modeling in a way that would perform better than the custom relation approach?

I imagine LLBLGen could determine that a relation is in fact based solely on the columns of the parent entity, load them all in a single query and construct appropriate objects.

Robert.

Are there any plans to address the issue of complex object oriented modeling in a way that would perform better than the custom relation approach?

I imagine LLBLGen could determine that a relation is in fact based solely on the columns of the parent entity, load them all in a single query and construct appropriate objects.

I can't think of a use case to be realized with this feature. Maybe you can help us with a use case.

Let's take for example the case of a Customer and his ContactInfo. I can only think of the following options:

• A customer has its contact info in the same table as your example, and then I see no reason to split it into 2 different classes.

• If 2 classes are needed then maybe the database design should have been changed to have a different table for the contact Info.

• LLBLGen Pro can define different classes to the same database table, to realize an inheritance scenario, where you need to have multiple types in the same table, descriminated by values in a column. And yet subtype will have access to the superType fields using inheritance not by compostion (having a property for the superType object).

Hi Walaa,

Thanks for picking up the topic.

Let's take for example the case of a Customer and his ContactInfo. I can only think of the following options:

• A customer has its contact info in the same table as your example, and then I see no reason to split it into 2 different classes.

• If 2 classes are needed then maybe the database design should have been changed to have a different table for the contact Info.

DISCLAIMER: In the following paragraphs I'm not trying to teach (or preach) OO design even if it may appear like I am. I'm sure you have already been thinking about this when developing LLBLGen. Please criticize at will my interpretation of OO design principles I might be supporting myself with if they don't feel right to you.

Let's start with a simple question: "why a Customer and ContactInfo are stored in the same table". Obviously for performance reasons. Usually when a customer or a collection of customers is required we also want the address and phone number to be displayed along with the customer's name. Therefore it makes sense to keep those in a single table to be able to do it with one sql query. Why is ContactInfo (consisting of Address(es) and phone numbers) separate from a customer then? Well, that would be a natural choice in OO world. Neither a customer is a ContactInfo (think: address) nor a ContactInfo (address) is a customer - customer has contact information - thus composition. I like this code:

customer.HomeContactInfo.Address.Line1 customer.HomeContactInfo.PhoneNumber

better than:

customer customer.HomeAddressLine1

Believe me, I would prefer to have a 100% normalized model with Address table and CustomerToAddress table and PhoneNumbers table with CustomerToPhoneNumber table but in most cases this won't scale. As I see it with current version of LLBLGen I might as well use the 100% normalized model and rely on concrete relations instead of custom relations if I don't want my entities to be flat - the performance benefit of joining otherwise weakly related (but related enough to fetch them together) entities seems to be marginal if not none.

Maybe a slightly broader case but somehow related to the essence of the post. (By all means correct me if I'm not right in the next statement.) LLBLGen seems to be very coupled to the database (one could say that's the purpose of the O/RM tool) and doesn't really support domain driven design that well (think: table A has address fields and table B has the same address fields but they are separate entities even though from the domain perspective both of those addresses are one type of entity and should be substitutable). That layer of abstraction would be nice to have: Defining an Address class with no relation to an database table and then add a property of type Address to an entity and map it's fields to entity columns. This approach should also allow a single query fetch of such entity of course.

I hope this makes my reasoning behind this feature inquiry clear enough.

Best regards, Robert.

Value objects without identity (thus objects which are mapped onto fields inside the table / view target of the entity containing the value object) have a limited scope really. The most used example is 'address', which is a bit of a problematic entity anyway in relational database land (often this is modeled as a separate entity while it is never re-used and therefore can easily be migrated into the entity referencing it). However are there many more use cases for such a feature, besides address?

I'm not that sure, I couldn't come up with many examples, and therefore postponed the feature to a future version.

Hi Frans,

1) Future version doesn't mean the next version, right?

2) Sometimes it's impossible to design the schema of the database to our liking . A customer might have a system in place with other systems depending on its database schema. A new system is needed and we would like to have some flexibility modeling the domain of the new system.

3) I can give you a use case from the system we are currently implementing.

We have an order and an invoice with 1:1 mapping stored in a single table. Sometimes an invoice is needed with complete order data, and another time we can do without the order. Since the system is handling a lot of invoices it has been decided to keep the two entities in a single table for performance reasons.

Also there are a lot of examples where we have an entity that has some detail fields stored in the same table which are not required each time we fetch an entity and grouping them into a sub entity would improve the programming model.

4) Please note that an ideal implementation would include relation fields in sub entities. So an invoice has an order property and the order has order rows (in separate table). But maybe that goes without saying.

5) Another benefit that would be a consequence of implementing this feature would be easier exclusion of a set of fields - not specifying a prefetch path for sub entity would exclude all the fields from a query.

Of course the biggest advantage according to me would be additional flexibility in modeling the domain entities.

Best regards, Robert Wilczynski.

Robert.W wrote:

Hi Frans,

1) Future version doesn't mean the next version, right?

The next version we'll release is v2.6 which will have besides Linq support some minor API tweaks and bugfixes we had to postpone because they would break code. V3 is the version after that which will have at its core a different concept with respect to mapping, so this is a good candidate for this feature.

2) Sometimes it's impossible to design the schema of the database to our liking . A customer might have a system in place with other systems depending on its database schema. A new system is needed and we would like to have some flexibility modeling the domain of the new system.

Ok, though keep in mind that this will be hard to do. It's often waved away as something 'the o/r mapper should solve' but it's actually something that is pretty hard to solve, if at all possible: you can't always have one class model which is mappable on a relational schema AND have it perform OK AND have every o/r mapper feature available (e.g. saving of entities).

The basic idea is that the relational schema is actually a result of an abstact entity model, and your classes should be the result of that same model as well. Otherwise your classes represent for example an entity 'customer' which is actually defined as something completely different in the entity model used as a basis for the relational schema.

So i.o.w.: if you use model first (entity model in class form/abstract form) and create the relational schema from that, you don't have a choice but create a new schema. If you have to use the existing schema, you can't map a completely different entity model in class form onto that, simply because it perhaps would be impossible to save an entity.

In theory this is what the entity framework tried to solve, many moons ago. The current iteration is not that ambitions though. For example, you won't see examples with entities mapped on a set of table fields without a PK, or when the field set is a result of a join, the FK fields and PK fields have to be included, if you want to save the entity in the relational schema.

3) I can give you a use case from the system we are currently implementing.

We have an order and an invoice with 1:1 mapping stored in a single table. Sometimes an invoice is needed with complete order data, and another time we can do without the order. Since the system is handling a lot of invoices it has been decided to keep the two entities in a single table for performance reasons.

Also there are a lot of examples where we have an entity that has some detail fields stored in the same table which are not required each time we fetch an entity and grouping them into a sub entity would improve the programming model.

Denormalization often leads to tables which are indeed a bit hard to map entities to: you're often left with clutter you don't need in some occasions. These can indeed be an example to use, however it's not our goal to offer a mapping solution to denormalized tables. the problem is that the tables resulting from a denormalization have no real meaning: they don't represent an 'entity', they're the result of an optimization.

Mapping value types on subset of fields and make them always dependent on an entity mapped on the same table (so invoice can't live without order. the PK of order IS the pk of order and not of invoice) isn't that hard per se. Defining the mapping is at the moment a bit of a pain, but we're trying to solve that in v3. Entities which are grouped together in a single table and which gets a new ID is however a different matter: these should be seen as a new entity of which all grouped entities are actually value types of and thus depend on.

However, personally, I think mapping entities on denormalized tables is a bit of a slippery slope: the base of an entity isn't the abstract entity created from the result of analysis but a trick to get the performance up. What if that performance demand changes? Then the entity itself changes, which is IMHO database sin no.1, because if there's no theoretical base for why your entity looks the way it does, there's no way the software will ever be maintainable over time.

4) Please note that an ideal implementation would include relation fields in sub entities. So an invoice has an order property and the order has order rows (in separate table). But maybe that goes without saying.

Aren't value types always 1:1 related? Otherwise it's IMHO not possible and it should get its own table (because you need identity in that situation ! )

I find OrderLine a bit of a problematic value type. Evans tends to make it a value object, but if you for example pick the Northwind database, orderdetails (the orderline entity there) has a pk with FK's to order and product. To which aggregate root does it belong to? Unclear. Ok, the Orderline doesn't exist (semantically!) without an order, however physically, it depends on product as well.

5) Another benefit that would be a consequence of implementing this feature would be easier exclusion of a set of fields - not specifying a prefetch path for sub entity would exclude all the fields from a query.

True.

An another use case scenario: How to do subselect an entity say for example I am having a combobox that is bound to an table that has say Country { countryid int, continentcd char(1), countryname varchar(50), countrydescription varchar(255), populationcharacteristicsnote text, culturecharacteristicsnote text, countrymappicture binary }. I don't want to use all of the columns in the select list besides my predicate being continentcd = 'S' I want the generated select to be selct countryid, countryname from Country where continentcd = 'S'

However the same Country table I want in the code maintenance window to be able to see countryid , countryname , continentcd in the datagridview.

I want to do "lazy get" of note & description columns on detail or freeform mode of a particular row where I edit only those column with id there.

I want another scenario of having to select all of them.

I now have 4 different select statements.

The scenarios why people want to do sub select on a table is to have security, performance & I don't want to have all the columns when I don't need all of them. When the query generator can do partial update, partial delete, partial insert. That is columns only that are dirty are impacted in the DML. Is there a way to do the same for SELECT as well?

One of the approach I could see to address is this if I have 3 views on the table with the subselected columns & the view is updateable & the ORM has update capability on view.

Another approach could be is I can have a stored procedure or a view. And a mechanism to instance it to the Entity for the table. Provided the view also has the primary key, make it updateable instead of a newrow. (Old, dirty trick)

Or is there a better method?

One could argue with having to create a new extender table. But then the records are 1:1 relation. And having it to split into multiple tables, joining when I need all of the columns & having them to join with other tables when required in a collection. I just see big time performance issues.