Hi, Kudos for providing a self contained test project. Much appreciated!
I tried your code, and indeed, no function calls were included in the projection! After investigating a bit further I found that any part of the expression tree which contains Constant expressions (in your case, the arguments to the extension method) will be executed locally, instead of in HQL/SQL. I've submitted a pull request with code that fixes this. Hopefully it (or some other solution) will be accepted. https://github.com/nhibernate/nhibernate-core/pull/6 /Gunnar 2011/9/1 mysterd429 <[email protected]> > Gunnar, > > I sent you a link to a ZIP file with a sample that demonstrates the > issue. If we find a resolution, I'll post the problem and the > correction to the list. > > Thank you again! > > Don > > On Aug 31, 11:31 am, Gunnar Liljas <[email protected]> wrote: > > 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 > > > > ... > > > > 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.
