How to use dynamic DbSet in Entity Framework?

Question

coluld you please be so kind to tell me how do I choose DbSet depending on string variable? What I have is the following:

public class DataContext : DbContext
{
    public DataContext() : base("myDb") { }

    public DbSet<Entry> RurEntries { get; set; }
    public DbSet<Entry> UsdEntries { get; set; }
    public DbSet<Entry> EurEntries { get; set; }
}

There are 3 tables for each currency: Rur,Usd,Eur. All have same structure. There is string variable named CurrentCurrency which is changed from UI and may be one of 3 currencies. In my previous code without Entity Framework I had code that read db with pure sql, someting like:

string sqlQuery = "Select * from " + CurrentCurrency

Now I decided to rewrite code with Entity Framework and faced that problem. Any answer will be appreciated. Thanks in advance.

Why don't you just introduce a flag to the table instead of creating three tables ? — Zein Makki, Aug 15 '16 at 10:13
All 3 tables have unique_id field, which I receive from another software. I used unique flag on that column and it might be a problem if i put all entries to the same table — Smilley, Aug 15 '16 at 10:18

score 2 · Answer 1 · answered Aug 15 '16 at 10:19

2

You can simply switch on your CurrentCurrency string to get set that you need:

 var db = new DataContext();
        var CurrentCurrency = "RUR";
        DbSet<Entry> set = null;
        switch (CurrentCurrency) {
            case "RUR":
                set = db.RurEntries;
            break;
            case "EUR":
                set = db.EurEntries;
            break;
            case "USD":
                set = db.UsdEntries;
            break;
            default:
                throw new Exception();
        }
        var res = set.ToList();

answered Aug 15 '16 at 10:19

3615

3,787
3
20
35

Exactly what I needed. Thanks. – Smilley Aug 15 '16 at 10:24
As @CodeCaster wrote that doesn't work as I can only use an entity class T in a DbSet once per DbContext. Finally had to introduce field Currency to my class. – Smilley Aug 16 '16 at 08:02
@Smilley I think that should work if you use inheritance and Entry would be just an abstract base class. Anyway, solution suggested by CodeCaster seems to be more straight forward. – 3615 Aug 16 '16 at 08:29

score 1 · Accepted Answer · edited May 23 '17 at 12:24

You can only use an entity class T in a DbSet<T> once per DbContext. Your code won't run. See also Entity Framework 6 Creating Two table from the same entity object.

Given your comment:

All 3 tables have unique_id field, which I receive from another software. I used unique flag on that column and it might be a problem if i put all entries to the same table

You just need a composite primary key, comprised of Currency and ExternalId, as explained in Composite Key with EF 4.1 Code First:

public class Entry
{
    [Key]
    [Column(Order = 0)]
    public string Currency { get; set; }

    [Key]
    [Column(Order = 1)]
    public string ExternalId { get; set; }
}

Then you can read the "EUR" rows like this:

var eurRows = dbContext.Entries.Where(e => e.Currency == "EUR");

score 0 · Answer 3 · answered Aug 15 '16 at 10:14

You can execute raw SQL queries in Entity Framework. Like this:

var curs = context.Database.SqlQuery<Entry>("Select * from " + CurrentCurrency").ToList();

You can also make that more neat and create a stored procedure, where you send a parameter and execute a SELECT statement based on that parameter. Then using Entity framework, you call that procedure an map the results to a List<Entry>.

However, and as I stated in the comments I do personally prefer to have only one table with a CurrenyCode column. Instead of having 3 tables with the exact same structure but with different data. Then you can query like this:

var curs = var curs = context.Currencies.Where(x=> x.CurrencyCode = CurrentCurrency)
                                        .ToList();

How to use dynamic DbSet in Entity Framework?

3 Answers3