In EF Core, querying with FromSqlInterpolated() method on an entity that has an OwnedEntity doesn't work

Question

I have some complex entities in my context.

DbSet<EntityA> AEntities { get; set; }
DbSet<EntityB> BEntities { get; set; }

where EntityA inherits EntityB (it's a superset of it). There is a column Discriminator that was created by Entity Framework to distinguish between the 2. Up to here, no problem.

EntityA also has an Owned Type:

modelBuilder.Entity<EntityA>().OwnsOne(p => p.OwnedThing,
                ot =>
                {
                    // ot......;
                    ot.ToTable("SectionTrailInfo");
                });

As long as I use only EF to query the database, it's still all right.

But then for performance reasons I need to query an BEntities with a SQL Stored Proc.

// ...
return await BEntities.FromSqlInterpolated($"EXEC GetManyRecords @Id = {id}").ToListAsync();

That also worked before introducing the Owned Entity in EntityA. Now I get a 'FromSqlRaw' or 'FromSqlInterpolated' was called with non-composable SQL and with a query composing over it. error. The error is thrown as soon as I try the .ToQueryString() method.

I have tried modifying the Stored Proc to Left Join the Owned Type but it doesn't change a thing. In fact the query is never formed and never sent to the DB server. So I have to tell EF Core in a way or another how to deal with the Owned Type (that is not even part of this Entity, but a related one).

My only other option is to re-design completely my schema to removed the Owned Type but I am hoping I can avoid that.

Thanks in advance.

Answer 1

As kindly pointed out by @IvanStoev, the way to go is to use a Table-Valued-Function instead of a Stored Proc, in SQL.