ServiceStack OrmLite mapping with references not working

Question

I'm trying out OrmLite to see if I can replace Entity Framework in my projects. The speed is quite significant on simple queries. But I tried to map/reference a [1 to many- relation and read the documentation + examined the test code from the github page but without success. This is my example. Is there something I've forgot or should do to get it working like Entity Framework?

Example

// EF: returns +15.000 records + mapped > product.StockItems (slow)
dbContext.Products.Include(x => x.StockItems).ToList();

// OrmLite: returns +100.000 records (NO mapping > product.StockItems)  
db.Select<Product>(db.From<Product>().Join<StockItem>());

// OrmLite: +15.000 separate requests to sql server (bad workarround + slow)
foreach (var product in db.Select<Product>())
{
    // manual mapping
    product.StockItems = db.Select<StockItem>(x => x.ProductId == product.Id);
}

Product.cs

public class Product
{
    public int Id { get; set; }
    public ProductType ProductType { get; set; }         
    public string Name { get; set; }       
    public string Description { get; set; }     
    public int DisplayOrder { get; set; }
    public bool LimitedToStores { get; set; }
    public string Sku { get; set; }
    public decimal Price { get; set; }
    public decimal OldPrice { get; set; }
    public decimal SpecialPrice { get; set; }
    public decimal DiscountPercentage { get; set; }       
    public DateTime? DateChanged { get; set; }
    public DateTime? DateCreated { get; set; }
    //...

    [Reference] 
    public virtual IList<StockItem> StockItems { get; set; } = new List<StockItem>();

}

StockItem.cs

public class StockItem
{
    public int Id {get; set;}
    [References(typeof(Product))]
    public int ProductId { get; set; }
    public string Size { get; set; } 
    public int TotalStockQuantity { get; set; }   
    public string Gtin { get; set; }
    public int DisplayOrder { get; set; }
    // ...

    [Reference] 
    public virtual Product Product { get; set; }
}

Answer 1

Ideally your POCOs/DTOs shouldn't use interfaces and you don't need to use virtual as ORM only populates your own POCOs (i.e. it doesn't create proxies of your models like other Heavy ORMs), I also prefer to use [AutoIncrement] for integer Ids (unless you need to populate specific Ids) so my Models would look like:

public class Product
{
    [AutoIncrement]
    public int Id { get; set; }
    public ProductType ProductType { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public int DisplayOrder { get; set; }
    public bool LimitedToStores { get; set; }
    public string Sku { get; set; }
    public decimal Price { get; set; }
    public decimal OldPrice { get; set; }
    public decimal SpecialPrice { get; set; }
    public decimal DiscountPercentage { get; set; }
    public DateTime? DateChanged { get; set; }
    public DateTime? DateCreated { get; set; }

    [Reference]
    public List<StockItem> StockItems { get; set; }
}

public class StockItem
{
    [AutoIncrement]
    public int Id { get; set; }
    [References(typeof(Product))]
    public int ProductId { get; set; }
    public string Size { get; set; }
    public int TotalStockQuantity { get; set; }
    public string Gtin { get; set; }
    public int DisplayOrder { get; set; }
}

OrmLite's POCO References only populate 1-level deep and it's not a good idea to have cyclical relationships as they're not serializable so I'd remove the back reference on StockItems as it's not going to be populated.

You also need to use LoadSelect in order to query and return POCOs with references, so to return Product with their StockItem references you can just do:

db.LoadSelect<Product>();

You can also populate this manually with 2 queries by using Merge extension method to merge 2 disconnected record sets, e.g:

var q = db.From<Product>().Join<StockItem>();
var products = db.Select(q.SelectDistinct());
var stockItems = db.Select<StockItem>();

products.Merge(stockItems);

Which will merge Products with their StockItems which you can quickly see by running:

products.PrintDump();