Clever Geek Handbook

How to query objects with a specific enum flag using db int to store it

I have a Flag enumerated object with several possible "uses". The flag listing uses the correct cardinality of 2.

By checking if a variable has a specific flag, I can do it using .NET 4 HasFlag()

BUT:

If I store this combination of flags as int in the database ... how can I get objects that have a specific flag when using Entity Framework?

For example, if my object is of type " Contact

", I would like to query for the ones that are actually Clients and Friends, being the Clients and Friends flags in the ContactType

Enum.

+3


source to share


3 answers


I doubt the ORM will be able to adapt HasFlags to the appropriate SQL for your DBMS.

What you will most likely need to do is either write a stored procedure or manually run a SQL statement to do so.

You don't specify which DBMS you are using, but if I assume you are using SQL Server, you are in luck since it has &; (Bitwise AND) .



Practical example like 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
+2


source 


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


+4


source


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

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
}

To combine these enum flags, 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 how bit flags work
int combinedFlagsInt = (int)combinedFlags;

Now you can go ahead and save this value to db. If you want to request multiple bit flags, you:

  • combine them
  • convert them to int
  • and use the resulting variable / value as a filter in the sentence Where

    .
+2


source






More articles:


All Articles