我正在将 EF6.x 项目更新到 EF Core 3.1。决定回到基础并再次遵循如何从头开始建立关系的示例。
根据微软官方文档,EF Core 关系示例 https://learn.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api%2Cfluent-api-simple-key%2Csimple-key,我将示例翻译成下面的控制台应用程序:
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace BlogPostsExample
{
class Program
{
async static Task Main(string[] args)
{
// SQL Running in a Docker container - update as required
var conString = "data source=localhost,14330;initial catalog=BlogsDb;persist security info=True;user id=sa;password=<Your super secure SA password>;MultipleActiveResultSets=True;App=EntityFramework;";
var ctx = new MyContext(conString);
await ctx.Database.EnsureCreatedAsync();
var result = await ctx.Posts.GroupBy(p => p.Blog).ToArrayAsync();
}
}
class MyContext : DbContext
{
private readonly string _connectionString;
public MyContext(string connectionString)
{
_connectionString = connectionString;
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
base.OnConfiguring(optionsBuilder);
if (!optionsBuilder.IsConfigured)
{
optionsBuilder
.UseSqlServer(_connectionString);
}
}
public DbSet<Blog> Blogs { get; set; }
public DbSet<Post> Posts { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Post>()
.HasOne(p => p.Blog)
.WithMany(b => b.Posts)
.HasForeignKey(p => p.BlogId) //Tried with and without these keys defined.
.HasPrincipalKey(b => b.BlogId);
}
}
public class Blog
{
public int BlogId { get; set; }
public string Url { get; set; }
public List<Post> Posts { get; set; }
}
public class Post
{
public int PostId { get; set; }
public string Title { get; set; }
public string Content { get; set; }
public int BlogId { get; set; }
public Blog Blog { get; set; }
}
}
DB 中没有数据。 EF Core 无法转换
ctx.Posts.GroupBy(p => p.Blog)
到商店查询。在我看来,这是您可以尝试的最简单的 GroupBy 示例。
当您运行此代码时,您会收到以下异常:
System.InvalidOperationException: 'The LINQ expression 'DbSet<Post>
.Join(
outer: DbSet<Blog>,
inner: p => EF.Property<Nullable<int>>(p, "BlogId"),
outerKeySelector: b => EF.Property<Nullable<int>>(b, "BlogId"),
innerKeySelector: (o, i) => new TransparentIdentifier<Post, Blog>(
Outer = o,
Inner = i
))
.GroupBy(
source: p => p.Inner,
keySelector: p => p.Outer)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'
让它工作的唯一方法是在之前添加类似 AsEnumerable() 的内容GroupBy
.
从性能的角度来看,这显然不是很好,它将分组操作变成了客户端操作,您确实希望在服务器端进行分组。
我是否错过了一些显而易见的事情?我很难相信 EF Core 无法通过 EF 框架从第一天起就一直在做的最简单的分组。这似乎是任何数据驱动应用程序的基本要求? (或任何具有少量数据的应用程序!)
Update:
添加属性(例如相关博客的主键)没有什么区别。
更新2:
如果你关注这篇 JetBrains 文章 https://blog.jetbrains.com/dotnet/2020/11/25/getting-started-with-entity-framework-core-5/, 你可以这样做:
var ctx = new EntertainmentDbContext(conString);
await ctx.Database.EnsureCreatedAsync();
var dataTask = ctx
.Ratings
.GroupBy(x => x.Source)
.Select(x => new {Source = x.Key, Count = x.Count()})
.OrderByDescending(x => x.Count)
.ToListAsync();
var data = await dataTask;
But NOT this:
var ctx = new EntertainmentDbContext(conString);
await ctx.Database.EnsureCreatedAsync();
var dataTask = ctx
.Ratings
.GroupBy(x => x.Source)
// .Select(x => new {Source = x.Key, Count = x.Count()})
// .OrderByDescending(x => x.Count)
.ToListAsync();
var data = await dataTask;
它仅适用于聚合函数,例如上面的 Count。
SQL 中类似的东西也有效
SELECT COUNT(R.Id), R.Source
FROM
[EntertainmentDb].[dbo].[Ratings] R
GROUP BY R.Source
但是,删除聚合函数后,COUNT 不会,您会收到类似以下内容的消息:
Column 'EntertainmentDb.dbo.Ratings.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
所以看起来我想问 EF Core 一个我无法在 TSQL 中问的问题