EF6 loads one property of a related object

Question

In EF6, I have an object client with a navigation property for an organization address. The address object contains the City property.

I can load the Address object, getting all clients as follows:

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

This gives me all the clients, with all the address properties being loaded.

Of course, this works fine, but the only thing I need in the Address table is the City field, and it’s not very good to get all the address properties from the persistent data store (SQL Server), while they are not required.

I tried the following:

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

... but this gives me an exception at runtime:

 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 because City is just a field, not a relation to another table / entity.

But is there any other way to accomplish what I want, or is it best to just include the entire Address object, even if I only need the city field ???

I want me to be able to use myCustomer.Address.City without having an extra query for the database, but for example, when I use myCustomer.Address.Street, the Street property is not loading and should be optional from the database ...

Answer 1

If you are really determined to use the same object throughout the code base, you can work around the problem by using something similar to what Stef suggested:

 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 take DTO very much and do not use entity objects in the entire code base, but the above will give you a list of Customer that has Address objects with only City field filled. Obviously, I make the assumption that your objects have public setters, whose object objects usually have.

Answer 2

Select only the properties you want, EF will load only what you need.

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