Complex Join/Query with OrmLite ServiceStack

Question

I'm having trouble to convert an EF linq Sample into OrmLite. I've got most of the query nailed, but some deep joins or subqueries is reasing some problems.

This is my EF query:

var q = from orderProduct in orderProducts
        join order in _erpContext.Orders.Include(x => x.BillingAddress.Country) on orderProduct.OrderId equals order.Id
        join product in _erpContext.Products.Include(x => x.ProductCategories).Include(x => x.ProductManufacturers) on orderProduct.ProductId equals product.Id
        where (storeId == 0 || storeId == order.StoreId) &&
            (!startDate.HasValue || startDate.Value <= order.DateCreated) &&
            (!endDate.HasValue || endDate.Value >= order.DateCreated) &&
            (!orderStatusId.HasValue || orderStatusId == (int)order.OrderStatus) &&
            (!orderTypeId.HasValue || orderTypeId == (int)order.OrderType) &&
            (!paymentStatusId.HasValue || paymentStatusId == (int)order.PaymentStatus) &&
            (!shippingStatusId.HasValue || shippingStatusId == (int)order.ShippingStatus) &&
            (!order.Deleted) &&
            (!product.Deleted) &&
            (categoryId == 0 || product.ProductCategories.Count(pc => pc.CategoryId == categoryId) > 0) &&
            (manufacturerId == 0 || product.ProductManufacturers.Count(pm => pm.ManufacturerId == manufacturerId) > 0) &&
            (billingCountryId == 0 || order.BillingAddress.CountryId == billingCountryId);

As you can see, I'm using the Include() function inside the Joins. That is the part were I'm stuck.

This is my OrmLite query:

var q = _erpDbConnection.From<OrderProduct>()
.Join<Order>((x, y) => x.OrderId == y.Id)
.Join<Product>((x, y) => x.ProductId == y.Id)    
.Where<OrderProduct>(x => x.ProductId != null && !x.Order.ContainsFreeMaterial && !x.Order.IsFitSizeOrder && x.Order.OrderType != OrderType.Stock)
.And<Order, Product>((o, p) => !o.Deleted && !p.Deleted);

if (storeId > 0)
{
    q = q.And<Order>(x => x.StoreId == storeId);
}

if (billingCountryId > 0)
{
    q = q.And<Order>(x => x.BillingAddress.CountryId == billingCountryId);
}

if (startDate.HasValue)
{
    q = q.And<Order>(x => x.DateCreated <= startDate);
}

if (endDate.HasValue)
{
    q = q.And<Order>(x => x.DateCreated >= endDate);
}

if (orderStatusId.HasValue)
{
    q = q.And<Order>(x => (int) x.OrderStatus == orderStatusId);
}

if (orderTypeId.HasValue)
{
    q = q.And<Order>(x => (int)x.OrderType == orderTypeId);
}

if (paymentStatusId.HasValue)
{
    q = q.And<Order>(x => (int)x.PaymentStatus == paymentStatusId);
}

if (shippingStatusId.HasValue)
{
    q = q.And<Order>(x => (int)x.ShippingStatus == shippingStatusId);
}

if (categoryId > 0)
{
    q = q.And<Product>(x => x.ProductCategories.Any(y => y.CategoryId == categoryId));
}

if (manufacturerId > 0)
{
    q = q.And<Product>(product => product.ProductManufacturers.Any(y => y.ManufacturerId == manufacturerId));
}

var filteredOrderProducts = _erpDbConnection.Select<OrderProduct>(q);

mythz mythz · Accepted Answer · 2017-05-29T15:42:24

OrmLite provides a typed 1:1 API mapping over normal SQL so it's generally easier to convert generated SQL than EF queries where it's often unclear at what query is being generated.

By default OrmLite SELECT's the source table which in this case is OrderProduct:

var q = _erpDbConnection.From<OrderProduct>()

Which is what's returned when you execute the query, i.e:

List<OrderProduct> results = db.Select(q);

To include related data you need to define POCO References on your source OrderProduct which can then be loaded (1-level deep) using the Load* APIs, e.g:

List<OrderProduct> results = db.LoadSelect(q);

You can also select multiple tables at once using SelectMulti, e.g:

var results = db.SelectMulti<OrderProduct, Order, Product>(q);

foreach (var tuple in results)
{
    OrderProduct orderProduct = tuple.Item1;
    Order order = tuple.Item2;
    Product product = tuple.Item3;
}

For any other Custom results you'll need to specify a Custom Select with the results you want selected, e.g:

q.Select<OrderProduct,Order,Product>((op,o,p) => 
    new {
        op,     // all fields from OrderProduct table
        o.Id,
        p.Name,
        etc...
    });

But then you'll need to access the custom result-set either in a custom schema containing public properties that matches the above query, e.g:

var results = db.Select<OrderProductView>(q);

Or by using one of the OrmLite's dynamic result set APIs.

Also note you can't do nested querying in OrmLite like:

.Where<OrderProduct>(x => x.ProductId != null 
    && !x.Order.ContainsFreeMaterial 
    && !x.Order.IsFitSizeOrder 
    && x.Order.OrderType != OrderType.Stock)

You need to query the field on the table (as you would do in normal SQL), you can also query multiple tables in the same condition with:

.Where<OrderProduct,Order>((op,o) => ...);

Complex Join/Query with OrmLite ServiceStack

1 Answers