Linq: Selecting specific columns using DTO

<!-- language-all: csharp -->

I know that in manual way I can choose what columns are fetched from database (simplified examples):

db.Products.Where(...).Select(p => new {p.Id, p.Name...})

But, when i have DTO object:

public class ProductDTO {
	public int Id { get; set; }
	public string Name { get; set; }

	public ProductDTO(Product p) {
		Id = p.Id;
		Name = p.Name;           
	}
}

and query:

db.Products.Where(...).Select(p => new ProductDTO(p))

from database are fetched ALL FIELDS, not only Id and Name. It's waste of resources.

Why linq can't see what properties are in DTO and fetch only them?

I guess that's what the DTO is for, so that I don't have to write a manual query every time.

In your case, when you create a new instance of ProductDTO, unfortunatelly LINQ doesn't have any information about the properties of the ProductDTO class. But it knows that your ProductDTO waits for Product objecy, and therefore downloads the full entity from the database, as you tell to get the FULL Product p into your ProductDTO constructor.

> db.Products.Where(...).Select(p => new ProductDTO(p))

The possible solution is to define the mapping of needed properties EXPLICITLY in your LINQ query:

db.Products.Where(...).Select(p => new ProductDTO(p.Id, p.Name))

You could also use AutoMapper for that to avoid code repeating. It gives you access to ProjectTo<T>() extension method that should help you:

db.Products.Where(...).ProjectTo<ProductDTO>()

Note: make sure you configured the mapping properly:

Mapper.Initialize(cfg => {
    cfg.CreateMap<Product, ProductDTO>();
});

If the Products and ProductsDTO have identical property names and types, you can use a simple mapper.

Here's an example of a method that can automatically build an expression tree to perform the mapping:

    public static Expression<Func<TSource, TResult>> BuildAutoProjection<TSource, TResult>()
    {
        var srcparam = Expression.Parameter(typeof(TSource), "src");
        var createObject = Expression.New(typeof(TResult));
        var propmaps = typeof(TSource).GetProperties().
            Select(p => (p, typeof(TResult).GetProperty(p.Name)))
            .Where(x => x.Item2 is not null)
            .Select(x => Expression.Bind(x.Item2, Expression.Property(srcparam, x.Item1.Name)));
        var InitializePropertiesOnObject = Expression.MemberInit(createObject, propmaps.ToArray());

        return Expression.Lambda<Func<TSource, TResult>>(InitializePropertiesOnObject, srcparam);
    }

You can then use it like this:

db.Products.Where(...).Select(BuildAutoProjection<Products, ProductDTO>());

This method does not require AutoMapper and generates a SELECT statement only with columns that exist in the ProductDTO type.