Reference Support, POCO style

OrmLite lets you Store and Load related entities in separate tables using [Reference] attributes in primary tables in conjunction with {Parent}Id property convention in child tables, e.g:

public class Customer
    public int Id { get; set; }
    public string Name { get; set; }

    [Reference] // Save in CustomerAddress table
    public CustomerAddress PrimaryAddress { get; set; }

    [Reference] // Save in Order table
    public List<Order> Orders { get; set; }

public class CustomerAddress
    public int Id { get; set; }
    public int CustomerId { get; set; } //`{Parent}Id` convention to refer to Customer
    public string AddressLine1 { get; set; }
    public string AddressLine2 { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string Country { get; set; }

public class Order
    public int Id { get; set; }
    public int CustomerId { get; set; } //`{Parent}Id` convention to refer to Customer
    public string LineItem { get; set; }
    public int Qty { get; set; }
    public decimal Cost { get; set; }

With the above structure you can save a POCO and all its entity references with db.Save(T,references:true), e.g:

var customer =  new Customer {
    Name = "Customer 1",
    PrimaryAddress = new CustomerAddress {
        AddressLine1 = "1 Australia Street",
        Country = "Australia"
    Orders = new[] {
        new Order { LineItem = "Line 1", Qty = 1, Cost = 1.99m },
        new Order { LineItem = "Line 2", Qty = 2, Cost = 2.99m },

db.Save(customer, references:true);

This saves the root customer POCO in the Customer table, its related PrimaryAddress in the CustomerAddress table and its 2 Orders in the Order table.

Querying POCO's with References

The Load* APIs are used to automatically load a POCO and all it's child references, e.g:

var customer = db.LoadSingleById<Customer>(customerId);

Using Typed SqlExpressions:

var customers = db.LoadSelect<Customer>(x => x.Name == "Customer 1");

More examples available in LoadReferencesTests.cs

Unlike normal complex properties, references:

  • Doesn't persist as complex type blobs
  • Doesn't impact normal querying
  • Saves and loads references independently of itself
  • Are serializable with Text serializers (only populated are visible).
  • Loads related data only 1-reference-level deep

Basically they provide a better story when dealing with referential data that doesn't impact the POCO's ability to be used as DTOs.

Merge Disconnected POCO Result Sets

The Merge extension method can stitch disconnected POCO collections together as per their relationships defined in OrmLite's POCO References.

For example, you can select a collection of Customers who've made an order with quantities of 10 or more and in a separate query select their filtered Orders and then merge the results of these 2 distinct queries together with:

//Select Customers who've had orders with Quantities of 10 or more
var q = db.From<Customer>()
          .Where<Order>(o => o.Qty >= 10)

List<Customer> customers = db.Select<Customer>(q);

//Select Orders with Quantities of 10 or more
List<Order> orders = db.Select<Order>(o => o.Qty >= 10);

customers.Merge(orders); // Merge disconnected Orders with their related Customers

customers.PrintDump();   // Print merged customers and orders datasets

Custom Load References

You can selectively specify which references you want to load using the include parameter, e.g:

var customerWithAddress = db.LoadSingleById<Customer>(customer.Id, include: new[] { "PrimaryAddress" });

var customerWithAddress = db.LoadSingleById<Customer>(customer.Id, include: x => new { x.PrimaryAddress });

Custom Select with JOIN

You can specify SQL Aliases for ambiguous columns using anonymous properties, e.g:

var q = db.From<Table>()
    .Select<Table, JoinedTable>((a, b) => new { a, JoinId = b.Id, JoinName = b.Name });

Which is roughly equivalent to:

SELECT a.*, b.Id AS JoinId, b.Name AS JoinName

Where it selects all columns from the primary Table as well as Id and Name columns from JoinedTable, returning them in the JoinId and JoinName custom aliases.

Nested JOIN Table Expressions

You can also query POCO References on JOIN tables, e.g:

var q = db.From<Table>()
    .Join<Join1, Join2>()
    .Where(x => !x.IsValid.HasValue && 
        x.Join1.IsValid &&
        x.Join1.Join2.Name == theName &&
        x.Join1.Join2.IntValue == intValue)
    .GroupBy(x => x.Join1.Join2.IntValue)
    .Having(x => Sql.Max(x.Join1.Join2.IntValue) != 10)
    .Select(x => x.Join1.Join2.IntValue);

Table aliases

The TableAlias APIs lets you specify table aliases when joining same table multiple times together to differentiate from any ambiguous columns in Queries with multiple self-reference joins, e.g:

var q = db.From<Page>(db.TableAlias("p1"))
    .Join<Page>((p1, p2) => 
        p1.PageId == p2.PageId && 
        p2.ActivityId == activityId, db.TableAlias("p2"))
    .Join<Page,Category>((p2,c) => Sql.TableAlias(p2.Category) == c.Id)
    .Join<Page,Page>((p1,p2) => Sql.TableAlias(p1.Rank,"p1") < Sql.TableAlias(p2.Rank,"p2"))
    .Select<Page>(p => new {
        ActivityId = Sql.TableAlias(p.ActivityId, "p2")

var rows = db.Select(q);

Unique Constraints

In addition to creating an Index with unique constraints using [Index(Unique=true)] you can now use [Unique] to enforce a single column should only contain unique values or annotate the class with [UniqueConstraint] to specify a composite unique constraint, e.g:

[UniqueConstraint(nameof(PartialUnique1), nameof(PartialUnique2), nameof(PartialUnique3))]
public class UniqueTest
    public int Id { get; set; }

    public string UniqueField { get; set; }

    public string PartialUnique1 { get; set; }
    public string PartialUnique2 { get; set; }
    public string PartialUnique3 { get; set; }

Auto populated Guid Ids

Support for Auto populating Guid Primary Keys is available using the [AutoId] attribute, e.g:

public class Table
    public Guid Id { get; set; }

In SQL Server it will populate Id primary key with newid(), in PostgreSQL it uses uuid_generate_v4() which requires installing the the uuid-ossp extension by running the SQL below on each PostgreSQL RDBMS it's used on:


For all other RDBMS's OrmLite will populate the Id with Guid.NewGuid(). In all RDBMS's it will populate the Id property on db.Insert() or db.Save() with the new value, e.g:

var row = new Table { ... };
row.Id //= Auto populated with new Guid

BelongTo Attribute

The [BelongTo] attribute can be used for specifying how Custom POCO results are mapped when the resultset is ambiguous, e.g:

class A { 
    public int Id { get; set; }
class B {
    public int Id { get; set; }
    public int AId { get; set; }
class C {
    public int Id { get; set; }
    public int BId { get; set; }
class Combined {
    public int Id { get; set; }
    public int BId { get; set; }

var q = db.From<A>()

var results = db.Select<Combined>(q); //Combined.BId = B.Id

Advanced Example

Seeing how the SqlExpression is constructed, joined and mapped, we can take a look at a more advanced example to showcase more of the new API's available:

List<FullCustomerInfo> rows = db.Select<FullCustomerInfo>(  // Map results to FullCustomerInfo POCO
  db.From<Customer>()                                       // Create typed Customer SqlExpression
    .LeftJoin<CustomerAddress>()                            // Implicit left join with base table
    .Join<Customer, Order>((c,o) => c.Id == o.CustomerId)   // Explicit join and condition
    .Where(c => c.Name == "Customer 1")                     // Implicit condition on base table
    .And<Order>(o => o.Cost < 2)                            // Explicit condition on joined Table
    .Or<Customer,Order>((c,o) => c.Name == o.LineItem));    // Explicit condition with joined Tables

The comments next to each line document each Type of API used. Some of the new API's introduced in this example include:

  • Usage of LeftJoin for specifying a LEFT JOIN, RightJoin and FullJoin also available
  • Usage of And<Table>(), to specify an AND condition on a Joined table
  • Usage of Or<Table1,Table2>, to specify an OR condition against 2 joined tables

More code examples of References and Joined tables are available in: