Why am I seeing a difference between.Cast<int>() and .Select(a => (int)a)?

Question

I'm trying to work out the difference between the following:

someListOfEnums.Cast<int>()

and

someListOfEnums.Select(a => (int)a)?

I have found that the former causes an exception when used in a Where clause in Entity Framework Core 3.1 but the latter does not. I would have expected them to act similarly.

Take the following example: public enum Fruit { Apple, Banana, Orange }

public class FruitTable
{
    public int Id { get; set; }
    public Fruit Value { get; set; }
}

public class FruitContext : DbContext
{
    public DbSet<FruitTable> Fruit { get; set; }
}

public void TestMethod(FruitContext context)
{
    var list = new List<Fruit>{Fruit.Apple, Fruit.Orange};

    var breaks = list.Cast<int>();
    var works = list.Select(a => (int)a);

    var fruits1 = context.Fruit.Where(a => works.Contains(a.Value)).ToList();  //This works
    var fruits2 = context.Fruit.Where(a => breaks.Contains(a.Value)).ToList();  //This breaks
}

It seems like using .Cast<int>() results in a where clause containing the enum's name (Apple, Orange, etc.) whereas using .Select(a => (int)a) does not.

---

UPDATE

I've realised that my example above doesn't cause the same problem (apologies). I've been through and created a program which definitely reproduces the issue.

Using the following Database:

CREATE DATABASE Fruit

USE Fruit

CREATE TABLE Fruit
(
Id INT NOT NULL PRIMARY KEY,
Value INT NOT NULL,
)

INSERT INTO Fruit VALUES (1, 0)
INSERT INTO Fruit VALUES (3, 2)

The following program:

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
namespace ConsoleApp
{
    public class Program
    {
        static void Main(string[] args)
        {
            FruitTable.TestMethod(new FruitContext());
        }

        public enum Fruit
        {
            Apple,
            Banana,
            Orange
        }

        public class FruitTable
        {
            public int Id { get; set; }
            public int Value { get; set; }

            public static void TestMethod(FruitContext context)
            {
                IEnumerable<Fruit> list = new Fruit[] {Fruit.Apple, Fruit.Orange};

                var breaks = list.Cast<int>();
                var works = list.Select(a => (int) a);

                var fruits1 = context.Fruit.Where(a => works.Contains(a.Value)).ToList(); //This works
                var fruits2 = context.Fruit.Where(a => breaks.Contains(a.Value)).ToList(); //This breaks
            }
        }

        public class FruitContext : DbContext
        {
            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                optionsBuilder.UseSqlServer("Server=.;Database=fruit;Trusted_Connection=True;ConnectRetryCount=0");
            }

            public DbSet<FruitTable> Fruit { get; set; }
        }
    }
}

Causes the following error:

'Invalid column name 'Orange'. Invalid column name 'Apple'.'

---

Edit

Just to add the problem was not present in .Net Core 2.2, it appeared when we migrated to 3.1. Thinking about it - it may be due to this: https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/breaking-changes#linq-queries-are-no-longer-evaluated-on-the-client

Answer 1

Actually, from .net perspective Cast<int> and Select(a => (int)a is different. Cast will box the values to the objects and then will unbox it back to int.

static IEnumerable<TResult> CastIterator<TResult>(IEnumerable source) {
            foreach (object obj in source) yield return (TResult)obj;
        }

And as a rule, object can be unboxed only to the type which it is boxed from. Otherwise exception will be thrown.

But, as underlying value of your Enum is Int as well, Cast<int> will work as expected.

Update:

As commented, for solving the issue you can append ToList() to the end of query. Now that query will be evaluated in .net side in a proper way. Otherwise, EF Core 3.0 will try to generate Sql and in case of failure it will throw exception.

 var breaks = list.Cast<int>().ToList();

---

Regarding to your edit:

Just to add the problem was not present in .Net Core 2.2, it appeared when we migrated to 3.1. Thinking about it - it may be due to this:

It is really explained well in that link, why it was working in .net core 2.2. It seems that, in previous versions when EF Core couldn't convert an expression that was part of a query to either SQL or a parameter, it automatically evaluated the expression on the client.

And it is really bad. Because, as noted:

For example, a condition in a Where() call which can't be translated can cause all rows from the table to be transferred from the database server, and the filter to be applied on the client.

So, it seems previously you were just loading all data to the client and then applying filter on the client side.