CODEWITHSUNDEEP
c#.net-4.0entity-framework-4.1
Romias
Romias

Reputation: 14133

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

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.

Upvotes: 5

Views: 4280

Answers (3)

ntziolis
ntziolis

Reputation: 10221

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.

Upvotes: 2

user111013
user111013

Reputation:

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

Upvotes: 5

usr
usr

Reputation: 171178

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

Upvotes: 6

Related Questions