Efcore: SQL Query columns when doing select loading on children entities

[rudeseagull]

I'm really lost trying to understand the loading strategy of children entities when using navigational properties of parent entity. Especially in regard to the SQL Query produced by LINQ. If this is the expected behaviour I would like to know how I can achieve the result I'm expecting.

Steps to reproduce

I'm not in charge of the database. Below Models have been created by reverse-scaffolding. I removed non pertinent properties for the sake of the demo.

public partial class Orders
    {
        public Guid OrderId { get; set; }
        public DateTime OrderCreationDate { get; set; }
        public virtual Addresses Address { get; set; }
    }

    public partial class Addresses
    {
        public Guid AddressId { get; set; }
        public string Name { get; set; }
        public string Company { get; set; }
        public string VatNumber { get; set; }
        public string Street { get; set; }
        public string Number { get; set; }
        public string City { get; set; }
        public int? PostalCode { get; set; }
        public string Country { get; set; }
        public double? Latitude { get; set; }
        public double? Longitude { get; set; }

        public virtual ICollection<Invoices> Invoices { get; set; }
        public virtual ICollection<Orders> OrdersAddress { get; set; }
        public virtual ICollection<Orders> OrdersAddressEnvois { get; set; }
        public virtual ICollection<Users> Users { get; set; }
    }

I want to map my Orders to OrdersDTO with a child AddressDTO. Result looks like this

 {
    "OrderID": "e62e83ab-ba64-4ba1-a073-cbf5e7ff6042",
    "Address": {
      "Street": "Road Rainbow",
      "Number": "420",
      "Zip": 4000,
      "City": "Sky"
    }
  }

I use a simple Select projection in the often called query object pattern style with an extension method on IQueryable

    public static class Mapping
    {
        public static IQueryable<OrderDTO> MapOrderDTO(this IQueryable<Orders> orders)
        {
            return orders
                .Select(order => new OrderDTO
                {
                    OrderID = order.OrderId,
                    AddressCustom = order.Address.MapAddressDTO(),
                });
        }

I want to use the same pattern for my child property AddressCustom, so I write an extension method for Addresses

public static AddressDTO MapAddressDTO(this Addresses address)
        {
            return new AddressDTO
            {
                City = address.City,
                Street = address.Street,
                Num = address.Number,
                Zip = address.PostalCode
            };
        }

Then at the top level service

await _context.Orders
                .AsNoTracking()
                .OrderByDescending(o => o.OrderCreationDate)
                .MapOrderDTO()
                .Page(options.PageNumber - 1, options.PageSize)
                .ToListAsync()

So far so good BUT when you look at the SQL Query you can see that the whole Address entity is selected, not just the column I want but everything. It's like an Include when I specifically don't want that.

SELECT TOP(10) [o].[OrderID], [a].[AddressID], 
[a].[City], [a].[Company], [a].[Country], [a].[Latitude], 
[a].[Longitude], [a].[Name], [a].[Number], [a].[PostalCode], [a].[Street], [a].[VatNumber]
FROM [Orders] AS [o]
LEFT JOIN [Addresses] AS [a] ON [o].[AddressID] = [a].[AddressID]
ORDER BY [o].[OrderCreationDate] DESC

I really don't understand this because in production my business logic and my DTO are obviously more complicated with a lot of properties, nested collections, etc. The SQL queries quickly become bloated with hundreds of unnecessary columns in the select part and performances are going down by a huge margin.

The only work around I found is to give up my extension method approach entirely and declare a new AddressDTO immediately after the property declaration, like this.

    public static class Mapping
    {
        public static IQueryable<OrderDTO> MapOrderDTO(this IQueryable<Orders> orders)
        {
            return orders
                .Select(order => new OrderDTO
                {
                    OrderID = order.OrderId,
                    AddressCustom = new AddressDTO 
                    { 
                        City = order.Address.City, 
                        Street = order.Address.Street, 
                        Number = order.Address.Number, 
                        Zip = order.Address.PostalCode 
                    }
                });
        }

The SQL query is now correct

SELECT TOP(10) [o].[OrderID], [a].[City], 
[a].[Street], [a].[Number], [a].[PostalCode] AS [Zip]
FROM [Orders] AS [o]
LEFT JOIN [Addresses] AS [a] ON [o].[AddressID] = [a].[AddressID]
ORDER BY [o].[OrderCreationDate] DESC

As you probably can tell, my business logic would be extremely hard to express - maybe even impossible in some cases - if my only choice is to use direct object instantiation like this. The example is over simplified to illustrate my problem but I can reproduce much more complex cases if needed.

I really need a way to build complex object without SQL loading huge number of unnecessary columns when I'm not using them anywhere in the codebase.

I would very glad if someone can point me to the issue here (even if I'm the issue).

Further technical details

EF Core version: 3.1.2
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: NET Core 3.1
Operating system: Windows 10
IDE: Microsoft Visual Studio Community 2019 Version 16.5.1

[smitpatel]

Your method MapOrderDTO takes IQueryable and returns back IQueryable. It actually builds the expression tree to integrate the content of Select call you are doing. MapAddressDTO is not creating any expression tree inside. It is an opaque method in the projection which we don't have any way to know what is inside. So in order to evaluate this opaque method, we need to construct object.

Since Address is not an enumerable type, there is no easy way of doing what you want manually without creating expression tree dynamically. Consider using AutoMapper which allows you to specify mapping for each model type and generates dynamic select for you.

[rudeseagull]

Thank you very much for your answer, I'll give AutoMapper a try then. Closing.