Advanced Retrieve

Mapping to multiple types

All samples assume the following entity definition:

public class Customer 
{
    [PrimaryKey, MapColumn("CustomerID")]
    public string Id { get; set; }
    public string CompanyName { get; set; }
    public List<Order> Orders { get; set; }
}

public class Order 
{
    public int OrderId { get; set; }
    [References(typeof(Customer))]
    public string CustomerId { get; set; }
    public string ShipPostalCode { get; set; }
}

Besides mapping to a single result query, it is possible to map to multi-result query.

• Here is an example of eager load, which is the most appropriate for producing an object graph. Aggregate extension method requires a developer to specify aggregate root type.

  // Retrieve customers with orders as object graph
  var retrieve_customer_with_orders_graph = ((IMultiResult)ObjectFactory.Retrieve<Customer, Order>(
                                               sql: @"select * from Customers where CustomerID = @CustomerID; 
                                                     select * from Orders where CustomerID = @CustomerID",
                                               parameters: new ParamList { CustomerId => "ALFKI" })
                                             ).Aggregate<Customer>();
  var customer = retrieve_customer_with_orders_graph.First();
  // Notice that customer.Orders is loaded with relevant Order instances

  Note: In order for eager load to work with multiple DTO types, a developer needs to setup reference relations similar to defining a foreign key. There is also an alternative way to eager load objects using ObjectFactory.Select method that can be found here.

• Lazy loading is supported by consuming a stream of data as IMultiResult interface. Client's code is responsible for consuming the data and constructing an object graph from the stream. It is also possible to skip over results by retrieving the type a developer is interested in; however there is no way of backtracking since IMultiResult is forward only.

  // Retrieve customers with orders as multi-result
  // Note: IMultiResult implements IEnumerable<T> where T is the first type parameter
  // specified in ObjectFactory.Retrieve
  var retrieve_customer_with_orders_lazy = ObjectFactory.Retrieve<Customer, Order>(
                                               sql: @"select * from Customers where CustomerID = @CustomerID;
                                                      select * from Orders where CustomerID = @CustomerID",
                                               parameters: new ParamList { CustomerId => "ALFKI" });
  var lazy_customer = retrieve_customer_with_orders_lazy.FirstOrDefault(); 
  // The following will also work
  // var lazy_customer = ((IMultiResult)retrieve_customer_with_orders_lazy).Retrieve<Customer>().FirsOrDefault();
  var lazy_orders = ((IMultiResult)retrieve_customer_with_orders_lazy).Retrieve<Order>();
  // Notice that lazy_customer.Order is not loaded with orders; 
  // at this point it is up to a developer to link customers with relevant orders 
  lazy_customer.Orders = lazy_orders.ToList();

Mapping single row to multiple types

Sometimes a query with joins returns a result-set where a single row may contain data for more than one DTO. Thus a single result-set may contain whole object graph. These scenarios can be handled by providing a mapping delegate to ObjectFactory.Retrieve method. A mapping delegate is responsible for assembling related objects in the object graph. It is important to remember that the first type parameter in ObjectFactory.Retrieve is also a root DTO type.

// Retrieve orders with customer as a single row mapping
var retrieve_orders_with_customer = ObjectFactory.Retrieve<Order, Customer>(
                                     sql: @"select c.CustomerID, c.CompanyName, o.OrderID, o.ShipPostalCode 
                                            from Customers c left join Orders o on o.CustomerID = c.CustomerID 
                                            where c.CustomerID = @CustomerID",
                                     parameters: new ParamList { CustomerId => "ALFKI" },
                                     map: (o, c) => { o.Customer = c; return o; });

It is also possible to use Customer as aggregate root type; however mapping becomes more complicated. If a mapping delegate produces null, Nemo does not propagate to the consumer. This fact can be used to construct a more sophisticated mapping delegate to "normalize" the result-set.

Here is the sample mapper code, however there is also a built-in mapper DefaultAggregatePropertyMapper that can achieve very similar result:

public class CustomerOrderMapper
{
    private Customer _current;
    private ObjectEqualityComparer<Customer> _comparer = new ObjectEqualityComparer<Customer>();
    
    public Customer Map(Customer c, Order o)
    {
        // Terminating call.  Since we can return null from this function
        // we need to be ready for Nemo to callback later with null
        // parameters
        if (c == null)
            return _current;

        // Is this the same customer as the current one we're processing
        // just add this order to the current customer's collection of orders
        if (_current != null && _comparer.Equals(_current, c))
        {
            _current.Orders.Add(o);
            // Return null to indicate we're not done with this customer yet
            return null;
        }

        // Otherwise this is either a different customer  
        // or this is the first time through 
        var prev = _current;
        _current = c;
        _current.Orders = new List<IOrder>();
        _current.Orders.Add(o);

        // Return previous customer
        return prev;
    }
}

Now we can use the mapper when we call ObjectFactory.Retrieve method.

// Retrieve customers with orders as a single row mapping
var retrieve_customer_with_orders = ObjectFactory.Retrieve<Customer, Order>(
                                     sql: @"select c.CustomerID, c.CompanyName, o.OrderID, o.ShipPostalCode 
                                            from Customers c left join Orders o on o.CustomerID = c.CustomerID 
                                            where c.CustomerID = @CustomerID",
                                     parameters: new ParamList { CustomerId => "ALFKI" },
                                     map: new DefaultAggregatePropertyMapper<Customer, Order>().Map);