Hi, I don't see any real conceptual difference between the examples and your requirement. It's not about mimicking SQL functions in .NET, it's about making is possible to express db specific functions in the Linq expression tree.
Could you upload the mere fundamentals of your implementation to https://gist.github.com/ or similar, so that I can take a look at it and give it a shot. In private email, if you so wish. /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.
