Lots of select operation in group by when entity has OwnsOne configuration

[leehom0123]

Hello,
I use group by query with OnwnsOne Entity, which like this:

public partial class Fee
{
        public Guid Id { get; set; }
 
        public ShareCroppingMoney ShareCroppingMoney { get; set; } = new ShareCroppingMoney();
}

public class ShareCroppingMoney
{
	public virtual decimal TaxServiceCharge { get; set; }

	public virtual decimal Nation { get; set; }

	public virtual decimal Province { get; set; } 

	public virtual decimal City { get; set; } 

	public virtual decimal District { get; set; }

	public virtual decimal Town { get; set; } 

	public virtual decimal Industry { get; set; }

	public virtual decimal Company { get; set; } 
}


    public partial class FeeMap : IEntityTypeConfiguration<Fee>
    {


        public void Configure(EntityTypeBuilder<Fee> builder)
        {
            builder.ToTable("Fp_Fee");
            builder.HasKey(x => x.Id);

        
            builder.Property(x => x.Money).HasColumnType("decimal(18, 2)");
         
            builder.OwnsOne(x => x.ShareCroppingMoney, shareCropping =>
             {
                 shareCropping.Property(x => x.TaxServiceCharge).HasColumnType("decimal(18, 2)");
                 shareCropping.Property(x => x.Nation).HasColumnType("decimal(18, 2)");
                 shareCropping.Property(x => x.Province).HasColumnType("decimal(18, 2)");
                 shareCropping.Property(x => x.City).HasColumnType("decimal(18, 2)");
                 shareCropping.Property(x => x.District).HasColumnType("decimal(18, 2)");
                 shareCropping.Property(x => x.Town).HasColumnType("decimal(18, 2)");
                 shareCropping.Property(x => x.Industry).HasColumnType("decimal(18, 2)");
                 shareCropping.Property(x => x.Company).HasColumnType("decimal(18, 2)");
             });
        }
    }

Then, query like this:

            var sum = await dbContext.Fees.GroupBy(x => 1).Select(x => new
            {
                Money = x.Sum(s => s.Money),
                City = x.Sum(s => s.ShareCroppingMoney.City),
                Company = x.Sum(s => s.ShareCroppingMoney.Company),
                District = x.Sum(s => s.ShareCroppingMoney.District),
                Industry = x.Sum(s => s.ShareCroppingMoney.Industry),
                Nation = x.Sum(s => s.ShareCroppingMoney.Nation),
                Province = x.Sum(s => s.ShareCroppingMoney.Province),
                TaxServiceCharge = x.Sum(s => s.ShareCroppingMoney.TaxServiceCharge)
            }).ToListAsync();

And you will see lot of select:

How can I resolve this problem? Is that a bug?

[smitpatel]

This happens because each aggregate operator contains nav expansion due to relinq structure.
If you rewrite query to have nav expanded out before group by then you would be able to get single select.

                var sum = db.Fees.Select(x => new { x, x.ShareCroppingMoney })
                    .GroupBy(x => 1)
                    .Select(x => new
                    {
                        //Money = x.Sum(s => s.Money),
                        City = x.Sum(s => s.ShareCroppingMoney.City),
                        Company = x.Sum(s => s.ShareCroppingMoney.Company),
                        District = x.Sum(s => s.ShareCroppingMoney.District),
                        Industry = x.Sum(s => s.ShareCroppingMoney.Industry),
                        Nation = x.Sum(s => s.ShareCroppingMoney.Nation),
                        Province = x.Sum(s => s.ShareCroppingMoney.Province),
                        TaxServiceCharge = x.Sum(s => s.ShareCroppingMoney.TaxServiceCharge)
                    }).ToList();

[smitpatel]

Further, if you are just applying aggregate operator over ShareCroppingMoney fields, then you can also write query following way and it would do aggregate operations on server.

                var sum = db.Fees
                    .GroupBy(x => 1, x => x.ShareCroppingMoney )
                    .Select(x => new
                    {
                        //Money = x.Sum(s => s.Money),
                        City = x.Sum(s => s.City),
                        Company = x.Sum(s => s.Company),
                        District = x.Sum(s => s.District),
                        Industry = x.Sum(s => s.Industry),
                        Nation = x.Sum(s => s.Nation),
                        Province = x.Sum(s => s.Province),
                        TaxServiceCharge = x.Sum(s => s.TaxServiceCharge)
                    }).ToList();

[leehom0123]

This happens because each aggregate operator contains nav expansion due to relinq structure.
If you rewrite query to have nav expanded out before group by then you would be able to get single select.

                var sum = db.Fees.Select(x => new { x, x.ShareCroppingMoney })
                    .GroupBy(x => 1)
                    .Select(x => new
                    {
                        //Money = x.Sum(s => s.Money),
                        City = x.Sum(s => s.ShareCroppingMoney.City),
                        Company = x.Sum(s => s.ShareCroppingMoney.Company),
                        District = x.Sum(s => s.ShareCroppingMoney.District),
                        Industry = x.Sum(s => s.ShareCroppingMoney.Industry),
                        Nation = x.Sum(s => s.ShareCroppingMoney.Nation),
                        Province = x.Sum(s => s.ShareCroppingMoney.Province),
                        TaxServiceCharge = x.Sum(s => s.ShareCroppingMoney.TaxServiceCharge)
                    }).ToList();

I try to use this, and here is the sql:

SELECT [x].[Id], [x].[BusinessDate], [x].[CloseAccountId], [x].[CompanyId], [x].[FeeTypeId], [x].[IndustryId], [x].[LuAdministrativeDivisionId], [x].[LuLevelId], [x].[Money], [x].[PaymentDate], [x].[ReportingPeriodBegin], [x].[ReportingPeriodEnd], [x].[Status], [x].[TaxAdministrativeDivisionId], [x].[TaxBase], [x].[TaxLevelId], [x].[TaxRate], [x].[TransferId], [x].[Id], [x].[ShareCroppingMoney_City], [x].[ShareCroppingMoney_Company], [x].[ShareCroppingMoney_District], [x].[ShareCroppingMoney_Industry], [x].[ShareCroppingMoney_Nation], [x].[ShareCroppingMoney_Province], [x].[ShareCroppingMoney_TaxServiceCharge], [x].[ShareCroppingMoney_Town]
FROM [Fp_Fee] AS [x]
ORDER BY (SELECT 1)

It looks like Sum in MEMORY

[leehom0123]

Further, if you are just applying aggregate operator over ShareCroppingMoney fields, then you can also write query following way and it would do aggregate operations on server.

                var sum = db.Fees
                    .GroupBy(x => 1, x => x.ShareCroppingMoney )
                    .Select(x => new
                    {
                        //Money = x.Sum(s => s.Money),
                        City = x.Sum(s => s.City),
                        Company = x.Sum(s => s.Company),
                        District = x.Sum(s => s.District),
                        Industry = x.Sum(s => s.Industry),
                        Nation = x.Sum(s => s.Nation),
                        Province = x.Sum(s => s.Province),
                        TaxServiceCharge = x.Sum(s => s.TaxServiceCharge)
                    }).ToList();

I try this too, and there are more select query.