Get multiple tables using Entity Framework Core

Question

I need to populate many selectlists and i'm doing it like this

     public void OnGet()
    {
        ViewData["CelulaId"] = new SelectList(_context.Celulas, "Id", "Nome");
        ViewData["UAPId"] = new SelectList(_context.UAP, "Id", "Nome");
        ViewData["ReferenciaId"] = new SelectList(_context.Referencias, "Id", "Nome");
        ViewData["CelulaTipoId"] = new SelectList(_context.CelulaTipos, "Id", "Nome");
    }

I assume this is a poor approach because of the consecutive calls. Is there a way to do all in one go?

user4864425 · Accepted Answer · 2019-06-19 12:34:56Z

2

You can construct a query that combines all id and name fields in one query, which you can filter afterwards if you add a discriminator as well. I don't have time to test the code, but it could be something like this:

var res = _context.Celulas.Select(c => new { Id, Nome, Discriminator = "Celulas"})
    .Union(_context.UAP.Select(c => new { Id, Nome, Discriminator = "UAP"}))
    .Union(_context.Referencias.Select(c => new { Id, Nome, Discriminator = "Referencias"}))
    .Union(_context.CelulaTipos.Select(c => new { Id, Nome, Discriminator = "CelulaTipos"}));

And then pass it to the viewmodel:

var viewModel = new ViewModel
{
    CelulaId = new SelectList(res.Where(r => r.Discriminator == "Celulas"), "Id", "Nome"),
    UAPId = new SelectList(res.Where(r => r.Discriminator == "UAP"), "Id", "Nome"),
    ReferenciaId = new SelectList(res.Where(r => r.Discriminator == "Referencias"), "Id", "Nome"),
    CelulaTipoId = new SelectList(res.Where(r => r.Discriminator == "CelulaTipos"), "Id", "Nome")
};

But I doubt this is really faster than what you already have. It only adds complexity. An alternative may be to use enumerations so you don't have to query the database at all.

As a side note, I would not use ViewData for this. Instead add the lists to the ViewModel. I would use ViewData to communicate with parts that do not have access to the model, e.g. the title in the layout.

answered Jun 19, 2019 at 12:34

user4864425

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

4 Comments

Jackal Over a year ago

Why should i not use ViewData when it is the approach used on the tutorials? Even when scaffolding? Besides making it hard typed

user4864425 Over a year ago

You can ignore the remark about ViewData, it's not relevant to the answer and may be a matter of preference. But there are several advantages of strongly typed models. Less error-prone and it offers intellisense. Take a look at this article for comparison. As for tutorials that don't use ViewData: learnrazorpages.com/razor-pages/forms/select-lists and pluralsight.com/guides/…

user4864425 Over a year ago

Besides the ViewData, does this answer your question?

Jackal Over a year ago

Yes I suppose in a way it does althought i was expecting a more performant way

Collectives™ on Stack Overflow

Get multiple tables using Entity Framework Core

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related