Search code examples
nhibernatefluent-nhibernatefluent-nhibernate-mapping

nHibernate Formula Containing A Join

When I specify the following formula:

Map(x => x.PendingInviteCount)
    .Access
    .ReadOnly()
    .Formula("(SELECT COUNT(ui.UserInviteId) 
               FROM [UserInvite] ui JOIN [UserInviteOrganisation] uio 
               ON ui.UserInviteId = uio.UserInviteId 
               WHERE uio.OrganisationId = organisationId)");

the sql that is generated looks like this:

SELECT COUNT(ui.UserInviteId) 
FROM [UserInvite] ui 
JOIN [UserInviteOrganisation] organisati0_.uio on ui.UserInviteId = uio.UserInviteId 
WHERE uio.OrganisationId = organisati0_.organisationId

which fails due to uio being prefixed with organisatio0_.

Is it possible to create a formula that contains a JOIN?

Solution

  • You should use the AS keyword for setting the alias of the table. I don't think that NHibernate picks up on it the way it is currently written.