Multiple selection and integration with LINQ and Lambda

Question

How to do query with LINQ and LAMBDA ?

QUERY

 Select san_negocio.imovel_id ,san_negocio.negocio_id ,san_imovel.credenciada_id ,san_proposta.proposta_id ,san_proposta.credenciada_id from san_negocio join san_proposta on san_negocio.imovel_id = san_proposta.imovel_id join san_imovel on san_negocio.imovel_id = san_imovel.imovel_id where san_negocio.credenciadacaptadora_id is null and san_negocio.credenciadavendedora_id is null and san_proposta.statusproposta_id = 2 

I tried:

 var objetos = db.San_Negocio.Join(db.San_Proposta, a => a.Imovel_Id, b => b.Imovel_Id, (a, b) => new { San_Negocio = a, San_Proposta = b }) .Join(db.San_Imovel, a => a.San_Negocio.Imovel_Id, c => c.Imovel_Id, (a, c) => new { San_Negocio = a, San_Imovel = c }) .Where(a => a.San_Negocio.San_Negocio.CredenciadaCaptadora_Id == null && a.San_Negocio.San_Negocio.CredenciadaVendedora_Id == null) .Select(a => new { a.San_Negocio.San_Negocio.Negocio_Id, a.San_Negocio.San_Negocio.Imovel_Id, a.San_Imovel.Credenciada_Id }); 

My doubt is in my Select . How can I call the San_Proposta table?

Answer 1

Here is the correct linq statement:

 from neg in db.san_negocio join prop in san_proposta on neg.imovel.id equals prop.imovel_id join imo in san_imovel on neg.imovel_id = imo.imovel_id where neg.credenciadacaptadora_id == null && neg.credenciadavendedora_id == null && prop.statusproposta_id == 2 select new { ImovelID = neg.imovel_id, NegocioID = neg.negocio_id, Imo_CredenciadaID = imo.credenciada_id, PropostaID = prop.proposta_id Prop_CredenciadaID = prop.credenciada_id }; 

This will create IQueryable anonymous objects with the properties listed above.

Answer 2

You hide San_Proposta in a field called San_Negocio , so calling a.San_Negocio.San_Proposta will have access to it, but I recommend recording your connections so that the fields are not nested, for example:

 var objetos = db.San_Negocio .Join(db.San_Proposta, a => a.Imovel_Id, b => b.Imovel_Id, (a, b) => new { San_Negocio = a, San_Proposta = b }) .Join(db.San_Imovel, a => a.San_Negocio.Imovel_Id, c => c.Imovel_Id, (a, c) => new { a.San_Negocio, a.San_Proposta, San_Imovel = c }) .Where(a => a.San_Negocio.CredenciadaCaptadora_Id == null && a.San_Negocio.CredenciadaVendedora_Id == null) .Select(a => new { a.San_Negocio.Negocio_Id, a.San_Negocio.Imovel_Id, a.San_Proposta.San_Proposta_Id, a.San_Imovel.Credenciada_Id });