Exceptions in NHibernate when using named sql query for full text index search

Question

I am implementing a full text search using a view vw_SearchSite which has all the searchable fields and returning sites which have site IDs in common with the search results.

the query:

.....

<return alias="site" class="Site"/>

SELECT DISTINCT {site.*}
FROM v_Site {site}
WHERE {site}.Id IN (
  SELECT Id
  FROM vw_SearchSite
  WHERE CONTAINS(vw_SearchSite.*,:pattern)
)

the implementation:

 public Site[] GetSitesforSearch(string search)
    {
        using (var session = GetSession())
        {

            var q1 = session.GetNamedQuery("SearchSite").SetString("pattern", search);
            var q2 = q1.List<Site>().ToArray();
            return q2;
        }
    }

the exception when I unit test it:

in expected: {site} [SELECT DISTINCT {site.*}
FROM v_Site {site}
WHERE {site}.Id IN (
  SELECT Id
  FROM vw_SearchSite
  WHERE CONTAINS(vw_SearchSite.*,:pattern)
)]

I have full permissions to the localhost database. I tried all permutations and combinations I could with the above code and failed to figure a way out. the query is working absolutely fine when I executed it in SQL SERVER management studio.

This is My Unit Test:

public void Getsitesforsearch_returns_all_matching_sites_test() { //Arrange const string search = "abc"; var country = _entityBuilder.CreateCountry(); country.Name = "wall country"; var country1 = _entityBuilder.CreateCountry(); country.Name = "crappy country"; var state1 = _entityBuilder.CreateState(); state1.Country = country; var state2 = _entityBuilder.CreateState(); state2.Country = country1; state1.Name = "wall state"; state1.Abbreviation = "WS"; state2.Name = "crap"; state2.Abbreviation = "CR";

        var site1 = _entityBuilder.CreateSite();
        var site2 = _entityBuilder.CreateSite();
        var site3 = _entityBuilder.CreateSite();
        var site4 = _entityBuilder.CreateSite();

        site1.Name = "abc";
        site1.City = "wallsite";
        site1.PostalCode = "33333";
        site1.State = state2;

        site2.State = state2;
        site2.City = "wallsite";
        site2.PostalCode = "44444";
        site2.Name = "site wall";

        site3.State = state1;
        site3.City = "wallsite";
        site3.PostalCode = "55555";
        site3.Name = "site";

        site4.City = "walstreet";
        site4.PostalCode = "66666";
        site4.Name = "site wall";
        site4.State = state2;

        PersistEntities(state1, state2, country1,country);
        PersistEntities(site1,site2,site3,site4);
        var sites = new[] {site2,site3,site4};

        //Act
        var repository = CreateRepository();
        var result = repository.GetSitesforSearch(search);

        //Assert
        result.ShouldNotContain(site1);
        result.ShouldEqual(sites) ;
        result.ShouldContain(site2);
        result.ShouldContain(site3);
        result.ShouldContain(site4);

    }

This is the error:

SiteRepositoryTester.Getsitesforsearch_returns_all_matching_sites_test : Failed

NHibernate: select deviceinst0_.Id as Id5_, deviceinst0_.Name as Name5_, deviceinst0_.Latitude as Latitude5_, deviceinst0_.Longitude as Longitude5_, deviceinst0_.InstallationDate as Installa5_5_, deviceinst0_.RemovalDate as RemovalD6_5_, deviceinst0_.DeviceId as DeviceId5_, deviceinst0_.PrimaryGroupId as PrimaryG8_5_, deviceinst0_.SecondaryGroupId as Secondar9_5_ from tg.v_DeviceInstall deviceinst0_ NHibernate: select basestatio0_.BaseStationId as BaseStat1_4_, basestatio0_.BaseId as BaseId4_, basestatio0_.InstalledOn as Installe3_4_, basestatio0_.SiteId as SiteId4_ from tg.vw_BaseStation basestatio0_ NHibernate: select threshold0_.Id as Id8_, threshold0_.ReadingTypeId as ReadingT2_8_, threshold0_.UpperBound as UpperBound8_, threshold0_.LowerBound as LowerBound8_, threshold0_.DeviceInstallId as DeviceIn5_8_ from tg.v_Threshold threshold0_ NHibernate: select state0_.Id as Id9_, state0_.Name as Name9_, state0_.Abbreviation as Abbrevia3_9_, state0_.CountryId as CountryId9_ from tg.vw_State state0_ NHibernate: select devicegrou0_.Id as Id0_, devicegrou0_.Name as Name0_, devicegrou0_.OldId as OldId0_, devicegrou0_.DeviceGroupTypeId as DeviceGr4_0_, devicegrou0_.SiteId as SiteId0_ from tg.vw_DeviceGroup devicegrou0_ NHibernate: select user0_.Id as Id2_, user0_.Username as Username2_, user0_.Password as Password2_, user0_.FirstName as FirstName2_, user0_.LastName as LastName2_ from tg.v_User user0_ NHibernate: select site0_.Id as Id10_, site0_.Name as Name10_, site0_.Address as Address10_, site0_.City as City10_, site0_.PostalCode as PostalCode10_, site0_.Latitude as Latitude10_, site0_.Longitude as Longitude10_, site0_.PrimaryGroupName as PrimaryG8_10_, site0_.SecondaryGroupName as Secondar9_10_, site0_.StateId as StateId10_ from tg.v_Site site0_ NHibernate: select device0_.Id as Id7_, device0_.DeviceTypeId as DeviceTy2_7_, device0_.Name as Name7_, device0_.NodeId as NodeId7_, device0_.SiteId as SiteId7_ from tg.v_Device device0_ NHibernate: select connection0_.ConnectionId as Connecti1_6_, connection0_.RemoteIP as RemoteIP6_, connection0_.ConnectedOn as Connecte3_6_, connection0_.DisconnectedOn as Disconne4_6_, connection0_.BaseStationId as BaseStat5_6_ from tg.vw_BaseConnection connection0_ NHibernate: select country0_.Id as Id1_, country0_.Name as Name1_ from tg.vw_Country country0_ NHibernate: INSERT INTO tg.vw_Country (Name) VALUES (@p0); select SCOPE_IDENTITY(); @p0 = 'United States' NHibernate: INSERT INTO tg.vw_State (Name, Abbreviation, CountryId) VALUES (@p0, @p1, @p2); select SCOPE_IDENTITY(); @p0 = 'Minnesota', @p1 = 'MN', @p2 = '347' NHibernate: NHibernate: INSERT INTO tg.vw_Country (Name) VALUES (@p0); select SCOPE_IDENTITY(); @p0 = 'United States' NHibernate: INSERT INTO tg.vw_State (Name, Abbreviation, CountryId) VALUES (@p0, @p1, @p2); select SCOPE_IDENTITY(); @p0 = 'Minnesota', @p1 = 'MN', @p2 = '348' NHibernate: NHibernate: INSERT INTO tg.vw_Country (Name) VALUES (@p0); select SCOPE_IDENTITY(); @p0 = 'United States' NHibernate: INSERT INTO tg.vw_Country (Name) VALUES (@p0); select SCOPE_IDENTITY(); @p0 = 'crappy country' NHibernate: NHibernate: INSERT INTO tg.v_Site (Name, Address, City, PostalCode, Latitude, Longitude, PrimaryGroupName, SecondaryGroupName, StateId) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8); select SCOPE_IDENTITY(); @p0 = 'abc', @p1 = '12343 Testing Blvd', @p2 = 'wallsite', @p3 = '33333', @p4 = '55', @p5 = '-92.2', @p6 = 'Pri', @p7 = 'Sec', @p8 = '181' NHibernate: INSERT INTO tg.v_Site (Name, Address, City, PostalCode, Latitude, Longitude, PrimaryGroupName, SecondaryGroupName, StateId) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8); select SCOPE_IDENTITY(); @p0 = 'site wall', @p1 = '12343 Testing Blvd', @p2 = 'wallsite', @p3 = '44444', @p4 = '55', @p5 = '-92.2', @p6 = 'Pri', @p7 = 'Sec', @p8 = '181' NHibernate: INSERT INTO tg.v_Site (Name, Address, City, PostalCode, Latitude, Longitude, PrimaryGroupName, SecondaryGroupName, StateId) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8); select SCOPE_IDENTITY(); @p0 = 'site', @p1 = '12343 Testing Blvd', @p2 = 'wallsite', @p3 = '55555', @p4 = '55', @p5 = '-92.2', @p6 = 'Pri', @p7 = 'Sec', @p8 = '180' NHibernate: INSERT INTO tg.v_Site (Name, Address, City, PostalCode, Latitude, Longitude, PrimaryGroupName, SecondaryGroupName, StateId) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8); select SCOPE_IDENTITY(); @p0 = 'site wall', @p1 = '12343 Testing Blvd', @p2 = 'walstreet', @p3 = '66666', @p4 = '55', @p5 = '-92.2', @p6 = 'Pri', @p7 = 'Sec', @p8 = '181'

NHibernate: SELECT DISTINCT site.Id as Id10_0_, site.Name as Name10_0_, site.Address as Address10_0_, site.City as City10_0_, site.PostalCode as PostalCode10_0_, site.Latitude as Latitude10_0_, site.Longitude as Longitude10_0_, site.PrimaryGroupName as PrimaryG8_10_0_, site.SecondaryGroupName as Secondar9_10_0_, site.StateId as StateId10_0_ FROM v_Site site WHERE site.Id IN ( SELECT Id FROM vw_SearchSite WHERE CONTAINS(vw_SearchSite.*,@p0) ); @p0 = 'abc'

NHibernate: select deviceinst0_.Id as Id5_, deviceinst0_.Name as Name5_, deviceinst0_.Latitude as Latitude5_, deviceinst0_.Longitude as Longitude5_, deviceinst0_.InstallationDate as Installa5_5_, deviceinst0_.RemovalDate as RemovalD6_5_, deviceinst0_.DeviceId as DeviceId5_, deviceinst0_.PrimaryGroupId as PrimaryG8_5_, deviceinst0_.SecondaryGroupId as Secondar9_5_ from tg.v_DeviceInstall deviceinst0_ NHibernate: select basestatio0_.BaseStationId as BaseStat1_4_, basestatio0_.BaseId as BaseId4_, basestatio0_.InstalledOn as Installe3_4_, basestatio0_.SiteId as SiteId4_ from tg.vw_BaseStation basestatio0_ NHibernate: select threshold0_.Id as Id8_, threshold0_.ReadingTypeId as ReadingT2_8_, threshold0_.UpperBound as UpperBound8_, threshold0_.LowerBound as LowerBound8_, threshold0_.DeviceInstallId as DeviceIn5_8_ from tg.v_Threshold threshold0_ NHibernate: select state0_.Id as Id9_, state0_.Name as Name9_, state0_.Abbreviation as Abbrevia3_9_, state0_.CountryId as CountryId9_ from tg.vw_State state0_ NHibernate: select devicegrou0_.Id as Id0_, devicegrou0_.Name as Name0_, devicegrou0_.OldId as OldId0_, devicegrou0_.DeviceGroupTypeId as DeviceGr4_0_, devicegrou0_.SiteId as SiteId0_ from tg.vw_DeviceGroup devicegrou0_ NHibernate: select user0_.Id as Id2_, user0_.Username as Username2_, user0_.Password as Password2_, user0_.FirstName as FirstName2_, user0_.LastName as LastName2_ from tg.v_User user0_ NHibernate: select site0_.Id as Id10_, site0_.Name as Name10_, site0_.Address as Address10_, site0_.City as City10_, site0_.PostalCode as PostalCode10_, site0_.Latitude as Latitude10_, site0_.Longitude as Longitude10_, site0_.PrimaryGroupName as PrimaryG8_10_, site0_.SecondaryGroupName as Secondar9_10_, site0_.StateId as StateId10_ from tg.v_Site site0_ NHibernate: SELECT primarygro0_.SiteId as SiteId1_, primarygro0_.Id as Id1_, primarygro0_.Id as Id0_0_, primarygro0_.Name as Name0_0_, primarygro0_.OldId as OldId0_0_, primarygro0_.DeviceGroupTypeId as DeviceGr4_0_0_, primarygro0_.SiteId as SiteId0_0_ FROM tg.vw_DeviceGroup primarygro0_ WHERE ((primarygro0_.DeviceGroupTypeId = 1)) and primarygro0_.SiteId=@p0; @p0 = '381' NHibernate: SELECT secondaryg0_.SiteId as SiteId1_, secondaryg0_.Id as Id1_, secondaryg0_.Id as Id0_0_, secondaryg0_.Name as Name0_0_, secondaryg0_.OldId as OldId0_0_, secondaryg0_.DeviceGroupTypeId as DeviceGr4_0_0_, secondaryg0_.SiteId as SiteId0_0_ FROM tg.vw_DeviceGroup secondaryg0_ WHERE ((secondaryg0_.DeviceGroupTypeId = 2)) and secondaryg0_.SiteId=@p0; @p0 = '381' NHibernate: SELECT primarygro0_.SiteId as SiteId1_, primarygro0_.Id as Id1_, primarygro0_.Id as Id0_0_, primarygro0_.Name as Name0_0_, primarygro0_.OldId as OldId0_0_, primarygro0_.DeviceGroupTypeId as DeviceGr4_0_0_, primarygro0_.SiteId as SiteId0_0_ FROM tg.vw_DeviceGroup primarygro0_ WHERE ((primarygro0_.DeviceGroupTypeId = 1)) and primarygro0_.SiteId=@p0; @p0 = '382' NHibernate: SELECT secondaryg0_.SiteId as SiteId1_, secondaryg0_.Id as Id1_, secondaryg0_.Id as Id0_0_, secondaryg0_.Name as Name0_0_, secondaryg0_.OldId as OldId0_0_, secondaryg0_.DeviceGroupTypeId as DeviceGr4_0_0_, secondaryg0_.SiteId as SiteId0_0_ FROM tg.vw_DeviceGroup secondaryg0_ WHERE ((secondaryg0_.DeviceGroupTypeId = 2)) and secondaryg0_.SiteId=@p0; @p0 = '382' NHibernate: SELECT primarygro0_.SiteId as SiteId1_, primarygro0_.Id as Id1_, primarygro0_.Id as Id0_0_, primarygro0_.Name as Name0_0_, primarygro0_.OldId as OldId0_0_, primarygro0_.DeviceGroupTypeId as DeviceGr4_0_0_, primarygro0_.SiteId as SiteId0_0_ FROM tg.vw_DeviceGroup primarygro0_ WHERE ((primarygro0_.DeviceGroupTypeId = 1)) and primarygro0_.SiteId=@p0; @p0 = '383' NHibernate: SELECT secondaryg0_.SiteId as SiteId1_, secondaryg0_.Id as Id1_, secondaryg0_.Id as Id0_0_, secondaryg0_.Name as Name0_0_, secondaryg0_.OldId as OldId0_0_, secondaryg0_.DeviceGroupTypeId as DeviceGr4_0_0_, secondaryg0_.SiteId as SiteId0_0_ FROM tg.vw_DeviceGroup secondaryg0_ WHERE ((secondaryg0_.DeviceGroupTypeId = 2)) and secondaryg0_.SiteId=@p0; @p0 = '383' NHibernate: SELECT primarygro0_.SiteId as SiteId1_, primarygro0_.Id as Id1_, primarygro0_.Id as Id0_0_, primarygro0_.Name as Name0_0_, primarygro0_.OldId as OldId0_0_, primarygro0_.DeviceGroupTypeId as DeviceGr4_0_0_, primarygro0_.SiteId as SiteId0_0_ FROM tg.vw_DeviceGroup primarygro0_ WHERE ((primarygro0_.DeviceGroupTypeId = 1)) and primarygro0_.SiteId=@p0; @p0 = '384' NHibernate: SELECT secondaryg0_.SiteId as SiteId1_, secondaryg0_.Id as Id1_, secondaryg0_.Id as Id0_0_, secondaryg0_.Name as Name0_0_, secondaryg0_.OldId as OldId0_0_, secondaryg0_.DeviceGroupTypeId as DeviceGr4_0_0_, secondaryg0_.SiteId as SiteId0_0_ FROM tg.vw_DeviceGroup secondaryg0_ WHERE ((secondaryg0_.DeviceGroupTypeId = 2)) and secondaryg0_.SiteId=@p0; @p0 = '384' NHibernate: select device0_.Id as Id7_, device0_.DeviceTypeId as DeviceTy2_7_, device0_.Name as Name7_, device0_.NodeId as NodeId7_, device0_.SiteId as SiteId7_ from tg.v_Device device0_ NHibernate: select connection0_.ConnectionId as Connecti1_6_, connection0_.RemoteIP as RemoteIP6_, connection0_.ConnectedOn as Connecte3_6_, connection0_.DisconnectedOn as Disconne4_6_, connection0_.BaseStationId as BaseStat5_6_ from tg.vw_BaseConnection connection0_ NHibernate: select country0_.Id as Id1_, country0_.Name as Name1_ from tg.vw_Country country0_ NHibernate: SELECT states0_.CountryId as CountryId1_, states0_.Id as Id1_, states0_.Id as Id9_0_, states0_.Name as Name9_0_, states0_.Abbreviation as Abbrevia3_9_0_, states0_.CountryId as CountryId9_0_ FROM tg.vw_State states0_ WHERE states0_.CountryId=@p0; @p0 = '347' NHibernate: SELECT states0_.CountryId as CountryId1_, states0_.Id as Id1_, states0_.Id as Id9_0_, states0_.Name as Name9_0_, states0_.Abbreviation as Abbrevia3_9_0_, states0_.CountryId as CountryId9_0_ FROM tg.vw_State states0_ WHERE states0_.CountryId=@p0; @p0 = '348' NHibernate: SELECT states0_.CountryId as CountryId1_, states0_.Id as Id1_, states0_.Id as Id9_0_, states0_.Name as Name9_0_, states0_.Abbreviation as Abbrevia3_9_0_, states0_.CountryId as CountryId9_0_ FROM tg.vw_State states0_ WHERE states0_.CountryId=@p0; @p0 = '349' NHibernate: SELECT states0_.CountryId as CountryId1_, states0_.Id as Id1_, states0_.Id as Id9_0_, states0_.Name as Name9_0_, states0_.Abbreviation as Abbrevia3_9_0_, states0_.CountryId as CountryId9_0_ FROM tg.vw_State states0_ WHERE states0_.CountryId=@p0; @p0 = '350' NHibernate: NHibernate: NHibernate: NHibernate:

System.Data.SqlClient.SqlException: Invalid object name 'v_Site'.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd) at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean autoDiscoverTypes, Boolean callable, RowSelection selection, ISessionImplementor session) at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)

NHibernate.ADOException: could not execute query [ SELECT DISTINCT site.Id as Id10_0_, site.Name as Name10_0_, site.Address as Address10_0_, site.City as City10_0_, site.PostalCode as PostalCode10_0_, site.Latitude as Latitude10_0_, site.Longitude as Longitude10_0_, site.PrimaryGroupName as PrimaryG8_10_0_, site.SecondaryGroupName as Secondar9_10_0_, site.StateId as StateId10_0_ FROM v_Site site WHERE site.Id IN ( SELECT Id FROM vw_SearchSite WHERE CONTAINS(vw_SearchSite.,?) ) ] Name:pattern - Value:abc [SQL: SELECT DISTINCT site.Id as Id10_0_, site.Name as Name10_0_, site.Address as Address10_0_, site.City as City10_0_, site.PostalCode as PostalCode10_0_, site.Latitude as Latitude10_0_, site.Longitude as Longitude10_0_, site.PrimaryGroupName as PrimaryG8_10_0_, site.SecondaryGroupName as Secondar9_10_0_, site.StateId as StateId10_0_ FROM v_Site site WHERE site.Id IN ( SELECT Id FROM vw_SearchSite WHERE CONTAINS(vw_SearchSite.,?) )]

at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) at NHibernate.Loader.Custom.CustomLoader.List(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results) at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results) at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters) at NHibernate.Impl.SqlQueryImpl.List() at Toro.TurfGuard.Common.Infrastructure.DataAccess.Impl.SiteRepository.GetSitesforSearch(String search) in SiteRepository.cs: line 33 at Toro.TurfGuard.Common.IntegrationTests.Infrastructure.DataAccess.Impl.SiteRepositoryTester.Getsitesforsearch_returns_all_matching_sites_test() in SiteRepositoryTester.cs: line 109

So, could anyone help me with handling these exceptions. immediate help appreciated.

It doesn't look like you included the whole error. Could you include the full message, along with a stack trace? — Gabe Moothart
I included the full message... couldn't find the stack trace in it. — Anant

JulianM JulianM · Accepted Answer · 2010-07-10T03:46:06

Is the table name, v_Site, correct?

System.Data.SqlClient.SqlException: Invalid object name 'v_Site'

Exceptions in NHibernate when using named sql query for full text index search

1 Answers