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
– Zein Makki, Commented Aug 15, 2016 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
– Smilley, Commented Aug 15, 2016 at 10:18

3615 · Accepted Answer · 2016-08-15 10:19:22Z

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, 2016 at 10:19

3615

3,8853 gold badges22 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Smilley Over a year ago

Exactly what I needed. Thanks.

Smilley Over a year ago

As @CodeCaster wrote that doesn't work as I can only use an entity class T in a DbSet<T> once per DbContext. Finally had to introduce field Currency to my class.

3615 Over a year ago

@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.

Community · Accepted Answer · 2017-05-23 12:24:48Z

1

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");

edited May 23, 2017 at 12:24

CommunityBot

11 silver badge

answered Aug 15, 2016 at 10:48

CodeCaster

153k24 gold badges237 silver badges287 bronze badges

Comments

Zein Makki · Accepted Answer · 2016-08-15 10:14:47Z

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();

Collectives™ on Stack Overflow

How to use dynamic DbSet in Entity Framework?

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related