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.
