Since you're inlining this in a SELECT, you should not use your SP. Either use FREETEXTTABLE directly or create a UDF.
The steps are: 1. Customize the SQL dialect and configure NHibernate to use it 2. Register the functions in the Linq provider 3. Something like what you described. /G 2011/8/30 mysterd429 <[email protected]> > Hi, Gunnar, > > Okay, that will work, I think. I would use my existing stored > procedure rather than FREETEXTTABLE. Would these be the steps: > > 1. Customize the LINQ provider using something like > RegisterFunction("GetRank", /* SQLFunctionTemplate or > StandardSQLFunction, not sure, that calls DO_SEARCH using T-SQL */). > 2. Do something like this: > > String field1, field2, field3; > // assign field1, field2, field3 > IQueryable<SearchResult> queryable = > Session.Query<ResultObject>().Select(ro => new SearchResult > { ResultObject = ro, Rank = GetRank(ro, field1, field2, > field3) }).Where(ro => GetRank(ro, field1, field, field3) ?? 0 > 0); > if(myUtilityInstance != null) queryable = > myUtilityInstance.Paginate(queryable); > results = queryable.ToList(); > > Does that seem about right? Thanks so much for your help! > > Regards, > > Don > > On Aug 30, 3:40 pm, Gunnar Liljas <[email protected]> wrote: > > Hi, > > > > While there may be ways to get the result set of a stored procedure into > an > > NhQueryable, it doesn't make much sense, since any extra Linq predicates > you > > apply will then by run on the full result set (when paging etc.), and if > you > > do that, you might just as well apply it after the query, with in memory > > operations. > > > > I still think that a custom SQL function is feasible. You will have to > > reshape it into a scalar value... > > > > SELECT ...., (SELECT [RANK] FROM FREETEXTTABLE(tableref,columnref,value) > > WHERE [KEY]=tableref.Id) as [RANK] FROM ... > > > > Maybe not easy, but doable. > > > > /G > > > > 2011/8/30 mysterd429 <[email protected]> > > > > > > > > > > > > > > > > > Thanks for the reply, Gunnar. Unfortunately, I don't see how I can > > > use these links. I'm trying to use FREETEXTTABLE, which provides a > > > result set with the search results, not FREETEXT, which is scalar. I > > > need to know not only that a row matches, but what its relative rank > > > is in the result set. From the MSDN documentation, FREETEXTTABLE > > > "[r]eturns a table of zero, one, or more rows for those columns > > > containing character-based data types for values that match.... > > > FREETEXTTABLE can only be referenced in the FROM clause of a SELECT > > > statement like a regular table name." The stored procedure I have > > > essentially wraps FREETEXTTABLE. > > > > > Is there any way to get the result set of a stored procedure into an > > > NhQueryable? > > > > > Thanks! > > > > > On Aug 30, 11:55 am, Gunnar Liljas <[email protected]> wrote: > > > > > http://www.primordialcode.com/blog/post/nhibernate-customize-linq-pro... > > > > > > and > > > > > > > http://darioquintana.com.ar/blogging/2009/03/13/registering-freetext-... > > > > > > 2011/8/30 mysterd429 <[email protected]> > > > > > > > Hi all, > > > > > > > I've got an existing database that uses FREETEXTTABLE in a stored > > > > > procedure. I've been using the NHibernate LINQ provider (since > we're > > > > > switching from LINQ to SQL to NHibernate), and I'd like to know if > > > > > there's a way to use FREETEXTTABLE using the LINQ provider. The > > > > > stored procedure takes a few parameters and selects the desired > result > > > > > set (KEY and RANK columns). I have a class for these search result > > > > > rows. > > > > > > > Currently, I can use the stored procedure using a named SQL query > and > > > > > get back attached entities: > > > > > > > IQuery query = Session.GetNamedQuery("DoSearch"); > > > > > query.SetParameter("searchTerm", searchTerm); > > > > > IList<SearchResult> results = query.List<SearchResult>(); > > > > > // results[0].ResultObject is the first underlying object. > > > > > > > We have a few utility methods that use LINQ for some processing > > > > > (sorting, pagination, etc) that I'd like to reuse with this stored > > > > > procedure. > > > > > > > Ideally, I'd like to do something like: > > > > > > > var doSearch = > Session.GetQueryableObjectFromNamedQuery("DoSearch"); > > > > > doSearch.SetParameter("serachTerm", searchTerm); > > > > > IQueryable<SearchResult> resultsQueryable = doSearch.AsQueryable(); > > > > > IList<SearchResult> results = > > > > > myUtilityInstance.SortAndPaginate(resultsQueryable); > > > > > > > Any ideas? Thanks! > > > > > > > -- > > > > > You received this message because you are subscribed to the Google > > > Groups > > > > > "nhusers" group. > > > > > To post to this group, send email to [email protected]. > > > > > To unsubscribe from this group, send email to > > > > > [email protected]. > > > > > For more options, visit this group at > > > > >http://groups.google.com/group/nhusers?hl=en. > > > > > -- > > > You received this message because you are subscribed to the Google > Groups > > > "nhusers" group. > > > To post to this group, send email to [email protected]. > > > To unsubscribe from this group, send email to > > > [email protected]. > > > For more options, visit this group at > > >http://groups.google.com/group/nhusers?hl=en. > > -- > You received this message because you are subscribed to the Google Groups > "nhusers" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/nhusers?hl=en. > > -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
