EF Core - Pulling Data From Two Identical Tables

Question

TLDR: I have a two tables with identical definitions, and I need to pull from one table or the other depending on some switch.

For example, assume a table with a bunch of "generic" FooItem and a table with "Fancy" FooItem... and the only difference between the two tables is the names, SIMPLE_FOO and FANCY_FOO

Depending on a boolean value, like GetFancy == true, I should read from FANCY_FOO, otherwise, SIMPLE_FOO.

This gets a complicated in the DbContext. I can't have more than one DbSet<FooItem> in my context, and I can't dynamically "inject" the table name in the ModelBuilder.Entity<FooItem>(e => e.ToView("pickOne")) ... and I don't want to duplicate an entire dbContext just to have a different table name in there.

I'm sure the solution is simple, but I'm just not seeing it. Any assistance is appreciated.

EDIT: I cannot change the database. It's a dumb way to do it, but that's the way they did it and I have to live with it.

Answer 1

The easiest thing to do, in my opinion, is to leverage the .FromSql() method and use that to choose which table:

var foos = GetFancy
  ? dbContext.FromSql(select * from FANCY_FOO)
  : dbContext.FromSql(select * from SIMPLE_FOO)

return foos.Where(/* etc /*)