Fluent NHibernate Custom SQL Query

Question

I'm new to NHibernate and FNH. I'm trying to query for multiple possible objects in a single query and I am not sure what the most efficient query is. I have a dictionary of words:

public class Word
{
   public virtual int Id { get; set; }
   public virtual string Text { get; set; }
}

And I want to query for all Word objects that are contained in a list. The SQL I have is:

SELECT (*) FROM dbo.Word WHERE Text LIKE 'word1%' OR Text LIKE 'word2%' ...

Right now I'm just getting the list of words and generating the WHERE clause of the SQL query. I've created an ISQLQuery but I'm not sure how to execute it and get back a collection of Word objects.

Did you forget the "%" in your sample query? It makes a difference... — Mike Cole
– Mike Cole, Commented Mar 2, 2011 at 22:54

Vadim · Accepted Answer · 2011-03-02 23:24:56Z

3

Since you're using NHibernate why not use the facilities provided for you rather than writing custom SQL that is likely prone to SQL injection.

public IList<Word> GetWords(IList<string> filters)
{
    var criteria = Session.CreateCriteria<Word>();
    var disjunction = Restrictions.Disjunction();
    foreach (var filter in filters)
    {
        disjunction.Add(Restrictions.Like("Text", filter, MatchMode.Start));
    }
    criteria.Add(disjunction);

    return criteria.List<Word>();
}

edited Mar 2, 2011 at 23:24

answered Mar 2, 2011 at 22:56

Vadim

18k5 gold badges41 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Wesley Tansey Over a year ago

@Yads When I run using that approach, I get an exception: Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.

Wesley Tansey Over a year ago

Well the user is inputting effectively limitless text and I want to pull all valid words out. For this specific test case, I think I'm only doing around 200.

Vadim Over a year ago

@Wesley, try the updated code, however, you shouldn't allow unlimited query parameters.

Wesley Tansey Over a year ago

@Yads Excellent, it works! Thank you! What would you recommend I do instead of unlimited query parameters? Split them up into chunks of N (not sure what a good N is)?

Vadim Over a year ago

@Wesley, not sure what your app does, but if possible just prevent the user from entering more than x words. You'll need to play around to figure out a good value for x.

|

Collectives™ on Stack Overflow

Fluent NHibernate Custom SQL Query

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related