Aaron Aaron - 2 months ago 7
C# Question

Is it possible to dynamically switch database table in query?

Im quite new to C# programming. I have a situation where I need to read data dynamically from database tables and I need to switch tables depending of category. Data that I read from tables is always same, just tables are different.
Is it possible to dynamically switch database table in query?

I made an abstract code to explain My thoughts:

var userPost = if(Category == "News"){ db.News } else { db.Sport}
.Include(x => x.Popular)
.Include(x => x.Popular.Category)
.Where(x => x.User.Id == userId)
.OrderByDescending(x => x.CreatedAt)
.ProjectTo<PostViewModel>(_mapper.ConfigurationProvider)
.ToPagedList(pageIndex, pageSize);


Thank You ;)

Answer

Assuming your ORM is mapping both tables to the same types, you should be able to do this:

var userPost = (Category == "News" ? db.News : db.Sport)
                .Include(x => x.Popular)
                .Include(x => x.Popular.Category)
                .Where(x => x.User.Id == userId)
                .OrderByDescending(x => x.CreatedAt)
                .ProjectTo<PostViewModel>(_mapper.ConfigurationProvider)
                .ToPagedList(pageIndex, pageSize);

Or, perhaps a bit more readable:

var table = (Category == "News" ? db.News : db.Sport);

var userPost = table
                .Include(x => x.Popular)
                .Include(x => x.Popular.Category)
                .Where(x => x.User.Id == userId)
                .OrderByDescending(x => x.CreatedAt)
                .ProjectTo<PostViewModel>(_mapper.ConfigurationProvider)
                .ToPagedList(pageIndex, pageSize);

If you have more than 2 tables you want to multiplex, you might want to split the selection logic out into another method:

internal IQueryable<ROWTYPE> SelectTable(string category)
{
    switch (category)
    {
        case "News": return db.News;
        case "Sport": return db.Sport;
        case "Arts": return db.Arts;
        default: throw new ArgumentException("Unsupported category: " + category);
    }
} 



var table = SelectTable(Category);

var userPost = table
                .Include(x => x.Popular)
                .Include(x => x.Popular.Category)
                .Where(x => x.User.Id == userId)
                .OrderByDescending(x => x.CreatedAt)
                .ProjectTo<PostViewModel>(_mapper.ConfigurationProvider)
                .ToPagedList(pageIndex, pageSize);
Comments