How to use flags enums in Linq to Entities queries?

Question

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).

Jugurtha

You're query (if it worked) would only return those records whose status is simultaneously Active and Unknown. Is that what you want? – Intermission

No, it was a OR not an AND. So 'Status s = Status.Active | Status.Unknown;' is correct. Thanks! – Jugurtha

@Vasi: That part is correct, but "Status.Active|Status.Unknown" is equivalent to "1|4" which is 5. So your where clause is effectively saying "where (r.Status & 5) == r.Status", which is the same as "where r.Status == 5", which is the same as saying (in English) "where r.Status is both Active and Unknown"! – Intermission

You can convert your entities to enumerables. See : https://mcmap.net/q/1434928/-specified-type-member-not-supported-in-linq-to-entities – Abracadabra

As of EF6.1 HasFlag is supported in LINQ-to-Entities. See: data.uservoice.com/forums/… and entityframework.codeplex.com/workitem/1497 – Plantation

Answer 1

Just use HasFlag()

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

Rosenfeld

It's the other way around, where s.HasFlag(r.Status) – Groveman

@M.MennanKara can you confirm that you tried this and it works? I am facing this issue now – Legato

Answer 2

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

Abey

Answer 3

In DB Flags enum must be integer. After that you can try it like this:

Status s = Status.Active | Status.Unknown;

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

Eckblad

Answer 4

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

Idiom

r.Status is byte already, and I added the cast in te question also. This is not the problem, the problem is that it will not run. It compiles but doesn't run. See the error in the question. – Jugurtha

Answer 5

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

Intermission

I think the evaluation in Where is not translatable to esql or somethong like that. That's why I get the exception: "Unable to create a constant value of type 'Closure type'. Only primitive types ('such as Int32, String, and Guid') are supported in this context." – Jugurtha

Answer 6

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

Arundell

Using LINQ to Entities over a MS SQL Server. – Jugurtha

No, it is a problem that it cannot translate the result of the enum to a native SQL type. Thus using an integer comparison might work. – Arundell

I think it cannot translate the bit comparison into eSql. I used the cast to byte in my code before posting this question, but I missed it when I wrote the short example here. So it is not a cast problem, I think it is a bit comparison problem, not being supported bu EF. – Jugurtha

what is the datatype of the Status column? – Arundell

Still, please try a cast to int. This is really all about finding a workaround for EF LINQ->SQL translator deficiency... you have to try the corner cases. – Virchow

Answer 7

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;
        }

Preamble