How to only load certain fields of a child object in Entity Framework 6.1?

Question

I'm working on a model that has two classes, Product and Transaction.

public class Product
{
    [DataMember]
    public Guid ProductId {get; set;}

    [DataMember]
    public virtual ICollection<Transaction> Transactions { get; set; }
}

public class Transaction
{
    [DataMember]
    public Guid TransactionId {get; set;}

    [DataMember]
    public DateTimeOffset Date { get; set; }

    [DataMember]
    public String Customer { get; set; }
}

How do I do a query that will retrieve a product and the Date of its transactions? I tried something like

var product = db.Products.Include(p => p.Transactions.Select(t => new { t.Date })).Where(p => p.ProductId = productId);

But it throws an exception:

The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties

Edit for clarification: What I want to achieve is actually not loading TransactionId and Customer when Transaction is loaded.

Answer 1

To achieve what you need you don't have other choice than project your query to an anonymous type or a DTO. As you can see, in the Include extension method you can just specify the related entities you want to load which is translated in an inner join with a table (or several joins, see the Remarks section in the quoted link), but that doesn't mean you're going to load all the properties from the related entities. If you call the Select method you can choose which columns you want to project, but you can't project a Linq to Entities query using an entity type, you must use one of the two options that I commented above. So, my advice is create in your business logic layer a set of classes (DTOs) to project the result of your queries, eg:

 public class ProductDTO
 {
    [DataMember]
    public Guid ProductId {get; set;}
    [DataMember]
    public virtual IEnumerable<DateTime> TransactionDates { get; set; }
 }

Later you can do something like this:

var product = db.Products.Where(p => p.ProductId = productId)
                         .Select(pr=> new ProductDTO
                         {
                           ProductId = pr.ProductId,
                           TransactionDates = pr.Transactions.Select(tr=>tr.Date),
                         }.ToList();
 

See I don't need to call Include extension method in this case, because in the Select I'm projecting a column from Transactions table. In that point the data is not still loaded, you are just defining a linq query that later is translated to sql. When that take place?, when you call ToList extension method.

As a last recommendation, I suggest you to take a look to Automapper. Once you have mapped your entities with their respective DTOs, your queries could be this way:

var product = db.Products.Where(p => p.ProductId == productId)
                         .ProjectTo<ProductDTO>()    
                         .ToList();

More info about ProjectTo extension method in this link