Using the DTO Class Model with LLBLGen Pro Runtime Framework

Fetching DTO instances

To fetch DTO instances, the projection methods are used to append a projection to a Linq query, formulated in an IQueryable<T>, or a QuerySpec query, formulated in an EntityQuery<T>. Below an example is given of using these projection methods, both in Linq and QuerySpec.

Important!

While the projection functions are also generated for QuerySpec, these are only generated for C#, not for VB.NET due to limitations in the VB.NET compiler.

The methods fetch a set of Customer DTO class instances projected from a set of Customer entity instances, using a Linq query and QuerySpec query. The Customer DTO class instantiated is given in the Generated DTO class structure section.

Tip

The generated projection methods automatically add prefetch path directives to the Linq query and QuerySpec query. It's therefore not necessary to specify prefetch paths to load the related entities for the projection.

Fetching the queries is equal to fetching a Linq or QuerySpec query, so all the methods, including async/await, are available to you.

Info

Inheritance and derived elements is limited, and only available using the Linq based projection methods. Due to the fact the Linq query projection is run on the raw data coming from the database, it's not possible to determine at runtime which derived element subtype to materialize: no supported ORM framework supports this. The downside is that for m:1/1:1 related embedded derived elements, subtypes aren't materialized.

Example projection on database query

List<Customer> results = null;
using(var adapter = new DataAccessAdapter())
{
    var metaData = new LinqMetaData(adapter);
    var q = (from c in metaData.Customer
             where c.VisitingAddressCountry == "USA"
             select c)
            .ProjectToCustomer();
    results = q.ToList();
}

List<Customer> results = null;
using(var adapter = new DataAccessAdapter())
{
    var qf = new QueryFactory();
    var q = qf.Customer.Where(CustomerFields.VisitingAddressCountry
                        .Equal("USA")).ProjectToCustomer(qf);
    results = adapter.FetchQuery(q);
}

var metaData = new LinqMetaData();
var q = (from c in metaData.Customer
         where c.VisitingAddressCountry == "USA"
         select c)
        .ProjectToCustomer();
List<Customer> results = q.ToList();

var qf = new QueryFactory();
var q = qf.Customer.Where(CustomerFields.VisitingAddressCountry
                    .Equal("USA")).ProjectToCustomer(qf);
List<Customer> results = new TypedListDAO().FetchQuery(q);

In the query above, a normal entity fetch query is appended with a call to ProjectToCustomer which is a generated method in the generated class RootNamespace.Persistence.CustomerPersistence. This method simply constructs a lambda which converts the entity into a DTO during query fetch, which means the data read from the database is immediately converted to the DTO class Customer, without first instantiating entity class instances.

As the ProjectToCustomer methods are generated code, you can see for yourself how it's done: they contain a straightforward Linq or QuerySpec projection. It's located in a partial class, and it's easy to add your own projections to the class if you want to.

Tip

The generated ProjectToDerivedElementName method has a user code region which isn't overwritten by the code generator, and which allows you to add additional elements to the projection (C# only. VB.NET doesn't allow comments in multi-line spanning statements)

Example projection on in-memory datastructure

Info

Projecting in-memory datastructures is only available through the IQueryable<T> interface. In practice this doesn't really matter much, but if you extend the QuerySpec projection method, it won't run when you use the in-memory projection: you have to extend the Linq variant.

Instead of projecting DTOs from IQueryable<T> queries directly on the database, you can also project an in-memory construct to DTO instances. This is illustrated below. First the set of entity instances to project are fetched as entity instances and placed into a list. Then the entity instances in the list are projected into DTO instances. In this case, this is less efficient than the query in the previous example, but in case you have to do specific work with the entity instances, it can be beneficial. 

List<CustomerEntity> entities = null;
using(var adapter = new DataAccessAdapter())
{
    var metaData = new LinqMetaData(adapter);
    var q = (from c in metaData.Customer
             where c.VisitingAddressCountry == "USA"
             select c);
    entities = q.ToList();
}
// 'entities' is now a list of materialized entity instances. 
// you can now project these entities into DTO instances.
List<Customer> dtos = entities.AsQueryable().ProjectToCustomer().ToList();

var metaData = new LinqMetaData();
var q = (from c in metaData.Customer
         where c.VisitingAddressCountry == "USA"
         select c);
List<CustomerEntity> entities = q.ToList();
// 'entities' is now a list of materialized entity instances. 
// you can now project these entities into DTO instances.
List<Customer> dtos = entities.AsQueryable().ProjectToCustomer().ToList();
Important!

Be aware that the in-memory projection doesn't contain any null checks, so if a related entity is null (Nothing in VB.NET) and it's used in a navigation in the projection, using the projection method will lead to a NullReferenceException being thrown.

Writing changes from DTO to entity instances

For the preset SD.DTOClasses.ReadWriteDTOs, additional extension methods are generated to use the DTO instance to update the root entity instance it was initially projected from. This is useful if you receive a filled DTO instance or set of DTO instances from e.g. the client application and the entity instances they represent have to be updated with the values in the DTO / DTOs.

The generated code is designed to use the following pattern:

  1. Load the original entity instance using a filter created from the DTO instance
  2. Update the loaded entity instance with the values from the DTO instance
  3. Persist the loaded, updated entity instance to the database.

For step 1 there are two methods available: 'RootDerivedElementNamePersistence.CreatePkPredicate(dto)' and 'RootDerivedElementNamePersistence.CreateInMemoryPkPredicate(dto)'. The first method, CreatePkPredicate(dto), is used with a Linq query to fetch the entity or entities from the persistence storage (database). The second method is used to obtain the entity from an in-memory datastructure, e.g. an IEnumerable<T> and is used in a Linq-to-objects query. The method CreatePkPredicate(dto) has an overload which accepts a set of DTOs instead of a single DTO instance. This is usable if you want to update a set of entities based on a set of DTO instances, you can then obtain the entities with one Linq query.

For step 2, the extension method 'UpdateFromRootDerivedElementName(dto)' is used, on the entity instance to update. For step 3, the regular code to save an entity is used.

Examples

Below examples are given for obtaining entities from in-memory datastructures as well as obtaining entities from the database using Linq. The Root Derived Element used is a 'CustomerOrder' which is derived from the entity 'Customer' and has embedded 'Order' derived elements derived from the related 'Order' entity.

Info

By design, only the entity the Root Derived Element derives from is updated, related entities aren't updated.

Updating single entity, fetched from database

The variable dto contains the dto with the data, received from the client. 

using(var adapter = new DataAccessAdapter())
{
    var metaData = new LinqMetaData(adapter);
    // fetch the entity from the DB
    var entity = metaData.Customer
                    .FirstOrDefault(CustomerOrderPersistence.CreatePkPredicate(dto));
    if(entity==null)
    {
        // doesn't exist, so create a new instance.
        entity = new CustomerEntity();
    }
    // update entity with values of dto
    entity.UpdateFromCustomerOrder(dto);
    // save entity
    adapter.SaveEntity(entity);
}

var metaData = new LinqMetaData();
// fetch the entity from the DB
var entity = metaData.Customer
                .FirstOrDefault(CustomerOrderPersistence.CreatePkPredicate(dto));
if(entity==null)
{
    // doesn't exist, so create a new instance.
    entity = new CustomerEntity();
}
// update entity with values of dto
entity.UpdateFromCustomerOrder(dto);
// save entity
entity.Save();

Updating set of entities, fetched from database

The variable dtos contains the set of dto instances with the data, received from the client. 

using(var adapter = new DataAccessAdapter())
{
    var metaData = new LinqMetaData(adapter);
    // fetch the entities from the DB
    var entities = metaData.Customer
                            .Where(CustomerOrderPersistence.CreatePkPredicate(dtos))
                            .ToList();
    var uow = new UnitOfWork2();
    foreach(var dto in dtos)
    {
        // find the entity the current dto derived from, using in-memory pk filter
        var entity = entities
                        .FirstOrDefault(CustomerOrderPersistence.CreateInMemoryPkPredicate(dto));
        if(entity == null)
        {
            // new entity
            entity = new CustomerEntity();
        }
        // update entity with values of dto
        entity.UpdateFromCustomerOrder(dto);
        // log for persistence
        uow.AddForSave(entity);
    }
    // persist all changed entities in one transaction
    uow.Commit(adapter);
}

var metaData = new LinqMetaData();
// fetch the entities from the DB
var entities = metaData.Customer
                        .Where(CustomerOrderPersistence.CreatePkPredicate(dtos))
                        .ToList();
var uow = new UnitOfWork();
foreach(var dto in dtos)
{
    // find the entity the current dto derived from, using in-memory pk filter
    var entity = entities
                    .FirstOrDefault(CustomerOrderPersistence.CreateInMemoryPkPredicate(dto));
    if(entity == null)
    {
        // new entity
        entity = new CustomerEntity();
    }
    // update entity with values of dto
    entity.UpdateFromCustomerOrder(dto);
    // log for persistence
    uow.AddForSave(entity);
}
// persist all changed entities in one transaction
using(var trans = new Transaction(IsolationLevel.ReadCommitted, "Update DTOs"))
{
    uow.Commit(trans);
}

Updating single entity, obtained from in-memory structure

The variable dto contains the dto with the data, received from the client, entities is a List<CustomerEntity> in-memory structure.

// obtain the entity from an in-memory list
var entity = entities
                .FirstOrDefault(CustomerOrderPersistence.CreateInMemoryPkPredicate(dto));
if(entity==null)
{
    // doesn't exist, so create a new instance.
    entity = new CustomerEntity();
}
// update entity with values of dto
entity.UpdateFromCustomerOrder(dto);
using(var adapter = new DataAccessAdapter())
{
    // save entity
    adapter.SaveEntity(entity);
}

// obtain the entity from an in-memory list
var entity = entities
                .FirstOrDefault(CustomerOrderPersistence.CreateInMemoryPkPredicate(dto));
if(entity==null)
{
    // doesn't exist, so create a new instance.
    entity = new CustomerEntity();
}
// update entity with values of dto
entity.UpdateFromCustomerOrder(dto);
// save entity
entity.Save();

Updating set of entities, obtained from in-memory structure

The variable dtos contains the set of dto instances with the data, received from the client, entities is a List<CustomerEntity> in-memory structure.

var uow = new UnitOfWork2();
foreach(var dto in dtos)
{
    // find the entity the current dto derived from, using in-memory pk filter
    var entity = entities
                    .FirstOrDefault(CustomerOrderPersistence.CreateInMemoryPkPredicate(dto));
    if(entity == null)
    {
        // new entity
        entity = new CustomerEntity();
    }
    // update entity with values of dto
    entity.UpdateFromCustomerOrder(dto);
    // log for persistence
    uow.AddForSave(entity);
}
using(var adapter = new DataAccessAdapter())
{
    // persist all changed entities in one transaction
    uow.Commit(adapter);
}

var uow = new UnitOfWork();
foreach(var dto in dtos)
{
    // find the entity the current dto derived from, using in-memory pk filter
    var entity = entities
                    .FirstOrDefault(CustomerOrderPersistence.CreateInMemoryPkPredicate(dto));
    if(entity == null)
    {
        // new entity
        entity = new CustomerEntity();
    }
    // update entity with values of dto
    entity.UpdateFromCustomerOrder(dto);
    // log for persistence
    uow.AddForSave(entity);
}
// persist all changed entities in one transaction
using(var trans = new Transaction(IsolationLevel.ReadCommitted, "Update DTOs"))
{
    uow.Commit(trans);
}

Adjusting the generated DTO types with code

Info

This is an LLBLGen Pro Runtime Framework specific feature, it's not available in the generated code of Derived Models for other target frameworks

Adjusting a generated projection for a derived element for the LLBLGen Pro framework, offers two options: adding new elements to the projection and replacing an element in the generated projection with a new element.

To adjust a projection, a new partial class of the generated persistence class has to be created. Then, an implementation of the partial method for the particular projection method has to be implemented.

The format of this partial method is for linq: GetAdjustmentsFor_ProjectionMethodName_, and for QuerySpec: GetAdjustmentsFor_ProjectMethodName_QS. The QuerySpec variant has a suffix, even though the argument is of a different type, to make it easier to see which method to implement. It receives a ref argument which should be filled with the projection lambda. How it works is shown below in an example.

Example

Say the persistence class is CustomerOrderPersistence and the projection method is ProjectToCustomerOrder. The CustomerOrderPersistence class contains a partial method definition called GetAdjustmentsForProjectToCustomerOrder. In a partial class of CustomerOrderPersistence, add an implementation of that partial method. The method receives a ref argument called projectionAdjustments.

In the partial method implementation define the projection in the same fashion as the generated method, so assign a lambda to the passed in ref argument which uses a parameter and creates a new instance of the derived element class. 

public static partial class CustomerOrderPersistence
{
    static partial void GetAdjustmentsForProjectToCustomerOrder(ref System.Linq.Expressions.Expression<Func<NWMM.Adapter.EntityClasses.CustomerEntity,
    NWMM.Dtos.DtoClasses.CustomerOrder>> projectionAdjustments)
    {
        projectionAdjustments = p0 => new NWMM.Dtos.DtoClasses.CustomerOrder()
        {
            VisitingAddress = p0.VisitingAddressAddress + ", " + p0.VisitingAddressCity + " (" + p0.VisitingAddressCountry + ")",      // New member
            CompanyName = "!" + p0.CompanyName,          // Replacing existing member
        };
    }
}

By specifying new members that aren't specified in the original generated projection method, the member will be added to the projection. By specifying the same member as in the original generated projection method, the member in the original method will be replaced in the final projection.

This gives a flexible way to adjust the generated DTO classes for instance with expressions that would otherwise not be possible.

For QuerySpec the same method looks like:

static partial void GetAdjustmentsForProjectToCustomerOrderQs(ref System.Linq.Expressions.Expression<Func<NWMM.Dtos.DtoClasses.CustomerOrder>> projectionAdjustments)
{
    projectionAdjustments = () => new NWMM.Dtos.DtoClasses.CustomerOrder()
    {
         VisitingAddress = CustomerFields.VisitingAddressAddress.Source("__BQ").ToValue<String>() + ", " +
                  CustomerFields.VisitingAddressCity.Source("__BQ").ToValue<string>() + " (" +
                  CustomerFields.VisitingAddressCountry.Source("__BQ").ToValue<string>() + ")",
         CompanyName = "!" + CustomerFields.CompanyName.Source("__BQ").ToValue<string>(),
     };
}

It uses the alias __BQ to refer to the base query's alias. This is required.

Specifying optional Where/OrderBy clauses for nested sets in derived elements

The query for the main derived elements can be ordered and filtered using normal linq / queryspec constructs however the nested sets in the projection require a specific object to be able to specify optional where / orderby clauses.

Say you have a derived element based on 'Customer' called CustomerOrder and a nested 'Orders' set in that CustomerOrder element. Fetching Customers and projecting them using the generated ProjectToCustomerOrder() method will result in all orders for each projected customer being fetched, and they're not ordered.

To specify a filter for the orders per customer and e.g. sort them on a field, use the generated class CustomerOrderProjectionParams. These classes are called DerivedElementNameProjectionParams, there's one for each derived element. These generated classes offer both for Linq and QuerySpec methods to specify where clauses and orderby clauses, for each nested set.

For Linq, use LinqWhereClause and AppendLinqOrderBy. For QuerySpec use QSWhereClause and AppendQSOrderBy.

In our example above, we can do the following:

var ordersParams = new CustomerOrderProjectionParams();
ordersParams.OrdersProjectionParams.LinqWhereClause = e => e.OrderId < 10700;
ordersParams.OrdersProjectionParams.AppendLinqOrderBy(o => o.ShipCountry, false);
ordersParams.OrdersProjectionParams.AppendLinqOrderBy(o => o.EmployeeId, true);

This defines for the nested set Orders a where clause (Orders with Ids smaller than 10700 are accepted) and two sort clauses. The Linq methods are used so they're only taking effect when the ordersParams is used in a Linq query for fetching the derived elements.

Full example

Linq

var ordersParams = new CustomerOrderProjectionParams();
ordersParams.OrdersProjectionParams.LinqWhereClause = e => e.OrderId < 10700;
ordersParams.OrdersProjectionParams.AppendLinqOrderBy(o => o.ShipCountry, false);
ordersParams.OrdersProjectionParams.AppendLinqOrderBy(o => o.EmployeeId, true);
using(var adapter = new DataAccessAdapter())
{
    var md = new LinqMetaData(adapter);
    var q = md.Customer.Where(c => c.VisitingAddressCountry == "USA");
    var withOrders = q.ProjectToCustomerOrder(ordersParams).ToList();
    // fetched 13 rows, filtered the orders rows in each customer element on the where clause
}

-- query 1
SELECT ((((([LPLA_1].[Address] + @p2) + [LPLA_1].[City]) + @p4) + [LPLA_1].[Country]) + @p6) AS [VisitingAddress],
       (@p8 + [LPLA_1].[CompanyName])                                                        AS [CompanyName],
       [LPLA_1].[CustomerID]                                                                 AS [CustomerId],
       1                                                                                     AS [LPFA_6]
FROM   [Northwind].[dbo].[Customers] [LPLA_1]
WHERE  ((((([LPLA_1].[Country] = @p9))))) 

-- query 2
SELECT [LPA_L1].[FirstName],
       [LPA_L1].[LastName],
       [LPA_L2].[OrderDate],
       [LPA_L2].[OrderID]    AS [OrderId],
       [LPA_L2].[CustomerID] AS [CustomerId]
FROM   ([Northwind].[dbo].[Employees] [LPA_L1]
        INNER JOIN [Northwind].[dbo].[Orders] [LPA_L2]
            ON [LPA_L1].[EmployeeID] = [LPA_L2].[EmployeeID])
WHERE  ((((((([LPA_L2].[OrderID] < @p1)))))
     AND ([LPA_L2].[CustomerID] IN (@p2, @p3, @p4, @p5,
                                    @p6, @p7, @p8, @p9,
                                    @p10, @p11, @p12, @p13, @p14))))
ORDER  BY [LPA_L2].[ShipCountry] ASC,
          [LPA_L2].[EmployeeID] DESC

QuerySpec

var ordersParams = new CustomerOrderProjectionParams();
ordersParams.OrdersProjectionParams.QSWhereClause = OrderFields.OrderId.LesserThan(10700);
ordersParams.OrdersProjectionParams.AppendQSOrderBy(OrderFields.OrderDate.Ascending());
ordersParams.OrdersProjectionParams.AppendQSOrderBy(OrderFields.EmployeeId.Descending());
var qf = new QueryFactory();
var q = qf.Customer.Where(CustomerFields.VisitingAddressCountry == "USA")
                   .ProjectToCustomerOrder(qf, ordersParams);
using(var adapter = new DataAccessAdapter())
{
    var withOrders = adapter.FetchQuery(q);
    // fetched 13 rows, filtered the orders rows in each customer element on the where clause
}

-- query 1
SELECT [LPA__1].[VisitingAddressAddress],
       [LPA__1].[VisitingAddressCity],
       [LPA__1].[VisitingAddressCountry],
       [LPA__1].[CompanyName],
       [LPA__1].[CustomerId],
       1 AS [LLBLV_1]
FROM   (SELECT [Northwind].[dbo].[Customers].[CompanyName],
               [Northwind].[dbo].[Customers].[ContactName],
               [Northwind].[dbo].[Customers].[ContactTitle],
               [Northwind].[dbo].[Customers].[CustomerID] AS [CustomerId],
               [Northwind].[dbo].[Customers].[Fax],
               [Northwind].[dbo].[Customers].[Phone],
               [Northwind].[dbo].[Customers].[PostalCode],
               [Northwind].[dbo].[Customers].[Address]    AS [VisitingAddressAddress],
               [Northwind].[dbo].[Customers].[City]       AS [VisitingAddressCity],
               [Northwind].[dbo].[Customers].[Country]    AS [VisitingAddressCountry],
               [Northwind].[dbo].[Customers].[Region]     AS [VisitingAddressRegion]
        FROM   [Northwind].[dbo].[Customers]
        WHERE  (([Northwind].[dbo].[Customers].[Country] = @p1))) [LPA__1] 

-- query 2
SELECT [LPA__2].[FirstName],
       [LPA__2].[LastName],
       [LPA__1].[OrderDate],
       [LPA__1].[OrderID]    AS [OrderId],
       [LPA__1].[CustomerID] AS [CustomerId]
FROM   ([Northwind].[dbo].[Orders] [LPA__1]
        INNER JOIN [Northwind].[dbo].[Employees] [LPA__2]
            ON [LPA__1].[EmployeeID] = [LPA__2].[EmployeeID])
WHERE  (([LPA__1].[OrderID] < @p1)
    AND ([LPA__1].[CustomerID] IN (@p2, @p3, @p4, @p5,
                                   @p6, @p7, @p8, @p9,
                                   @p10, @p11, @p12, @p13, @p14)))
ORDER  BY [LPA__1].[OrderDate] ASC,
          [LPA__1].[EmployeeID] DESC