Left Join in LINQ

Asked

Viewed 468 times

2

I have the representation of 2 classes.

first class:

using System;
using System.Runtime.Serialization;

namespace WebService
{
    [DataContract]
    public class PessoaDados
    {
        private Nullable<int> id;
        private string tipo;
        private string razao_social;
        private string nome_fantasia;
        private string cpf_cnpj;
        private string rg_insc_estadual;
        private string insc_substituicao;
        private string insc_municipal;
        private DateTime? data_expedicao_rg;
        private string orgao_expedidor_rg;

        [DataMember]
        public int? Id { get => id; set => id = value; }

        [DataMember]
        public string Tipo { get => tipo; set => tipo = value; }

        [DataMember]
        public string Razao_social { get => razao_social; set => razao_social = value; }

        [DataMember]
        public string Nome_fantasia { get => nome_fantasia; set => nome_fantasia = value; }

        [DataMember]
        public string Cpf_cnpj { get => cpf_cnpj; set => cpf_cnpj = value; }

        [DataMember]
        public string Rg_insc_estadual { get => rg_insc_estadual; set => rg_insc_estadual = value; }

        [DataMember]
        public string Insc_substituicao { get => insc_substituicao; set => insc_substituicao = value; }

        [DataMember]
        public string Insc_municipal { get => insc_municipal; set => insc_municipal = value; }

        [DataMember]
        public DateTime? Data_expedicao_rg { get => data_expedicao_rg; set => data_expedicao_rg = value; }

        [DataMember]
        public string Orgao_expedidor_rg { get => orgao_expedidor_rg; set => orgao_expedidor_rg = value; }

    }
}

2nd class

using System;
using System.Runtime.Serialization;

namespace WebService
{
    [DataContract]
    public class Pessoa_enderecoDados
    {
        private Nullable<int> id;
        private int pessoa_id;
        private string logradouro;
        private int numero;
        private string complemento;
        private string ponto_referencia;
        private string cep;
        private string bairro;
        private int cidade_id;
        private string nome_contato;
        private string telefone_1;
        private string ramal_telefone_1;
        private string telefone_2;
        private string ramal_telefone_2;
        private string celular;
        private string email;

        [DataMember]
        public int? Id { get => id; set => id = value; }

        [DataMember]
        public int Pessoa_id { get => pessoa_id; set => pessoa_id = value; }

        [DataMember]
        public string Logradouro { get => logradouro; set => logradouro = value; }

        [DataMember]
        public int Numero { get => numero; set => numero = value; }

        [DataMember]
        public string Complemento { get => complemento; set => complemento = value; }

        [DataMember]
        public string Ponto_referencia { get => ponto_referencia; set => ponto_referencia = value; }

        [DataMember]
        public string Cep { get => cep; set => cep = value; }

        [DataMember]
        public string Bairro { get => bairro; set => bairro = value; }

        [DataMember]
        public int Cidade_id { get => cidade_id; set => cidade_id = value; }

        [DataMember]
        public string Nome_contato { get => nome_contato; set => nome_contato = value; }

        [DataMember]
        public string Telefone_1 { get => telefone_1; set => telefone_1 = value; }

        [DataMember]
        public string Ramal_telefone_1 { get => ramal_telefone_1; set => ramal_telefone_1 = value; }

        [DataMember]
        public string Telefone_2 { get => telefone_2; set => telefone_2 = value; }

        [DataMember]
        public string Ramal_telefone_2 { get => ramal_telefone_2; set => ramal_telefone_2 = value; }

        [DataMember]
        public string Celular { get => celular; set => celular = value; }

        [DataMember]
        public string Email { get => email; set => email = value; }
    }

}

I need to make a Join of person with personal addressee using LINQ. I tried to do it this way but it’s not working yet:

 public IList<PessoaDados> Select()
    {
        takeeatEntities context = new takeeatEntities();
        IList<PessoaDados> listaPessoasEntity = new List<PessoaDados>();



        IList<pessoa> pessoas = (from p in context.pessoa
                                   join e in context.pessoa_endereco on e.pessoa_id equals p.id ed
                                 from d in ed.DefaultIfEmpty()
                                   select new { * });

        PessoaDados pessoa_dados = null;
        foreach (pessoa Pessoa in pessoas)
        {
            pessoa_dados = new PessoaDados();
            pessoa_dados.Tipo = Pessoa.tipo;
            pessoa_dados.Razao_social = Pessoa.razao_social;
            pessoa_dados.Nome_fantasia = Pessoa.nome_fantasia;
            pessoa_dados.Cpf_cnpj = Pessoa.cpf_cnpj;
            pessoa_dados.Rg_insc_estadual = Pessoa.rg_insc_estadual;
            pessoa_dados.Insc_substituicao = Pessoa.insc_substituicao;
            pessoa_dados.Insc_municipal = Pessoa.insc_municipal;
            pessoa_dados.Data_expedicao_rg = Pessoa.data_expedicao_rg;
            pessoa_dados.Orgao_expedidor_rg = Pessoa.orgao_expedidor_rg;

            listaPessoasEntity.Add(pessoa_dados);
        }

        return listaPessoasEntity;
    }

I’m still a beginner in LINQ, Lambda. Below follows the error image, which does not even compile: inserir a descrição da imagem aqui

using System;
using System.Runtime.Serialization;

namespace WebService
{
    [DataContract]
    public class PessoaDados
    {
        private Nullable<int> id;
        private string tipo;
        private string razao_social;
        private string nome_fantasia;
        private string cpf_cnpj;
        private string rg_insc_estadual;
        private string insc_substituicao;
        private string insc_municipal;
        private DateTime? data_expedicao_rg;
        private string orgao_expedidor_rg;

        [DataMember]
        public int? Id { get => id; set => id = value; }

        [DataMember]
        public string Tipo { get => tipo; set => tipo = value; }

        [DataMember]
        public string Razao_social { get => razao_social; set => razao_social = value; }

        [DataMember]
        public string Nome_fantasia { get => nome_fantasia; set => nome_fantasia = value; }

        [DataMember]
        public string Cpf_cnpj { get => cpf_cnpj; set => cpf_cnpj = value; }

        [DataMember]
        public string Rg_insc_estadual { get => rg_insc_estadual; set => rg_insc_estadual = value; }

        [DataMember]
        public string Insc_substituicao { get => insc_substituicao; set => insc_substituicao = value; }

        [DataMember]
        public string Insc_municipal { get => insc_municipal; set => insc_municipal = value; }

        [DataMember]
        public DateTime? Data_expedicao_rg { get => data_expedicao_rg; set => data_expedicao_rg = value; }

        [DataMember]
        public string Orgao_expedidor_rg { get => orgao_expedidor_rg; set => orgao_expedidor_rg = value; }

        public virtual Pessoa_enderecoDados pessoa_enderecoDados { get; set; }
    }

    [DataContract]
    public class Pessoa_enderecoDados
    {
        private Nullable<int> id;
        private int pessoa_id;
        private string logradouro;
        private int numero;
        private string complemento;
        private string ponto_referencia;
        private string cep;
        private string bairro;
        private int cidade_id;
        private string nome_contato;
        private string telefone_1;
        private string ramal_telefone_1;
        private string telefone_2;
        private string ramal_telefone_2;
        private string celular;
        private string email;

        [DataMember]
        public int? Id { get => id; set => id = value; }

        [DataMember]
        public int Pessoa_id { get => pessoa_id; set => pessoa_id = value; }

        [DataMember]
        public string Logradouro { get => logradouro; set => logradouro = value; }

        [DataMember]
        public int Numero { get => numero; set => numero = value; }

        [DataMember]
        public string Complemento { get => complemento; set => complemento = value; }

        [DataMember]
        public string Ponto_referencia { get => ponto_referencia; set => ponto_referencia = value; }

        [DataMember]
        public string Cep { get => cep; set => cep = value; }

        [DataMember]
        public string Bairro { get => bairro; set => bairro = value; }

        [DataMember]
        public int Cidade_id { get => cidade_id; set => cidade_id = value; }

        [DataMember]
        public string Nome_contato { get => nome_contato; set => nome_contato = value; }

        [DataMember]
        public string Telefone_1 { get => telefone_1; set => telefone_1 = value; }

        [DataMember]
        public string Ramal_telefone_1 { get => ramal_telefone_1; set => ramal_telefone_1 = value; }

        [DataMember]
        public string Telefone_2 { get => telefone_2; set => telefone_2 = value; }

        [DataMember]
        public string Ramal_telefone_2 { get => ramal_telefone_2; set => ramal_telefone_2 = value; }

        [DataMember]
        public string Celular { get => celular; set => celular = value; }

        [DataMember]
        public string Email { get => email; set => email = value; }
    }
}

consultation:

 public IList<PessoaDados> Select()
        {
            takeeatEntities context = new takeeatEntities();
            IList<PessoaDados> listaPessoasEntity = new List<PessoaDados>();

            var pessoas = context.pessoa
                         .GroupJoin(context.pessoa_endereco, p => p.id, a => a.pessoa_id, (p, a) => new { p, a })
                         .SelectMany(a => a.a.DefaultIfEmpty(), (p, a) => new PessoaDados
                         {
                             Id = p.p.id,
                             Razao_social = p.a.razao_social
                         })
                         .ToList();


            PessoaDados pessoa_dados = null;
            foreach (pessoa Pessoa in pessoas)
            {
                pessoa_dados = new PessoaDados();
                pessoa_dados.Tipo = Pessoa.tipo;
                pessoa_dados.Razao_social = Pessoa.razao_social;
                pessoa_dados.Nome_fantasia = Pessoa.nome_fantasia;
                pessoa_dados.Cpf_cnpj = Pessoa.cpf_cnpj;
                pessoa_dados.Rg_insc_estadual = Pessoa.rg_insc_estadual;
                pessoa_dados.Insc_substituicao = Pessoa.insc_substituicao;
                pessoa_dados.Insc_municipal = Pessoa.insc_municipal;
                pessoa_dados.Data_expedicao_rg = Pessoa.data_expedicao_rg;
                pessoa_dados.Orgao_expedidor_rg = Pessoa.orgao_expedidor_rg;
                pessoa_dados.

                listaPessoasEntity.Add(pessoa_dados);
            }

            return listaPessoasEntity;
        }
c# linq lambda-expressions wcf

  • What is the error obtained?

  • You are using Entity Framework for data load? Entities are related?

  • put an image with the error, yes it is Entity framework. model.edmx with the tables loaded. they have relationships with each other, my intention is to select and feed these 2 classes, or unite them, I just have no idea how I do it, I added an image of the error

  • In the case exists pessoa that does not have pessoa_endereco and you want to bring all the people?

  • That’s right, I need it like this

  • @Intelidersistemas from what I understand, is mounting a DTO and marking with DataContract, is sending this data to a service WCF or riding a ViewModel to the MVC?

  • I am sending to a WCF, when I do with only one table works ok

Show 2 more comments

1 answer

2


First I’d like to get into the merits of WCF longitude.: WCF Is Dead and Web API Is Dying – Long Live MVC 6!

So if you’re building a new system using an architecture SOA or of Microservices, I strongly advise you to use ASP.NET WebAPI and abuse of REST.

Now let’s discuss some of your DTO (Data Transfer Object), they make sense when the format of the data obtained in the database are in a different format than is exported by API (be it SOAP/WCF or REST/WebAPI). In your case, you’re just turning your entity into a class with the same structure, in my view this kind of mapping is unnecessary.

Yeah, I know the WCF has problems with virtual properties, and WebAPI In a way, you have it, too. In this case I advise you to change the LazyLoadingEnabled and ProxyCreationEnabled for false and remove the virtual of its navigational properties. By doing this, you will be giving up the LazyLoad, but a EagerLoad well planned has a positive impact on application performance.

If I were you, the first thing I’d do is leave the EDMX/Database First and adopt the Code First, you can create all the initials entities from your Banco de Dados existing.

The classes you will obtain will have the same name as in Banco de Dados, I advise you to change them to stay within the standards of C#

[DataContract(IsReference = true)]
[Table(Name = 'pessoa')]
public class PessoaDados
{
    [DataMember]
    [Key, Column(Name = "id")]
    public int PessoaId { get; set; }

    [DataMember]
    [Column(Name = "tipo")]
    public string Tipo { get; set; }

    [DataMember]
    [Column(Name = "razao_social")]
    public string RazaoSocial { get; set; }

    [DataMember]
    [Column(Name = "nome_fantasia")]
    public string NomeFantasia { get; set; }

    [DataMember]
    [Column(Name = "cpf_cnpj")]
    public string CpfCnpj { get; set; }

    [DataMember]
    [Column(Name = "rg_insc_estadual")]
    public string RgInscEstadual { get; set; }

    [DataMember]
    [Column(Name = "insc_substituicao")]
    public string InscSubstituicao { get; set; }

    [DataMember]
    [Column(Name = "insc_municipal")]
    public string InscMunicipal { get; set; }

    [DataMember]
    [Column(Name = "data_expedicao_rg")]
    public DateTime? DataExpedicaoRg { get; set; }

    [DataMember]
    [Column(Name = "orgao_expedidor_rg")]
    public string Orgao_expedidor_rg { get; set; }

    [DataMember]
    public ICollection<Endereco> Enderecos { get; set; }
}

[DataContract(IsReference = true)]
[Table(Name = 'pessoa_endereco')]
public class Endereco
{
    [DataMember]
    [Key, Column(Name = "id")]
    public int EnderecoId { get; set; }

    [DataMember]
    [Column(Name = "pessoa_id")]
    public int PessoaId { get; set; }

    [DataMember]
    [Column(Name = "logradouro")]
    public string Logradouro { get; set; }

    [DataMember]
    [Column(Name = "numero")]
    public int Numero { get; set; }

    [DataMember]
    [Column(Name = "complemento")]
    public string Complemento { get; set; }

    [DataMember]
    [Column(Name = "ponto_referencia")]
    public string PontoReferencia { get; set; }

    [DataMember]
    [Column(cep)]
    public string Cep { get; set; }

    [DataMember]
    [Column(Name = "bairro")]
    public string Bairro { get; set; }

    [DataMember]
    [Column(Name = "cidadeId")]
    public int CidadeId { get; set; }

    [DataMember]
    [Column(Name = "nome_contato")]
    public string NomeContato { get; set; }

    [DataMember]
    [Column(Name = "telefone_1")]
    public string Telefone1 { get; set; }

    [DataMember]
    [Column(Name = "ramal_telefone_1")]
    public string RamalTelefone1 { get; set; }

    [DataMember]
    [Column(Name = "telefone_2")]
    public string Telefone2 { get; set; }

    [DataMember]
    [Column(ramal_telefone_2)]
    public string RamalTelefone2 { get; set; }

    [DataMember]
    [Column(Name = "celular")]
    public string Celular { get; set; }

    [DataMember]
    [Column(Name = "email")]
    public string Email { get => email; set => email = value; }

    [DataMember]
    public Pessoa Pessoa { get; set; }
}

Once this is done, the method of your service can simply be so.:

public async Task<List<Pessoa>> GetPessoas(string tipo)
{
    using (var db = new DbContext())
    {
        return await db.Pessoas
            .Include(x => x.Enderecos)
            .Where(x => x.Tipo == tipo)
            .ToListAsync();
    }
}

Of course, if your contract exposes only part of your entities, then it makes sense to have a DTO, as in the example below.

[DataContract(IsReference = true)]
public class PessoaDTO
{
    [DataMember]
    public int PessoaId { get; set; }

    [DataMember]
    public string Tipo { get; set; }

    [DataMember]
    public string RazaoSocial { get; set; }

    [DataMember]
    public string CpfCnpj { get; set; }

    [DataMember]
    public int? EnderecoId { get; set; }

    [DataMember]
    public EnderecoDTO Endereco { get; set; }

    [DataMember]
    public ContatoDTO Contato { get; set; }
}

[DataContract(IsReference = true)]
public class EnderecoDTO
{
    [DataMember]
    public string Logradouro { get; set; }

    [DataMember]
    public int Numero { get; set; }

    [DataMember]
    public string Complemento { get; set; }

    [DataMember]
    public string PontoReferencia { get; set; }

    [DataMember]
    [Column(cep)]
    public string Cep { get; set; }

    [DataMember]
    public string Bairro { get; set; }

    [DataMember]
    public int CidadeId { get; set; }
}

[DataContract(IsReference = true)]
public class ContatoDTO
{
    [DataMember]
    public string Telefone { get; set; }

    [DataMember]
    public string Celular { get; set; }

    [DataMember]
    public string Email { get => email; set => email = value; }     
}

Your operation would look like this.:

public async Task<List<Pessoa>> GetPessoasResumo(string tipo)
{
    using (var db = new DbContext())
    {
        return await db.Pessoas
            .Include(x => x.Enderecos)
            .Where(x => x.Tipo == tipo)
            .Select(x => new PessoaDTO {
                PessoaId = x.PessoaId,
                Tipo = x.Tipo,
                RazaoSocial = x.RazaoSocial,
                CpfCnpj = x.CpfCnpj,
                EnderecoId = x.Endereco.Any() ? x.Endereco.FirstOrDefault().EnderecoId,
                Endereco = x.Endereco.Any() ? x.Endereco.Select(y => new EnderecoDTO {
                    Logradouro = y.EnderecoId,
                    Numero = y.EnderecoId,
                    Complemento = y.EnderecoId,
                    PontoReferencia = y.EnderecoId,
                    Cep = y.EnderecoId,
                    Bairro = y.EnderecoId,
                    CidadeId = y.EnderecoId,
                }).FirstOrDefault() : default(EnderecoDTO),
                Contato = x.Endereco.Any() ? x.Endereco.Select(y => new ContatoTO {
                    Telefone = y.Telefone,
                    Celular = y.Celular,
                    Email = y.Email
                }).FirstOrDefault() : default(ContatoTO),
            }).ToListAsync();
    }
}

  • person comes from the mapping of edmx, ie is the table of the database, while person_data is the class I made to represent it, my idea, is to select Join of the 2 tables and play within that class I created, which alias is missing the fields of the address.

  • thanks for the great attention and detail of the case, I will read a little to change, but I was a little sad, I will explain why. I had the webservice running and functional in asmx, however as I am rewriting the app, now using Cordova to be cross-platform, I thought of rewriting the webservice, I read several articles saying that asmx was a legacy and that I should use wcf or webapi, of the two I chose wcf, however now you say that he died and the webapi is dying, in your opinion would have something better than the webapi, which would give me more longevity in that part of the system?

  • @Intelidersistemas WCF is practically dead, especially with the popularization of Microserviços, now as to ASP.NET WebAPI, to say that she died, is just one bait click, in reality there was a unification of the ASP.NET WebAPI and of ASP.NET MVC in the MVC 5, in the MVC 6 we had a few more changes, so the correct one would say "The old Webapi died, long life to new". Then you can write a REST API using the MVC 5 ou 6 without fear of being happy.

  • Thanks Obias for the help!.

  • after reading the article you referred me at the beginning of the post I will actually use the mv6 webapi, thank you!

Browser other questions tagged c# linq lambda-expressions wcf

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