0

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.

SaleCar
  • 1,086
  • 9
  • 23
  • The lambda `p => new ProductDTO(p)` converts into a delegate that EF doesn't know what to do with. Simply EF Core provider can not translate it to SQL. So it executes first and then goes into constructor of `ProductDTO` Have you tried turning it into an expression? What else have you tried? – Sebastian Siemens Feb 26 '23 at 12:51
  • I can see that, but why EF can't see what are unused vars from DTO class and skip fetching it from database. A little intelligence isn't such a problem, is it? I don't know, maybe there is another solution? How do I pull the columns that are only according to DTO? Why would I write everything twice, there is no logic to it. Or the unnecessary consumption of resources is inevitable, so that it always pulls all the data from the database. – SaleCar Feb 26 '23 at 12:58

2 Answers2

3

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>();
});
Ivan Vydrin
  • 273
  • 7
  • Here is some related question might be helpful to configure AutoMapper in combination with EF: https://stackoverflow.com/questions/53528967/how-to-use-projectto-with-automapper-8-0-dependency-injection – Ivan Vydrin Feb 26 '23 at 13:34
1

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.

Yuri Kovalenko
  • 11
  • 1