查詢類型Query Types

注意

這項功能的新 EF Core 2.1This feature is new in EF Core 2.1

EF Core 模型可以包含實體類型,除了_查詢類型_,可用來執行資料庫查詢不會對應到實體類型的資料。In addition to entity types, an EF Core model can contain query types, which can be used to carry out database queries against data that isn't mapped to entity types.

比較查詢類型,與實體類型Compare query types to entity types

查詢類型如下所示的實體類型中,它們:Query types are like entity types in that they:

  • 可以是加入至模型中OnModelCreating或透過 「 設定 」 上的屬性衍生_DbContext_。Can be added to the model either in OnModelCreating or via a "set" property on a derived DbContext.
  • 支援許多相同的對應功能,例如繼承對應和導覽屬性。Support many of the same mapping capabilities, like inheritance mapping and navigation properties. 在關聯式存放區,他們可以設定的目標資料庫物件和透過 fluent API 方法或資料註解的資料行。On relational stores, they can configure the target database objects and columns via fluent API methods or data annotations.

不過,它們是不同於實體中的型別,它們:However, they are different from entity types in that they:

  • 不需要定義的索引鍵。Do not require a key to be defined.
  • 會永遠不會追蹤變更_DbContext_ ,因此會永遠不會插入、 更新或刪除資料庫。Are never tracked for changes on the DbContext and therefore are never inserted, updated or deleted on the database.
  • 永遠不會探索到的慣例。Are never discovered by convention.
  • 只有特別支援瀏覽對應功能的子集:Only support a subset of navigation mapping capabilities - Specifically:
    • 它們可能永遠不會做為關聯性的主要末端。They may never act as the principal end of a relationship.
    • 它們只可以包含指向實體的參考導覽屬性。They can only contain reference navigation properties pointing to entities.
    • 實體不能包含查詢類型的導覽屬性。Entities cannot contain navigation properties to query types.
  • 都能解決_ModelBuilder_使用Query方法而非Entity方法。Are addressed on the ModelBuilder using the Query method rather than the Entity method.
  • 在對應_DbContext_透過屬性的型別DbQuery<T>而非 DbSet<T>Are mapped on the DbContext through properties of type DbQuery<T> rather than DbSet<T>
  • 會對應至使用的資料庫物件ToView方法,而非ToTableAre mapped to database objects using the ToView method, rather than ToTable.
  • 可對應到_定義查詢_-定義查詢是次要的查詢,可做為查詢類型的資料來源模型中宣告。May be mapped to a defining query - A defining query is a secondary query declared in the model that acts a data source for a query type.

使用案例Usage scenarios

查詢類型的主要使用案例包括:Some of the main usage scenarios for query types are:

  • 臨機操作做為傳回型別FromSql()查詢。Serving as the return type for ad hoc FromSql() queries.
  • 將對應至資料庫檢視。Mapping to database views.
  • 沒有定義主索引鍵的資料表的對應。Mapping to tables that do not have a primary key defined.
  • 對應至模型中定義的查詢。Mapping to queries defined in the model.

對應至資料庫物件Mapping to database objects

對應至資料庫物件的查詢類型用來達成ToViewfluent API。Mapping a query type to a database object is achieved using the ToView fluent API. EF Core 的觀點而言,此方法中指定的資料庫物件是_檢視_,這表示它會被視為唯讀查詢來源而且無法更新目標的、 插入或刪除作業。From the perspective of EF Core, the database object specified in this method is a view, meaning that it is treated as a read-only query source and cannot be the target of update, insert or delete operations. 不過,這不表示資料庫物件實際上需要為資料庫檢視,-它也可以將視為唯讀的資料庫資料表。However, this does not mean that the database object is actually required to be a database view - It can alternatively be a database table that will be treated as read-only. 相反地,實體類型 EF Core 假設中指定的資料庫物件ToTable方法可用來當做_資料表_,這表示它可以當做查詢來源,但是也鎖定更新刪除和插入作業。Conversely, for entity types, EF Core assumes that a database object specified in the ToTable method can be treated as a table, meaning that it can be used as a query source but also targeted by update, delete and insert operations. 事實上,您可以在其中指定的名稱中的資料庫檢視ToTable和所有項目應可正常運作,只要檢視已成為可更新資料庫。In fact, you can specify the name of a database view in ToTable and everything should work fine as long as the view is configured to be updatable on the database.

範例Example

下列範例示範如何使用查詢型別來查詢資料庫檢視。The following example shows how to use Query Type to query a database view.

提示

您可以在 GitHub 上檢視此文章的範例 (英文)。You can view this article's sample on GitHub.

首先,我們會定義一個簡單的部落格和後置模型:First, we define a simple Blog and Post model:

public class Blog
{
    public int BlogId { get; set; }
    public string Name { get; set; }
    public string Url { get; set; }
    public ICollection<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; }
}

接下來,我們會定義簡單的資料庫檢視,讓我們查詢每個部落格相關聯的貼文數目:Next, we define a simple database view that will allow us to query the number of posts associated with each blog:

db.Database.ExecuteSqlCommand(
    @"CREATE VIEW View_BlogPostCounts AS 
        SELECT Name, Count(p.PostId) as PostCount from Blogs b
        JOIN Posts p on p.BlogId = b.BlogId
        GROUP BY b.Name");

接下來,我們會定義類別以包裝資料庫檢視的結果:Next, we define a class to hold the result from the database view:

public class BlogPostsCount
{
    public string BlogName { get; set; }
    public int PostCount { get; set; }
}

接下來,我們設定中的查詢類型_OnModelCreating_使用modelBuilder.Query<T>API。Next, we configure the query type in OnModelCreating using the modelBuilder.Query<T> API. 我們可以使用標準的 fluent 組態 Api 來設定查詢類型的對應:We use standard fluent configuration APIs to configure the mapping for the Query Type:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder
        .Query<BlogPostsCount>().ToView("View_BlogPostCounts")
        .Property(v => v.BlogName).HasColumnName("Name");
}

最後,我們可以查詢資料庫檢視,以標準方式:Finally, we can query the database view in the standard way:

var postCounts = db.BlogPostCounts.ToList();

foreach (var postCount in postCounts)
{
    Console.WriteLine($"{postCount.BlogName} has {postCount.PostCount} posts.");
    Console.WriteLine();
}

提示

請注意,我們也已定義的內容層級查詢屬性 (DbQuery) 做為查詢對這種類型的根。Note we have also defined a context level query property (DbQuery) to act as a root for queries against this type.