How can I specify a bitmask/bitfield WHERE clause using NHibernate's Criterion API

Question

Possible Duplicate:
How to query flags stored as enum in NHibernate

I have three tables - Recipient, Message, MessageType

MessageType looks like this:

| ID | Description  |
|====|==============|
|  1 | JobAlert     |
|  2 | Newsletter   |
|  3 | SpecialOffer |
|  4 | Survey       |

Recipient contains an integer column which is used as a bitfield; recipients can choose what types of message they want to receive; If a recipient wants to receive newsletters and special offers, we'll set their bitfield to (2 ^ 2) | (2 ^ 3)

Message contains a reference to MessageTypeId, and computed column MessageTypeBitFlag which is defined as POWER(2, MessageTypeId)

My query expressed in SQL looks something like:

SELECT * FROM Message, Recipient
  WHERE Recipient.MessageTypeBitField & Message.MessageTypeBitFlag > 0

by doing a bitwise-AND on the bitfield and bitflag columns, it's easy to select only the messages that a particular recipient is interested in.

Problem is, I'm not doing this in SQL - I need to add this as an additional option to a fairly rich system built on the NHibernate Criteria API.

Is there any way to express this criteria via the NHibernate API - either using the API or by adding an SQL/HQL clause to the existing criteria?

Answer 1

OK, here's a specific implementation based on this linked post submitted by Firo, because I had to adapt this a little to make it work:

/// <summary>An NHibernate criterion that does bitwise comparison to match a bit flag against a bitmask.</summary>
public class BitMask : LogicalExpression {
    private BitMask(string propertyName, object value, string op) :
        base(new SimpleExpression(propertyName, value, op), Expression.Sql("?", 0, NHibernateUtil.Int64)) {
    }

    protected override string Op {
        get { return ">"; }
    }
    /// <summary>Create a bitwise filter criterion - i.e. one that will be satisified if <code><paramref name="propertyName"/> &amp; <paramref name="bits"/> &gt; 0</code></summary>
    public static BitMask Matches(string propertyName, long bits) {
        return new BitMask(propertyName, bits, " & ");
    }
}

and then use it via the Criteria API as follows:

public IEnumerable<Message> GetMessagesForRecipient(Recipient r) {
    var messages = session.CreateCriteria<Message>()
        .Add(BitMask.Matches("MessageTypeBitFlag ", r.MessageTypeBitField))
        .List<Message>();
    return(messages);
}