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