Converting SQL query to Lambda query using best practice

Question

I'm trying to transcribe the SQL below to Lambda Expression and wondered how best to me indicate to this conversion.

declare @codigoGrupo int
declare @nomeModelo varchar(20)

set @codigoGrupo = 8 -- null
set @nomeModelo = '%segundo%'

select g.dc_nome, m.dc_nome
from hlp.grupo_solicitacao g
     left join hlp.modelo_solicitacao m on g.codigo_grupo_solicitacao = m.codigo_grupo_solicitacao
     where 1=1
     and (g.codigo_grupo_solicitacao = @codigoGrupo or @codigoGrupo is null)
     and (m.dc_nome like @nomeModelo or @nomeModelo is null)

Is this for Linq to Entities ( Entity Framework )?

Nick Butler
– Nick Butler

2012-05-03 12:36:29 +00:00
Commented May 3, 2012 at 12:36 — Nick Butler
– Nick Butler, Commented May 3, 2012 at 12:36
It's for linq2sql, isn't it ?

ykatchou
– ykatchou

2012-05-03 12:38:41 +00:00
Commented May 3, 2012 at 12:38 — ykatchou
– ykatchou, Commented May 3, 2012 at 12:38

Arion · Accepted Answer · 2012-05-03 12:41:35Z

1

I really think that it will be strange with lambda expression. It is easier to do it like this and much cleaner:

int? codigoGrupo =8;
string nomeModelo="segundo";
var result= (
    from g in grupo_solicitacao
    from m in modelo_solicitacao
         .Where(a=>a.codigo_grupo_solicitacao=g.codigo_grupo_solicitacao)
        .DefaultIfEmpty()
    where 1==1
    && (g.codigo_grupo_solicitacao==codigoGrupo || !codigoGrupo.HasValue)
    && (m.dc_nome.Contains(nomeModelo) || nomeModelo==null)
    select new{g.dc_nome, m.dc_nome}
);

answered May 3, 2012 at 12:41

Arion

31.3k10 gold badges74 silver badges89 bronze badges

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

1 Comment

Arion Over a year ago

If you are fine with the answer then you might consider accepting it

Nick Butler · Accepted Answer · 2012-05-03 12:48:34Z

0

Something like this:

int? codigoGrupo = 8;
string nomeModelo = "segundo"

bool filterCodigoGrupo = codigoGrupo.HasValue;
bool filterNomeModelo = !String.IsNullOrEmpty( filterNomeModelo );

var list = context.grupo_solicitacao
  .Where( o =>
    ( 1 == 1 )
    &&
    ( !filterCodigoGrupo || o.codigo_grupo_solicitacao == codigoGrupo.Value )
    &&
    ( !filterNomeModelo || o.modelo_solicitacao.dc_nome.Contains( nomeModelo ) )
  )
  .Select( o => new { codigo = o.dc_nome, model = o.modelo_solicitacao.dc_nome } )
  .ToList();

answered May 3, 2012 at 12:48

Nick Butler

24.5k4 gold badges51 silver badges72 bronze badges

Collectives™ on Stack Overflow

Converting SQL query to Lambda query using best practice

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related