How to use flags enums in Linq to Entities queries?

Question

Hi,

I have a [Flags] enum like this:

[Flags]
public enum Status
{
  None = 0,
  Active = 1,
  Inactive = 2,
  Unknown = 4
}

A Status enum may contain two values such as:

Status s = Status.Active | Status.Unknown;

Now I need to create a linq query (LINQ to ADO.NET Entities) and ask for records whose status is s above, that is Active or Unknown;

var result = from r in db.Records
             select r
             where (r.Status & (byte)s) == r.Status

Of course I get an error, because LINQ to Entities only knows to handle primitive types in the Where clause.

The error is:

Unable to create a constant value of type 'Closure type'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

Is there a workable way? I may have a status Enum with 10 possible values and to query for 5 of the statuses. How do I construct the query using Flags enum in an elegant way?

Thanks.

Update

This seems to be a Linq to Entities problem. I think in LINQ to SQL it works (not sure, didn't tested).

Answer 1

the solution is to cast Status to byte in a variable and use that in the query:

    byte bs = (byte)s;

    var result = from r in db.Records
                 where (r.Status & bs) == r.Status
                 select r;

<< Problem with casting enums in LINQ to Entities >>

Answer 2

I don't know EF, but could inserting additional casts work?

var result = from r in db.Records
             where ((byte)r.Status & (byte)s) == (byte)r.Status
             select r

Answer 3

Try it like this:

byte status = (byte)(Status.Active | Status.Unknown);

var result = from r in db.Records
             select r
             where (r.Status & status) != 0

Answer 4

var result = from r in db.Records
             where r.Status == s
             select r

Answer 5

I am unsure if a bitwise AND-operation will work, but try casting s to an int:

        int i = (int)s;
        var result = from r in db.Records
             select r
             where (r.Status & i) == r.Status

Which database engine are you using? Possibly the engine does not support bitwise operations.

Reference: http://www.matthidinger.com/archive/2008/02/26/entity-framework-comparison-frustration-explained.aspx

Answer 6

The folloiwng works for me in C#

    public const StatusTypes ActiveAlert = StatusTypes.Accepted | StatusTypes.Delivered;

        int flags = (int)ActiveAlert;

        try
        {
            var query = from p in model.AlertsHistory
                        where (p.UserId == clientId
                        && (p.Status.HasValue && (p.Status.Value & flags) != 0))
                        select p;
            var aList = query.ToList();

            return (aList);


        }
        catch (Exception exc)
        {
            log.Error("Exception getting Alerts History for user.", exc);
            throw;
        }