locked
How can I run the query with Linq? RRS feed

  • Question

  • User-1350042179 posted

    Hi 

    I have the query:

    select * from Unidad u left join ParametroValor p1 on u.IdTipoUnidad = p1.Id
    left join ParametroValor p2 on u.IdEstado = p2.Id
    where u.Placa = '' and p1.test='333'

    How can I run the query with Linq?

     

    Tuesday, January 9, 2018 2:42 AM

All replies

  • User-832373396 posted

    <g class="gr_ gr_44 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="44" data-gr-id="44">Hi</g> <g class="gr_ gr_5 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="5" data-gr-id="5">neoaguil</g>,

    Sir, based on your code, please refer to this example:

     
            public void  test6 (){
    
                List<Unidad> unidadList = new List<Unidad> {
                    new Unidad { IdEstado=1 ,IdTipoUnidad=1, UnidadName= "UnidadName1" , Placa="" },
                    new Unidad { IdEstado=1 ,IdTipoUnidad=1, UnidadName= "UnidadName2" ,Placa="Placa1"},
                    new Unidad { IdEstado=1 ,IdTipoUnidad=1, UnidadName= "UnidadName3" ,Placa=""},
                };
                List<ParametroValor> parametroValorList = new List<ParametroValor>
                {
                    new ParametroValor{ Id=2, ParametroValorName= "ParametroValorName1", test="122"},
                    new ParametroValor{ Id=1, ParametroValorName= "ParametroValorName2", test="222"},
                    new ParametroValor{ Id=1, ParametroValorName= "ParametroValorName3", test="333"},
                    new ParametroValor{ Id=1, ParametroValorName= "ParametroValorName4"}
                };
     
                var c=from u in unidadList
                join p1 in parametroValorList on u.IdTipoUnidad equals p1.Id into up1
                from subup1 in up1.DefaultIfEmpty()
                join p2 in parametroValorList on u.IdEstado equals p2.Id into up2
                from subp2 in up2.DefaultIfEmpty()   
                where u.Placa == "" &&  subup1.test=="333"            
                select new {u.IdEstado,u.IdTipoUnidad,u.Placa,u.UnidadName, subup1.Id, subup1.test, 
    subup1.ParametroValorName, subp2test = subp2.test, subp2Id =subp2.Id};

    and 

       class ParametroValor
            {
                public int Id { get; set; }
                public string ParametroValorName { get; set; }
                public string test { get; set; }
                
            }
            class Unidad
            {
                public int IdTipoUnidad { get; set; }
                public int IdEstado { get; set; }
                public string UnidadName { get; set; }
                public string Placa { get; set; }
                
            }

    (note: There are three type of outer join
    1) Left Outer Join = Left Join
    2) Right Outer Join = Right Join
    3) Full Outer Join = Full Join
    )

    References: https://www.mssqltips.com/sqlservertip/3169/understanding-linq-to-join-multiple-tables-where-null-match-are-expected/ 

    https://stackoverflow.com/questions/17142151/linq-to-sql-multiple-tables-left-outer-join 

    http://www.devcurry.com/2011/01/linq-left-join-example-in-c.html 

    With regards, Angelina Jolie

    Thursday, January 11, 2018 10:14 AM