Left join with ServiceStack.OrmLite returns an empty object instead of null

user_19818 0
Viewed 388

I've created a repository with a complete example of what I'm trying to do.

I have the following schema:

class Order
{
    public int OrderId { get; set; }
}

class LineItem
{
    public int LineItemId { get; set; }
    public int OrderId { get; set; }
}

I'm using ServiceStack.OrmLite to left join Order with LineItem, using this code:

var query = db.From<Order>()
              .LeftJoin<LineItem>()
              .Where(o => o.OrderId == 1);

var results = db.SelectMulti<Order, LineItem>(query);

SelectMulti() returns a List<Tuple<Order, LineItem>>. When an order has no line items, I'm getting back new LineItem() instead of null.

I expected to get null back so I could tell the difference between "no line items exist for this order" and "this order has a line item with default values".

I could check for the line item's OrderId being equal to the order's OrderId, but in theory I could have an order with OrderId 0, so in that case I wouldn't be able to tell.

Is there a better way to do this left join with OrmLite?

ormlite-servicestack servicestack c#
2 Answers

The quick and dirty solution I used was to check whether the related objects returned were "empty".

I did that with a small inline function as follows:

bool IsEmptyObject<T>(T instance)
{
    var empty = Activator.CreateInstance(instance.GetType());
    return empty.ToJson() == instance.ToJson();
}
user_14975820

I expected to get null back so I could tell the difference between "no line items exist for this order" and "this order has a line item with default values".

The issue with LEFT JOIN is that the row does exist but all fields are returned as null which OrmLite maps to an instance where no fields are initialized which is indistinguishable from a row that doesn't exist.

I could check for the line item's OrderId being equal to the order's OrderId, but in theory I could have an order with OrderId 0, so in that case I wouldn't be able to tell.

This should not be possible as Auto Incrementing Primary Keys start at 1 (by default) and increment upwards. You should consider it invalid state (which is likely an indication it was added incorrectly) if you have an int primary key with 0 so checking against default(int) should suffice.

user_857850
Related