views:

14

answers:

0

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.