showing incorrect syntax near the keyword With

You're absolutely right. There's a known breaking change in Entity Framework Core (EF Core) 8.0 that affects LINQ queries using Contains with older SQL Server versions. Here's the breakdown:

The Issue:

• In EF Core 8.0, the way Contains is translated to SQL for older SQL Servers (pre-2016) has changed.
• This change might cause an error like "Incorrect syntax near 'with'" because the generated SQL might not be compatible with those versions.

Affected Scenarios:

• You're using EF Core 8.0 with a SQL Server version older than 2016.
• Your LINQ query uses Contains to filter entities.

The Code Snippet:

var query = from entity in dbContext.YourEntity
            where entity.SomeProperty.Contains(someValue)
            select entity;

Solutions:

• Upgrade SQL Server: The recommended approach is to upgrade your SQL Server to a version that supports the new translation method (2016 or later). This ensures optimal functionality with EF Core 8.0.
• Use Compatibility Level: If upgrading isn't an option, you can temporarily set the SQL Server compatibility level to a version that supports the older translation:

ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 130; -- Set to 130 for SQL Server 2016

Alternative Approaches (if compatibility changes are not preferred):

• EF Core UseCompatibilityLevel: You can configure EF Core to use the older translation method, but this might have performance implications:

dbContext.Set().UseCompatibilityLevel(130); // Set to 130 for SQL Server 2016

• Manual Translation: Rewrite the Contains logic using a different SQL expression supported by your older SQL Server version (e.g., LIKE).

Choosing the Right Solution:

The best approach depends on your specific circumstances. Upgrading SQL Server is generally recommended for long-term performance and functionality benefits. If upgrading isn't feasible, consider setting the compatibility level cautiously, with an understanding of potential trade-offs.

Remember to test your application thoroughly after making any changes to ensure everything works as expected.