C#•3mo ago

EF Core - Joining Two Similar Tables Before In-Memory-Buffering Into One DTO

Because of how Stripe's API stores two specific objects (Subscription and SubscriptionSchedule), I have to have two EF Core models for those. However, they essentially have almost all the same columns. I want to present these as a single data set to my clients - orderable and sortable as if they were just one data set. How can I query both of those models in my DbContext and then combine them into a single Queryable result set as a SubscriptionDto - without triggering the in-memory buffering. I want to be able to order them/paginate them later on as a single result set so I don't want them to be buffered into memory until they are combined into a single Dto.

25 Replies

mg•3mo ago

There are a few options If inheritance is appropriate you could use that i.e. just have one table for both entities Although it's likely that that's not appropriate What I would probably do is have an entity for what you want to query and use FromSql() and write the query myself Though I'm not sure if that would be optimal

nox7•3mo ago

I'm not entirely sure I understand how I would do this to get the desired affect (the queries all happening on SQL and not being buffered). I am also assuming .Concat() on two IQueryables would trigger in-memory buffers; because that was my first idea. I was currently doing

var subSchedules = DbContext.SubscriptionSchedules.Select(new Dto(){});

var subs = DbContext.Subscriptions.Select(new Dto(){});

var subSchedules = DbContext.SubscriptionSchedules.Select(new Dto(){});

var subs = DbContext.Subscriptions.Select(new Dto(){});

And then using .Contact() on them to get one result set.

D.Mentia•3mo ago

If it's still an IQueryable then it's not materialized from the db yet, that should work

mg•3mo ago

You can create your MySubscription entity and then configure it with ToSqlQuery() so that it's queried from a query you specify instead of creating a new table for it

nox7•3mo ago

Oh, if that's the case then that'll make things simpler. @D.Mentia

D.Mentia•3mo ago

that is two roundtrips though, you might could get around that with some clever querying on a navigation prop that assumedly Subscription has, but it's not really a big deal

mg•3mo ago

e.g.

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

public class MyEntityTypeConfiguration : IEntityTypeConfiguration<MyEntity>
{
  public void Configure(EntityTypeBuilder<MyEntity> builder)
  {
    builder.ToSqlQuery("SELECT * FROM MyEntities");
  }
}

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

public class MyEntityTypeConfiguration : IEntityTypeConfiguration<MyEntity>
{
  public void Configure(EntityTypeBuilder<MyEntity> builder)
  {
    builder.ToSqlQuery("SELECT * FROM MyEntities");
  }
}

nox7•3mo ago

Thanks guys - this information will give me enough to go off of to make the right decision for this data set.

mg•3mo ago

Looking at Stripe's API it looks like those are dependent entities right?

nox7•3mo ago

Not entirely. The SubscriptionSchedule exists first, and when the first renewal period hits (SubscriptionSchedule is used to schedule a Subscription object for the future), then a Subscription is created and contains pretty much the same data as the SubscriptionSchedule it spawned from. However, I want my clients to see "Subscriptions" seamlessly and not have to worry about if one is a schedule for the future or an actual Subscription yet.

mg•3mo ago

Ah I see

nox7•3mo ago

It's an unnecessary complexity in Stripe's API, but it's how they've scaled and remained backwards compatible. Most payment processors would just have this "scheduling" data as columns in the Subscription object; but they introduced all this "scheduling" stuff later. An unfortunate reality.

mg•3mo ago

lmao i'd kill to be using stripe right now

D.Mentia•3mo ago

I mean that does sound like SubscriptionSchedule could inherit from Schedule, and EF can make that a single table in the database while still exposing two separate 'tables' for you to query (or vice versa)

mg•3mo ago

the powers that be are making me use an api that 404's on ratelimit hits

nox7•3mo ago

That's really interesting, actually. I'll look into this.

mg•3mo ago

But a schedule need not have a subscription associated with it, right?

nox7•3mo ago

Yeah, that's right. It will eventually, just doesn't always have one yet. Schedule is the egg before the chicken

mg•3mo ago

Yeah I'm not sure inheritance would be appropriate here Because a schedule has both its own identifier and an identifier for the subscription it manages

Core•3mo ago

Hello, what you are looking for is called table-per-concrete-type (TPC), it is available from .NET 7 and above

Core•3mo ago

https://learn.microsoft.com/en-us/ef/core/modeling/inheritance#table-per-concrete-type-configuration

Inheritance - EF Core

How to configure entity type inheritance using Entity Framework Core

D.Mentia•3mo ago

yup, that's usually the one I go with with inheritance. If the entities work well with inheritance, anyway I don't know if I'd recommend making some fake third entity with some properties null depending on which type it really is, just to make the inheritance work, but that's an option if necessary

Core•3mo ago

TPC makes it possible to use the base class to query all the tables at once with a single query

Core•3mo ago

Here is a nice and straightforward example: https://www.learnentityframeworkcore.com/inheritance/table-per-concrete

Table Per Concrete Inheritance

EF Core support for inheritance uses the Table Per Concrete pattern

Core•3mo ago

It would look like this:

c#
public abstract class BaseSchedule
{
    public int Id { get; set; }
    public string? Name { get; set; }
}
public class Schedule: BaseSchedule
{

}
public class SubscriptionSchedule : BaseSchedule
{
    public string? SomeOtherField{ get; set; } 
}

c#
public abstract class BaseSchedule
{
    public int Id { get; set; }
    public string? Name { get; set; }
}
public class Schedule: BaseSchedule
{

}
public class SubscriptionSchedule : BaseSchedule
{
    public string? SomeOtherField{ get; set; } 
}

c#
public class ScheduleDbContext : DbContext
{
    public DbSet<BaseSchedule> BaseSchedule{ get; set; }
    public DbSet<Schedule> Schedule { get; set; }
    public DbSet<SubscriptionSchedule> SubscriptionSchedule { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Data Source=(localdb)\ProjectsV13;Initial Catalog=AnimalsDb;");
    }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<BaseSchedule>().UseTpcMappingStrategy();
        modelBuilder.Entity<Schedule>();
        modelBuilder.Entity<SubscriptionSchedule >();
    }
}

c#
public class ScheduleDbContext : DbContext
{
    public DbSet<BaseSchedule> BaseSchedule{ get; set; }
    public DbSet<Schedule> Schedule { get; set; }
    public DbSet<SubscriptionSchedule> SubscriptionSchedule { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Data Source=(localdb)\ProjectsV13;Initial Catalog=AnimalsDb;");
    }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<BaseSchedule>().UseTpcMappingStrategy();
        modelBuilder.Entity<Schedule>();
        modelBuilder.Entity<SubscriptionSchedule >();
    }
}

This is how your configuration would look like. You would have one DbSet for each type, even for the BaseSchedule. Keep an eye one BaseSchedule, it is abstract, so no table will eb created for it. But it can be used to query the other 2 tables at once, in the background it will do a UNION ALL operation.

Gaming

Programming

EF Core - Joining Two Similar Tables Before In-Memory-Buffering Into One DTO