ProjectToType Causing Every Row to Load into Memory

[MoMack20]

We are using ProjectToType to load all data from a table that has maybe 8-10 rows. The table has a relation to our Orders table which has millions of rows. Somehow loading the OrderType table with 8-10 rows forces the Orders table to load which quickly blows out the memory for the application.

Here is the OrderType table setup:

   public class OrderType : IEntityTypeConfiguration<OrderType>
    {
        public OrderType()
        {
            Orders = new HashSet<Order>();
        }

        public byte Id { get; set; }
        public string TypeName { get; set; } = null!;
        public bool IsStaging { get; set; }

        public ICollection<Order> Orders { get; set; }

        public void Configure(EntityTypeBuilder<OrderType> builder)
        {
            builder.ToTable("OrderType", "Sales").HasKey(ot => ot.Id);

            builder.HasMany(ot => ot.Orders)
                .WithOne(o => o.OrderType)
                .HasForeignKey(o => o.OrderTypeId);
        }
    }

Here is the Order table setup:

public Order()
    {
        Adjustments = new HashSet<OrderAdjustment>();
        Items = new HashSet<OrderItem>();
        Notes = new HashSet<OrderNote>();
        Taxes = new HashSet<OrderTax>();
        OrderAddresses = new HashSet<OrderAddress>();
        OrderTransactions = new HashSet<OrderTransaction>();
        AutoReservationLogs = new HashSet<AutoReservationLog>();
    }

    public int OrderNumber { get; set; }
    public string? WebOrderNumber { get; set; }
    public int CustomerId { get; set; }
    public string? OrderSource { get; set; }
    public DateTimeOffset OrderDate { get; set; }
    public DateTimeOffset CreateDate { get; set; }
    public DateTimeOffset? ShipOnDate { get; set; }
    public bool IsApproved { get; set; }
    public DateTimeOffset? DateApproved { get; set; }
    public DateTimeOffset? DateCompleted { get; set; }
    public bool IsCancelled { get; set; }
    public bool HasBackorderedItem { get; set; }
    public DateTimeOffset UpdatedDate { get; set; }
    public int UpdatedBy { get; set; }
    public byte OrderTypeId { get; set; }
    public byte? ShippingMethodId { get; set; }
    public byte? AddressValidationStatusId { get; set; }
    public int StorageGroupId { get; set; }

    public Customer Customer { get; set; } = null!;
    public OrderDetail OrderDetail { get; set; } = null!;
    public UserProfile UserProfile { get; set; } = null!;
    public OrderReturn? OrderReturn { get; set; }
    public AddressValidationStatus? AddressValidationStatus { get; set; }
    public OrderType? OrderType { get; set; }
    public ShippingMethod? ShippingMethod { get; set; }
    public StorageGroup StorageGroup { get; set; } = null!;
    public ICollection<OrderAdjustment> Adjustments { get; set; }
    public ICollection<OrderItem> Items { get; set; }
    public ICollection<OrderNote> Notes { get; set; }
    public ICollection<OrderTax> Taxes { get; set; }
    public ICollection<OrderAddress> OrderAddresses { get; set; }
    public ICollection<OrderTransaction> OrderTransactions { get; set; }
    public ICollection<AutoReservationLog> AutoReservationLogs { get; set; }

    public void Configure(EntityTypeBuilder<Order> builder)
    {
        builder.ToTable("Order", "Sales").HasKey(b => b.OrderNumber);

        builder.HasOne(b => b.Customer)
            .WithMany(b => b.Orders)
            .HasForeignKey(b => b.CustomerId);

        builder.HasMany(o => o.Adjustments)
            .WithOne(oa => oa.Order)
            .HasForeignKey(oa => oa.OrderNumber);

        builder.HasMany(o => o.Items)
            .WithOne(oi => oi.Order)
            .HasForeignKey(oi => oi.OrderNumber);

        builder.HasMany(o => o.Notes)
            .WithOne(n => n.Order)
            .HasForeignKey(oi => oi.OrderNumber);

        builder.HasMany(o => o.Taxes)
            .WithOne(t => t.Order)
            .HasForeignKey(oi => oi.OrderNumber);

        builder.HasOne(b => b.UserProfile)
            .WithMany(b => b.Orders)
            .HasForeignKey(b => b.UpdatedBy);
    }

Here is the EF Query to load OrderTypes:

public async Task<List<OrderType>> GetOrderTypes()
        {
            async Task<List<OrderType>> Query(OrderManagerDbContext ctx) =>
                await ctx.OrderTypes.OrderBy(f => f.Id)
                    .ProjectToType<OrderType>().ToListAsync();
            return await OrderManagerContextFactory.ExecuteSnapshotQueryAsync(Query);
        }

public async Task<TResult> ExecuteSnapshotQueryAsync<TResult>(Func<TContext, Task<TResult>> query)
    {
        TResult result;
        await using (var ctx = await CreateDbContext())
        {
            await using (var trans = await ctx.Database.BeginTransactionAsync(IsolationLevel.Snapshot))
            {
                result = await query(ctx);
                await trans.CommitAsync();
            }
        }
        return result;
    }

Here is the resulting heap diagnostic 4 minutes after loading all OrderTypes:

[MoMack20]

I think I figured out where the problem is coming from. Elsewhere in our code we always ProjectToType from the EF model to a DTO. In this scenario its being ProjectToType from the EF model to the EF model. That seems to be what's causing the problem

[andrerav]

I'm not sure how to interpret your last comment -- is this an issue in Mapster, or an issue in your code?

On a sidenote -- it's usually a good idea to materialize the query before executing ProjectToType(). Mapster can run into issues with losing its context when using ProjectToType with async queries.

[MoMack20]

I don't think it is an issue with Mapster as much as an odd behavior with using ProjectToType() before materializing the query and mapping to a class that is also configured with EF.