Using nHibernate and QueryOver how can I join 3 tables

Question

BACKGROUND: Given 3 tables

results contains 2 columns vId and pId  
vTable contains 2 columns vId and data  
pTable contains 2 columns pId and data

I want to accomplish this sort of SQL query using QueryOver

SELECT v.data, p.data  
from results r  
inner join vTable v on r.vId = v.vId   
inner join pTable p on r.pId = p.pId

I've tried the following:

var res = GetResults(some parameters)
            .Select(x => x.vId
            .Select(x => x.pID);

var dataset = session.QueryOver<vTable>()
                .WithSubquery.WhereProperty(v => v.vId).In(res)
                .Select(v => v.vId)
                .Select(v => v.data)

which works just fine to get data from vTable

however, when I add the 2nd table

var dataset = session.QueryOver<vTable>()
                .WithSubquery.WhereProperty(v => v.vId).In(res)
                .JoinQueryOver<pTable>(p => p.pId)
                .WithSubquery.WhereProperty(p => p.pId).In(res)
                .Select(v => v.vId)
                .Select(v => v.data)
                .Select(p => p.pId)
                .Select(p => p.data)

I get the error

Delegate 'System.Func<System.Collections.Generic.IEnumerable<pTable>>' does not take 1 arguments

What am I doing wrong?

lcornejo · Accepted Answer · 2011-08-17 20:30:07Z

2

.JoinQueryOver<pTable>(p => p.pId)

has to point to a mapped entity or collection not it an id, if you can't map it in the hbm. And also the JoinQueryOver will return pTables not vTables, you might want to use JoinAlias instead if you wan to retain the return type to be a list of vTables, but if all you want is that projection make sure you add aliases the QueryOver and JoinQueryOver calls

answered Aug 17, 2011 at 20:30

lcornejo

794 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using nHibernate and QueryOver how can I join 3 tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related