LEFT Outer Join does not work on LINQ when 0 is the reference? null only

Asked

Viewed 160 times

2

I’m having doubts on Left Outer Join, I’m using MVC 5 C#, Net 4.5.2.

I have the following Model Fornecedor

 public class fechamentoFornecedor
    {
        public int FornecedorId { get; set; }
        public int Tipo { get; set; }
        public string FornecedorNome { get; set; }
        public bool IsEntrada { get; set; }
        public virtual ICollection<fechamentoMovimentacao> Movimentacoes { get; set; }
    }

And the Model Movimentação

  public class fechamentoMovimentacao
    {
        public int MovimentacaoId { get; set; }
        public int FornecedorId { get; set; }
        public byte Tipo { get; set; }
        public string Texto { get; set; }
        public DateTime Data { get; set; }
        public decimal Valor { get; set; }
        public bool IsEntrada { get; set; }

        public virtual fechamentoFornecedor Fornecedor { get; set; }
}

Initially by a logic of mine I tried to do:

 public virtual fechamentoFornecedor? Fornecedor

To try to show that she could be Null, didn’t work out.

So I tried to do it for the instruction Linq

var Linq = (from Movimentacao in db.fechamentoMovimentacoes
                       join Fornecedor in db.fechamentoFornecedores
                       on Movimentacao.FornecedorId equals Fornecedor.FornecedorId into output
                       from Fornecedor in output.DefaultIfEmpty()
                       where Movimentacao.Data.Year == hoje.Year && Movimentacao.Data.Month == MesNumber &&  Movimentacao.IsEntrada == IsEntrada
                       orderby Movimentacao.MovimentacaoId descending
                       select new MovimentacaoDataTable
                       {
                           MovimentacaoId = Movimentacao.MovimentacaoId,
                           Data = Movimentacao.Data,
                           Tipo = Movimentacao.Tipo,
                           FornecedorNome = Fornecedor !=null ? Fornecedor.FornecedorNome : "",
                           Texto = Movimentacao.Texto,
                           Valor = Movimentacao.Valor
                       });

From what I understand the main thing is here:

from Fornecedor in output.DefaultIfEmpty()

This query does not generate Errors, but does not bring results when there is no relation No Supplier (on Movimentacao.FornecedorId equals Fornecedor.FornecedorId )

In the Movimentacao.FornecedorId, when it has no values (relationship with Vendor), I enter the value 0 instead of null that would be the problem?

c# asp.net-mvc linq entity-framework-6 join

  • If you take out Join with vendor, the query returns something?

  • Actually this query already returns, but returns only when the supplier exists, when the Drive.Suppliersd =0 hence it does not bring this result.. and the function of LEFT Outer Join would be just this, bring results even if in the first table there

  • And why you save 0 instead of null?

  • old database, third party, long history....rs. But for sure this is a detail, I just don’t know how to do the right Word.

1 answer

1


Your model does not allow the Fornecedor be null. It would need to be so:

public class fechamentoMovimentacao
{
    public int MovimentacaoId { get; set; }
    public int? FornecedorId { get; set; } // Aqui precisa ser Nullable.
    public byte Tipo { get; set; }
    public string Texto { get; set; }
    public DateTime Data { get; set; }
    public decimal Valor { get; set; }
    public bool IsEntrada { get; set; }

    public virtual fechamentoFornecedor Fornecedor { get; set; }
}

Browser other questions tagged c# asp.net-mvc linq entity-framework-6 join

You are not signed in. Login or sign up in order to post.