Here's the gist of it. I have a class that models something like a table of tax rates. So let's say I'm trying to compare two tax rate tables, call them A and B. A is the standard table of rates for different types of items, and it has a row for every known type of item. Table A, being the standard table, has a known Id of 0. B is a different table, and B might only have a handful of tax rates that override specific rates for items in A. When we do the actual calculations, if there's no entry in B, we'll use the rate found in A. There's no direct (modeled) relationship between two tables or the rates they contain.
What I'm trying (and failing) to do is a theta-style join to return ALL the rates in A joined to any rates in B that also have rates in A. I am trying to get Nulls where there is no rate in B that corresponds to an item in A.
I should mention I'm new to NHibernate, and what I really want is a left join, but I'm not sure if I can do that when there is no modeled relationship between the things I'm trying to join, hence theta-style.
I'm psuedocoding to protect the innocent, so bear with me if I screw something up.
Code:
TaxRateTables
-------------
Id - Primary Key
TableName - varchar(100)
TaxRates
--------
Id - Primary Key
TableId - Foreign Key to TaxRateTables
ItemType - int (FK to another table, call it 'ItemTypes', would likely be modeled as a class 'Item')
Rate - currency
Dim query As New StringBuilder
query.Append("from TaxRateTables A, TaxRateTables B")
query.Append("where (A.Item = B.Item or B.Item is null) ")
query.Append("and A.Id = 0 ")
query.Append("and B.Id = ? ")
Dim myQuery As IQuery = myNHSession.CreateQuery(query.ToString)
myQuery.SetInt32(0, tableIdForB)
Dim myList As IList = myQuery.List()
Login
FAQ
Search
