Join through multiple tables in nHibernate via QueryOver

Question

I have these tables: components, devicehardwareprofiles, componenttemplates, cashrecyclercounters on MsSql database

I need to fill this variables: int capacity, loaded by data from this query:

select sum(ct.capacity * cc.denomination) as capacity, sum(cc.loaded * cc.denomination) as loaded
from components c 
left outer join devicehardwareprofiles dhp 
    on dhp.deviceid = c.deviceid 
left outer join componenttemplates ct 
    on ct.hardwareprofileid = dhp.hardwareprofileid and ct.typecode = c.typecode and ct.position = 1 
left outer join cashrecyclercounters cc 
    on cc.componentid = c.componentid 
where c.deviceid = 72 and c.typecode = 155 and cc.currency = 810

How to convert this SQL to nHibernate QueryOver expression and get required data?

Addition: DTO and mappings are here: https://github.com/elscript/CashMaster

Typecode 155 stands for CashRecyclerHopper

I've already tried this one expression:

CashRecyclerHopper component = null;
DeviceHardwareProfile profile = null;
ComponentTemplate template = null;

var query = _cmSession.QueryOver(() => profile)
    .JoinQueryOver(x => x)
    .JoinAlias(a => a.HardwareProfileId, () => template, JoinType.LeftOuterJoin)
    .JoinAlias(b => b.DeviceId == component.ParentDevice.DeviceId, () => component, JoinType.LeftOuterJoin)
.JoinAlias(c => c.HardwareProfileId == template.HardwareProfileId, () => template, JoinType.LeftOuterJoin);

But it gave me the exception:

Could not determine member from (b.DeviceId == value(CashMasterWeb.Controllers.OptimizationController+<>c__DisplayClass2).component.ParentDevice.DeviceId)

Exception Details: System.Exception: Could not determine member from (b.DeviceId == value(CashMasterWeb.Controllers.OptimizationController+<>c__DisplayClass2).component.ParentDevice.DeviceId)

Source Error:

Line 81: ComponentTemplate template = null; Line 82: Line 83: var query = _cmSession.QueryOver(() => profile) Line 84: .JoinQueryOver(x => x) Line 85: .JoinAlias(a => a.HardwareProfileId, () => template, JoinType.LeftOuterJoin)

@Andrew Whitaker, not really. Is that a problem? If so, how do you suggest to map them for my purpose? — el_script, Aug 14 '15 at 10:03
In order to `join` between tables with NHibernate, you need to map the associations. Mapping the foreign key column won't be enough. — Andrew Whitaker, Aug 16 '15 at 15:25
@Andrew Whitaker, could you point any reference or some example for me to be clear? — el_script, Aug 17 '15 at 16:37
I'll try to find a good guide, but you want to look for "mapping associations with nhibernate" or "one-to-many with nhibernate" — Andrew Whitaker, Aug 17 '15 at 17:35
On the line `.JoinAlias(b => b.DeviceId == component.ParentDevice.DeviceId, () => component, JoinType.LeftOuterJoin)` you are using the alias `component` even before it is set. Is the correct? — Suhas, Aug 19 '15 at 21:41
@Suhas I guess, it's not correct.But the real problem is mapping, that are actually not full. — el_script, Aug 26 '15 at 12:23

Join through multiple tables in nHibernate via QueryOver

0 Answers0