LINQ fails to translate an SqlFunctions method

April 5, 2024

I have a problem with my C# app. I am trying to fetch an item from the SQL Server database, in the database I can use the following query:

SELECT *
FROM [table]
WHERE [field] IS NOT NULL 
    AND [field] != '' 
    AND CONCAT(REPLICATE('0', 10 - LEN([field])), [field]) = 'literal'

This works perfectly and returns the desired row on the table, so I KNOW for a fact that the item exists.

Then I try to access the same data from my C# backend I do so through a LINQ expression as follows:

var literal = 'xxxxx'
desired = dbContext.dbSet.Where(x => x.field != null && x.field != "" && (
    string.Concat(SqlFunctions.Replicate("0", 10 - x.CodigoProductoCia.Length), x.CodigoProductoCia) == literal
)

The previous snippet of code produces the following exception message:

The LINQ expression

DbSet<Entity>().Where(p => p.field != null && p.field != "" && SqlFunctions.Replicate(target: "0", count: (int?)(10 - p.CodigoProductoCia.Length)) + p.CodigoProductoCia == "") could not be translated.

Additional information: Translation of method ‘System.Data.Entity.SqlServer.SqlFunctions.Replicate’ failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to ‘AsEnumerable’, ‘AsAsyncEnumerable’, ‘ToList’, or ‘ToListAsync’. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.’

My understanding is that LINQ cannot translate the query to SQL, which is fair and all, but in honesty, I’m only using methods that work in LINQ, and string.Concat() produces the same result as simply a +.

In fact, the error is specifically complaining about SqlFunctions#Replicate which is a method SPECIFICALLY impossible to invoke outside of "LINQ to Entities" queries, which makes this even more confusing.

Any advice?

>Solution :

SqlFunctions.Replicate is part of the System.Data and is not translated. EF Core analog is EF.Functions but currently it does not have an option for REPLICATE for SQL Server (see this github issue).

As workaround you can consider writing a user-defined function mapping for now.