The EF Core LINQ-to-SQL Translation Catalog: Strings, DateTime, Math, Collections, EF.Functions and HasDbFunction

Version note: The code in this post was written and tested with EF Core 10 and SQL Server 2025; the EF Core 11 notes were tested with EF Core 11.0.0.

Guess First

A user types "dune" into the search box of Bookstore, the online bookstore used as the shared example across this site's series, and you want them to find "Dune". To make letter case irrelevant you write this:

var term = "dune";
var titles = db.Books
    .Where(b => b.Title.ToLower().Contains(term.ToLower()))
    .Select(b => b.Title)
    .ToList();

There is an index on Books.Title. How does this query reach SQL Server, and does the index help? Second question: did you need ToLower at all? The answer to the first is in the translation catalog below, the second in the sargability section.

In post 09 we saw how LINQ is translated to SQL on SQLite. In this post we put together the catalog of which C# expression becomes which SQL on SQL Server, with a real Bookstore model. The examples live in the samples/Bookstore.QueryLab console app; each query prints its SQL with ToQueryString() first, then its result. To set up the database and create the SQL functions:

dotnet run --project samples/Bookstore.QueryLab -- setup
dotnet run --project samples/Bookstore.QueryLab -- strings

String Functions and LIKE

The SQL for the opening query and its close relatives:

DECLARE @term_contains nvarchar(200) = N'%dune%';

SELECT [b].[Title]
FROM [Books] AS [b]
WHERE [b].[Title] LIKE @term_contains ESCAPE N'\'

SELECT [b].[Title]
FROM [Books] AS [b]
WHERE [b].[Title] LIKE N'The%'

DECLARE @ToLower_contains nvarchar(200) = N'%dune%';

SELECT [b].[Title]
FROM [Books] AS [b]
WHERE LOWER([b].[Title]) LIKE @ToLower_contains ESCAPE N'\'

The first is Title.Contains(term), the second Title.StartsWith("The"), the third the opening query. The value from the variable became a parameter, and EF Core added ESCAPE N'\' to escape the % and _ characters. If the user types 50% into the search box, the parameter goes out as N'%50\%%'; the % the user typed is escaped, so it is searched as plain text, not as a wildcard. The constant "The", on the other hand, was embedded directly in the SQL. term.ToLower() ran in memory (the parameter value is already lower case), but Title.ToLower() was applied to the column as LOWER. We'll measure the cost of that shortly.

The other string methods find a direct SQL counterpart too:

db.Books.OrderBy(b => b.Id).Take(2).Select(b => new
{
    b.Title,
    Upper = b.Title.ToUpper(),
    First3 = b.Title.Substring(0, 3),
    Space = b.Title.IndexOf(" "),
    b.Title.Length,
    Trimmed = b.Title.Trim()
});

SELECT TOP(@p) [b].[Title], UPPER([b].[Title]) AS [Upper], SUBSTRING([b].[Title], 0 + 1, 3) AS [First3], CHARINDEX(N' ', [b].[Title]) - 1 AS [Space], CAST(LEN([b].[Title]) AS int) AS [Length], LTRIM(RTRIM([b].[Title])) AS [Trimmed]
FROM [Books] AS [b]
ORDER BY [b].[Id]
  { Title = Dune, Upper = DUNE, First3 = Dun, Space = -1, Length = 4, Trimmed = Dune }

C# indexes start at 0, SQL at 1; EF Core bridges the gap with 0 + 1 and - 1. For "Dune", which has no space, IndexOf returned -1 just as in C#.

On grouped data, string.Join becomes an aggregate:

db.Books
    .GroupBy(b => b.Author.Name)
    .OrderBy(g => g.Key)
    .Select(g => new { Author = g.Key, Titles = string.Join(", ", g.OrderBy(b => b.Title).Select(b => b.Title)) });

SELECT [a].[Name] AS [Author], COALESCE(STRING_AGG([b].[Title], N', ') WITHIN GROUP (ORDER BY [b].[Title]), N'') AS [Titles]
FROM [Books] AS [b]
INNER JOIN [Authors] AS [a] ON [b].[AuthorId] = [a].[Id]
GROUP BY [a].[Name]
ORDER BY [a].[Name]
  { Author = Jane Austen, Titles = Emma, Pride and Prejudice }

Microsoft's function mapping table lists the ordered STRING_AGG ... WITHIN GROUP translation under EF Core 11, but I got this SQL on EF Core 10.0.15. When I asked for the same result through the navigation instead of GroupBy (a.Books.OrderBy(...).Select(...)), EF Core didn't produce STRING_AGG; it fetched all rows with a LEFT JOIN and did the joining in memory. Same intent, two different SQL statements: without looking at ToQueryString() you can't tell which one you got.

Collation and the Turkish İ

My database collation is SQL_Latin1_General_CP1_CI_AS, which is case-insensitive. The catalog contains "İnce Memed"; the user types "ince":

db.Books.Where(b => b.Title.StartsWith("ince"));
db.Books.Where(b => EF.Functions.Collate(b.Title, "Turkish_CI_AS").StartsWith("ince"));

SELECT [b].[Title]
FROM [Books] AS [b]
WHERE [b].[Title] LIKE N'ince%'

SELECT [b].[Title]
FROM [Books] AS [b]
WHERE [b].[Title] COLLATE Turkish_CI_AS LIKE N'ince%'
  İnce Memed

The first query came back empty. In the Latin collation, "İ" is not the upper-case form of "i"; in the Turkish collation it is. We saw the SQL side of this trap in the SQL Fundamentals series, in the post on SQL data types and CREATE TABLE. EF.Functions.Collate sets a collation per query, but applying COLLATE to a column also breaks the index order. If Turkish search is the main scenario, it's better to define the column with a Turkish collation from the start; we'll do that with UseCollation in the next post.

Date and Time

var now = DateTime.UtcNow;
db.Orders.OrderBy(o => o.Id).Take(2).Select(o => new
{
    o.Id,
    Day = o.CreatedAt.Date,
    o.CreatedAt.Year,
    o.CreatedAt.Month,
    DueDate = o.CreatedAt.AddDays(14),
    Age = EF.Functions.DateDiffDay(o.CreatedAt, now)
});

SELECT TOP(@p) [o].[Id], CONVERT(date, [o].[CreatedAt]) AS [Day], DATEPART(year, [o].[CreatedAt]) AS [Year], DATEPART(month, [o].[CreatedAt]) AS [Month], DATEADD(day, CAST(14.0E0 AS int), [o].[CreatedAt]) AS [DueDate], DATEDIFF(day, [o].[CreatedAt], @now) AS [Age]
FROM [Orders] AS [o]
ORDER BY [o].[Id]
  { Id = 1, Day = 11/12/2026 12:00:00 AM, Year = 2026, Month = 11, DueDate = 11/26/2026 9:57:20 PM, Age = 2 }

.Date became CONVERT(date, ...), Year and Month became DATEPART, AddDays became DATEADD. Since AddDays takes a double, 14 was cast first to 14.0E0 and then to int; harmless, but it can be confusing when you read the SQL. EF.Functions.DateDiffDay maps straight to DATEDIFF(day, ...); it's a method with no C# equivalent that only works inside a query. DateOnly is translated the same way: b.PublishedOn.Year < 1900 became a DATEPART, AddYears(100) a DATEADD.

There's one distinction to watch. When you put the value in the now variable, it became the @now parameter and the time came from the application server. If you write DateTime.UtcNow directly inside the lambda, EF Core translates it to GETUTCDATE() and the time comes from the database server. The clock difference between the two servers means different results at boundaries such as "the last 30 days".

The "best sellers of the last 30 days" query brings all of this together:

var since = DateTime.UtcNow.AddDays(-30);
var top = db.OrderItems
    .Where(i => i.Order.CreatedAt >= since)
    .GroupBy(i => i.Book.Title)
    .Select(g => new { Title = g.Key, Units = g.Sum(i => i.Quantity) })
    .OrderByDescending(x => x.Units)
    .Take(3);

SELECT TOP(@p) [b].[Title], COALESCE(SUM([o].[Quantity]), 0) AS [Units]
FROM [OrderItems] AS [o]
INNER JOIN [Orders] AS [o0] ON [o].[OrderId] = [o0].[Id]
INNER JOIN [Books] AS [b] ON [o].[BookId] = [b].[Id]
WHERE [o0].[CreatedAt] >= @since
GROUP BY [b].[Title]
ORDER BY COALESCE(SUM([o].[Quantity]), 0) DESC
  { Title = Dune, Units = 3 }
  { Title = The Hobbit, Units = 3 }
  { Title = İnce Memed, Units = 2 }

You could also write the same filter as EF.Functions.DateDiffDay(i.Order.CreatedAt, DateTime.UtcNow) <= 30; the result was the same, and the SQL became WHERE DATEDIFF(day, [o0].[CreatedAt], GETUTCDATE()) <= 30. The first leaves the column bare, the second wraps it in a function. We'll measure the difference in the sargability section.

EF Core 11 note: EF Core 11 brings EF.Functions.DateTrunc, which translates to DATETRUNC from SQL Server 2022. To bucket orders by week:

db.Orders
    .GroupBy(o => EF.Functions.DateTrunc("week", o.CreatedAt))
    .Select(g => new { Week = g.Key, Orders = g.Count() })
    .OrderBy(x => x.Week);

SELECT [o0].[Key] AS [Week], COUNT(*) AS [Orders]
FROM (
    SELECT DATETRUNC(week, [o].[CreatedAt]) AS [Key]
    FROM [Orders] AS [o]
) AS [o0]
GROUP BY [o0].[Key]
ORDER BY [o0].[Key]

The same release translates MaxBy/MinBy: await db.Books.MaxByAsync(b => b.Price) returned the most expensive book (Dune, 18.50) in a single query.

Math, Guid and Collections

db.Books.Where(b => b.Price > 12).Select(b => new
{
    b.Title,
    Discounted = Math.Round(b.Price * 0.85m, 2),
    Floor = Math.Floor(b.Price),
    Diff = Math.Abs(b.Price - 12.50m)
});
db.Books.OrderBy(b => Guid.NewGuid()).Select(b => b.Title).Take(1);

SELECT [b].[Title], ROUND([b].[Price] * 0.85, 2) AS [Discounted], FLOOR([b].[Price]) AS [Floor], ABS([b].[Price] - 12.5) AS [Diff]
FROM [Books] AS [b]
WHERE [b].[Price] > 12.0
  { Title = Dune, Discounted = 15.7300, Floor = 18, Diff = 6.00 }

SELECT TOP(@p) [b].[Title]
FROM [Books] AS [b]
ORDER BY NEWID()

Math.Round became ROUND, Guid.NewGuid() became NEWID(); that's how you write "suggest a random book". The extra zeros in 15.7300 come from SQL Server widening the scale in decimal multiplication; the value is right, format it when you display it.

On the collection side there are two different cases. Contains on an in-memory array becomes an IN that expands into separate parameters, as we saw in the EF Core 10 post. If the column itself is a collection, as with the primitive collections from post 26, EF Core opens the JSON column into a table with OPENJSON. Book.Tags is a List<string>:

int[] ids = [1, 3, 5];
db.Books.Where(b => ids.Contains(b.Id));
db.Books.Where(b => b.Tags.Contains("classic"));

DECLARE @ids1 int = 1;
DECLARE @ids2 int = 3;
DECLARE @ids3 int = 5;

SELECT [b].[Title]
FROM [Books] AS [b]
WHERE [b].[Id] IN (@ids1, @ids2, @ids3)

SELECT [b].[Title]
FROM [Books] AS [b]
WHERE N'classic' IN (
    SELECT [t].[value]
    FROM OPENJSON([b].[Tags]) WITH ([value] nvarchar(max) '$') AS [t]
)

EF Core 11 note: when you target SQL Server 2025 with UseCompatibilityLevel(170), EF Core 11 translates the same Contains to WHERE JSON_CONTAINS([b].[Tags], N'classic') = 1. When I tried it against the table created by EF Core 10, SQL Server refused: "Argument data type nvarchar(max) is invalid for argument 1 of json_contains function." JSON_CONTAINS wants the column to have the new json type; after converting the column to json, the same query returned the five classics. So raising the compatibility level isn't enough on its own; the schema has to be updated too (with a migration).

The EF.Functions Catalog

EF.Functions is the gateway to SQL features that have no C# equivalent. These methods only mean something inside a query; call them in memory and you get an exception. The most commonly used ones in the SQL Server provider:

db.Books.Where(b => EF.Functions.Like(b.Title, "%[Mm]arch"));
db.Books.Where(b => EF.Functions.Like(b.Isbn, "978!_%", "!"));
db.Books.Select(b => new { b.Title, Bytes = EF.Functions.DataLength(b.Title) });
db.Books.Where(b => EF.Functions.FreeText(b.Title, "hobbit"));

WHERE [b].[Title] LIKE N'%[Mm]arch'
  Middlemarch

WHERE [b].[Isbn] LIKE N'978!_%' ESCAPE N'!'

SELECT TOP(@p) [b].[Title], DATALENGTH([b].[Title]) AS [Bytes]
  { Title = Dune, Bytes = 8 }

WHERE FREETEXT([b].[Title], N'hobbit')

Like does what Contains can't: it sends SQL Server-specific wildcards such as [Mm] as they are, and you can supply your own escape character. DataLength counts bytes, not characters; "Dune" is nvarchar, so it's 8 bytes. There are also IsDate and IsNumeric, useful for weeding out dates written into text columns in old schemas. The DateDiffDay family (DateDiffHour, DateDiffMonth…) and DateFromParts belong here too.

FreeText and Contains are for full-text search and need a full-text index on the table. The SQL Server image in Docker doesn't include the full-text component; I got the SQL with ToQueryString(), and when I ran it SQL Server said:

SqlException: Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Books' because it is not full-text indexed.

If your own SQL Server installation has the Full-Text Search component, you first need to create the catalog and the index. EF Core 11 can also set them up through migrations (HasFullTextIndex), but that's outside the scope of this post.

Your Own Functions with HasDbFunction

In Bookstore the discount calculation has lived in a SQL Server function for years, and the reports use it too. Instead of writing the same calculation again in C#, you want to call that function from LINQ. db/functions.sql has two functions, one scalar and one table-valued:

CREATE OR ALTER FUNCTION dbo.fn_DiscountedPrice (@price decimal(10,2), @percent int)
RETURNS decimal(10,2)
AS
BEGIN
    RETURN ROUND(@price * (100 - @percent) / 100.0, 2);
END;
GO

CREATE OR ALTER FUNCTION dbo.fn_BestSellers (@since datetime2)
RETURNS TABLE
AS
RETURN
    SELECT oi.BookId, SUM(oi.Quantity) AS Units
    FROM dbo.OrderItems AS oi
    JOIN dbo.Orders AS o ON o.Id = oi.OrderId
    WHERE o.CreatedAt >= @since
    GROUP BY oi.BookId;

On the C# side you write a bodiless static method for the scalar function and a method using FromExpression on the context for the table-valued one, then map both with HasDbFunction:

public static class BookstoreFunctions
{
    // Mapped to dbo.fn_DiscountedPrice; only usable inside LINQ queries.
    public static decimal DiscountedPrice(decimal price, int percent)
        => throw new NotSupportedException("Only for use in LINQ queries.");
}

public class BookstoreContext(DbContextOptions<BookstoreContext> options) : DbContext(options)
{
    public IQueryable<BestSeller> BestSellers(DateTime since)
        => FromExpression(() => BestSellers(since));

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasDbFunction(typeof(BookstoreFunctions).GetMethod(nameof(BookstoreFunctions.DiscountedPrice))!)
            .HasName("fn_DiscountedPrice")
            .HasSchema("dbo");

        modelBuilder.Entity<BestSeller>().HasNoKey().ToTable((string?)null);
        modelBuilder.HasDbFunction(typeof(BookstoreContext).GetMethod(nameof(BestSellers), [typeof(DateTime)])!)
            .HasName("fn_BestSellers");
    }
}

I added the ToTable((string?)null) line afterwards. On the first attempt EnsureCreated had created an empty table in the database for BestSeller, the function's row type; this line says that type isn't mapped to a table. Now both functions are part of LINQ:

db.Books
    .Where(b => BookstoreFunctions.DiscountedPrice(b.Price, 20) < 10)
    .Select(b => new { b.Title, b.Price, Sale = BookstoreFunctions.DiscountedPrice(b.Price, 20) });

db.BestSellers(DateTime.UtcNow.AddDays(-30))
    .Join(db.Books, s => s.BookId, b => b.Id, (s, b) => new { b.Title, s.Units })
    .OrderByDescending(x => x.Units);

SELECT [b].[Title], [b].[Price], [dbo].[fn_DiscountedPrice]([b].[Price], 20) AS [Sale]
FROM [Books] AS [b]
WHERE [dbo].[fn_DiscountedPrice]([b].[Price], 20) < 10.0
  { Title = Emma, Price = 9.90, Sale = 7.92 }
  { Title = Pride and Prejudice, Price = 8.75, Sale = 7.00 }
  { Title = İnce Memed, Price = 11.40, Sale = 9.12 }

SELECT [b].[Title], [f].[Units]
FROM [dbo].[fn_BestSellers](@since) AS [f]
INNER JOIN [Books] AS [b] ON [f].[BookId] = [b].[Id]
ORDER BY [f].[Units] DESC

Because the table-valued function returns an IQueryable, you can add joins, filters and ordering on top of it, and everything goes out as a single SQL statement. Compare it with FromSqlRaw from post 15: there we wrote the SQL as a string, here the function's signature sits typed in C#. The API's /books/bestsellers endpoint uses this function as well. In this post we set up the functions themselves with a SQL file; in post 32 we'll version them with migrations.

Untranslatable Methods and the Final Select

When you write ToUpper(new CultureInfo("tr-TR")) for Turkish upper case, EF Core has no SQL equivalent:

var tr = new CultureInfo("tr-TR");
db.Books.Where(b => b.Title.ToUpper(tr) == "İNCE MEMED").ToList();

The LINQ expression 'DbSet<Book>()
    .Where(b => b.Title.ToUpper(@tr) == "İNCE MEMED")' could not be translated. Additional information: Translation of method 'string.ToUpper' 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.

Look at two places when you read the message. "Translation of method 'string.ToUpper' failed" tells you which call is the problem; the parameterless ToUpper() is translated, the overload that takes a culture isn't. The second link (2132413) points exactly to this post's HasDbFunction section: it says you can map this to your own function.

The same method doesn't throw in the final Select, because the client evaluation rule from post 09 applies there:

SELECT [b].[Title]
FROM [Books] AS [b]
WHERE [b].[Price] > 12.0
  DUNE
  THE HOBBİT
  MİDDLEMARCH

The SQL only has Title; the upper-casing happened in memory with Turkish rules, and "The Hobbit" became THE HOBBİT. The conversion is correct; by Turkish rules the upper case of "i" is "İ". But was that what you wanted for an English book title? Because client evaluation runs silently, you only see results like this when you look at the output.

Sargability: Making an Index Unusable

Now the second half of the opening question. To measure it, I added 50,000 rows to the Books table and ran the same search two ways; the SET STATISTICS IO ON output:

-- Title.StartsWith(term): WHERE [b].[Title] LIKE @term_startswith ESCAPE N'\'
Table 'Books'. Scan count 1, logical reads 4, ...

-- Title.ToLower().StartsWith("dune"): WHERE LOWER([b].[Title]) LIKE N'dune%'
Table 'Books'. Scan count 1, logical reads 300, ...

In the execution plan the first is an Index Seek, the second an Index Scan. Once the column goes inside a function, SQL Server can't use the order of the index; it has to compute LOWER for every row and compare. This is called sargability. We saw it from the SQL side in the SQL Fundamentals series, in the post on scalar functions and CASE; in LINQ the same trap is easier to fall into, because ToLower() looks like an innocent C# call.

The answer to the second opening question is here too: the database collation is already case-insensitive (CI), so ToLower does nothing useful except take the index out of play. Contains is an Index Scan anyway because of the leading %; the way to speed up "contains" search on large tables isn't an index but full-text search.

The same rule applies to dates. DATEDIFF(day, CreatedAt, GETUTCDATE()) <= 30 puts the column inside a function; CreatedAt >= @since leaves it bare and can benefit from an index on CreatedAt. Compute the boundary in C# and leave the column alone.

Common Pitfalls

First, using ToLower/ToUpper for case-insensitive search. Check the column's collation first. If it's CI, you don't need them; if it's CS (case-sensitive) and this search is frequent, the solution is to define the column or the query with a suitable collation and build the index accordingly.

Second, putting an expensive method in the final Select. Since client evaluation is allowed there, EF Core doesn't complain; it fetches the columns the method needs and runs the C# for every row. If the method makes an HTTP call, runs a regex or queries the database, it runs 10,000 times for a 10,000-row list. The ToUpper(tr) example above was cheap, but its result was still unexpected.

Third, the STRING_AGG limit. When I linked 50,000 books to the same author for testing, the author list query stopped with this error:

Microsoft.Data.SqlClient.SqlException (0x80131904): STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation.

Since Title is nvarchar(200), the STRING_AGG result is nvarchar(4000), which is limited to 8000 bytes. It's an error that never shows up with small test data; if the group can grow, do the joining in memory or limit the list with Take.

Fourth, mixing up clock sources. DateTime.UtcNow inside the lambda is the database's clock; a variable computed outside is the application's clock. Don't mix the two in the same query.

A Small Experiment

★☆☆ Get the ToQueryString() output for Title.EndsWith(term), Title.Replace(" ", "-") and string.IsNullOrEmpty(b.Title), and compare it with the function mapping table.

★★☆ Add an index on Orders.CreatedAt. Write the "last 30 days" filter both as CreatedAt >= since and with DateDiffDay, and compare the SET STATISTICS IO output of the two queries.

★★★ Write a dbo.fn_AuthorBookCount(@authorId int) RETURNS int function that returns an author's number of books, map it with HasDbFunction and use it inside Where on the author list. Then replace the same function with a normal C# method in the final Select and examine how the SQL changes.

In this post we barely touched the model itself; apart from HasMaxLength and HasPrecision we relied on conventions. In the next post we put together the Fluent API catalog: column types, default and computed values, sequences and HiLo, index options, and bulk settings with ConfigureConventions.