Support partial loading (i.e. not eagerly loading all scalar properties)

[lvasiliou]

Can you please add partial loading and by that I mean in addition to lazy and eager loading of relationships, the third option - lets call it partial for now - loads the related entity but only containing the primary keys and no other data. If needed I could resolve the rest of the data manually.
For example I have a Contact entity with one to many relationship with Address. With partial loading, I will get Contact.Address.AddressId only. I could then resolve the rest of the properties manually. Maybe all maybe the ones I specify via DbContext.Entry(address).Resolve(a=>a.Line1;....

[ajcvickers]

See #9385 for some more ideas in this area.

[ajcvickers]

See #8138 for additional ideas in this area--specifically around lazy loading of scalar properties.

[faibistes]

As a workaround (and that's good enough for me), I do something like this:
Let's assume we have a "estado" table with the field Id as PK and a big text column called "observaciones". This is how you define the entities:

public partial class Estado
{
	public Guid Id { get; set; }
	public DateTime Fecha { get; set; }
	public Observaciones Observaciones { get; set; }
}
public class Observaciones
{
	public Guid Estado { get; set; }
	public string Texto { get; set; }
	public Estado Estado { get; set; }
}
modelBuilder.Entity<Observaciones>(entity =>
{
	entity.HasKey(e => e.Id)
		.HasName("PK_estado");
	entity.HasOne(e => e.Estado).WithOne(o => o.Observaciones).HasForeignKey<Estado>(e => e.Id);
	entity.Property(e => e.Texto)
		.HasColumnName("observaciones")
		.HasColumnType("text");
	entity.ToTable("estado", "reactivos");
});
modelBuilder.Entity<Estado>(entity =>
{
	entity.HasKey(e =>  e.Id)
		.HasName("PK_estado");
	entity.ToTable("estado", "reactivos");
	entity.Property(e => e.Id).HasColumnName("id");
	entity.Property(e => e.Fecha).HasColumnName("fecha");
	entity.HasOne(e => e.Observaciones).WithOne(o => o.Estado).HasForeignKey<Observaciones>(e => e.Id);
});

This works with EF 2.0 (you need to Include() to load observaciones column) I guess that this will lazy load if you define Observaciones and Estado as virtual in EF 2.1, but I haven't tested it.

[bbrandt]

@faibistes Does this produce one query or 2 queries against the same table? If one query, is the table joining with itself on the Id? Any issues from this?

[inlineHamed]

I have tested faibistes solutions and it worked for EF Core 2.1.
also I tired to set Observaciones as Owned Entity but I figured out that Owned Entities are included by default.
If we had an option for owned Entities to not be included by default and be in select part only when they are included, that would be a cleaner solution.

[AndriySvyryd]

@hmdhasani The solution @faibistes showed is Table splitting, that's the recommended way to lazy load scalar properties.

Owned entities will always be eagerly loaded.

[inlineHamed]

Ok, anyway the downside of using table split is adding an unnecessary complexity to data model that can be a high cost for big data models.
It would be more simple if we can do something like these:

var images = _context.Set<Image>()
                           .Ignore(i => i.Content)
                           .ToList();

And

var documents = _context.Set<Document>()
                                    .Include(d => d.Images)
                                        .Ignore(i => i.Content)
                                    .ToList();

I wonder is there currently any way to do this? ( maybe by somehow interposition or manipulation of query generation process )

[inlineHamed]

After trying some approaches I ended up with this workaround:
The base idea is to turn for example this query:

SELECT [f].[Id], [f].[Report], [f].[CreationDate]
FROM [File] AS [f]

into this:

SELECT [f].[Id], '' as [Report], [f].[CreationDate]
FROM [File] AS [f]

by overriding DefaultQuerySqlGenerator.GenerateList()
when the query is like this:

var files = context.Files.AsNoTracking()
                         .IgnoreProperty(f => f.Report)
                         .ToList();

I added AsNoTracking() to become sure the value of ignored column will not change.

here is the full code: ( most parts were taken from https://www.chasingdevops.com/sql-generation-ef-core/ )

public static class IQueryableExtensions
    {
        internal static readonly MethodInfo _ignorePropertyMethodInfo
          = typeof(IQueryableExtensions).GetTypeInfo().GetDeclaredMethod(nameof(IgnoreProperty));

        public static IQueryable<TEntity> IgnoreProperty<TEntity, TProperty>(this IQueryable<TEntity> source, Expression<Func<TEntity, TProperty>> propertyPath) where TEntity : class
            => source.Provider is EntityQueryProvider
                ? source.Provider.CreateQuery<TEntity>(
                  Expression.Call(
                    instance: null,
                    method: _ignorePropertyMethodInfo.MakeGenericMethod(typeof(TEntity), typeof(TProperty)),
                    arguments: new Expression[] { source.Expression, propertyPath }))
                : source;
    }

    internal class IgnorePropertyResultOperator : SequenceTypePreservingResultOperatorBase, IQueryAnnotation
    {
        public IQuerySource QuerySource { get; set; }
        public QueryModel QueryModel { get; set; }

        public LambdaExpression PropertyPathLambda { get; set; }

        public override ResultOperatorBase Clone(CloneContext cloneContext)
          => new IgnorePropertyResultOperator();

        public override StreamedSequence ExecuteInMemory<T>(StreamedSequence input) => input;

        public override void TransformExpressions(Func<Expression, Expression> transformation)
        {
        }
    }

    internal class IgnorePropertyExpressionNode : ResultOperatorExpressionNodeBase
    {
        public static readonly IReadOnlyCollection<MethodInfo> SupportedMethods = new[]
        {
            IQueryableExtensions._ignorePropertyMethodInfo
        };

        private readonly LambdaExpression _propertyPathLambda;

        public IgnorePropertyExpressionNode(MethodCallExpressionParseInfo parseInfo, LambdaExpression propertyPathLambda)
          : base(parseInfo, null, null) => _propertyPathLambda = propertyPathLambda;

        protected override ResultOperatorBase CreateResultOperator(ClauseGenerationContext clauseGenerationContext)
          => new IgnorePropertyResultOperator()
          {
              PropertyPathLambda = _propertyPathLambda,
          };

        public override Expression Resolve(
              ParameterExpression inputParameter,
              Expression expressionToBeResolved,
              ClauseGenerationContext clauseGenerationContext)
                => Source.Resolve(inputParameter, expressionToBeResolved, clauseGenerationContext);
    }

    internal class CustomMethodInfoBasedNodeTypeRegistryFactory : DefaultMethodInfoBasedNodeTypeRegistryFactory
    {
        public override INodeTypeProvider Create()
        {
            RegisterMethods(IgnorePropertyExpressionNode.SupportedMethods, typeof(IgnorePropertyExpressionNode));
            return base.Create();
        }
    }

    public class PropertyIgnorableSelectExpression : SelectExpression
    {
        public List<PropertyInfo> IgnoredProperties { get; } = new List<PropertyInfo>();

        public PropertyIgnorableSelectExpression(
          SelectExpressionDependencies dependencies,
          RelationalQueryCompilationContext queryCompilationContext) : base(dependencies, queryCompilationContext) => SetCustomSelectExpressionProperties(queryCompilationContext);

        public PropertyIgnorableSelectExpression(
          SelectExpressionDependencies dependencies,
          RelationalQueryCompilationContext queryCompilationContext,
          string alias) : base(dependencies, queryCompilationContext, alias) => SetCustomSelectExpressionProperties(queryCompilationContext);

        private void SetCustomSelectExpressionProperties(RelationalQueryCompilationContext queryCompilationContext)
        {
            var lastTrackingModifier
                = queryCompilationContext.QueryAnnotations
                    .OfType<TrackingResultOperator>()
                    .LastOrDefault();

            if (lastTrackingModifier?.IsTracking == false)
            {
                foreach (var ignorePropertyResultOperator in queryCompilationContext.QueryAnnotations.OfType<IgnorePropertyResultOperator>())
                {
                    if (ignorePropertyResultOperator.PropertyPathLambda.Body is MemberExpression memberExpression)
                    {
                        IgnoredProperties.Add((PropertyInfo) memberExpression.Member);
                    }
                }
            }
        }
    }

    internal class PropertyIgnorableSelectExpressionFactory : SelectExpressionFactory
    {
        public PropertyIgnorableSelectExpressionFactory(SelectExpressionDependencies dependencies)
          : base(dependencies)
        {
        }

        public override SelectExpression Create(RelationalQueryCompilationContext queryCompilationContext)
          => new PropertyIgnorableSelectExpression(Dependencies, queryCompilationContext);

        public override SelectExpression Create(RelationalQueryCompilationContext queryCompilationContext, string alias)
          => new PropertyIgnorableSelectExpression(Dependencies, queryCompilationContext, alias);
    }

    internal class CustomSqlServerQuerySqlGeneratorFactory : QuerySqlGeneratorFactoryBase
    {
        public CustomSqlServerQuerySqlGeneratorFactory(
          QuerySqlGeneratorDependencies dependencies,
          ISqlServerOptions sqlServerOptions) : base(dependencies) { }

        public override IQuerySqlGenerator CreateDefault(SelectExpression selectExpression)
          => new CustomQuerySqlGenerator(
            Dependencies,
            selectExpression);
    }

    public class CustomQuerySqlGenerator : DefaultQuerySqlGenerator
    {
        public CustomQuerySqlGenerator(QuerySqlGeneratorDependencies dependencies, SelectExpression selectExpression) : base(dependencies, selectExpression)
        {
        }

        protected override void GenerateList<t>(
             IReadOnlyList<t> items,
             Action<t> generationAction,
             Action<IRelationalCommandBuilder> joinAction = null)
        {
            if (typeof(t) == typeof(Expression) && SelectExpression is PropertyIgnorableSelectExpression expression && expression.IgnoredProperties.Any())
            {
                GenerateListExpression(items, generationAction, joinAction, expression);
                return;
            }

            base.GenerateList(items, generationAction, joinAction);
        }

        protected void GenerateListExpression<t>(
            IReadOnlyList<t> items,
            Action<t> generationAction,
            Action<IRelationalCommandBuilder> joinAction,
            PropertyIgnorableSelectExpression selectExpression)
        {
            NotNull(items, nameof(items));
            NotNull(generationAction, nameof(generationAction));

            joinAction = joinAction ?? (isb => isb.Append(", "));

            for (var i = 0; i < items.Count; i++)
            {
                if (i > 0)
                {
                    joinAction(Sql);
                }

                var item = items[i];
                if (item is ColumnExpression column && column.Property?.PropertyInfo != null
                                && selectExpression.IgnoredProperties.Any(ip =>
                                       ip.PropertyType == column.Property.PropertyInfo.PropertyType
                                    && ip.DeclaringType == column.Property.PropertyInfo.DeclaringType
                                    && ip.Name == column.Property.PropertyInfo.Name)
                                    )
                {
                    string defaultValue;
                    if (column.Property.IsNullable)
                    {
                        defaultValue = "null";
                    }
                    else
                    {
                        //TODO: add more types here

                        if (column.Property.PropertyInfo.PropertyType == typeof(string))
                        {
                            defaultValue = "''";
                        }
                        else
                        {
                            throw new NotSupportedException($"Ignoring {column.Property.PropertyInfo.PropertyType} not supported by {nameof(IQueryableExtensions.IgnoreProperty)}");
                        }
                    }

                    base.Sql.Append($"{defaultValue} AS [{column.Name}]");
                    continue;
                }

                generationAction(item);
            }
        }

        public static T NotNull<T>(T value, string parameterName)
        {
#pragma warning disable IDE0041 // Use 'is null' check
            if (ReferenceEquals(value, null))
#pragma warning restore IDE0041 // Use 'is null' check
            {
                throw new ArgumentNullException(parameterName);
            }

            return value;
        }
    }

it requires this in DbContext:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
              .ReplaceService<INodeTypeProviderFactory, CustomMethodInfoBasedNodeTypeRegistryFactory>()
              .ReplaceService<ISelectExpressionFactory, PropertyIgnorableSelectExpressionFactory>()
              .ReplaceService<IQuerySqlGeneratorFactory, CustomSqlServerQuerySqlGeneratorFactory>();

            base.OnConfiguring(optionsBuilder);
        }

[QuanTran91]

Are there any approaches that are less complicated than @hmdhasani ?
I need to ignore some audit properties (createdDate, userId,...) when querying from db (reduce extra step to encrypt/decrypt) but I also want to add or update them.

[AndriySvyryd]

See #21251 for ideas on how to combine with Include

[QuanTran91]

Thanks @AndriySvyryd but it would be better if it has Exclude :)

[benstevens48]

I'd also like to upvote this idea. The amount of data returned by a query has a large effect on performance. It's currently quite difficult in to return only the data needed without writing new code every time. My suggestion is like this. Add two extension methods

IncludeProperties<T>(this IQueryable<T> source, Type entityType, IEnumerable<string> propertyNames);
ExcludeProperties<T>(this IQueryable<T> source, Type entityType, IEnumerable<string> propertyNames);

These do not do anything by themselves, but they add metadata to the expression tree and act as a filter when generating the SQL query for an entity of the given type so that only matching properties are included in the select statement. It would be used like this

class User{
 public Guid Id {get;set;}
 public string Name {get;set;}
 public string PsswordHash {get;set;}
//More properties...
}
class Post{
 public Guid Id {get;set;}
 public string Title {get;set;}
 public string Content {get;set;}
 public Guid AuthorId {get;set;}
 public DateTimeOffset Date {get;set;}
 public User Author{get;set;}
}
class PostHelper{
 public static readonly List<string> CommonProperties = new List<string>(){ nameof(Post.Id), nameof(Post.Title) };
}

PostsDbSet
.IncludeProperties(typeof(Post), PostHelper.CommonProperties)
.IncludeProperties(typeof(User), new string[]{nameof(User.Id), nameof(User.Name)})
.Where(item => item.Date > myDate).Include(item => item.Author);

The generated SQL would then only include the Id and Title properties in the select query for the Post, and the Author would be included but only with the Id and Name. ExcludeProperties would work in a similar way.

With such a query, I guess tracking should be disabled by default as it would be confusing to track entities with only partial properties.

With these methods, it would be far easier to write efficient queries that only return the required data without having to write out all the properties in every single query or define complex expressions for later use (which don't really work for navigation properties anyway). Data transfer objects can then be created from entities using standard functions which run after the data has been fetched from the database rather than trying to write a reusable expression incorporating the data transfer object.

[roji]

Just since it doesn't seem to appear above, I'm mentioning the obvious solution of projecting out to an anonymous type (as documented in our perf docs):

var results = context.Blogs
    .Where(....)
    .Select(b => new { b.Id, b.Name })

This will efficiently project out only the two properties in SQL. The main drawback here is that you get a different type and not Blog, which may be problematic to pass on in your code (but a new Blog can be constructed from that as a workaround). Also, this is by nature an untracked query.

[benstevens48]

@roji - The other main drawback of that is that you have to write out the properties every time you write such a query, and then also do the mapping to your eventual data type every time, which is extremely cumbersome, especially if you end up adding new properties to your model. You can define a reusable expression with a data transfer object but that is also cumbersome and doesn't work for child navigation properties. My proposed solution above allows you to define a set of 'CommonProperties' to include like the example I gave, which can be reused.

[roji]

If the goal is to always load the same set of common properties, and then construct a Blog instance from that, it's pretty trivial should be pretty trivial to just do that in an extension method that can be used everywhere:

public static IEnumerable<Blog> WithMinimalProperties(this IQueryable<Blog> source)
{
    return source
        .Select(b => new { b.Id, b.Name })
        .AsEnumerable()
        .Select(x => new Blog { Id = x.Id, Name = x.Name });
}

You could then call it as follows:

var blogs = ctx.Blogs.WithMinimalProperties().ToList();

... or something along those lines. Not saying it's a perfect solution, but unless you specifically need tracking, something like this seems fine to me.

[roji]

Of course, after writing the above I realized you can just do:

public static IQueryable<Blog> WithMinimalProperties(this IQueryable<Blog> source)
    => source.Select(b => new Blog { Id = b.Id, Name = b.Name });

[benstevens48]

That's not bad for simple cases but is it possible to mix and match that with partial navigation properties? Like can I optionally include Blog.Author.Name as well, without writing out all the properties for blog again?

[roji]

I don't see why not:

public static IQueryable<Blog> WithMinimalProperties(this IQueryable<Blog> source)
    => source.Select(b => new Blog
    {
        Id = b.Id,
        Name = b.Name,
        Posts = b.Posts.Select(p => new Post { Id = p.Id }).ToList()
    });

This produces the following SQL:

SELECT [b].[Id], [b].[Name], [p].[Id]
FROM [Blogs] AS [b]
LEFT JOIN [Post] AS [p] ON [b].[Id] = [p].[BlogId]
WHERE [b].[Name] = N'hello'
ORDER BY [b].[Id], [p].[Id]

Note that both Blog and Post have additional properties which aren't being fetched here. Note also that this doesn't wire the Posts to point back to their Blog, as EF Core does, but you should be able to do that as well if you want.

[benstevens48]

@roji - Ok, but the point is I don't always want to include the post properties. Maybe I should have provided a fuller example in the first place. Suppose I have a Post class which has an Author property of type User.

class User{
 public Guid Id {get;set;}
 public string Name {get;set;}
 public string PsswordHash {get;set;}
//More properties...
}
class Post{
 public Guid Id {get;set;}
 public string Title {get;set;}
 public string Content {get;set;}
 public Guid AuthorId {get;set;}
 public DateTimeOffset Date {get;set;}
 public User Author{get;set;}
}

Then I define some core properties associated with each class like this. (I write them like this although I don't mind writing them another way).

class PostHelper{
 public static readonly List<string> CoreProperties = new List<string>(){ nameof(Post.Id), nameof(Post.Title) };
}
class UserHelper{
 public static readonly List<string> CoreProperties = new List<string>(){ nameof(User.Id), nameof(User.Name) };
}

I don't want to declare/write out these properties more than once. Now I want to be able to do the following. I want to be able to select users from the users table, only returning the user's CoreProperties. I want to be able to select Posts from the Posts table, only returning the post's CoreProperties (and not including the Author). I want to be able to select Posts from the Posts table, including the post's core properties and including the post's Author with only CoreProperties for the author.

I can't see any way of doing that currently without writing out the properties multiple times. Please correct me if I'm wrong!

[roji]

As I said above, this isn't a perfect solution, and yes, it would require duplicating the list of common properties in multiple places. If you really want to, you should be able to centralize the list of common properties by manually building the expression tree passed to Select inside WithMinimalProperties above (i.e. with Expression.New and similar); that would allow you to have a single method which produces the initializers for the common properties, which can be called from any place where you need to initialize a given type.

But of course that gets a bit more complicated. I'm not saying that built-in partial loading in EF Core should be implemented - although if so, the main reason is probably per-property change tracking rather than just centralizing the list of common properties. But the code I posted above - albeit imperfect - should provide you with an acceptable workaround until the full feature is implemented.

[benstevens48]

@roji - thanks for the discussion. Honestly, I think you are underestimating how off-putting it is to have to repeatedly write out a list of properties, and I think there would be a great benefit from a simple way to include partial properties in a reusable way, even without the consideration of entity tracking. The solution I suggested has the advantage that it's still possible to use the rest of the EF core API (which is great in general) such as Include and ThenInclude in the same way, although I don't claim to know how it would be implemented behind the scenes. But any solution that allows achieving a similar result would be good. Thanks for the tip about expressions. I am already using a simple expression to achieve reusability (which has a similar effect to the extension method you suggested), but for navigation properties at the moment I either include the full item or not at all, because my knowledge of expressions was zero until a few days ago and that was the best I could do in limited time!

Is there any chance of moving on the discussion slightly, such as coming up with a proposed API? Or is it still too uncertain whether this feature would be included?

[roji]

I think you are underestimating how off-putting it is to have to repeatedly write out a list of properties

Note that above I proposed repeating the common properties only in each WithMinimalProperties definition. When writing queries, you'd just write WithMinimalProperties without having to specify the properties.

Is there any chance of moving on the discussion slightly, such as coming up with a proposed API? Or is it still too uncertain whether this feature would be included?

This issue is in the backlog, so we won't be getting to work on it for 6.0; as such, it's probably too early to go into any concrete discussion (this is why I'm also suggesting looking at workarounds).

[benstevens48]

Ok, thanks again for the discussion. Fingers crossed this or something equivalent will make it into v7.0 :)

[bzbetty]

Current work around using TagWith and an interceptor to rewrite the SQL.

I imagine it'd also be possible to write an extension method like Select that takes a list of what to exclude

[rahou25]

Just since it doesn't seem to appear above, I'm mentioning the obvious solution of projecting out to an anonymous type (as documented in our perf docs):

var results = context.Blogs
    .Where(....)
    .Select(b => new { b.Id, b.Name })

This will efficiently project out only the two properties in SQL. The main drawback here is that you get a different type and not Blog, which may be problematic to pass on in your code (but a new Blog can be constructed from that as a workaround). Also, this is by nature an untracked query.

This one is a good workaround, but it's quicker most of the time to just exclude 1 or 2 columns than selecting those needed.