Filtering child references

ServiceStack Auto Query
1

I have a service that brings back a List result with IJoin interfaces. In the service I get the Query and append where clauses to the query. This is working fine. I have a reference property on the Item class that brings back inventory items that share the Item’s ItemKey. I was wondering if there was a method to only return certain inventory items that have quantities?

This part works like I expect. I am getting any item that has any quantities available.

[Route("/MAS/Item/{CompanyID}")]
[Route("/MAS/Item/{CompanyID}/{ItemID}")]
[Route("/MAS/Item/{CompanyID}/{ItemID}/{Status}")]
[NamedConnection("MAS")]
public class MASItemQuery : QueryDb<MASItem>, IJoin<MASItem, ItemUnitOfMeasure>, IJoin<MASItem, BinInventory>, IJoin<BinInventory, WarehouseBin>
{
    [QueryDbField(Field = "ItemID", Template = "{Field} like {Value}", ValueFormat = "%{0}%" )]
    public string ItemID { get; set; }

    public string CompanyID { get; set; }

    public int? Status { get; set; }

}


   public object Any(MASItemQuery query)
    {
        var q = AutoQuery.CreateQuery(query, base.Request);
        q.Where<BinInventory>(bi => bi.QtyOnHand > 0 || bi.PendQtyDecrease > 0 || bi.PendQtyIncrease > 0).Where<WarehouseBin>(b => b.Status == 1);
        return AutoQuery.Execute(query, q);
    }

This is an abbreviated example of the Item class.

public class MASItem
{

    [Display(AutoGenerateField = false, AutoGenerateFilter = true, ShortName = "ItemKey")]
    public int ItemKey { get; set; }
    [Reference]
    public List<BinInventory> BinInventories { get; set; } = new List<BinInventory>();
}

This is an abbreviated example of the BinInventory class.

public class BinInventory
{

    [Display(AutoGenerateField = true, AutoGenerateFilter = true, ShortName = "WhseBinKey")]
    [References(typeof(WarehouseBin))]
    public int WhseBinKey { get; set; }


    [Display(AutoGenerateField = true, AutoGenerateFilter = true, ShortName = "ItemKey")]
    [References(typeof(MASItem))]
    public int ItemKey { get; set; }
   [Display(AutoGenerateField = true, AutoGenerateFilter = true, ShortName = "PendQtyDecrease")]
    public decimal PendQtyDecrease { get; set; }

    [Display(AutoGenerateField = true, AutoGenerateFilter = true, ShortName = "PendQtyIncrease")]
    public decimal PendQtyIncrease { get; set; }

    [Display(AutoGenerateField = true, AutoGenerateFilter = true, ShortName = "QtyOnHand")]
    public decimal QtyOnHand { get; set; }
}

What I am trying to do is return a BinInventory item if any of the quantities have values > 0.

Is there a way to accomplish this?

2

No you can’t query child references. In your custom AutoQuery implementation you can remove all results you don’t want in the returned Response DTO.

3

I figured that was going to be your answer :). My belief is that the AutoQuery.Execute is doing a LoadSelect, I get that for the ability of being an “AutoQuery”. Could you point me in a direction of maybe doing my own execute where I could do a select instead and load the reference my self with a merge?

4

You wouldn’t be able to use AutoQuery as that uses LoadSelect, so you’d need to make 2 individual queries then use the .Merge() extension method to merge the individual datasets, an example of this is in OrmLite repo:

5

Yeah I kinda already knew this :). One more question, I think I have what I wanted but had a question about the best way to go about this scenario. I am looking for a parent where the count of its children based on a where would be zero. Basically show me items where there are no children.

6

Sounds like you want a NOT IN with a subselect that returns records with children. i.e something like:

Where Id NOT IN (SELECT Id ... )
7

So is that injecting sql somewhere or is there a function derivative of that?

8

Sorry that was a complete waste of your time. My brain finally kicked in. I just needed a SelectDistinct.