How to configure navigation property for custom SQL in EF Core 5

Disclaimer: What are you asking is not supported naturally by EF Core 5.0, hence the provided workaround most likely will break in future EF Core versions. Use it on your own risk, or use what is supported (mapping to real database view containing the desired SQL, as mentioned by other people).

Now, the problems. First, the entity type you want to map to SQL and also use in relationship cannot be keyless. It's simply because currently keyless entity types

Only support a subset of navigation mapping capabilities, specifically:

• They may never act as the principal end of a relationship.
• They may not have navigations to owned entities
• They can only contain reference navigation properties pointing to regular entities.
• Entities cannot contain navigation properties to keyless entity types.

In your case, Customer is violating the last rule by defining navigation property to keyless entity. But without it you won't be able to use Include, which is the end goal of all that.

There is no workaround for that limitation. Even if with some hackery you map the relationship and get correct SQL translation, still the navigation property won't be loaded because all EF Core related data loading methods rely on change tracking, and it requires entities with keys.

So, the entity must be "normal" (with key). There is no problem with that since the query has unique column which defines one-to-one relationship. However this hits another current EF Core limitation - you get NotImplemented exception for normal entities mapped to SqlQuery during the model finalization. Unfortunately this is inside static function used by many places inside the relational model finalization, which is also a static method, so virtually it's not possible to intercept and fix it from outside.

Once you know the problems (what is supported and what is not), here is the workaround. The supported mapping is normal entity to view. So we'll use that (ToView instead of failing ToSqlQuery), but instead of name will provide the SQL enclosed with () to be able to recognize and extract it from the associated EF Core metadata. Note that EF Core does not validate/care what are you providing them as names in ToTable and ToView methods - just wheter they are null or not.

Then we need to plug into EF Core query processing pipeline and replace the "view name" with the actual SQL.

Following is the implementation of the above idea (put it in some code file inside your EF Core project):

namespace Microsoft.EntityFrameworkCore
{
    using Metadata.Builders;
    using Query;

    public static class InlineSqlViewSupport
    {
        public static DbContextOptionsBuilder AddInlineSqlViewSupport(this DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder.ReplaceService<ISqlExpressionFactory, CustomSqlExpressionFactory>();

        public static EntityTypeBuilder<TEntity> ToInlineView<TEntity>(this EntityTypeBuilder<TEntity> entityTypeBuilder, string sql)
            where TEntity : class => entityTypeBuilder.ToView($"({sql})");
    }
}

namespace Microsoft.EntityFrameworkCore.Query
{
    using System.Linq.Expressions;
    using Metadata;
    using SqlExpressions;

    public class CustomSqlExpressionFactory : SqlExpressionFactory
    {
        public override SelectExpression Select(IEntityType entityType)
        {
            var viewName = entityType.GetViewName();
            if (viewName != null && viewName.StartsWith("(") && viewName.EndsWith(")"))
            {
                var sql = viewName.Substring(1, viewName.Length - 2);
                return Select(entityType, new FromSqlExpression("q", sql, NoArgs));
            }
            return base.Select(entityType);
        }

        private static readonly Expression NoArgs = Expression.Constant(new object[0]);

        public CustomSqlExpressionFactory(SqlExpressionFactoryDependencies dependencies) : base(dependencies) { }
    }
}

First two methods are just for convenience - one for adding the necessary plumbing and one for encoding the sql inside the name. The actual work is inside the third class which replaces one of the standard EF Core services, intercepts the Select method which is responsinble for table/view/TVF expression mapping, and converts the special view names to SQL queries.

With these helpers in hand, you can use your sample model and DbSets as is. All you need is to add the following to your derived DbContext class:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    // ...
    optionsBuilder.AddInlineSqlViewSupport(); // <--
}

and use the following fluent configuration:

modelBuilder.Entity<MaxOrder>(builder =>
{
    builder.HasKey(e => e.CustomerId);
    builder.ToInlineView(
        @"SELECT CustomerId, SUM(Amount) AS TotalAmount 
          FROM Orders O 
          WHERE Id = (SELECT MAX(Id) 
                      FROM Orders 
                      WHERE CustomerId = O.CustomerId)
        GROUP BY CustomerId");
});

Now

var test = dbContext.Customers
    .Include(x => x.MaxOrder)
    .ToList();

will run w/o errors and generate SQL like

SELECT [c].[Id], [c].[Name], [q].[CustomerId], [q].[TotalAmount]
FROM [Customers] AS [c]
LEFT JOIN (
    SELECT CustomerId, SUM(Amount) AS TotalAmount 
                          FROM Orders O 
                          WHERE Id = (SELECT MAX(Id) 
                                      FROM Orders 
                                      WHERE CustomerId = O.CustomerId)
                        GROUP BY CustomerId
) AS [q] ON [c].[Id] = [q].[CustomerId]

and more importantly, will correctly populate the Customer.MaxOrder property. Mission done :)

I was unable to get it working using ToSqlQuery as I received a NotImplementedException: SqlQuery exception when setting up the relationship between MaxOrder and Customer. Using a view, it worked without issue. If you are able to create a view, I suggest you do that.

MaxOrder needs a key, which is the FK to Customer and a 1:1 relationship is defined for MaxOrder:Customer. Replace the .ToView("vwMaxOrder") call with .ToSqlQuery(<body of view>) to reproduce the exception described above.

public class TestDbContext : DbContext
{
    public TestDbContext(DbContextOptions<TestDbContext> options)
        : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>()
            .ToTable("Customer");

        modelBuilder.Entity<Order>()
            .ToTable("Order")
            .HasOne(o => o.Customer)
            .WithMany(c => c.Orders)
            .IsRequired();

        modelBuilder.Entity<OrderItem>()
            .ToTable("OrderItem")
            .HasOne(oi => oi.Order)
            .WithMany(o => o.Items)
            .IsRequired();

        modelBuilder.Entity<OrderItem>()
            .HasOne(oi => oi.Item)
            .WithMany()
            .IsRequired();

        modelBuilder.Entity<Item>()
            .ToTable("Item");

        modelBuilder.Entity<MaxOrder>()
            .ToView("vwMaxOrder")
            .HasKey(mo => mo.CustomerId);

        modelBuilder.Entity<MaxOrder>()
            .HasOne(mo => mo.Customer)
            .WithOne(c => c.MaxOrder)
            .HasForeignKey<MaxOrder>(mo => mo.CustomerId);
    }

    public DbSet<Customer> Customers { get; set; }
    public DbSet<Order> Orders { get; set; }
    public DbSet<Item> Items { get; set; }
}

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<Order> Orders { get; set; }
    public MaxOrder MaxOrder { get; set; }
}

public class Order
{
    public int Id { get; set; }
    public Customer Customer { get; set; }
    public ICollection<OrderItem> Items { get; set; }
    public DateTime Created { get; set; }
}

public class OrderItem
{
    public int Id { get; set; }
    public Order Order { get; set; }
    public Item Item { get; set; }
    public int Quantity { get; set; }
    public decimal Price { get; set; }
}

public class Item
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class MaxOrder
{
    public int CustomerId { get; set; }
    public Customer Customer { get; set; }
    public decimal Value { get; set; }
}

View:

CREATE VIEW [dbo].[vwMaxOrder]
    AS 
select
    c.Id CustomerId
    , Value = MAX(OrderTotal.Value)
from
    Customer c
    inner join [Order] o
        on c.Id = o.CustomerId
    inner join
        (
            select
                oi.OrderId
                , Value = SUM(oi.Price * oi.Quantity)
            from
                OrderItem oi
            group by
                oi.OrderId
        ) OrderTotal
            on o.Id = OrderTotal.OrderId
group by
    c.Id

Demo program:

class Program
{
    static void Main(string[] args)
    {
        using var db = CreateDbContext();

        //AddCustomers(db);
        //AddItems(db);
        //AddOrders(db);
        //AddOrderItems(db);

        var customers = db.Customers
            .Include(c => c.Orders)
                .ThenInclude(o => o.Items)
            .Include(c => c.MaxOrder)
            .ToArray();

        foreach(var customer in customers)
        {
            Console.WriteLine("----------------------");
            Console.WriteLine($"Customer ID {customer.Id} max order amount: {customer.MaxOrder.Value}");
            
            foreach (var order in customer.Orders)
            {
                var total = order.Items.Sum(oi => oi.Price * oi.Quantity);

                Console.WriteLine($"Order ID {order.Id} total: {total}");
            }
        }
    }

    static TestDbContext CreateDbContext()
    {
        var opts = new DbContextOptionsBuilder<TestDbContext>()
            .UseSqlServer("Data Source=(localdb)\\MSSQLLocalDB;Database=DemoDB;Trusted_Connection=True;")
            .Options;

        return new TestDbContext(opts);
    }

    static void AddCustomers(TestDbContext db)
    {
        db.Customers.Add(new Customer()
        {
            Name = "Customer A"
        });

        db.Customers.Add(new Customer()
        {
            Name = "Customer B"
        });

        db.SaveChanges();
    }

    static void AddItems(TestDbContext db)
    {
        db.Items.Add(new Item()
        {
            Name = "Item A",
        });

        db.Items.Add(new Item()
        {
            Name = "Item B",
        });

        db.SaveChanges();
    }

    static void AddOrders(TestDbContext db)
    {
        db.Orders.Add(new Order()
        {
            Created = DateTime.Now,
            Customer = db.Customers.First(),
        });

        db.Orders.Add(new Order()
        {
            Created = DateTime.Now.AddDays(-1),
            Customer = db.Customers.First(),
        });

        db.Orders.Add(new Order()
        {
            Created = DateTime.Now.AddDays(-2),
            Customer = db.Customers.Skip(1).First(),
        });

        db.Orders.Add(new Order()
        {
            Created = DateTime.Now.AddDays(-3),
            Customer = db.Customers.Skip(1).First(),
        });

        db.SaveChanges();
    }

    static void AddOrderItems(TestDbContext db)
    {
        var orders = db.Orders.Include(o => o.Items).ToArray();
        var items = db.Items.ToArray();

        for(var i = 0; i < orders.Length; ++i)
        {
            var order = orders[i];

            for(var j = 0; j < items.Length; ++j)
            {
                order.Items.Add(new OrderItem()
                {
                    Item = items[j],
                    Quantity = i + j + 1,
                    Price = 20 - i * 2 - j * 3,
                });
            }
        }

        db.SaveChanges();
    }
}

Results:

----------------------
Customer ID 1 max order amount: 81.00
Order ID 1 total: 54.00
Order ID 2 total: 81.00
----------------------
Customer ID 2 max order amount: 111.00
Order ID 3 total: 100.00
Order ID 4 total: 111.00

I would propose more universal and easy to maintain solution:

public static class Associations
{
    [Expandable(nameof(MaxOrderImpl)]
    public static MaxOrder MaxOrder(this Customer customer)
        => throw new NotImplementedException();

    private static Expression<Func<Customer, MaxOrder>> MaxOrderImpl()
    {
        return c => c.Orders.OrderByDescending(o => o.Id)
            .Selec(o => new MaxOrder{ CustomerId = o.CustomerId, TotalAmount = o.Amount })
            .FirstOrDefault();
    }
}

Then you can use this extension in queries:

dbcontext.Customers.Select(x => new CustomerDto 
{
    Id = x.Id,
    Name = x.Name,
    MaxOrder = x.MaxOrder()
});

Queries are writtent in LINQ, extensions can be added easily and reused in other queries.

Such solution requires LINQKit and configuring your context:

builder
    .UseSqlServer(connectionString)
    .WithExpressionExpanding(); // enabling LINQKit extension