How about..
Session.Query<ResultObject>().Select(ro => new
{Id=ro.Id,Rank=ro.DoSearch("foo", "bar")}).OrderBy(p =>p.Rank)
?
/G
2011/8/31 mysterd429 <[email protected]>
> When executing Session.Query<ResultObject>().OrderBy(ro =>
> ro.DoSearch("foo", "bar").Select(ro => ro.DoSearch("foo", "bar").
>
> On Aug 31, 10:34 am, mysterd429 <[email protected]> wrote:
> > Gunnar,
> >
> > Both of these examples mimic the SQL in .NET, which does not make
> > sense for this application. There is no way to mimic the results of
> > FREETEXT (which my UDF wraps), so I'm a little confused: what is the
> > purpose of mimicking the SQL in .NET? My understanding based on the
> > articles that you provided was that the HQL generator would always be
> > used and that the method was just a placeholder. There is no
> > mechanism to mimic my UDF.
> >
> > I implemented the BuildHql method (remarkably simple), and it's used
> > for OrderBy, but not for Select. If there is no way to force
> > NHibernate to translate to HQL by both? Here's the SQL that I get
> > (formatted and with the names of our actual domain objects removed):
> >
> > select
> > resultobject0_.RESULT_OBJECT_ID
> > , /* other properties */
> > from
> > RESULT_OBJECT resultobject0_
> > order by
> > (
> > SELECT
> > RANK
> > FROM
> > DO_SEARCH(@p0, @p1)
> > WHERE
> > RESULT_OBJECT_ID = resultobject0_.RESULT_OBJECT_ID
> > ) asc
> >
> > I was expecting something more like this:
> >
> > select
> > DO_SEARCH(@p0, @p1)
> > from
> > RESULT_OBJECT resultobject0_
> > order by
> > (
> > SELECT
> > RANK
> > FROM
> > DO_SEARCH(@p0, @p1)
> > WHERE
> > RESULT_OBJECT_ID = resultobject0_.RESULT_OBJECT_ID
> > ) asc
> >
> > Thanks for your help. I appreciate it.
> >
> > On Aug 31, 10:04 am, Gunnar Liljas <[email protected]> wrote:
> >
> >
> >
> >
> >
> >
> >
> > > That is what this article is all about.
> >
> > >http://www.primordialcode.com/blog/post/nhibernate-customize-linq-pro.
> ..
> >
> > > or this
> >
> > >http://fabiomaulo.blogspot.com/2010/07/nhibernate-linq-provider-exten.
> ..
> >
> > > /G
> >
> > > 2011/8/31 mysterd429 <[email protected]>
> >
> > > > Gunnar,
> >
> > > > Thanks for your help so far. I originally had a UDF, so I've
> > > > converted back to the UDF. I've got an extension method for
> > > > ResultObject called GetSearchRank, which takes the same parameters as
> > > > the UDF in addition the "this" parameter and returns -1 (temporarily
> > > > for testing). I've created a dialect that is a subclass of
> > > > MsSql2008Dialect, an HQL generator that is a subclass of
> > > > BaseHqlGeneratorForMethod, and an HQL generators registry that is a
> > > > subclass of another HQL generators registry common to all of our
> > > > projects (all it does it add the "trim" function for strings.)
> >
> > > > When I try to run the code, the generator registry is getting built,
> > > > and execution goes to the BuildHql gets called (I've not yet
> > > > implemented it) when I do Session.Query<ResultObject>().OrderBy(ro =>
> > > > ro.GetSearchRank("foo", "bar")), but when I do
> > > > Session.Query<ResultObject>().Select(ro => ro.GetSearchRank("foo",
> > > > "bar")), I just get a queryable full of -1.
> >
> > > > How can I get NHibernate to translate the method call to SQL instead
> > > > of executing it on the .NET side?
> >
> > > > Thanks!
> >
> > > > Don
> >
> > > > On Aug 30, 7:54 pm, Gunnar Liljas <[email protected]> wrote:
> > > > > 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
> >
> > ...
> >
> > read more ยป
>
> --
> 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.
