在實體EF Core中包含計算屬性

entity-framework-core

我有一個真正的代碼優先部署我的數據模型,我需要執行一個通常與視圖關聯的功能,但我不知道如何完成它。

為了簡化問題,我的模型包含註冊用戶創建的內容以及其他用戶可以與之交互的帖子。我正在設計一種算法來說明這些事物的受歡迎程度,這可以歸結為與交互類型相關的加權因子乘以某個日期週期的倒數。

舉個例子,我們說“Likes”,“Comments”和“Purchases”的權重分別為1,2和3。我們還說我的時間段是1週。在那種情況下,3週前購買的重量與本週的同等重量相當。

因此,模型的相關部分是Thing ,它具有主鍵和一些額外的元數據; InteractionType ,具有主鍵,權Weight值和一些其他元數據;的Interaction ,其具有複合主鍵(由的Thing主鍵, User對象主鍵,並且InteractionType主鍵)和一個DateValue柱和任何其它內容相關的元數據。

最終,我希望以實體框架兼容的方式執行以下查詢:

;WITH InteractionScore
AS
(
  SELECT t.Id ThingId, SUM(it.Weight/CAST(DATEDIFF(wk, i.DateValue, GETDATE()) AS float)) IntScore
  FROM Things t
  INNER JOIN Interactions i ON t.Id = i.ThingId
  INNER JOIN InteractionTypes it ON i.InteractionTypeId = it.Id
  GROUP BY t.ID
)
SELECT TOP(10) t.*
FROM Things t
LEFT JOIN InteractionScore isc ON t.Id = isc.ThingId
ORDER BY ISNULL(isc.IntScore, 0.0) DESC

數據模型類定義如下:

namespace Project.Entities
{
  public class User
  {
    [Key]
    public int Id {get; set;}
    public string IdentityRef {get;set;}
    public string DisplayName {get;set;}
    [InverseProperty("Owner")]
    public ICollection<Thing> OwnedThings {get; set;}
  }

  public class InteractionType
  {
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public double Weight { get; set; }
  }

  public class Thing
  {
    [Key]
    public int Id {get;set;}
    public int OwnerId {get; set;}
    [ForeignKey("OwnerId")]
    public User Owner {get; set;}
    public string Summary {get; set;}
    public string Description {get; set;}
    public ICollection<Interaction> Interactions {get;set;}
  }

  public class Interaction
  {
    public int ThingId { get; set; }
    public int UserId { get; set; }
    public int InteractionTypeId {get; set;}
    public Thing Thing {get; set;}
    public User User {get;set;}
    public InteractionType InteractionType {get;set;}
    public DateTime DateValue {get;set;}  
    public string Comment {get; set;}
    public string PurchaseReference {get;set;}      
  }

}

消費應用程序中的DbContext定義

namespace Project.Consumer
{
  public class ApplicationData : DbContext
  {
    public DbSet<User> Users {get;set;}
    public DbSet<Thing> Things {get;set;}        

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);

        builder.Entity<Interaction>()
               .HasKey(i => new {i.ThingId, i.UserId, i.InteractionTypeId});            
    }
  }

  public List<Thing> GetMostPopularThings(ApplicationData ctx)
  {
    return ctx.Things.OrderByDescending(lambdaMagic).Take(10).ToList();
  }
}

lambdaMagic當然是嗤之以鼻,但正如我所說,我不確定是否有記錄的方式這樣做,我只是錯過它或什麼。我已經看到了按照ctx.Things.FromSql("select string or sp here").Take(10).ToList();做某事的選項ctx.Things.FromSql("select string or sp here").Take(10).ToList();這可能沒關係,但我真的想減少項目之外存在的東西(例如SP定義)

自寫這個問題以來,我繼續創建另一個名為ThingStatistics的模型類:

public class ThingStatistics
{
  [Key]      
  public int ThingId {get; set;}
  [ForeignKey("ThingId")]
  public Thing Thing {get; set;}
  public double InteractionScore {get;set;}
}

然後我按如下方式擴充了Thing類:

public class Thing
{
    [Key]
    public int Id {get;set;}
    public int OwnerId {get; set;}
    [ForeignKey("OwnerId")]
    public User Owner {get; set;}
    public string Summary {get; set;}
    public string Description {get; set;}
    public ICollection<Interaction> Interactions {get;set;}
    public ThingStatistics Stats {get;set;}
}

我執行了Add-Migration步驟,然後按如下方式更改了結果Up / Down:

public partial class AggregationHack : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
       migrationBuilder.Sql("GO; CREATE VIEW ThingStatistics AS SELECT t.ID ThingId, ISNULL(it.Weight/CAST(DATEDIFF(wk, i.DateValue, GETDATE()) + 1 AS float), 0) InteractionScore FROM Things t LEFT JOIN Interactions i INNER JOIN InteractionTypes it ON i.InteractionTypeId = it.Id ON t.ID = i.ThingId GROUP BY t.Id; GO;");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
       migrationBuilder.Sql("DROP VIEW ThingStatistics; GO;");
    }
}

所以,現在我可以做ctx.Things.Include(t => t.Stats).OrderByDescending(t => t.Stats.InteractionScore).Take(10).ToList()並且有效,但我不知道如果這是正確的,因為它感覺像這樣的黑客......

一般承認的答案

ORM的想法是,連接大多被導航屬性取代。所以你的基本C#查詢看起來像

ctx.Things
   .Include(t => t.Interactions).ThenInclude(i => i.InteractionType) 
   .Select(t => new { 
      t.ThingId, 
      IntScore = t.Interactions.Sum(i => i.InteractionType.Weight /  ...) 
    })
   .OrderBy(x => x.IntScore)
   .Select(x => x.ThingId)
   .Take(10);

在那些...你需要替換DateDiff(NuGet上有第三方pkg)。
雖然這更簡單,更易讀,但更難以這種方式影響查詢的性能,您必須進行測試。您的SQL解決方案可能並不全是壞事。



Related

許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow