查詢類型Query Types

注意

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

EF 核心模型可以包含實體類型,除了_查詢類型_,可用來執行資料庫查詢不會對應到實體類型的資料。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.

查詢類型包含實體類型有許多相似之處:Query types have many similarities with entity types:

  • 它們也可以加入至模型可能是在OnModelCreating,或透過 「 設定 」 上的屬性衍生_DbContext_。They can also be added to the model either in OnModelCreating, or via a "set" property on a derived DbContext.
  • 它們支援許多相同的對應功能,像是繼承對應時,導覽屬性 (請參閱下面的限制),然後在關聯式存放區,讓您設定的目標資料庫物件和透過 fluent API 方法或資料註解的資料行。They support many of the same mapping capabilities, like inheritance mapping, navigation properties (see limitations below) and, on relational stores, the ability to 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.

主要的使用方式案例的查詢類型的部分包括: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.

提示

對應至資料庫物件的查詢類型用來達成ToViewfluent 應用程式開發的應用程式開發介面。Mapping a query type to a database object is achieved using the ToView fluent API. EF 核心的觀點而言,此方法中指定的資料庫物件是_檢視_,這表示它會被視為唯讀查詢來源而且無法更新目標的、 插入或刪除作業。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 核心假設中指定的資料庫物件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>應用程式開發介面。Next, we configure the query type in OnModelCreating using the modelBuilder.Query<T> API. 我們可以使用標準 fluent 應用程式開發設定應用程式開發介面來設定查詢類型的對應: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.