从实体框架核心中的包含属性中选择特定属性 [英] Select specific properties from include ones in entity framework core

问题描述

我使用Entity Framework Core 2.0并具有以下2类：

新闻

  public class News 
 {
 public int Id {get;组; } 
公用字串Title {get;组; } 
公用字串Text {get;组; } 
 public DateTime ReleaseDate {get;组; } 
 public int AuthorID {get;组; } 
公共作者作者{get;组; } 
} 
  

作者

 公共类作者
 {
公共作者（）
 {
新闻=新列表<新闻>（ ）; 
} 
 
 public ind Id {get;组; } 
公用字串Username {get;组; } 
公用字串Firstname {get;组; } 
公用字串Lastname {get;组; } 
公共ICollection< News>新闻{get; set;} 
} 
  

我想用作者的新闻加载新闻用户名仅是因为我不需要其他信息，例如作者的姓氏和名字。

如果我执行此操作：

< pre class = lang-cs prettyprint-override> 静态void Main（string [] args）
{
使用（var ctx = new DbContext（））
{
var result = ctx.News.Include（x => x.Author）.Select（news => new
{
news = news，
Username = pr .Author.Username
}）。ToList（）;
}
}


Entity Framework Core 2.0生成数据库查询，例如：

  SELECT [x]。[ID]，[x]。[Text]，[x] .. [Title]，[x]。[AuthorId]，[x]。[ReleaseDate]，[x.Author]。[ID]，[x.Verfasser]。[Username] AS [Username]，[x.Verfasser] 。[Firstname]，[x.Verfasser]。[Lastname] 
 FROM [News] AS [x] 
 INNER JOIN [Author] AS [x.Author] ON [x]。[AuthorId] = [x.Author]。[ID] 
  

有没有办法实现我的预期行为？

解决方案

如果我正确理解，则不想加载 Author 实体（因为无法加载仅填充了一些非导航属性的实体）。

然后，问题是为什么您要专门添加 Include （x => x.Author），它请求EF加载 Author 。 Include / ThenInclude 方法支持尽快加载相关数据实体。当您使用投影（选择）时不需要它们。

有趣的是，EFC之前的所有EF（Core）版本2.0忽略了对投影类型查询的包含。甚至当前的EFC文档都指出，投影查询属于已忽略的包含类别。但是，您已经注意到EFC 2.0并没有忽略它！因此，它要么是实现错误，将被修复，要么是文档错误，将被更新。

现在，只需不使用 Include 在投影查询中。如果执行此操作：

  var result = ctx.News.Select（news => new 
 {
 news =新闻，
用户名= news.Author.Username 
}）。ToList（）; 
  

现在EFC生成的SQL查询符合预期：

  SELECT [news]。[Id]，[news]。[AuthorID]，[news]。[ReleaseDate]，[news]。[文本]，[新闻]。[标题]，[新闻作者]。[用户名] 
来自[新闻] AS [新闻] 
内加入[作者] AS [新闻。作者] ON [新闻] ]。[AuthorID] = [news.Author]。[Id] 
  

I use Entity Framework Core 2.0 and have this 2 classes:

News

public class News
{
    public int Id { get; set; }
    public string Title{ get; set; }
    public string Text { get; set; }
    public DateTime ReleaseDate{ get; set; }
    public int AuthorID{ get; set; }
    public Author Author{ get; set; }
}

Author

public class Author 
{
    public Author ()
    {
     News = new List<News>();
    }

    public int Id { get; set; }
    public string Username{ get; set; }
    public string Firstname{ get; set; }
    public string Lastname{ get; set; }
    public ICollection<News> News {get;set;}
}

I want to load the news with the author's username only because I don't need the other information like author's first and lastname.

If I execute this:

static void Main(string[] args)
{
  using (var ctx = new DbContext())
  {
   var result= ctx.News.Include(x => x.Author).Select(news => new
   {
     news= news ,
     Username= pr.Author.Username
   }).ToList();
  }
}

Entity Framework Core 2.0 generates a database query like:

SELECT [x].[ID], [x].[Text], [x].[Title], [x].[AuthorId], [x].[ReleaseDate], [x.Author].[ID], [x.Verfasser].[Username] AS [Username], [x.Verfasser].[Firstname], [x.Verfasser].[Lastname]
FROM [News] AS [x]
INNER JOIN [Author] AS [x.Author] ON [x].[AuthorId] = [x.Author].[ID]

Is there a way to achieve my expected behaviour?

解决方案

If I understand correctly, you don't want to load the Author entity (because there is no way to load entity with just some non navigation properties populated).

Then the question is why did you specifically add Include(x => x.Author) which is requesting EF to load the Author. Include / ThenInclude methods support eager loading of the related data entities. They are not needed when you use projection (Select).

Interestingly, all EF (Core) versions before EFC 2.0 were ignoring includes for projection type queries. Even the current EFC documentation states that the projection queries fall into Ignored Includes category. However, as you've noticed EFC 2.0 is not ignoring it! So it's either implementation bug and will be fixed, or the documentation bug and will be updated.

For now, simply don't use Include in projection queries. If you execute this:

var result = ctx.News.Select(news => new
{
    news = news,
    Username = news.Author.Username
}).ToList();

the EFC generated SQL query now is as expected:

SELECT [news].[Id], [news].[AuthorID], [news].[ReleaseDate], [news].[Text], [news].[Title], [news.Author].[Username]
FROM [News] AS [news]
INNER JOIN [Authors] AS [news.Author] ON [news].[AuthorID] = [news.Author].[Id] 

这篇关于从实体框架核心中的包含属性中选择特定属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！