Can I use Keyless Entity Types to query CHANGETABLE in Entity Framework Core?

Question

I'm using SQL Server Change Tracking and I'm trying to adapt this article from Microsoft Docs to an Entity Framework application: Work with Change Tracking.

I want to run this SQL query using Entity Framework:

SELECT
    P.*, CT.*
FROM
    dbo.Product AS P
RIGHT OUTER JOIN
    CHANGETABLE(CHANGES dbo.Product, @last_synchronization_version) AS CT
ON
    P.ProductID = CT.ProductID

This is what I've got so far:

public class Product
{
    public int ProductID { get; set; }

    // omitted dozens of other properties
}

public class ProductChange
{
    public int ProductID { get; set; }

    public Product? Product { get; set; }

    public long SYS_CHANGE_VERSION { get; set; }

    public long? SYS_CHANGE_CREATION_VERSION { get; set; }

    public char SYS_CHANGE_OPERATION { get; set; }

    public byte[]? SYS_CHANGE_COLUMNS { get; set; }

    public byte[]? SYS_CHANGE_CONTEXT { get; set; }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<ProductChange>()
        .HasNoKey()
        .ToView(null);

    base.OnModelCreating(modelBuilder);
}

long lastSynchronizationVersion = ...; // obtained as described in "Work with Change Tracking"

const string sql = @"
    SELECT
        P.*, CT.*
    FROM
        dbo.Product AS P
    RIGHT OUTER JOIN
        CHANGETABLE(CHANGES dbo.Product, {0}) AS CT
    ON
        P.ProductID = CT.ProductID";

var changes = await dbContext.Set<ProductChange>.FromSqlRaw(sql, lastSynchronizationVersion);

It does not work, because EF does not understand how P.* maps to public Product? Product { get; set; }. When I remove the Product property and remove P.* from the query, things work as expected. However, I need all of the properties, not just the ID.

Copying all of Product's properties into ProductChange and making them all nullable works, but I really don't want to resort to doing that.

In practice I will be using Change Tracking not just for products, but for dozens of entity types, which all have many properties. Having to specify each property in two places just to make Entity Framework play nice with Change Tracking is not a good idea.

Is there a way to get Keyless Entity Types to do what I want? Or do I have to use ADO.NET's ExecuteReader and manually map the result?

Answer 1

It turns out you can use relationships with navigation properties on keyless entity types, just like you can with entity types.

Configure the relationship in OnModelCreating:

modelBuilder.Entity<ProductChange>()
    .HasNoKey()
    .HasOne(x => x.Entity).WithMany().HasForeignKey(x => x.ProductID) // I added this line.
    .ToView(null);

Now you can use Include instead of manually joining tables:

const string sql = "SELECT * FROM CHANGETABLE(CHANGES dbo.Product, {0}) AS CT";

var changes = await dbContext
    .Set<ProductChange>
    .FromSqlRaw(sql, lastSynchronizationVersion)
    .Include(x => x.Entity)
    .DefaultIfEmpty() // https://stackoverflow.com/a/63006304/1185136
    .ToArrayAsync();

// it works!

Additionally (this is optional), I created base types Change and Change<TEntity> that can be inherited from easily:

public abstract class Change
{
    public long Version { get; set; }
    public long? CreationVersion { get; set; }
    public char Operation { get; set; }
    public byte[]? Columns { get; set; }
    public byte[]? Context { get; set; }
}

public abstract class Change<TEntity> : Change
    where TEntity : class
{
    public TEntity? Entity { get; set; }
}

public ProductChange : Change<Product>
{
    public int ProductID { get; set; }
}

public OrderChange : Change<Order>
{
    public int OrderID { get; set; }
}

// etc...

You'll have to configure the relationship for each derived type in OnModelCreating.

modelBuilder.Entity<ProductChange>()
    .HasOne(x => x.Entity).WithMany().HasForeignKey(x => x.ProductID);

modelBuilder.Entity<OrderChange>()
    .HasOne(x => x.Entity).WithMany().HasForeignKey(x => x.OrderID);

// etc...

You won't have to repeat HasNoKey() and ToView(null) for every entity though, add this loop instead:

foreach (var changeType in modelBuilder.Model.FindLeastDerivedEntityTypes(typeof(Change)))
{
    var builder = modelBuilder.Entity(changeType.ClrType).HasNoKey().ToView(null);

    builder.Property(nameof(Change.Version)).HasColumnName("SYS_CHANGE_VERSION");
    builder.Property(nameof(Change.CreationVersion)).HasColumnName("SYS_CHANGE_CREATION_VERSION");
    builder.Property(nameof(Change.Operation)).HasColumnName("SYS_CHANGE_OPERATION");
    builder.Property(nameof(Change.Columns)).HasColumnName("SYS_CHANGE_COLUMNS");
    builder.Property(nameof(Change.Context)).HasColumnName("SYS_CHANGE_CONTEXT");
}

If you want to, you can move the ID property to Change<TEntity>. By doing this, you can remove the ProductChange, OrderChange etc. classes. But, you'll have to specify the column name so Entity Framework Core understands the ID property from Change<Product> maps to ProductID, and the ID property from Change<Order> maps to OrderID, etc. I opted not to do this because this approach won't work if you have composite keys.