Join with objects with composite keys

ServiceStack.OrmLite
1

Ok, first things first. I really love this product!!

I am probably asking a question that has been asked 100 times, but I have not found an answer that is final or that is definitive.

I have database that I cannot alter and the design is such that the relationships are something I am having trouble getting to work.

I can give a pseudo relationship and maybe someone can tell me what I might do to get this working.

I have really 5 to 6 tables involved.

Item --Item
Primary Key is ItemKey

WarehouseBin – Bins
Primary Key is WhseBinKey

WarehouseBinInventory Items and their quantities in a given bin.
Primary Key is ItemKey, WhseBinKey
References are ItemKey to Item, WhseBinKey to WarehouseBin

ItemLot – Lot Numbers and expriation dates for given item
Primary Key is InvtLotKey
References are ItemKey to Item

ItemLotBinInventory
PrimaryKey is InvtLotBinKey
Reference is to ItemLot on InvtLotKey and WarehouseBin on WhseBinKey

What i am trying to do is to take an Item and show WarehouseBinInventory with a parent Reference to a WarehouseBin and a child reference to WarehouseBinInventory.

Item
|_ WarehouseBinInventory - WarehouseBin
|_ItemLotBinInventory - ItemLot

I just cannot figure out the .Join syntax. Can someone help me or tell me it can’t be done.

Yes I know I could come up with a view that would work, would rather make it work with a direct reference to a Table if possible.

Thanks for the help.

2

I’m having trouble follow this example, can you add an example of this in Gistlyn I can look at.

Or do you have an example of what you’re trying to do in SQL?

Basically it sounds like you want a custom join and a custom select:

var q = db.From<WarehouseBin>()
    .Join<WarehouseBin,WarehouseBinInventory>((b,bi) => ....)
    .Select<WarehouseBin,WarehouseBinInventory,...>((b,bi,...) => new {
     });

Then you would need to use a custom Poco which matches the Select above:

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

Or you can select into a Dynamic Result Set.

3

Thanks for the reply. I guess I am just a newbie. With your help I was able to figure out the join to get what I needed.

However … :stuck_out_tongue_winking_eye: I am now trying to figure out a way to create a .Where using generics and a list of Filter objects. The idea is to be able to possibly use the UI to, with Automation attributes in xaml, create search criteria that would call the same dataservice function and be able to build the where clause dynamically. Any ideas?

Hope I am not breaking any rules by continuing this without a new thread.

Derek

4

It’s hard to workout what you want to do without sample source code, do you just mean being able to use Contains in a filter?

Otherwise please provide an example of what you’re trying to do in c#.

Note: it’s preferred that new questions to be asked in new threads as it helps discovery for others looking for similar solutions in future.

5

Sorry have been swamped. Have redesigned. This is not an issue for me now.