EFCore 分组嵌套列表

huangapple
go评论160阅读模式
英文:

EFCore GroupBy nested List

问题

我有以下的实体：

  1. public class CounterOwner
  2. {
  3.     public int Id { get; set; }
  4.     public string Name { get; set; } = null!;
  5.     public ICollection<CounterEntry> CounterEntries { get; set; } = new List<CounterEntry>();
  6. }
  8. public class CounterEntry
  9. {
  10.     public int Id { get; set; }
  11.     public DateTime DateTime { get; set; } = DateTime.Now;
  12.     public double Heating { get; set; }
  13.     public double WaterCounter1 { get; set; }
  14.     public double WaterCounter2 { get; set; }
  15.     public double WaterCounter3 { get; set; }
  16.     public double WaterCounter4 { get; set; }
  17.     public CounterOwner CounterOwner { get; set; } = null!;
  18.     public int CounterOwnerId { get; set; }
  19. }

还有在我的ApplicationDbContext中：

  1. ...
  2.  public DbSet<CounterOwner> CounterOwners => Set<CounterOwner>();
  3. ...

我想要通过CounterOwner.Name进行过滤，然后按天对CounterEntry进行分组，并计算组中最后一项和第一项之间的差异以获得每日金额。我可以将所有内容加载到内存中，但我想让数据库处理。在SQL中可以实现这一点。

基本上我想要一些代码，最终可能会得到一个匿名对象，其等效的SQL将是GroupBy和类似MAX()的聚合的组合。

如何使用EFCore实现这个？

英文:

I have the following Entities:

  1. public class CounterOwner
  2. {
  3.     public int Id { get; set; }
  4.     public string Name { get; set; } = null!;
  5.     public ICollection<CounterEntry> CounterEntries { get; set; } = new List<CounterEntry>();
  6. }
  8. public class CounterEntry
  9. {
  10.     public int Id { get; set; }
  11.     public DateTime DateTime { get; set; } = DateTime.Now;
  12.     public double Heating { get; set; }
  13.     public double WaterCounter1 { get; set; }
  14.     public double WaterCounter2 { get; set; }
  15.     public double WaterCounter3 { get; set; }
  16.     public double WaterCounter4 { get; set; }
  17.     public CounterOwner CounterOwner { get; set; } = null!;
  18.     public int CounterOwnerId { get; set; }
  19. }

And the following in my ApplicationDbContext

  1. ...
  2.  public DbSet<CounterOwner> CounterOwners => Set<CounterOwner>();
  3. ...

I want to filter by CounterOwner.Name and then group CounterEntry by day and calculate the difference between the last and first entry in the group to get the 'daily' amount. I could just load everything into memory, but I want the database to do the work. In SQL this is achievable.

Basically I want some code that results possibly in an anonymous object where the equivalent SQL would be a combination of GroupBy and an aggregate like MAX()

How do I do it with EFCore?

答案1

得分: 2

你可以使用 SelectMany 来展平一对多的关系：

  1. var result = context.CounterOwners
  2.     .Where(o => o.Name == "Doe")
  3.     .SelectMany(o => o.CounterEntries)
  4.     .GroupBy(e => e.DateTime.Date)
  5.     .Select(g => new {
  6.         Day = g.Key,
  7.         Amount1 = g.Max(e => e.WaterCounter1) - g.Min(e => e.WaterCounter1),
  8.         Amount2 = g.Max(e => e.WaterCounter2) - g.Min(e => e.WaterCounter2),
  9.         Amount3 = g.Max(e => e.WaterCounter3) - g.Min(e => e.WaterCounter3),
  10.         Amount4 = g.Max(e => e.WaterCounter4) - g.Min(e => e.WaterCounter4)
  11.     });
英文:

You can use SelectMany to flatten the one to many relationship:

  1. var result = context.CounterOwners
  2.     .Where(o => o.Name == "Doe")
  3.     .SelectMany(o => o.CounterEntries)
  4.     .GroupBy(e => e.DateTime.Date)
  5.     .Select(g => new {
  6.         Day = g.Key,
  7.         Amount1 = g.Max(e => e.WaterCounter1) - g.Min(e.WaterCounter1),
  8.         Amount2 = g.Max(e => e.WaterCounter2) - g.Min(e.WaterCounter2),
  9.         Amount3 = g.Max(e => e.WaterCounter3) - g.Min(e.WaterCounter3),
  10.         Amount4 = g.Max(e => e.WaterCounter4) - g.Min(e.WaterCounter4)
  11.     });

答案2

得分: -1

这是模拟你的实体数据库。

英文:

This is a simulatation of you entity database

<!-- begin snippet: js hide: false console: true babel: false -->

  1. namespace ConsoleApplication52
  2. {
  3.     class Program
  4.     {
  5.         static void Main(string[] args)
  6.         {
  7.             Context db = new Context();
  9.             var results = db.CounterOwner
  10.                 .Select(x =&gt; new {id = x.Id, name = x.Name, first = x.CounterEntries.OrderBy(y =&gt; y.DateTime).First(), last = x.CounterEntries.OrderBy(y =&gt; y.DateTime).Last()})
  11.                 .Select(x =&gt; new {
  12.                     id = x.id, 
  13.                     name = x.name,
  14.                     heating = x.last.Heating - x.first.Heating,
  15.                     waterCount1 = x.last.WaterCounter1 = x.first.WaterCounter1,
  16.                     waterCount2 = x.last.WaterCounter2 = x.first.WaterCounter2,
  17.                     waterCount3 = x.last.WaterCounter3 = x.first.WaterCounter3,
  18.                     waterCount4 = x.last.WaterCounter4 = x.first.WaterCounter4,
  19.                     }).ToList();
  21.  
  22.         }
  23.     }
  24.     public class Context
  25.     {
  26.         public List&lt;CounterOwner&gt; CounterOwner { get;set;} 
  27.         public List&lt;CounterEntry&gt; CounterEntry { get;set;} 
  28.     }
  30.     public class CounterOwner
  31.     {
  32.         public int Id { get; set; }
  33.         public string Name { get; set; }
  34.         public ICollection&lt;CounterEntry&gt; CounterEntries { get; set; } = new List&lt;CounterEntry&gt;();
  35.     }
  37.     public class CounterEntry
  38.     {
  39.         public int Id { get; set; }
  40.         public DateTime DateTime { get; set; }
  41.         public double Heating { get; set; }
  42.         public double WaterCounter1 { get; set; }
  43.         public double WaterCounter2 { get; set; }
  44.         public double WaterCounter3 { get; set; }
  45.         public double WaterCounter4 { get; set; }
  46.         public CounterOwner CounterOwner { get; set;}
  47.         public int CounterOwnerId { get; set; }
  48.     }
  50. }

<!-- end snippet -->

huangapple
  • 本文由 发表于 2023年3月20日 23:08:04
  • 转载请务必保留本文链接：https://go.coder-hub.com/75792007.html
  • c#
  • entity-framework-core
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定