Entity Framework Left Join

Asked

Viewed 2,765 times

2

Seeing the question Left Join with lambda Groupjoin and with condition, I have doubts about the way I do Left Join with Linus

Having the following entities

public class Cidade 
    {
        public int CidadeId { get; set; }
        public string Descricao { get; set; }
        public int EstadoId { get; set; }
    }

public class Estado
    {
        public int EstadoId { get; set; }
        public string Descricao { get; set; }
        public string Sigla { get; set; }
    }

the way I always made my left Join

 var estados = from e in context.Estado
                              join c in context.Cidade on e.EstadoId equals c.EstadoId into cl
                              from c in cl.DefaultIfEmpty()
                              select e;

SQL generated

SELECT 
    [Extent1].[EstadoId] AS [EstadoId], 
    [Extent1].[Descricao] AS [Descricao], 
    [Extent1].[Sigla] AS [Sigla], 
    [Extent1].[UsuarioCad] AS [UsuarioCad], 
    [Extent1].[DataHoraCad] AS [DataHoraCad], 
    [Extent1].[Ativo] AS [Ativo]
    FROM  [dbo].[Estado] AS [Extent1]
    LEFT OUTER JOIN [dbo].[Cidade] AS [Extent2] ON [Extent1].[EstadoId] = [Extent2].[EstadoId]

proposed form in the answers to the question Left Join with lambda Groupjoin and with condition

var dados2 = context.Estado
                    .GroupJoin(context.Cidade, e => e.EstadoId, c => c.EstadoId,
                            (estado, cidade) => new { E = estado, C = cidade.DefaultIfEmpty() })
                            .SelectMany(final => final.C, (final, ex) => new
                            {
                                EstadoId = final.E.EstadoId,
                                Descricao = final.E.Descricao,
                                Sigla = final.E.Sigla,
                                Cidade = ex.Descricao
                            }).ToList();

SQL generated

SELECT 
    [Extent1].[EstadoId] AS [EstadoId], 
    [Extent1].[Descricao] AS [Descricao], 
    [Extent1].[Sigla] AS [Sigla], 
    [Extent2].[Descricao] AS [Descricao1]
    FROM  [dbo].[Estado] AS [Extent1]
    LEFT OUTER JOIN [dbo].[Cidade] AS [Extent2] ON [Extent1].[EstadoId] = [Extent2].[EstadoId]

My doubt, what would be better in terms of performance?

In my opinion, the way I write Left Join is clearer, I confess that in the other approach I get a little confused.

From what I could observe the instructions SQL are basically the same

c# linq entity-framework-6

  • I also think that with LINQ becomes clearer. Using Lambda Expressions, expressions like Join and the GroupJoinThey get a little fuzzy and the code gets too long. My boss thinks with Lambda the darlings get faster, but I’m in doubt, too. Sometimes I notice that it is faster with Lambda but there are queries in LINQ that are also fast. It depends on the query that is made

  • Friend, both are Latin, the first case is "query Expression" and the second "lambda Expression". Internally "query Expression" are converted to "lambda Expression", due to this "lambda" is a little faster than "query", but I believe that this small gain does not compensate the legibility loss, finally you can look at the following website for a comparison: http://blog.thijssen.ch/2009/02/linq-vs-lambda-vs-loop-performance-test.html

  • There is no significant performance gain or loss considering both scenarios, since LINQ or so the expression methods are just ways to build a command that generates an SQL.

1 answer

1


My doubt, what would be better in terms of performance?

Both syntax are equal for the compiler that converts into the same code, no charge as it is Compile time.

The code below is the post quoted by Tobymosque, only in the right way. I will discuss below.

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;

namespace IterationPerformanceTest
{
    class Program
    {
        const int SIZE = 10000;
        const int RUNS = 1000;

        static Stopwatch stopwatch = new Stopwatch();
        static Random r = new Random();

        static List<int> intList = new List<int>();

        public static void Main(string[] args)
        {
            for (int i = 0; i < SIZE; i++)
                intList.Add(r.Next(int.MinValue, int.MaxValue));

            RunLoop();            

            RunQuerySyntax();

            RunFluentSyntax();

            Console.ReadLine();
        }

        static void RunQuerySyntax()
        {
            stopwatch.Reset();

            //Query Syntax  
            stopwatch.Start();

            for (int t = 0; t < RUNS; ++t)
            {
                var result = (from int i in intList where i < 10 select i).ToList();
            }

            stopwatch.Stop();

            Console.WriteLine(string.Format("Query Syntax : {0}, avg. {1}", stopwatch.Elapsed, new TimeSpan(stopwatch.ElapsedTicks / RUNS)));
        }

        static void RunFluentSyntax()
        {
            stopwatch.Reset();

            //Fluent Syntax
            stopwatch.Start();

            for (int t = 0; t < RUNS; ++t)
            {
                var result = intList.Where(i => i < 10).ToList();
            }

            stopwatch.Stop();

            Console.WriteLine(string.Format("Fluent Syntax: {0}, avg. {1}", stopwatch.Elapsed, new TimeSpan(stopwatch.ElapsedTicks / RUNS)));   
        }

        static void RunLoop()
        {
            stopwatch.Reset();

            //LOOP  
            stopwatch.Start();

            for (int t = 0; t < RUNS; ++t)
            {
                var result = new List<int>();

                foreach (var i in intList)
                {
                    if (i < 10) result.Add(i);
                }
            }

            stopwatch.Stop();

            Console.WriteLine(string.Format("Loop: {0}, avg. {1}", stopwatch.Elapsed, new TimeSpan(stopwatch.ElapsedTicks / RUNS)));

        }
    }
}

I isolated the calls to make it easy on CIL to analyze and something else...

Loop of Query Syntax:

// loop start (head: IL_004a)
        IL_0010: nop
        IL_0011: ldsfld class [mscorlib]System.Collections.Generic.List`1<int32> IterationPerformanceTest.Program::intList
        IL_0016: call class [mscorlib]System.Collections.Generic.IEnumerable`1<!!0> [System.Core]System.Linq.Enumerable::Cast<int32>(class [mscorlib]System.Collections.IEnumerable)
        IL_001b: ldsfld class [mscorlib]System.Func`2<int32, bool> IterationPerformanceTest.Program::'CS$<>9__CachedAnonymousMethodDelegate1'
        IL_0020: brtrue.s IL_0035

        IL_0022: ldnull
        IL_0023: ldftn bool IterationPerformanceTest.Program::'<RunQuerySyntax>b__0'(int32)
        IL_0029: newobj instance void class [mscorlib]System.Func`2<int32, bool>::.ctor(object, native int)
        IL_002e: stsfld class [mscorlib]System.Func`2<int32, bool> IterationPerformanceTest.Program::'CS$<>9__CachedAnonymousMethodDelegate1'
        IL_0033: br.s IL_0035

        IL_0035: ldsfld class [mscorlib]System.Func`2<int32, bool> IterationPerformanceTest.Program::'CS$<>9__CachedAnonymousMethodDelegate1'
        IL_003a: call class [mscorlib]System.Collections.Generic.IEnumerable`1<!!0> [System.Core]System.Linq.Enumerable::Where<int32>(class [mscorlib]System.Collections.Generic.IEnumerable`1<!!0>, class [mscorlib]System.Func`2<!!0, bool>)
        IL_003f: call class [mscorlib]System.Collections.Generic.List`1<!!0> [System.Core]System.Linq.Enumerable::ToList<int32>(class [mscorlib]System.Collections.Generic.IEnumerable`1<!!0>)
        IL_0044: stloc.1
        IL_0045: nop
        IL_0046: ldloc.0
        IL_0047: ldc.i4.1
        IL_0048: add
        IL_0049: stloc.0

        IL_004a: ldloc.0
        IL_004b: ldc.i4 1000
        IL_0050: clt
        IL_0052: stloc.2
        IL_0053: ldloc.2
        IL_0054: brtrue.s IL_0010
    // end loop

Loop of Fluent Syntax

// loop start (head: IL_0045)
        IL_0010: nop
        IL_0011: ldsfld class [mscorlib]System.Collections.Generic.List`1<int32> IterationPerformanceTest.Program::intList
        IL_0016: ldsfld class [mscorlib]System.Func`2<int32, bool> IterationPerformanceTest.Program::'CS$<>9__CachedAnonymousMethodDelegate3'
        IL_001b: brtrue.s IL_0030

        IL_001d: ldnull
        IL_001e: ldftn bool IterationPerformanceTest.Program::'<RunFluentSyntax>b__2'(int32)
        IL_0024: newobj instance void class [mscorlib]System.Func`2<int32, bool>::.ctor(object, native int)
        IL_0029: stsfld class [mscorlib]System.Func`2<int32, bool> IterationPerformanceTest.Program::'CS$<>9__CachedAnonymousMethodDelegate3'
        IL_002e: br.s IL_0030

        IL_0030: ldsfld class [mscorlib]System.Func`2<int32, bool> IterationPerformanceTest.Program::'CS$<>9__CachedAnonymousMethodDelegate3'
        IL_0035: call class [mscorlib]System.Collections.Generic.IEnumerable`1<!!0> [System.Core]System.Linq.Enumerable::Where<int32>(class [mscorlib]System.Collections.Generic.IEnumerable`1<!!0>, class [mscorlib]System.Func`2<!!0, bool>)
        IL_003a: call class [mscorlib]System.Collections.Generic.List`1<!!0> [System.Core]System.Linq.Enumerable::ToList<int32>(class [mscorlib]System.Collections.Generic.IEnumerable`1<!!0>)
        IL_003f: stloc.1
        IL_0040: nop
        IL_0041: ldloc.0
        IL_0042: ldc.i4.1
        IL_0043: add
        IL_0044: stloc.0

        IL_0045: ldloc.0
        IL_0046: ldc.i4 1000
        IL_004b: clt
        IL_004d: stloc.2
        IL_004e: ldloc.2
        IL_004f: brtrue.s IL_0010
    // end loop

Practically twins!

Note the fact that the example has refactored the calls in isolated methods, try to change the order of Fluent with the Query and depending on your processor/ the bottom will always be slightly faster, this has nothing to do with query performance and yes cache hit, each machine will have a different gain, since it is always the same collection to be traversed, if it is in the processor cache is faster.

Very careful with the examples on the internet, this quoted contained several errors of analysis and technique.

Browser other questions tagged c# linq entity-framework-6

You are not signed in. Login or sign up in order to post.