How can I run the query with Linq?

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?

 

Answer 1

User1120430333 posted

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

https://docs.microsoft.com/en-us/dotnet/csharp/linq/perform-left-outer-joins

https://www.linqpad.net/

Answer 2

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