EFCore 8.0.13 Linq Query Using Enum.GetValues().Cast<int>() Fails

Introduction

Entity Framework Core (EFCore) is a popular Object-Relational Mapping (ORM) framework for .NET developers. It provides a powerful and flexible way to interact with databases using .NET objects. However, with the release of EFCore 8.0.13, some developers have reported issues with Linq queries that use Enum.GetValues().Cast<int>(). In this article, we will explore the issue and provide a solution.

The Issue

The issue arises when using the following Linq query in EFCore 8.0.13:

JobDetails = await dbCtx.JobDetails
   .AsNoTracking()
   .Where(j => Enum.GetValues<JobType.IceRcti>().Cast<int>().Contains(j.JobTypeId))
   .FirstOrDefaultAsync();

This query works in .NET 6.0.36, but fails after upgrading to .NET 8.0.13 with EFCore 8.0.13. To get it to work again, an additional .ToList() is required in the pipeline:

JobDetails = await dbCtx.JobDetails
   .AsNoTracking()
   .Where(j => Enum.GetValues<JobType.IceRcti>().Cast<int>().ToList().Contains(j.JobTypeId))
   .FirstOrDefaultAsync();

The SQL produced with the .ToList() results in a nice "JobTypeId in (1, 2, 3, 4)" style predicate.

Workarounds

There are two workarounds to get the original method to work without the .ToList():

1. Extract and assign a variable: Extract and assign a variable IEnumerable<int> jobTypes outside of the Linq expression first, and then use the variable within the expression:

IEnumerable<int> jobTypes = Enum.GetValues<JobType.IceAP>().Cast<int>();
JobDetails = await dbCtx.JobDetails
   .AsNoTracking()
   .Where(j => jobTypes.Contains(j.JobTypeId))
   .FirstOrDefaultAsync();

However, the SQL produced using the IEnumerable<int> is quite ugly:

[j].[JobTypeId] IN (
    SELECT [j0].[value]
    FROM OPENJSON(@__jobTypes_0) WITH ([value] int '{{content}}#39;) AS [j0]
)

2. Use a different approach: Use a different approach to achieve the same result, such as using a HashSet<int> to store the enum values:

var jobTypes = new HashSet<int>(Enum.GetValues<JobType.IceAP&gt>();
JobDetails = await dbCtx.JobDetails
   .AsNoTracking()
   .Where(j => jobTypes.Contains(j.JobTypeId))
   .FirstOrDefaultAsync();

Conclusion

In conclusion, the issue with EFCore 8.0.13 Linq query using Enum.GetValues().Cast<int>() is a known problem that has been reported by several developers. While there are workarounds to get the original method to work, they may not produce the desired SQL predicate. We recommend using a different approach, such as using a HashSet<int>, to achieve the same result.

Additional Information

This issue has been moved from a ticket on Developer Community.

Related Issues

• EFCore 8.0.13 Linq query using Enum.GetValues().Cast() fails
• EFCore 8.0.13 Linq query using Enum.GetValues().Cast() fails with .NET 8.0.13

References

• Entity Framework Core
• LINQ (Language Integrated Query)
  EFCore 8.0.13 Linq Query Using Enum.GetValues().Cast() Fails: Q&A ====================================================================

Q: What is the issue with EFCore 8.0.13 Linq query using Enum.GetValues().Cast()?

A: The issue arises when using the following Linq query in EFCore 8.0.13:

JobDetails = await dbCtx.JobDetails
   .AsNoTracking()
   .Where(j => Enum.GetValues<JobType.IceRcti>().Cast<int>().Contains(j.JobTypeId))
   .FirstOrDefaultAsync();

This query works in .NET 6.0.36, but fails after upgrading to .NET 8.0.13 with EFCore 8.0.13.

Q: What is the solution to the issue?

A: To get the original method to work again, an additional .ToList() is required in the pipeline:

JobDetails = await dbCtx.JobDetails
   .AsNoTracking()
   .Where(j => Enum.GetValues<JobType.IceRcti>().Cast<int>().ToList().Contains(j.JobTypeId))
   .FirstOrDefaultAsync();

The SQL produced with the .ToList() results in a nice "JobTypeId in (1, 2, 3, 4)" style predicate.

Q: Are there any workarounds to get the original method to work without the .ToList()?

A: Yes, there are two workarounds:

1. Extract and assign a variable: Extract and assign a variable IEnumerable<int> jobTypes outside of the Linq expression first, and then use the variable within the expression:

IEnumerable<int> jobTypes = Enum.GetValues<JobType.IceAP>().Cast<int>();
JobDetails = await dbCtx.JobDetails
   .AsNoTracking()
   .Where(j => jobTypes.Contains(j.JobTypeId))
   .FirstOrDefaultAsync();

However, the SQL produced using the IEnumerable<int> is quite ugly:

[j].[JobTypeId] IN (
    SELECT [j0].[value]
    FROM OPENJSON(@__jobTypes_0) WITH ([value] int '{{content}}#39;) AS [j0]
)

2. Use a different approach: Use a different approach to achieve the same result, such as using a HashSet<int> to store the enum values:

var jobTypes = new HashSet<int>(Enum.GetValues<JobType.IceAP&gt>();
JobDetails = await dbCtx.JobDetails
   .AsNoTracking()
   .Where(j => jobTypes.Contains(j.JobTypeId))
   .FirstOrDefaultAsync();

Q: Why is the issue occurring in EFCore 8.0.13?

A: The issue is occurring due to a change in the way EFCore 8.0.13 handles Linq queries with Enum.GetValues().Cast<int>(). In previous versions of EFCore, this query was translated to a SQL query with a IN clause. However, in EFCore 8.0.13, this query is translated to a SQL query with an OPENJSON clause, which is not supported by all databases.

Q: How can I avoid this issue in the future?

A: To avoid this issue in the future, you can use one of the workarounds mentioned above, such as extracting and assigning a variable or using a different approach to achieve the same result.

Q: Is this issue specific to EFCore 8.0.13?

A: No, this issue is not specific to EFCore 8.0.13. It can occur in any version of EFCore that uses the Enum.GetValues().Cast<int>() approach.

Q: Can I report this issue to the EFCore team?

A: Yes, you can report this issue to the EFCore team by creating a new issue on the EFCore GitHub repository.