CODEWITHSUNDEEP
c#linqoptimizationleft-join
Simon
Simon

Reputation: 2362

C# - LINQ - Performances between 2 style of query

I had to create a LINQ query joining 3 IEnumerable (2 datatable, one List). I also have to "outer joins" my 2 joins.

I really like the second one, because a lot more lisible and comparable to SQL. But I face performance issues (3x longer) compared with the first one down-there, which is less readable, but quicker.

Can someone just explain to me what can I do to optimize the second query, and also why are they so much different, but returning the same data (I am new to LINQ, and have a hard time understanding every LINQ-TO-SQL/LAMBDA/SAMBA subtilities

The old one, really quick, but less lisible

 var Detailled_payroll = 
        from MainData in dt_BasicPayrollData.AsEnumerable()
           //joining (Externalize later...) with a list of days
           join MyDays in gdt_Liste_JourFérié 
              on MainData.Field<DateTime>("DENTREE") equals MyDays 
                  into MyDays_join //why?
           //joining (Externalize later...) with a list (datatable) of things
           join MyFilter in gdt_MyFilter.AsEnumerable()
              on MainData.Field<string>("KEY") equals MyFilter.Field<string>("KEY") 
                  into MyFilter_join   //why?

           //I think we "outer join" here, forcing the existing of a missing join                             
           from MyDays in MyDays_join.DefaultIfEmpty()
           from MyFilter in MyFilter_join.DefaultIfEmpty()

           let tmp = MainData.Field<string>("CEmploye") //dont remember why..

           select new Blabla()
           { 
                 EmpNumber=MainData.Field<string>("NoEmploye"),
                 IsInSpecialDays = MyDays == Default(DateTime) ? "NO" : "YES",
                 KeyWasFound = MyFilter == null ? "NO" : "YES"
           }

And here is the "new" one : 

    var Detailled_payroll = 
        from MainData in dt_BasicPayrollData.AsEnumerable()
        //joining (and externalize...) with a list (datatable) of things
        from MyFilter in MyGlobalVariable.gdt_FiltreEnCours.AsEnumerable()
           .Where(x => x.Field<string>("KEY") == MainData.Field<string>("KEY"))
           .DefaultIfEmpty()
        //joining (and externalize...) with a list (List<DateTime>) of days
        from MyDays in gdt_Liste_JourFérié  
           .Where(x => x == MainData.Field<DateTime>("DENTREE"))
           .DefaultIfEmpty()

        select new Blabla()
          { 
                 EmpNumber=MainData.Field<string>("NoEmploye"),
                 IsInSpecialDays = MyDays == Default(DateTime) ? "NO" : "YES",
                 KeyWasFound = MyFilter == null ? "NO" : "YES"
           }

Both have the exact same resulting data.

Any ideas?

Upvotes: 3

Views: 408

Answers (1)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33391

In a word, in the first you have join, in the second you have Cartesian product then you filter it.

Upvotes: 4

Related Questions