如何仅在Entity Framework 6.1中加载子对象的某些字段?

问题描述

我正在研究具有两个类，Product和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; }
}

我如何执行查询以检索产品及其交易日期?我尝试过类似的

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

但是会引发异常:

编辑以进行澄清:我要实现的实际上是 not 加载TransactionId和Customer并加载Transaction时.

解决方案

要实现所需的功能，除了将查询投影到匿名类型或DTO之外，别无选择.如您所见，在 Include 扩展方法，您可以只指定要加载的相关实体，该实体在与表的内部联接(或几个联接，请参见引号链接的备注部分)中进行翻译，并不意味着您将从相关实体中加载所有属性.如果调用Select方法，则可以选择要投影的列，但不能使用实体类型投影Linq to Entities查询，则必须使用我上面评论的两个选项之一.因此，我的建议是在您的业务逻辑层中创建一组类(DTO)，以投影查询结果，例如:

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

稍后您可以执行以下操作:

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

请参见在这种情况下，我不需要调用Include扩展方法，因为在Select中，我正在从Transactions表中投影一列.在这一点上，仍然没有加载数据，您只需要定义一个linq查询，然后将其转换为sql.什么时候发生?当您调用ToList扩展方法时.

作为最后的建议，我建议您查看 Automapper .将实体与其各自的DTO映射后，您可以通过以下方式进行查询:

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

此链接 ProjectTo扩展方法的更多信息>

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:

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

解决方案

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

这篇关于如何仅在Entity Framework 6.1中加载子对象的某些字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！