Conditionally Exclude Child Property in Entity Framework Query

Question

Given the following object graph

public class Bar {
    public Guid Id {get;set;}
    public string SomeBar {get;set;}
}

public class Foo {
    public Guid Id {get;set;}
    public string FooString {get; set;}
    public IList<Bar> Bars {get;set;}
}

How do I conditionally exclude the Bars property properly so that Entity Framework doesn't load the entire object graph?

// my thoughts
public Task<Foo> GetFooByIdAsync(Guid id, bool includeBars) 
{
    var query = _db.Foos.Where(f => f.Id == id);
    if(!includeBars)
    {
        return query.Select(f=> new Foo{
            Id = f.Id,
            FooString = f.FooString
        }).ToListAsync();
    } else {
        return query.ToListAsync();
    }
}

Have you seen this post: https://stackoverflow.com/questions/3718400/conditional-eager-loading ? — David Tansey, Sep 11 '17 at 23:54
@DavidTansey There are a lot of posts to filter through and if you don't get the search words just right, they don't show up. No, I have not seen that one. Based on that answer, I presume that as soon as you `foreach` iterate the iqueryable, it'll load the data, is that correct? Also is that different than calling `.Select(f => ...);` as in my example? — Chase Florell, Sep 12 '17 at 00:00
Sorry - I failed to notice that you showed one viable approach in your question. As far as the `foreach` in linked post vs. your code, my understanding of this matches exactly what you have described. The only thing I would add is this: if your question/situation extends further to any performance-related question when comparing the seemingly equivalent syntaxes -- take the time to examine the generated queries with SQL profiler to see if they truly are the equivalent. EF generates 'interesting' SQL sometimes. — David Tansey, Sep 12 '17 at 00:16

score 2 · Accepted Answer · answered Sep 12 '17 at 04:38

Just conditionally add the Include(f => f.Bars) into the query expression. Like this:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;


namespace ConsoleApp8
{
    public class Bar
    {
        public Guid Id { get; set; }
        public string SomeBar { get; set; }
    }

    public class Foo
    {
        public Guid Id { get; set; }
        public string FooString { get; set; }
        public IList<Bar> Bars { get; set; }
    }

    class Db: DbContext
    {

        public DbSet<Foo> Foos { get; set; }
        public DbSet<Bar> Bar { get; set; }

        public async Task<Foo> GetFooByIdAsync(Guid id, bool includeBars)
        {
            var query = (IQueryable<Foo>)Foos.AsNoTracking();

            if (includeBars)
            {
                query = query.Include(f => f.Bars);
            }

            query = query.Where(f => f.Id == id);

            var results = await query.ToListAsync();

            return results.FirstOrDefault();            
        }
    }



    class Program
    {      

        static void Main(string[] args)
        {

            Database.SetInitializer(new DropCreateDatabaseAlways<Db>());


            using (var db = new Db())
            {
                db.Database.Initialize(false);
                db.Database.Log = m => Console.WriteLine(m);

                var r1 = db.GetFooByIdAsync(Guid.Empty, false).Result;
                var r2 = db.GetFooByIdAsync(Guid.Empty, true).Result;

            }
            Console.WriteLine("Hit any key to exit");
            Console.ReadKey();


        }
    }
}

outputs

Opened connection asynchronously at 9/11/2017 11:37:26 PM -05:00

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[FooString] AS [FooString]
    FROM [dbo].[Foos] AS [Extent1]
    WHERE [Extent1].[Id] = @p__linq__0


-- p__linq__0: '00000000-0000-0000-0000-000000000000' (Type = Guid, IsNullable = false)

-- Executing asynchronously at 9/11/2017 11:37:26 PM -05:00

-- Completed in 22 ms with result: SqlDataReader



Closed connection at 9/11/2017 11:37:26 PM -05:00

Opened connection asynchronously at 9/11/2017 11:37:26 PM -05:00

SELECT
    [Project1].[C1] AS [C1],
    [Project1].[Id] AS [Id],
    [Project1].[FooString] AS [FooString],
    [Project1].[C2] AS [C2],
    [Project1].[Id1] AS [Id1],
    [Project1].[SomeBar] AS [SomeBar]
    FROM ( SELECT
        [Extent1].[Id] AS [Id],
        [Extent1].[FooString] AS [FooString],
        1 AS [C1],
        [Extent2].[Id] AS [Id1],
        [Extent2].[SomeBar] AS [SomeBar],
        CASE WHEN ([Extent2].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
        FROM  [dbo].[Foos] AS [Extent1]
        LEFT OUTER JOIN [dbo].[Bars] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Foo_Id]
        WHERE [Extent1].[Id] = @p__linq__0
    )  AS [Project1]
    ORDER BY [Project1].[Id] ASC, [Project1].[C2] ASC


-- p__linq__0: '00000000-0000-0000-0000-000000000000' (Type = Guid, IsNullable = false)

-- Executing asynchronously at 9/11/2017 11:37:26 PM -05:00

-- Completed in 2 ms with result: SqlDataReader



Closed connection at 9/11/2017 11:37:26 PM -05:00

Hit any key to exit

see, I was under the impression that EF would automatically load joins if not specifically excluded. Is it only the case if you request that data before `.ToListAsync()`? — Chase Florell, Sep 12 '17 at 15:29
Yes. See https://msdn.microsoft.com/en-us/library/jj574232(v=vs.113).aspx — David Browne - Microsoft, Sep 12 '17 at 15:30

Conditionally Exclude Child Property in Entity Framework Query

1 Answers1