How to query for objects with certain enum flag on, using a db int to store it

Question

I have an object with a Flag enum with several possible "uses". The flag enum uses the proper power of 2.

Checking if a variable has a certain flag on, I can do it using the .NET 4 HasFlag()

BUT:

If I store that flag combination as a int in database... how can I retrive the objects that have certain flag on using Entity Framework?

For example, if my object is a "Contact" type, I would like to query those of them that are actually "Customers and Friends", being Customers and Friends flags in the ContactType Enum.

@Tigran, just the int value of the enum combination of flags. Customer = 1 and Friend = 2... so the combination is 3. — Romias
– Romias, Commented Mar 17, 2012 at 13:37

score 6 · Accepted Answer · 2012-03-17 13:44:32Z

6

I doubt any ORM is going to have a way to adapt the HasFlags down to the appropriate SQL code for your DBMS.

What you are likely going to need to do is either write a stored procedure, or hand-crank the SQL Statement to be executed for this.

You don't mention what DBMS you're using - but if I assume you're using SQL Server, you are in luck as it has the & (Bitwise AND) operator.

Practical example as T-SQL:

-- Setup Test Data 
DECLARE @Contacts TABLE (id int, contactType int, name nvarchar(MAX)) 

INSERT INTO @Contacts VALUES (1, 0, 'Fred'); -- Not Wanted
INSERT INTO @Contacts VALUES (2, 3, 'Jim');  -- Wanted
INSERT INTO @Contacts VALUES (3, 36, 'Mary');  -- Not wanted
INSERT INTO @Contacts VALUEs (4, 78, 'Jo');  -- Wanted

-- Execute Query
SELECT *
FROM @Contacts
WHERE ContactType & 2 = 2

edited Mar 17, 2012 at 13:44

answered Mar 17, 2012 at 13:38

user111013

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

Comments

usr · Accepted Answer · 2012-03-17 13:39:14Z

6

db.Contacts.Where(c => (c.Flag & MyEnum.Flag3) != 0).ToList();

answered Mar 17, 2012 at 13:39

usr

172k38 gold badges251 silver badges380 bronze badges

Comments

ntziolis · Accepted Answer · 2012-03-17 13:44:35Z

2

You can get the combined bit value as int and store that value in the db as a column here is a sample:

public enum MessagingProperties
{
    // No options selected (value is 0)
    None = 0x00,
    // messages are not discarded if subscriber is slow (value is 1)
    Durable = 0x01,
    // messages are saved to disk in case messaging crashes (value is 2)
    Persistent = 0x02,
    // messages are buffered at send/receive point so not blocking (value is 4)
    Buffered = 0x04
}

In order to combine these flag enums you do:

// combine the bit flags
var combinedFlags = MessagingProperties.Durable | MessagingProperties.Persistent | 
                     MessagingProperties.Buffered;

// this will be equal 7, no other combination can sum up to seven so it is unique, that's how bit flags work
int combinedFlagsInt = (int)combinedFlags;

You can now go ahead and store this value in the db. If you want to query for multiple bit flags you:

combine them
cast them into an int
and use the resulting variable/value as a filter in the Where clause.

answered Mar 17, 2012 at 13:44

ntziolis

10.2k1 gold badge36 silver badges50 bronze badges

3 Comments

Romias Over a year ago

assume I stored in database the value "7" from your example. What if I want to filter all the messages with the flag "Durable". I don't think it can be done with a simple where clause, since there could be several int values that "hold" that flag.

ntziolis Over a year ago

@Romias correct, if this is what you are after you will have to store them in different fields

Romias Over a year ago

Thanks ntziolis... I will stay with several separate fields or I'll go with @Will Huges approach.

Collectives™ on Stack Overflow

How to query for objects with certain enum flag on, using a db int to store it

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related