Return a List of typed object via CreateSQLQuery in NHibernate

Question

Been trying to get the following query working for a few hours now and am running out of ideas. Can anyone spot where I'm going wrong. Any pointers much appreciated.

CalEvents = (List<CalEvent>)session.CreateSQLQuery(@"
    SELECT *
    FROM dbo.tb_calendar_calEvents
    INNER JOIN dbo.tb_calEvents
      ON (dbo.tb_calendar_calEvents.calEventID = dbo.tb_calEvents.id)
    WHERE dbo.tb_calendar_calEvents.calendarID = 'theCalID'"
)
.AddEntity(typeof(CalEvent))
.SetInt64("theCalID", cal.id);

Error:

Kanpeki.NUnit.CalUserTest.Should_return_logged_in_user: System.ArgumentException : Parameter theCalID does not exist as a named parameter in [SELECT * FROM dbo.tb_calendar_calEvents INNER JOIN dbo.tb_calEvents ON (dbo.tb_calendar_calEvents.calEventID = dbo.tb_calEvents.id) WHERE dbo.tb_calendar_calEvents.calendarID = 'theCalID']

Answer 1

"SELECT * FROM dbo.tb_calendar_calEvents INNER JOIN dbo.tb_calEvents ON (dbo.tb_calendar_calEvents.calEventID = dbo.tb_calEvents.id) WHERE dbo.tb_calendar_calEvents.calendarID = 'theCalID'"

should be

"SELECT * FROM dbo.tb_calendar_calEvents INNER JOIN dbo.tb_calEvents ON (dbo.tb_calendar_calEvents.calEventID = dbo.tb_calEvents.id) WHERE dbo.tb_calendar_calEvents.calendarID = :theCalID"

= 'theCalID' should be written as = :theCalId; :theCalId is how you use named parameters even in Native SQL Queries.