0

I have the following (simplified) Entity SQL query:

SELECT VALUE a
FROM Customers AS a
WHERE a.Status NOT IN { 2, 3 }

The Status property is an enumeration type, call it CustomerStatus. The enumeration is defined in the EDMX file.

As it is, this query doesn't work, throwing an exception to the effect that CustomerStatus is incompatible with Int32 (its underlying type is int). However, I couldn't find a way to define a list of CustomerStatus values for the IN {} clause, no matter what namespace I prefixed to the enumeration name. For example, 

SELECT VALUE a
FROM Customers AS a
WHERE a.Status NOT IN { MyModelEntities.CustomerStatus.Reject, MyModelEntities.CustomerStatus.Accept }

did not work, throwing an exception saying it could not find MyModelEntities.CustomerStatus in the container, or some such.

Eventually I resorted to casting the Status to int, such as 

SELECT VALUE a
FROM Customers AS a
WHERE CAST(a.Status AS System.Int32) NOT IN { 2, 3 }

but I was hoping for a more elegant solution.

Tsahi Asher
  • 1,767
  • 15
  • 28
  • Can't you just cast the enum to (int) before the query is sent? – Daniel Lorenz Jul 25 '16 at 16:11
  • @DanielLorenz what do you mean? As you can see in the last query, eventually that's what I did. – Tsahi Asher Jul 26 '16 at 05:41
  • Are you building the SQL yourself or are you using a DbSet? If you build the SQL yourself, you have to use an int as the database doesn't have a concept of an enum. EF translates that to an int. – Daniel Lorenz Jul 26 '16 at 14:56
  • @DanielLorenz it's statically defined. Entity SQL is executed against the conceptual model, not against the database directly. EF translates it to T-SQL later in the pipeline. So while the database has no concept of enums, EF does. So I thought I could use them in eSQL just as I can use them in LINQ to Entities. – Tsahi Asher Jul 27 '16 at 06:06
  • What version of EF are you using? It should automatically convert this for you in EF 6+. Not sure about 5, but 4 you have to manually convert to int. – Daniel Lorenz Aug 01 '16 at 13:39
  • @DanielLorenz it's EF6. Again, this is **Entity SQL**, not **T-SQL**. It seems like EF is unable to recognize the enum type I'm referring to in the query, let alone convert it to int. – Tsahi Asher Aug 01 '16 at 14:58

1 Answers1

0

Oooh, you are writing Entity SQL directly. I see... Any reason you aren't using DbSet instead of writing Entity SQL by hand? Could always do

var statuses = new [] { Status.A, Status.B };
var query = context.SomeTable.Where(a => !statuses.Contains(a.Status)).ToList();
Daniel Lorenz
  • 4,178
  • 1
  • 32
  • 39
  • The reason is the query is actually generated dynamically with `String.Format`, replacing the selected column from the entity (the actual query actually selects a navigation property from `a`) and modifies the `WHERE` clause. – Tsahi Asher Aug 02 '16 at 08:16
  • Hmm, I don't really see anything here that would warrant using dynamic SQL, really. You can't do ctx.Customers.Where(a => statuses..).Select(a => a.Value).ToList() ? – Daniel Lorenz Aug 03 '16 at 19:02
  • The actual query is more complicated. I may select `a.ValueA` or `a.ValueB`, and in addition to the status list, the `WHERE` clause has additional dynamic conditions. Something like `String.Format("SELECT VALUE a.{0} FROM Customers AS a WHERE CAST(a.Status AS System.Int32) NOT IN {{ 2, 3 }} AND a.{1} IS NOT NULL", selectedProperty, filterProperty)` – Tsahi Asher Aug 04 '16 at 06:20