EF6 eager load single property of related entity

Question

IN EF6, i have an entity Customer, with a navigation property to entity Address. Address entity contains a property "City".

I can eager load the Address entity while getting all Customers like this:

_dbSet.Customers.Include(customer => customer.Address);

This gives me all the customers, with all the Address properties eager loaded.

Of course this works fine, but the only thing i need from the Address table is the field "City", and it does not feel good to fetch all the address properties from the persistent data store (SQL Server) while not needing them.

I tried the following:

_dbSet.Customers.Include(customer => customer.Address.City);

...but this gives me a runtime exception:

An unhandled exception of type 'System.InvalidOperationException' occurred in mscorlib.dll

Additional information: A specified Include path is not valid. The EntityType 'MyModel.Address'does not declare a navigation property with the name 'City'.

I understand this, since City is just a field, and not a relation to another table / entity.

But is there another way to accomplish what i want, or is it best practice to just include the whole Address entity, even if i only need the city field???

What i want is that i can use myCustomer.Address.City, without having an extra query to the database, but for examle when i use myCustomer.Address.Street, the Street property is not eager loaded, and should be additionally fetched from the database...

Answer 1

Select only the properties you want, EF will only load what's needed.

var query = _dbSet.Customers.Include(customer => customer.Address);
var data = query.Select(c => new { Customer = c, City = c.Address.City });

Answer 2

If you are really set on using the same entity throughout your code base, then you could get around the issue using something similar to what Stef proposed:

var query = _dbSet.Customers.Include(customer => customer.Address);
var data = query
    .Select(c => new { Customer = c, City = c.Address.City })
    .ToList() //executes the IQueryable, and fetches the Customer and City (only) from the DB
    .ForEach(x => x.Customer.Address = new Address { City = x.City })
    .Select(x => x.Customer)
    .ToList();

I am very much in favour of DTOs and not using entity objects in the whole code base, but the above will give you a list of Customers which have Address objects with only the City field populated. Obviously, I make the assumption that your objects have public setters, which entity objects typically do have.