EF Core 11.0: Querying JSON Columns with JsonPathExists and JsonContains

In this post, let's have a look at two new functions introduced in EF Core 11.0: EF.Functions.JsonPathExists() and EF.Functions.JsonContains(). Both are aimed at making it easier to query JSON data stored in your database, without falling back to raw SQL.

For SQL Server, JsonPathExists() translates to the JSON_PATH_EXISTS function (available since SQL Server 2022), and JsonContains() translates to the JSON_CONTAINS function (which is new in SQL Server 2025).

At the time of writing, .NET 11 Preview 3 is the latest and we need to be using EF Core 11 Preview 3 NuGet packages for these functions to work.

<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="11.0.0-preview.3.26207.106">
  <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
  <PrivateAssets>all</PrivateAssets>
</PackageReference>
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="11.0.0-preview.3.26207.106" />

Let's set up a simple Customer with a Contact as a complex type stored as a JSON column. The Contact  wraps an Address. Note that JSON_CONTAINS requires SQL Server 2025, so we are also setting the compatibility level to 170 via UseCompatibilityLevel(170). Without that, EF Core won't translate JsonContains() for you.

public class Customer
{
    public int Id { get; set; }

    public required string Name { get; set; }

    public required Contact Contact { get; set; }
}

public class Contact
{
    public required Address Address { get; set; }
}

public class Address
{
    public required string Street { get; set; }

    public required string City { get; set; }

    public required string State { get; set; }

    public required string PostalCode { get; set; }
}

public class MyDbContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseSqlServer(@"<ConnectionString>", o => o.UseCompatibilityLevel(170));
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>()
            .ComplexProperty(c => c.Contact, b => b.ToJson());
    }
}

EF.Functions.JsonPathExists()

This checks whether a given JSON path exists inside a JSON document. The nice thing is, since our Contact is a complex type mapped to JSON, we can pass the property directly and use a typed-style path against the underlying JSON shape. Say we want to defensively pull customers whose Contact JSON actually has an Address.PostalCode:

// EF.Functions.JsonPathExists(): customers whose Contact JSON has an Address.PostalCode
List<Customer> customersWithPostalCode = await context.Customers
    .Where(c => EF.Functions.JsonPathExists(c.Contact, "$.Address.PostalCode"))
    .ToListAsync();

And the SQL generated:

SELECT [c].[Id], [c].[Name], [c].[Contact]
FROM [Customers] AS [c]
WHERE JSON_PATH_EXISTS([c].[Contact], N'$.Address.PostalCode') = 1

EF.Functions.JsonContains()

While EF Core 11 already automatically translates LINQ Contains queries over primitive collections to JSON_CONTAINS (when targeting SQL Server 2025), there are cases where you want to invoke it directly, for example, to search for a value at a specific JSON path. That's where this function comes in.

One small heads-up: at the time of writing, JsonContains() is flagged as experimental (diagnostic EF9106), so you'll need to suppress it at the call site for the project to build.

Let's pull all the customers in Redmond:

// EF.Functions.JsonContains(): customers in Redmond
#pragma warning disable EF9106 // JsonContains is for evaluation purposes only
List<Customer> redmondCustomers = await context.Customers
    .Where(c => EF.Functions.JsonContains(c.Contact, "Redmond", "$.Address.City") == 1)
    .ToListAsync();
#pragma warning restore EF9106

And the generated SQL:

SELECT [c].[Id], [c].[Name], [c].[Contact]
FROM [Customers] AS [c]
WHERE JSON_CONTAINS([c].[Contact], N'Redmond', N'$.Address.City') = 1

Both functions can be used with scalar string properties, complex types, and owned entity types mapped to JSON columns, so as you can see, you don't need a raw JSON string column to take advantage of these. And as a bonus, JSON_CONTAINS can take advantage of a JSON index, if one is defined on the column.

Read more:

   EF.Functions.JsonPathExists()

   EF.Functions.JsonContains()

   JSON_PATH_EXISTS (Transact-SQL)

   JSON_CONTAINS (Transact-SQL)

Hope this helps.

Happy Coding.

Regards,
Jaliya

EF Core 11.0: Create and Apply Migrations in a Single Command

When working with migrations in Entity Framework Core, what we usually do is first create the migration and then apply it to the database.

It has always been a two-step process, and that can be a bit annoying when you are continuously developing.

dotnet ef

dotnet ef migrations add Initial
dotnet ef database update

Package Manager Console/PowerShell

Add-Migration Initial
Update-Database

But with EF Core 11.0, we can create and apply the migration in a single step.

dotnet ef  

dotnet ef database update Initial --add

Note the new --add argument.

Package Manager Console/PowerShell

Update-Database -Migration Initial -Add

This will create a migration named Initial and apply it to the database in one go. The migration files will still be created and saved, so you can push it along with your code

Since EF Core 11.0 is still in preview, if you are using the global EF tool, make sure it is updated to the latest version.

dotnet ef --version
dotnet tool update --global dotnet-ef

Hope this helps.

Read more:

   What's New in EF Core 11

Happy Coding.

Regards,
Jaliya

EF Core 10.0: Global Query Filter Improvements

In this post, let's have a look at some nice improvements in Global Query Filters in EF Core 10.0.

We can use Global Query Filters at an entity level to attach an additional LINQ where operator whenever the entity type is queried.

Consider the following DbContext.

public class Customer
{
    public int Id { get; set; }
 
    public string TenantId { get; init; }
 
    public string Name { get; init; }
 
    public bool IsDeleted { get; set; }
}
 
public class MyDbContext(string tenantId) : DbContext
{
    public DbSet<Customer> Customers { get; set; }
 
    override protected void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>()
            .HasQueryFilter(x => x.TenantId == tenantId);
    }
}

And we can do something like this.

string tenantId = "Tenant1";
 
using var context = new MyDbContext(tenantId);
 
await context.Customers.AddRangeAsync(
    [
        new Customer
        {
            TenantId = tenantId,
            Name = "John Doe"
        },
        new Customer
        {
            TenantId = tenantId,
            Name = "Jane Doe",
            IsDeleted = true
        },
        new Customer
        {
            TenantId = "Tenant2",
            Name = "Jim Doe"
        }
    ]);
 
await context.SaveChangesAsync();
 
foreach (Customer? customer in await context.Customers.ToListAsync())
{
    Console.WriteLine($"Customer: {customer.Name}, Tenant: {customer.TenantId}");
}

When we run above code, the executed query is something below.

SELECT [c].[Id], [c].[IsDeleted], [c].[Name], [c].[TenantId]
FROM [Customers] AS [c]
WHERE [c].[TenantId] = @__P_0

As you can see, Query Filter was attached and we are getting the expected result.

Now prior to EF Core 10.0, if for some reason, we add another Query Filter by doing something like below;

modelBuilder.Entity<Customer>()
    .HasQueryFilter(x => x.TenantId == tenantId);
 
modelBuilder.Entity<Customer>()
    .HasQueryFilter(x => !x.IsDeleted);

And now if we run the above code, note following executed query.

SELECT [c].[Id], [c].[IsDeleted], [c].[Name], [c].[TenantId]
FROM [Customers] AS [c]
WHERE [c].[IsDeleted] = CAST(0 AS bit)

Only the last Query Filter was used.

This would not be the desired output. Prior to EF Core 10, when multiple filters are configured, prior filters are overridden.

The workaround is, 

modelBuilder.Entity<Customer>()
    .HasQueryFilter(x => x.TenantId == tenantId && !x.IsDeleted);

With EF Core 10.0, we can now define multiple Query Filters, but each filter has to be given a name.

modelBuilder.Entity<Customer>()
    .HasQueryFilter("TenantFilter", x => x.TenantId == tenantId)
    .HasQueryFilter("SoftDeletionFilter", x => !x.IsDeleted);

And this would generate the following query for the above code.

SELECT [c].[Id], [c].[IsDeleted], [c].[Name], [c].[TenantId]
FROM [Customers] AS [c]
WHERE [c].[TenantId] = @P AND [c].[IsDeleted] = CAST(0 AS bit)

And also we can ignore Query Filters by doing something like below.

// Query counts with a specific Query Filter ignored
int tenantCustomersCountIncludingDeleted = await context.Customers
    .IgnoreQueryFilters(["SoftDeletionFilter"])
    .CountAsync(); // 2
 
// Query counts with all Query Filters ignored
int allCustomersCount = await context.Customers
    .IgnoreQueryFilters()
    .CountAsync(); // 3

More read:

   What's New in EF Core 10

   Global Query Filters

Hope this helps.

Happy Coding.

Regards,
Jaliya

EF Core 10.0: Support for Partially Updating JSON Columns with ExecuteUpdate/ExecuteUpdateAsync

In this post, let’s explore a great new enhancement available in EF Core 10.0. EF Core 10.0 now supports partially updating JSON columns with ExecuteUpdate/ExecuteUpdateAsync.

Let's consider the following DbContext.

public class Customer
{
    public int Id { get; set; }
 
    public string Name { get; set; }
 
    public required Contact Contact { get; set; }
}
 
public class Contact
{
    public required Address Address { get; set; } 
}
 
public class Address
{
    public required string Street { get; set; }
 
    public required string City { get; set; }
 
    public required string State { get; set; }
 
    public required string PostalCode { get; set; }
}

public class MyDbContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }
 
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseSqlServer("<ConnectionString>", x =>
            {
                x.UseCompatibilityLevel(170); // Microsoft SQL Server 2025
            });
    }
 
    override protected void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder
            .Entity<Customer>(x =>
            {
                x.ComplexProperty(x => x.Contact, x => x.ToJson());
            });
    }
}

With above code Customer.Contact column will get created as a json data type (EF Core 10.0: Support for JSON Data Type in Microsoft SQL Server)

Now let's say we need to do a partial update on Customer.Contact.PostalCode.

await context.Customers
    .Where(x => x.Name == "John Doe")
    .ExecuteUpdateAsync(setters =>
        setters
            .SetProperty(c => c.Contact.Address.PostalCode, "98102")
    );

Above will create the following SQL query.

UPDATE [c]
SET [Contact].modify('$.Address.PostalCode', @p)
FROM [Customers] AS [c]
WHERE [c].[Name] = N'John Doe'

Note the partial update on PostalCode using the  modify method. The modify method is currently in preview and only available in Microsoft SQL Server 2025 Preview.

This even works with older versions of Microsoft SQL Server, where the JSON data is stored as nvarchar(max) column.

For example,

optionsBuilder
    .UseSqlServer("<ConnectionString>", x =>
    {
        x.UseCompatibilityLevel(160); // Microsoft SQL Server 2022
    });

This would create the Customer.Contact column as  nvarchar(max) and above ExecuteUpdateAsync would still work. In this case, generated query would be something like following.

UPDATE [c]
SET [c].[Contact] = JSON_MODIFY([c].[Contact], '$.Address.PostalCode', @p)
FROM [Customers] AS [c]
WHERE [c].[Name] = N'John Doe'

Note: this only works when mapping JSON data with ComplexProperty and not with owned entities.

More read:
   What's New in EF Core 10
   JSON Data Type

Hope this helps.

Happy Coding.

Regards,

Jaliya

EF Core 9.0: Introducing EF.Parameter(T)

In this post, let's have a look EF.Parameter<T>(T) method that was introduced with EF 9.0.

Consider the following simple LINQ query.

async Task<List<Employee>> GetEmployees(int employeeId)
{
    return await context.Employees
        .Where(e => e.Id == employeeId && e.IsActive == true)
        .ToListAsync();
}

This would generate a SQL query as follows.

--Executed DbCommand (24ms) [Parameters=[@__employeeId_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT [e].[Id], [e].[IsActive]
FROM [Employees] AS [e]
WHERE [e].[Id] = @__employeeId_0 AND [e].[IsActive] = CAST(1 AS bit)

Here you can see the employeeId is parameterized, and IsActive is a constant. When a constant is being used database engine can cache the query resulting a more efficient query.

However if for some reason you want to parameterize the value, you can use EF.Parameter<T>(T).

async Task<List<Employee>> GetEmployees(int employeeId)
{
    return await context.Employees
        .Where(e => e.Id == employeeId && e.IsActive == EF.Parameter(true))
        .ToListAsync();
}

This would generate a SQL query as follows.

--Executed DbCommand (26ms) [Parameters=[@__employeeId_0='?' (DbType = Int32), @__p_1='?' (DbType = Boolean)], CommandType='Text', CommandTimeout='30']
SELECT [e].[Id], [e].[IsActive]
FROM [Employees] AS [e]
WHERE [e].[Id] = @__employeeId_0 AND [e].[IsActive] = @__p_1

While we are on this topic, EF Core 8.0.2 introduced EF.Constant<T>(T) method which forces EF to use a constant even if a parameter would be used by default.

async Task<List<Employee>> GetEmployees(int employeeId)
{
    return await context.Employees
        .Where(e => e.Id == EF.Constant(employeeId) && e.IsActive == EF.Parameter(true))
        .ToListAsync();
}

And this would generate a SQL query as follows.

--Executed DbCommand (18ms) [Parameters=[@__p_1='?' (DbType = Boolean)], CommandType='Text', CommandTimeout='30']
SELECT [e].[Id], [e].[IsActive]
FROM [Employees] AS [e]
WHERE [e].[Id] = 10 AND [e].[IsActive] = @__p_1

Hope this helps.

Happy Coding.

Regards,
Jaliya

EF Core 8.0: Numeric Rowversion for Azure SQL/Microsoft SQL Server

In this post, let's have a look at this small yet handy EF Core 8.0 feature for troubleshooting concurrency issues.

Before EF Core 8.0, the rowversion property in C# classes needs to be of type byte[].

public record Post
{
    public int Id { get; set; }
 
    public string Title { get; set; }
 
    public byte[] Timestamp { get; set; }
}

And when debugging, it looks like the following.

Timestamp as byte[]

Now with EF Core 8.0, we can map the rowversion to long or ulong.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Post>()
        .Property(e => e.Timestamp)
        .HasConversion<byte[]>()
        .IsRowVersion();
}

public record Post
{
    public int Id { get; set; }
 
    public string Title { get; set; }
 
    public long Timestamp { get; set; }
}

When debugging it's more readable now.

Timestamp as long

Happy Coding.

Regards,
Jaliya

Installing SQL Server Express LocalDB in a GitHub Workflow

In this post, let's see how you can install SQL Server Express LocalDB in GitHub Workflows. This is useful when you want to run integration tests in a Workflow. 

Technically you can use this approach anywhere as long as the Agent is Windows, as this is just a set of PowerShell commands.

name: Build and deploy
 
on:
  push:
    branches:
      - main
  workflow_dispatch:
 
jobs:
  build:
    runs-on: windows-latest
 
    steps:
      - uses: actions/checkout@v2
 
      - ...
 
      - name: Install MSSQLLocalDB
        run: |
            Import-Module BitsTransfer
            Start-BitsTransfer `
               -Source https://download.microsoft.com/download/3/8/d/38de7036-2433-4207-8eae-06e247e17b25/SqlLocalDB.msi `
               -Destination SqlLocalDB.msi
            Start-Process `
               -FilePath "SqlLocalDB.msi" `
               -ArgumentList "/qn", "/norestart", "/l*v SqlLocalDBInstall.log", "IACCEPTSQLLOCALDBLICENSETERMS=YES" `
               -Wait
            sqlcmd -l 60 -S "(LocalDb)\MSSQLLocalDB" -Q "SELECT @@VERSION;"

First, we are importing the BITS (Background Intelligent Transfer Management) module, and downloading the  SqlLocalDB.msi. Then we are doing a silent install and the last command is to test the connectivity to the instance.

The specified link for SqlLocalDB.msi is for SQL Server 2022. If you want to use SQL Server 2019, you can this link: https://download.microsoft.com/download/7/c/1/7c14e92e-bdcb-4f89-b7cf-93543e7112d1/SqlLocalDB.msi

Hope this helps.

Happy Coding.

Regards,
Jaliya

Custom EF Core Function to Use Transact-SQL AT TIME ZONE

In this post, let's see how we can write a Custom EF Core Function to Use Transact-SQL AT TIME ZONE.

Consider the below example. I have the following Order entity in my DbContext.

public class Order
{
    public int Id { get; set; }
 
    public DateTimeOffset OrderDate { get; set; }
}

Now I have the following Extension method to Convert a given DateTimeOffset to a given TimeZone.

public static class DateTimeExtensions
{
    public static DateTimeOffset ConvertToTimeZone(this DateTimeOffset dateTimeOffset, string timeZone)
    {
        TimeZoneInfo timeZoneInfo = TimeZoneInfo.FindSystemTimeZoneById(timeZone);
        DateTime datetime = TimeZoneInfo.ConvertTimeFromUtc(dateTimeOffset.UtcDateTime, timeZoneInfo);
 
        return new DateTimeOffset(datetime, timeZoneInfo.GetUtcOffset(datetime));
    }
}

Now consider the below query.

var timeZone = "Eastern Standard Time";
 
var orders = await context.Orders
    .Select(x => new
    {
        OrderDate = x.OrderDate,
        ConvertedOrderDate = x.OrderDate.ConvertToTimeZone(timeZone)
    })
    .ToListAsync();

Note for ConvertedOrderDate, I am using the above CLR extension method. 

And when I run this query, the generated SQL statement is going to look like below.

SELECT [o].[OrderDate]
FROM [Orders] AS [o]

And with the below result.

OrderDate                     | ConvertedOrderDate
------------------------------| ------------------------------
20/02/2022 12:00:00 am +00:00 | 19/02/2022 7:00:00 pm -05:00

In the SQL statements EF generated, you can see it doesn't contain anything on ConvertedOrderDate or TimeZone conversion. That's because EF can't translate my CLR extension method into SQL statements. And if we have used this extension method in a Where statement (instead of Select), EF is going to loudly throw an error saying "Translation of method 'DateTimeExtensions.ConvertToTimeZone' failed". So here basically the TimeZone conversion was done In Memory.

And for these kinds of scenarios, we can use this feature in EF, where we can say how a method should get translated into SQL.

First, I am creating a static class with the following method.

public static class EfFunctions
{
    public static DateTimeOffset ConvertToTimeZone(DateTimeOffset dateTimeOffset, [NotParameterized] string timeZone)
        => throw new NotImplementedException("This method should be implemented in EF.");
}

We don't provide any implementation here.

Now we need to instruct EF on how to translate this method.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    MethodInfo methodInfo = typeof(EfFunctions).GetMethod(nameof(EfFunctions.ConvertToTimeZone));
    modelBuilder
        .HasDbFunction(methodInfo)
        .HasTranslation(x =>
        {
            ColumnExpression columnExpression = x.First() as ColumnExpression;
 
            SqlConstantExpression timeZoneExpression = x.Skip(1).First() as SqlConstantExpression;
 
            string timeZoneLiteralValue = timeZoneExpression.TypeMapping.GenerateSqlLiteral(timeZoneExpression.Value);
 
            SqlFragmentExpression valueExpression =
                new($"{columnExpression.Table.Alias}.{columnExpression.Name} AT TIME ZONE {timeZoneLiteralValue} AS DATETIMEOFFSET");
 
            return new SqlFunctionExpression(
                "CAST",
                new List<SqlExpression>() { valueExpression },
                false,
                new List<bool>() { false, false, false },
                typeof(DateTimeOffset),
                null
            );
    });
}

Here I am using Transact-SQL CAST and AT TIME ZONE to generate the proper SQL statements for TimeZone conversion.

And now I am changing my query as follows.

var timeZone = "Eastern Standard Time";
 
var orders = await context.Orders
    .Select(x => new
    {
        OrderDate = x.OrderDate,
        ConvertedOrderDate = EfFunctions.ConvertToTimeZone(x.OrderDate, timeZone)
    })
    .ToListAsync();

This time, for ConvertedOrderDate, I am using the EF Function we just wrote. 

And now when we execute the query, EF is creating the following SQL statement.

SELECT [o].[OrderDate], CAST(o.OrderDate AT TIME ZONE N'Eastern Standard Time' AS DATETIMEOFFSET) AS [ConvertedOrderDate]
FROM [Orders] AS [o]

With the following result as above.

OrderDate                     | ConvertedOrderDate
------------------------------| ------------------------------
20/02/2022 12:00:00 am +00:00 | 19/02/2022 7:00:00 pm -05:00

Hope this helps.

Read More: 

   User-defined function mapping

Happy Coding.

Regards,

Jaliya

Azure Automation Runbook: Execute SQL Scripts and Capture Print Statements

In my last post, I wrote about how we can execute SQL Scripts on an Azure SQL Database with Managed Identities using an Azure Automation Runbook. In this post, let's see how we can Capture the Print Statements in the SQL script, so we can output those.

Consider the following Stored Procedure.

CREATE OR ALTER PROC dbo.TestSelectStoredProcedure 
(
    @Param1 INT,
    @Param2 VARCHAR(100) 
)
AS
BEGIN
PRINT CONCAT('Param1: ', @Param1)
PRINT CONCAT('Param2: ', @Param2)
END

I need to execute this script and capture the Print messages, so I can output those. In this post, I am not going to explain how we can execute a SQL script from an Azure Automation Runbook, (for that you can read this post: Azure Automation Runbook: Execute SQL Scripts with Managed Identities).

My Runbook is PowerShell 7.1 (preview). This approach would still work if you are using PowerShell 5.1 Runtime.

Now to capture the messages the SP is printing, we need to register SqlInfoMessageEventHandler to the SqlConnection as follows.

# Reading from variables
$AzureSqlServer = Get-AutomationVariable -Name "AzureSqlServer"
$ApplicationDatabase = Get-AutomationVariable -Name "ApplicationDatabase"
$ClientId = Get-AutomationVariable -Name "AzureSqlManagedIdentityId"
 
# Getting AccessToken for User assigned Managed Identity
# Note: Query Parameter now includes the client_Id
$Resource = "https://database.windows.net/"
$QueryParameter = "?resource=$Resource&client_Id=$ClientId"
$Url = $env:IDENTITY_ENDPOINT + $QueryParameter
$Url = $env:IDENTITY_ENDPOINT + $QueryParameter
$Headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]" 
$Headers.Add("X-IDENTITY-HEADER", $env:IDENTITY_HEADER) 
$Headers.Add("Metadata", "True") 
$Content =[System.Text.Encoding]::Default.GetString((Invoke-WebRequest `
	-UseBasicParsing `
	-Uri $Url `
	-Method 'GET' `
	-Headers $Headers).RawContentStream.ToArray()) | ConvertFrom-Json 
$AccessToken = $Content.access_token 
 
# PowerShell/ADO.NET
$SqlConnection = New-Object System.Data.SqlClient.SQLConnection  
$SqlConnection.ConnectionString = "Server=$AzureSqlServer;Initial Catalog=$ApplicationDatabase;Connect Timeout=30" 
$SqlConnection.AccessToken = $AccessToken 
$Events = new-object System.Collections.Generic.List[Object]
$Handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] { param($sender, $event) $Events.Add($event) }
$SqlConnection.add_InfoMessage($Handler); 
$SqlConnection.FireInfoMessageEventOnUserErrors = $true;
$SqlConnection.Open();
$SqlCommand = $SqlConnection.CreateCommand()
$SqlCommand.CommandText = "exec dbo.TestStoredProcedure @Param1=100, @Param2='Hello World'"
$SqlCommand.ExecuteNonQuery() | Out-Null
ForEach($event in $Events)
{
    Write-Output $event.Message
}
$SqlConnection.Close();

And now when I test this Runbook, I am seeing the following output as expected.

SQL Script Print Messages Being Outputted

Hope this helps.

Happy Coding.

Regards,
Jaliya