Fixed: "Expression 'COLLATE utf8mb4_bin' in the SQL tree does not have a type mapping assigned" in EF Core with MySQL

I added StringComparison.OrdinalIgnoreCase to a Contains in an EF Core query, to make sure the search ignored case. With Oracle's MySQL provider, it failed with an unusual message: Expression '[SqlExpressions.SqlFunctionExpression] COLLATE utf8mb4_bin' in the SQL tree does not have a type mapping assigned. The search was already ignoring case, because the column's collation does. Removing the argument fixed it.

What I Was Doing

In a .NET 10 Web API with EF Core 10, Oracle's MySql.EntityFrameworkCore 10.0.12 and MySQL 9.7, I wrote a product search that says outright it should ignore case:

src/Dewiride.Inventory.Api/Program.cs (temporary endpoint)

app.MapGet("/demo/contains-comparison", async (string q, InventoryDbContext db) =>
    Results.Ok(await db.Products
        .Where(p => p.Name.Contains(q, StringComparison.OrdinalIgnoreCase))
        .Select(p => p.Name)
        .ToListAsync()));

It built without a warning. I searched for usb:

Request

GET http://localhost:5190/demo/contains-comparison?q=usb

What I Expected

The product names with USB-C in them.

What I Got

Response

HTTP/1.1 500 Internal Server Error
Content-Type: application/problem+json

{
  "type": "https://tools.ietf.org/html/rfc9110#section-15.6.1",
  "title": "An error occurred while processing your request.",
  "status": 500,
  "traceId": "00-879daef335d6bf91dec3ce61941db8ae-4fa2a0a38110d970-00"
}

API log (the lines that matter)

fail: Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware[1]
      An unhandled exception has occurred while executing the request.
      System.InvalidOperationException: Expression '[SqlExpressions.SqlFunctionExpression] COLLATE utf8mb4_bin' in the SQL tree does not have a type mapping assigned.

There's no Executed DbCommand line: the query failed while EF Core was building the SQL, so MySQL never saw it.

What I Checked

What EF Core says about this argument. EF Core's collations page covers it for string.Equals: "By design, EF Core refrains from translating these overloads to SQL, and attempting to use them will result in an exception." It gives two reasons: "EF Core does not know which case-sensitive or case-insensitive collation should be used", and "applying a collation would in most cases prevent index usage". So an exception is expected. This particular wording comes from Oracle's provider, which gets partway through translating the argument, then fails.

Whether the argument is needed. I ran the same search with plain Contains:

API log (plain Contains)

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[@q='?' (Size = 200)], CommandType='Text', CommandTimeout='30']
      SELECT `p`.`Name`
      FROM `products` AS `p`
      WHERE (@q LIKE '') OR (LOCATE(@q, `p`.`Name`) > 0)

It uses LOCATE, with no collation, so it follows the column's. The column's collation is utf8mb4_0900_ai_ci, where ci means case-insensitive. That's why usb matches USB-C here.

Why It Happens

EF Core turns your LINQ into a tree of SQL pieces, and each piece needs a type mapping before it can be written out. The message names a COLLATE utf8mb4_bin piece, so Oracle's provider appears to translate the StringComparison overload by adding that piece. It has no mapping, so building the SQL fails. The compiler can't catch it, because in C# the call is perfectly valid.

The Fix

Remove the argument, and let the database's collation decide:

src/Dewiride.Inventory.Api/Features/Products/ProductEndpoints.cs (excerpt)

products = products.Where(p => p.Name.Contains(search) || p.Sku.Contains(search));

Ask for a Case-Sensitive Search in SQL's Terms

If you really do need a case-sensitive search, EF Core's EF.Functions.Collate asks for it directly, and it works on Oracle's provider:

An endpoint with a case-sensitive search

d.MapGet("/collate-contains", async (string q, InventoryDbContext db) =>
    Results.Ok(await db.Products.Where(p => EF.Functions.Collate(p.Name, "utf8mb4_bin").Contains(q)).Select(p => p.Name).ToListAsync()));

?q=usb finds nothing:

Response

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8

[]

?q=USB finds the names, and the log shows the COLLATE in the SQL:

Response

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8

[
  "USB-C cable, 2 m",
  "USB-C cable, 1 m",
  "USB-C charger, 20 W",
  "USB-C charger, 65 W",
  "USB-C to USB-A adapter"
]

API log

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (4ms) [Parameters=[@q='?' (Size = 200)], CommandType='Text', CommandTimeout='30']
      SELECT `p`.`Name`
      FROM `products` AS `p`
      WHERE (@q LIKE '') OR (LOCATE(@q, `p`.`Name` COLLATE utf8mb4_bin) > 0)

EF Core's collations page warns about the cost: "Overriding case-sensitivity in a query via EF.Functions.Collate (or by calling string.ToLower) can have a very significant impact on your application's performance." How to Make a MySQL Text Comparison Case-Sensitive shows the collations MySQL offers.

After the Fix

Response

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8

[
  "USB-C cable, 2 m",
  "USB-C cable, 1 m",
  "USB-C charger, 20 W",
  "USB-C charger, 65 W",
  "USB-C to USB-A adapter"
]

Common Mistakes

Mistake	What happens	Do this instead
Passing a StringComparison in a query	An exception when the query runs	Leave it out, and rely on the collation
AsEnumerable() before the Where so C# can compare	Every row comes back from MySQL first	Keep the filter in SQL
p.Name.ToLower().Contains(q.ToLower())	It works, but adds work the collation already does	Use plain Contains
Assuming Contains is case-sensitive, as in C#	The results surprise you	Check the column's collation

Troubleshooting

Symptom	Likely cause	Fix
does not have a type mapping assigned, with COLLATE in the message	A StringComparison argument in a query	Remove it
The same message without COLLATE	Another expression the provider can't map	Simplify the expression, or run it in C# after the query
The LINQ expression … could not be translated	The usual message for a method with no SQL version	Rewrite it, or move it after ToListAsync
The search is case-sensitive without any argument	The column has a case-sensitive collation	Check with SHOW FULL COLUMNS

FAQ

Does the argument work outside a query?

Yes. On a string in memory, it's normal C#. The problem is only inside a LINQ query that EF Core has to turn into SQL.

Why doesn't the compiler catch it?

To the compiler, it's a method call on a string. Whether a provider can translate it is only known when the query runs.

Conclusion

"Expression 'COLLATE utf8mb4_bin' in the SQL tree does not have a type mapping assigned" comes from passing a StringComparison to Contains with Oracle's MySQL provider. EF Core doesn't translate those overloads by design. Remove the argument: MySQL's utf8mb4_0900_ai_ci collation already ignores case, and plain Contains follows it.

Additional Resources

• Collations and Case Sensitivity — why StringComparison isn't translated.

Related

• Fixed: EF Core StartsWith Finds Nothing While Contains Works (Case-Sensitive with Oracle's MySql.EntityFrameworkCore)
• How to Search Text with EF Core on MySQL: Contains, StartsWith and LIKE
• How to Check the MySQL Character Set and Collation