Multiple Select and Join with LINQ and Lambda

Question

How can I do this query using 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've 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 my San_Proposta table ?

Why does it need to be done using a lambda? That version looks absolutely awful to understand. — Arran
– Arran, Commented Nov 14, 2012 at 19:01
Can be without lambda. This is because I would like to understand better how lambda work with that. — Lucas_Santos
– Lucas_Santos, Commented Nov 14, 2012 at 19:03
I think it might be better if you first read some samples about linq code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b#nested and then decide what method you want to use — Babak Fakhriloo
– Babak Fakhriloo, Commented Nov 14, 2012 at 19:04

Risky Martin · Accepted Answer · 2012-11-14 20:03:12Z

6

You are hiding San_Proposta within a field called San_Negocio, so calling a.San_Negocio.San_Proposta will access it, but I recommend writing your joins in a way that fields aren't nested, like this:

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
                 });

answered Nov 14, 2012 at 20:03

Risky Martin

2,5312 gold badges15 silver badges17 bronze badges

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

2 Comments

Piotr Kula Over a year ago

Thank you!! And by the way. THIS IS REAL LINQ. Not that other stuff.

Robert Smith Over a year ago

Both are LINQ, Joel is using the Linq Query syntax and Risky is using the Lambda syntax.

Joel Etherton · Accepted Answer · 2012-11-14 19:10:01Z

5

Here is a proper 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 an IQueryable of anonymous objects with the listed properties above.

answered Nov 14, 2012 at 19:10

Joel Etherton

37.5k10 gold badges92 silver badges106 bronze badges

1 Comment

Lucas_Santos Over a year ago

Yes, thanks a lot. But how it works using lambda expression ?

Collectives™ on Stack Overflow

Multiple Select and Join with LINQ and Lambda

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related