Problem with left Join using Entity framework

Question

I’m having a problem performing a left Join on Asp.net mvc.

I own a view that lists all screens of the system, the same are registered in the database.

I created a table of type (tab), screen and screen functions. I’m giving a left Join to rescue the data from the three tables.

public IQueryable SelectForeignKey(string idEmpresa)
{
    var resultado = from aba in _sigconEntities.Aba
                    join tela in _sigconEntities.Tela on aba.idAba equals tela.idAba
                    join telaFuncao in _sigconEntities.TelaFuncao on tela.idTela equals telaFuncao.idTela
                    where aba.idEmpresa == idEmpresa
                    select new
                    {
                        abaIdAba = aba.idAba,
                        abaNome = aba.Nome,
                        abaIdAbaSistema = aba.idAbaSistema,
                        telaIdTela = tela.idTela,
                        telaIdAba = tela.idAba,
                        telaNome = tela.Nome,
                        telaIdTelaSistema = tela.idTelaSistema,
                        telaFuncaoIdTela = telaFuncao.idTela,
                        telaFuncaoNome = telaFuncao.Nome,
                        telaFuncaoIdFuncaoSistema = telaFuncao.idFuncaoSistema
                    };

    return resultado;
}

I’m returning the data to the controller and saving in a viewbag.

ViewBag.Permissoes = _acessoApp.SelectForeignKey("s8f50f03-c064-4afa-ba5e-397236cd2b03");

To rescue the data in the view do the following.

@foreach (var item in ViewBag.Permissoes)
{
    <li> item.abaNome - item.abaIdSistema </li>
}

As I have no practice and a lot of knowledge in Asp.net mvc was what I was able to do, I don’t know if it’s the best way to do it. Below follows the code of the models of the three classes.

public partial class Aba
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage",    "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Aba()
    {
        this.Tela = new HashSet<Tela>();
    }

    public int idAba { get; set; }
    public string idEmpresa { get; set; }
    public string Nome { get; set; }
    public string idAbaSistema { get; set; }

    public virtual Empresa Empresa { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Tela> Tela { get; set; }
}

public partial class Tela
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Tela()
    {
        this.TelaFuncao = new HashSet<TelaFuncao>();
    }
    public int idTela { get; set; }
    public int idAba { get; set; }
    public string Nome { get; set; }
    public string idTelaSistema { get; set; }

    public virtual Aba Aba { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<TelaFuncao> TelaFuncao { get; set; }
}

public partial class TelaFuncao
{
    public int idTelaFuncao { get; set; }
    public int idTela { get; set; }
    public string Nome { get; set; }
    public string idFuncaoSistema { get; set; }

    public virtual Tela Tela { get; set; }
}

Answer 1

There are several things that need to be said for those who come from other development paradigms, and I believe this answer will be ideal for this.

Premises of those coming from other architectures for ASP.NET MVC and Entity Framework

First I’m going to make this list of premises and then we go to what matters, which is the solution to your question.

1. Entity Framework is neither SQL nor relational model

That is, try to "use a left Join" in the code is wrong. Entity Framework does not work like this.

First, we don’t say we have tables in the Entity Framework: we say we have entities.

Second, the selection starts from the principle of lazy load, that not everything you select will actually be used in the View, unless you, programmer, say exactly the opposite, anticipating the load.

Thirdly, LINQ even works together with the Entity Framework (because LINQ to Entities is the embryo of the Entity Framework, and it came from another project called LINQ to SQL), but avoid using. It will confuse you even more, and it will not be productive, in addition to that early loading functions, logging monitoring and settings inline are extension methods that are not present in LINQ.

From what I understand of your code, you want to select all functions of all screens, which in turn are linked to tabs. In the Entity Framework, you should view the selection of data first by the purpose of the selection (which is precisely the screen functions) and not select the tabs, then the screens and finally the functions. We learn to think like this in SQL because that’s how SQL works best, but in Entity Framework we have a paradigm shift. What I’m going to do is assemble a sentence that selects the functions, and then select the rest of the related entities.

Another thing I’m going to show you is that SelectForeignKey does not need to be implemented. Let’s take it one step at a time.

Try to type any and all filter and collection objects. Strong typing is best for performance and type safety:

public IQueryable<TelaFuncao> SelectForeignKey(string idEmpresa) { ... }

Let’s use, instead of the LINQ syntax, the extension method syntax, which is simpler and more intuitive.

public IQueryable<TelaFuncao> SelectForeignKey(string idEmpresa) 
{
    return _sigconEntities.TelaFuncao.Where(tf => tf.idEmpresa == idEmpresa);
}

That’s all it is, Gypsy?

Yeah. That alone is enough for the screen to work. And why?

Because you’ve already filled in the relationships. The Entity Framework fills in the data of the related entities for you. They:

public partial class Aba
{
    ...

    public virtual Empresa Empresa { get; set; }
    public virtual ICollection<Tela> Tela { get; set; }
}

public partial class Tela
{
    ...

    public virtual Aba Aba { get; set; }
    public virtual ICollection<TelaFuncao> TelaFuncao { get; set; }
}

public partial class TelaFuncao
{
    ...

    public virtual Tela Tela { get; set; }
}

I mean, if I select a TelaFuncao, for example, like:

var telaFuncao = _sigconEntities.TelaFuncao.FirstOrDefault();

And do this:

var tela = telaFuncao.Tela;

Tela will be completed. As well as:

var aba = telaFuncao.Tela.Aba;

Aba will also be filled.

Likewise, I can read the N screens from a tab:

var telas = aba.Telas;

And the functions of a screen:

var telaFuncoes = tela.TelaFuncoes;

This is lazy load. The framework is occupied with the load and you are occupied with the systemic logic, which is what is important. If it does not get good, you can anticipate the load.

2. Viewbags shall never contain the main data of the View

This is a common error of who is starting in ASP.NET MVC. All View awaits a type of main data, which we call Model of View. You specify this type within the View by announcing @model, which usually sits on the first line of the file. In your case:

@model IEnumerable<MeuProjeto.Models.TelaFuncao>

The Controller passes to the View the Model of View through the declaration return View(), thus:

public ActionResult MinhaSelecaoDeTelaFuncao(Guid id)
{
    var permissoes = SelectForeignKey(id).ToList();
    return View(permissoes);
}

Again, in the View to read the information, we use:

@foreach (var item in Model) { ... }

Viewbags are used to mount auxiliary elements on the screen, for example, Dropdowns, some descriptions, some messages to the user, etc. Being a dynamic object, it is dangerous to use Viewbags for everything.

3. Dynamic data writing on screen uses @

Something was missing here:

@foreach (var item in ViewBag.Permissoes)
{
    <li>@item.abaNome - @item.abaIdSistema </li>
}

How do we change the example to use Model, actually gets:

@foreach (var item in Model)
{
    <li>@item.Tela.Aba.Nome - @item.Tela.Aba.IdSistema</li>
}

4. IQueryable and IEnumerable are two different things.

I’ve said it a few times, then I’ll just summarize: IQueryable generates an SQL. IEnumerable is a result of a generation of SQL.

We will now rule out SelectForeignKey. We don’t need her anymore:

public ActionResult MinhaSelecaoDeTelaFuncao(Guid id)
{
    var permissoes = _sigconEntities.TelaFuncao.Where(tf => tf.idEmpresa == id).ToList();
    return View(permissoes);
}

I’ll make it even better: I’ll separate the two moments, the first one we have IQueryable and the second we have IEnumerable:

public ActionResult MinhaSelecaoDeTelaFuncao(Guid id)
{
    var permissoesQuery = _sigconEntities.TelaFuncao.Where(tf => tf.idEmpresa == id); // Aqui não executamos o SQL ainda
    return View(permissoes.ToList()); // Aqui executamos o SQL
}

5. If the screen becomes slow, bring the load forward

Lazy Charge doesn’t solve everything. It’s a powerful tool to write code quickly, but it’s not silver bullet.

On your screen you will want to show the permission, the name of the screen and the name of the tab, I imagine. On the lazy load, for each permission, screen and tab you write, at least two more SELECTare made: one for the screen and one for the tab.

It’ll get slow, obviously.

To anticipate the load, we use the extension method Include thus:

var permissoes = _sigconEntities.TelaFuncao
                   .Include(tf => tf.Tela.Aba)
                   .Where(tf => tf.idEmpresa == id)
                   .ToList();

If you want to see the query generated, try doing the following:

var permissoes = _sigconEntities.TelaFuncao
                   .Include(tf => tf.Tela.Aba)
                   .Where(tf => tf.idEmpresa == id);
var sql = ((System.Data.Entity.Core.Objects.ObjectQuery)permissoes)
        .ToTraceString();

6. Continue to study

What I said is the basics of the basics. A paradigm shift implies a lot of different things. Try to understand how the Scaffolding of ASP.NET MVC, see the questions and answers here of the site and, in case of questions, ask more questions.