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:
.....
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.