Returning Partially Filled Entity

Question

My colleague is trying to fetch only needed properties as best practice for avoid unnecessary workload on database side as below.

    public async Task<IEnumerable<Product>> GetProducts(List<int> ids)
    {
        var items = await _context.Products.Where(u => ids.Contains(u.Id))
                        .Select(u => new { u.Id, u.Name, u.Published })
                        .ToListAsync();

        return items.Select(u => new Product{ Id = u.Id, Name = u.Name,Published = u.Published });
    }

    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public bool Published { get; set; }
        public string MetaTitle { get; set; }
        public string MetaKeywords { get; set; }
        public string Sku { get; set; }
        public decimal Price { get; set; }
        public decimal Cost { get; set; }
        public DateTime CreatedAt { get; set; }
        ///...And other properties goes here
    }

As you have seen, there are only 3 properties fetched from database despite product has many properties.

IMO fetching all properties is not really has important workload. I would only do this in the case when there is performance issue on the query. i have been pointed that it is not possible to know what exactly returned from method without reading implementation detail. Not only that, you can't use exactly same method name when you need another method which has exactly same method sign but fetching different properties. For example

public async Task<IEnumerable<Product>> GetProducts(List<int> ids)
{
    //logic goes here
    return items.Select(u => new Product{ Id = u.Id, Name = u.Name,Published = u.Published });
}

public async Task<IEnumerable<Product>> GetProducts(List<int> ids)
{
    //logic goes here
    return items.Select(u => new Product{ Id = u.Id, Name = u.Name,Cost = u.Cost, Sku = u.Sku });
}

Code is not going to be compiled because there are exactly same 2 methods and one of them should differentiate it's name. I am sure that it is really hard give meaningful names for such cases.

His arguments was returning specific Types(DTO) would cause a lot of types in the project.

It is actually a lot of types are not only issue. You can not use exactly same method name as well and it is very hard to give meaningful names for types and method names.

My solution to this problem, don't put this logic in a method. Inline query wherever it is used. Already there is no re-usability of the query and also query really doesn't contains any kind of logic which deserves to be reused in other places. But according to his idea, queries shouldn't not executed outside of service methods because strict layering rules.

Shortly what is your idea about returning partially filled entity from method ? Secondly what if we want to fetch only requested properties so what is best way to do that ?

I know you will say use DTO but trying to design everything this way going to make everything harder so in my opinion it should to be only used in the case of performance related issue but still inlining query fix all of these issues but some people have strict layering rules without pragmatic reasons.

I would like to hear your opinions.

Answer 1

No, the entity should have all of its data. Allowing it to be partially populated will result in odd errors as this type can be used anywhere but receivers will have no idea if its fully or partially populated. That's a good way to get unexpected behavior. Even worse will be eventually you'll need a different subset of the data than the case you're asking about now, and someone will create a new way to load only THAT subset of data. Your problem is now further exacerbated. Its also unlikely you'll have major performance improvements by selecting a smaller set of columns from a table; SQL still needs to read the entire row, I believe. Have you tested this approach too? It could be that you're prematurely optimizing.

Your objects should be designed around the use cases they support. Any data they have should be because that data is needed for the object to fulfil its purpose. DTOs match database tables (or other data layer things, maybe a web service) and so aren't really ever appropriate as entities. If you have a use case which doesn't need all the data, then it should be a separate type. If the existing class fulfils the needs of the use case, without modification no separate type need be created. But it shouldn't be special cased to only be partially populated.

Answer 2

Partially filling entities in database applications gives seldom a relevant performance gain, because the number of roundtrips of a query has typically a much bigger influence than the amount of data transferred in one roundtrip. In most practical cases, it does not make a huge difference if you pull 3 or 30 columns from a DB table as long as the number of queries does not change. So in 99% procent of the cases, using partially filled entities is not recommendable, this is premature optimization. For the same reason, it rarely brings benefits to use different DTOs for the same database table.

Of course, there are exceptions from this rule - for example, if an entity contains a large text or binary field, and you have use cases where you don't need the large field. Then it can be beneficial to load the entity without this field. But this is a rare case, you should optimize only for it after you have proven it to be the cause of a performance issue.

If you follow this advice, you won't get into you naming problem, since for those rare cases where you load the entities partially, it should not be hard to provide a method with a special name for this exceptional case, or a specific DTO if you prefer this. The standard case, however, should be still the case where the full entitity is returned with all attributes filled.

In the context of object-relational mapping, this is a well-known topic, it is called "partial objects". Some ORMs support them, some do not support them, and some support them, but with a big warning sign in the documentation.

Answer 3

If the use case dictates, prefer multiple entities versus partial filled. For example, maybe your initial query returns a list of products. If there are only a few properties needed, create an object with just those 3 properties. Once a user clicks on a particular product in the list, then go get the full product using a different object with all the properties filled.

This will optimize data transfer if the application is set up in that fashion.

Answer 4

I think you are confusing a database best practice (always specify the fields you are returning, rather than using a wildcard select) and selecting only the data you need.

The former issue is a best practice, not because it tries to return only data needed, but because it allows the table to evolve with fewer issues. For instance, you can add rows to the table and it won't break anything. Also, by specifying the fields, you can ensure the order the data will be selected in.

There is nothing wrong with returning all fields in a query, even if you're not using them (although if the table has 2000 columns that's probably a different story, but that's a bad design in itself).

In reality, this is often a "your mileage may vary" situation. let's say you're returning 1 million rows, then selecting a subset of columns can result in a much smaller amount of data returned. But in those cases, you should create new anonymous objects (if your language supports such) rather than partially filling an entity.

Remember, ORM's are not generally good at large batch updates, so you should not use entities if you need to update a large number of rows. You should issue those as batch commands. So that means you should limit your use of entity updating to smaller sets of data.