How to Fix EF Core BinaryData LIKE Queries in SQL Server

How did we discover the EF Core BinaryData issue in our enterprise ERP system?

While working on a large-scale modernization project for an enterprise ERP system, our engineering team encountered a deceptively complex database querying challenge. The architecture demanded that we store flexible, semi-structured JSON payloads alongside strongly typed relational data. Since we were operating on SQL Server 2022—prior to the introduction of the native JSON data type in SQL Server 2025—we engineered the database to store these JSON payloads in a varbinary(max) column to optimize storage and retrieval speed.

Within the application layer, we used Entity Framework Core (EF Core). To handle the serialization elegantly, we modeled the JSON column using the C# BinaryData type and implemented a custom ValueConverter to seamlessly translate between BinaryData and byte[]. During initial read/write operations, this design performed flawlessly.

However, during a sprint focused on enhancing the system’s global search functionality, we realized we had a problem. The business required the ability to perform partial text matching across these JSON payloads. When our developers attempted to use the standard EF.Functions.Like() operator on the BinaryData properties, the application crashed in our staging environment. This challenge inspired the following technical deep-dive so other architectural teams can avoid the same pitfall when manipulating raw binary data through LINQ.

Why did the LIKE operator fail on varbinary JSON payloads in SQL Server?

In standard T-SQL, applying a LIKE operator against a varbinary(max) column is supported. The database engine implicitly or explicitly handles the cast, allowing you to execute queries such as WHERE JsonPayload LIKE '%SearchTerm%'. From a pure database perspective, the operation is entirely valid, even if it is not the most optimal way to parse heavily nested JSON.

The problem surfaced entirely within the Object-Relational Mapping (ORM) layer. EF Core acts as a sophisticated translator, converting strongly typed C# LINQ expressions into optimized SQL dialects. When we mapped our varbinary(max) column to the BinaryData type, EF Core recognized the property as a binary array wrapper. The standard EF.Functions.Like() method is strictly typed to accept strings.

To bypass the compiler error, the intuitive approach is to invoke .ToString() on the BinaryData property before passing it to the LIKE function. Unfortunately, this creates a disconnect between what C# allows and what the EF Core expression tree provider knows how to translate.

What caused the LINQ expression translation exception in our application?

The exact failure manifested as a runtime exception: InvalidOperationException: The LINQ expression could not be translated.

Here is a sanitized version of the query that triggered the system failure:

var results = await dbContext.Examples
    .Where(t => EF.Functions.Like(t.JsonPayload.ToString(), "%SearchTerm%"))
    .ToListAsync();

When the EF Core query pipeline evaluated this expression tree, it encountered t.JsonPayload.ToString(). EF Core does not have a built-in SQL translation for the ToString() method of a BinaryData object mapped via a custom ValueConverter. Because the ORM could not convert this specific node of the expression tree into a valid T-SQL CAST or CONVERT statement, it abandoned the operation to prevent unpredictable client-side memory consumption.

What solutions did we consider for querying BinaryData effectively?

Before implementing our final architectural fix, we evaluated several alternative approaches to ensure we were balancing performance, maintainability and code cleanliness.

Did we consider client-side evaluation for the varbinary data?

One immediate workaround is to pull the records into memory and perform the filter using standard C# LINQ-to-Objects. By calling .AsEnumerable() or .ToListAsync() before the .Where() clause, the evaluation shifts from SQL Server to the application server. We immediately discarded this approach. Loading gigabytes of binary JSON payloads into application memory would cause massive latency spikes and inevitably lead to OutOfMemory (OOM) exceptions.

How about using raw SQL queries with FromSqlRaw?

EF Core allows developers to bypass LINQ translation entirely using raw SQL execution. We could have written: dbContext.Examples.FromSqlRaw("SELECT * FROM Examples WHERE CAST(JsonPayload AS varchar(max)) LIKE '%SearchTerm%'"). While this works, it breaks query composability. Our repository layers rely heavily on chaining IQueryable extensions for dynamic filtering, sorting and pagination. Introducing raw SQL would fragment the data access strategy and introduce potential SQL injection risks if not parameterized correctly.

Could we use SQL Server computed columns?

Another viable option was modifying the database schema to include a persisted or virtual computed column that automatically casts the varbinary(max) data to nvarchar(max). While this is highly performant—especially if indexed—we wanted to avoid schema sprawl for a feature that was only required in specific administrative search contexts.

How did we implement the custom DbFunction translation in EF Core?

To preserve the type-safety of LINQ while generating the exact T-SQL required, we opted to build a custom EF Core DbFunction mapping. This approach intercepts the LINQ expression and injects a custom Abstract Syntax Tree (AST) node instructing EF Core to apply a SQL CONVERT function before executing the LIKE operator.

First, we defined an extension method that acts as a placeholder for our LINQ queries. This method is never actually executed in memory; it exists purely to be recognized by the EF Core translator.

public static class DbContextExtensions
{
    public static string CastToString(this BinaryData binaryData)
    {
        throw new NotSupportedException("This method is only for EF Core SQL translation.");
    }
}

Next, we wired this method into the EF Core model configuration. Inside our AppDbContext class, we overrode the OnModelCreating method to explicitly define how CastToString() should be translated into a SQL Server Expression.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Existing Value Converter mapping
    modelBuilder.Properties<BinaryData>().HaveConversion<BinaryDataConverter>();
    // Map the custom extension method to a SQL CONVERT statement
    var methodInfo = typeof(DbContextExtensions).GetMethod(nameof(DbContextExtensions.CastToString));
    
    modelBuilder.HasDbFunction(methodInfo)
        .HasTranslation(args => 
        {
            // We construct an AST node representing: CONVERT(varchar(max), [Column])
            return new Microsoft.EntityFrameworkCore.Query.SqlExpressions.SqlFunctionExpression(
                "CONVERT",
                new Microsoft.EntityFrameworkCore.Query.SqlExpressions.SqlExpression[] 
                {
                    new Microsoft.EntityFrameworkCore.Query.SqlExpressions.SqlFragmentExpression("varchar(max)"),
                    args.First() // The binary column argument
                },
                nullable: true,
                argumentsPropagateNullability: new[] { false, true },
                typeof(string),
                null
            );
        });
}

With this translation registered in the application pipeline, our development team could now write fully composable, type-safe LINQ queries against the binary column:

var results = await dbContext.Examples
    .Where(t => EF.Functions.Like(t.JsonPayload.CastToString(), "%SearchTerm%"))
    .ToListAsync();

The resulting T-SQL generated by EF Core perfectly matched our expectations, pushing the evaluation down to the database engine without triggering translation failures.

What are the key engineering lessons for scaling backend architectures?

Solving this LINQ translation limitation reinforced several critical architectural guidelines for our database engineering practices:

• Understand the ORM Expression Tree: EF Core is not magic. When companies look to hire dotnet developers for enterprise modernization, they must ensure the engineers understand how C# code translates into AST nodes, rather than just knowing surface-level LINQ syntax.
• Push Computations to the Database: Pulling data into memory to resolve an ORM translation error is a severe anti-pattern. Always aim to push filtering logic down to the SQL engine using custom translations or interceptors.
• Leverage Database Native Functions: Often, when tech leaders hire python developers for scalable data systems, they emphasize native data types. The same applies in the C# ecosystem. While we solved this via EF Core, migrating to SQL Server 2025’s native JSON type in the future will remove the need for binary conversions entirely.
• Maintain Query Composability: Avoid raw SQL where possible. Utilizing HasDbFunction ensures that dynamic queries, paginations and complex joins remain intact and predictable.
• Think Beyond the C# Layer: Unstructured data requires specific handling. Similarly, if you hire ai developers for production deployment, processing large binary or JSON blobs efficiently is a prerequisite for feeding data ingestion pipelines safely.
• Consistent Data Consumption: Even if you hire app developer to create a mobile app that consumes these endpoints, the backend must return results with predictable latency. Database-level text searching ensures your APIs remain responsive under load.

How can your team wrap up this EF Core translation challenge?

Handling custom conversions like BinaryData in Entity Framework Core highlights the importance of bridging the gap between C# object models and raw relational database execution. By injecting custom SQL translations directly into the EF Core pipeline, we bypassed LINQ limitations, secured query composability and prevented application performance bottlenecks. If your architecture is facing similar scaling challenges and you need to deploy robust data processing layers, contact us to hire software developer experts who deliver optimized, enterprise-grade solutions.

Social Hashtags

#EFCore #EntityFrameworkCore #DotNet #CSharp #SQLServer #LINQ #BinaryData #DatabaseDevelopment #BackendDevelopment #SoftwareArchitecture