I have a rather large linq-to-nhibernate query. I now need to add a filter based on a user-defined function written in t-sql that i have to pass parameters into. In my case, I need to pass in a zipcode that the user types in and pass it to the t-sql function to filter by distance from this zip. Is this possible, or do i need to rewrite my query using the ICriteria api?
1
-
I have the same problem. Did you come up with a solution or rewrite the query using ICriteria api?Craig Bart– Craig Bart2010-02-15 16:58:06 +00:00Commented Feb 15, 2010 at 16:58
Add a comment
|
1 Answer
i did find a solution:
Note the NHibernate query (nquery) which has RegisterCustomAction:
private void CallZipSqlFunction(ListingQuerySpec spec, IQueryable<Listing> query)
{
var nQuery = query as NHibernate.Linq.Query<Listing>;
//todo: find some way to paramaterize this or use linq-to-nh and not criteria to call the function
// so i don thave to check for escape chars in zipcode
if (spec.ZipCode.Contains("'"))
throw new SecurityException("invalid character");
//yuck!
var functionString = "dbo.GetDistanceForListing('" + spec.ZipCode + "',{alias}.ID) as Distance";
//create a projection representing the function call
var distance = Projections.SqlProjection(functionString, new[] { "Distance" }, new IType[] { NHibernateUtil.String });
//create a filter based on the projection
var filter = Expression.Le(distance, spec.ZipCodeRadius.Value);
//add the distance projection as order by
nQuery.QueryOptions.RegisterCustomAction(x => x.AddOrder(Order.Asc(distance)));
//add teh distance filter
nQuery.QueryOptions.RegisterCustomAction(x => x.Add(filter));
}