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

Question

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?

Amplify

Answer 1

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();
}

Menell

This is pretty inefficient, Activator.CreateInstance() is slow when not cached and this allocates a new instance everytime. instance.GetType().GetDefaultValue().ToJson() == instance.ToJson() is more efficient, although if your Type is a struct or overrides Equals() it would be more efficient to use instance.GetType().GetDefaultValue().Equals(instance). – Gasolier

Answer 2

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.

Gasolier

Regarding the PK, there are valid situations for a PK not to be auto-incrementing, or the DBA could have chosen to start at 0 instead of 1. It's valid for OrmLite to choose not to support these situations, since they are uncommon, but the situations do exist. – Amplify

If the model has non-nullable fields but the database returns a null in that column, especially if it's a primary key, that's a pretty good indication that there were no rows on the right side of the join. I realize OrmLite does not interrogate the database to know with certainty if the column is nullable, but the .NET property being non-nullable could be a good heuristic. (Aside, I have looked at OrmLite's implementation and I see why this would be difficult to implement without a lot of refactoring.) – Amplify

@StephenJennings yeah if all fields were null and the resultset includes the PrimaryKey we could assume null instead of an empty object. It's technically possible but I don't imagine a scenario where it would be "valid" for an Order to have a 0 PK. – Gasolier

A contrived example: If the table were UnixUsers instead of Orders, then root would probably have a PK of 0. – Amplify

@StephenJennings A UnixUsers table wouldn't have an Auto Incrementing Id. – Gasolier