asp.net core系列 33 EF查詢數據 (2)

一. 原生SQL查詢

         接著上篇講。通過 Entity Framework Core 可以在使用關係資料庫時下降到原始 SQL 查詢。 在無法使用 LINQ 表達要執行的查詢時，或因使用 LINQ 查詢而導致低效的 SQL 查詢時非常有用。 原始 SQL 查詢可返回實體類型，或者從 EF Core 2.1 開始，可返回模型中的查詢類型。

　　1.1 基本的原始SQL查詢

　　　　可以使用FromSql擴展方法，基於原始的SQL查詢，開始LINQ查詢。

        var blogs = context.Blogs
            .FromSql("SELECT * FROM dbo.Blogs")
            .ToList();

        --通過sql server profiler監聽的sql，如下所示：
        select * from dbo.blogs

　　1.2 原生 SQL 查詢可用於執行存儲過程(GetMostPopularBlogs)

    var blogs = context.Blogs
        .FromSql("EXECUTE dbo.GetMostPopularBlogs")
        .ToList();

　　1.3 傳遞參數

　　　　在使用FromSql執行原始sql查詢時，傳遞參數要防止SQL註入攻擊，可以在SQL查詢字元串中包含參數占位符，然後提供參數值作為附加參數。任何參數值將自動轉換為DbParameter來防止SQL註入。

       var id = 4;
       var blgos=  BloggingContext.Blogs.FromSql("select * from dbo.blogs where BlogId={0}",id).ToList();

        --通過sql server profiler監聽的sql，如下所示：
        exec sp_executesql N'select * from dbo.blogs where BlogId=@p0',N'@p0 int',@p0=4

　　　　可以構造 DbParameter 並將其作為參數值提供。 這樣可以在 SQL 查詢字元串中使用命名參數(與上面占位符實現一樣，只不過這裡顯示提供了命名參數@BlogId)。

       var user = new SqlParameter("@BlogId", 4);
       var blgos=  BloggingContext.Blogs.FromSql("select * from dbo.blogs where BlogId={0}", user).ToList();

        --通過sql server profiler監聽的sql，如下所示：
        exec sp_executesql N'select * from dbo.blogs where BlogId=@BlogId',N'@BlogId int',@BlogId=4

　　1.4 使用 LINQ 編寫

　　　　發送到資料庫中的 SQL 查詢可以是組合的，則可以在原始 SQL 查詢後面緊跟著使用 LINQ 運算符。 以 SELECT 關鍵字開始的 SQL 查詢一般是可組合的。以下示例使用原始SQL查詢，然後使用LINQ對其進行編寫以執行過濾和排序。

          var blgos=  BloggingContext.Blogs
                .FromSql("select  blogid,url from dbo.blogs")
                .Where(b=>b.BlogId>2)
                .OrderByDescending(b=>b.BlogId)
                 .Select(b=> new{ b.BlogId, b.Url})
                .ToList();

       --通過sql server profiler監聽的sql，原始sql成了一個子查詢，如下所示：
　　　　SELECT [b].[BlogId], [b].[Url]
　　　　FROM (
   　　　　 select  blogid,url from dbo.blogs
　　　　) AS [b]
　　　　WHERE [b].[BlogId] > 2
　　　　ORDER BY [b].[BlogId] DESC

二. 非同步查詢

　　當在資料庫中執行查詢時，非同步查詢可避免阻止線程。 這有助於避免凍結富客戶端應用程式的 UI。 非同步操作還可以增加 Web 應用程式的吞吐量，可以在等資料庫操作完成時（I/O），釋放當前線程到線程池，該線程可去處理其他請求。

　　註意: EF Core 不支持在同一上下文實例上運行多個並行操作。 應始終等待操作完成，然後再開始下一個操作。 這通常是通過在每個非同步操作上使用 await 關鍵字完成的。

　　Entity Framework Core 提供了一組非同步擴展方法，可用作執行查詢並返回結果的 LINQ 方法的替代方法。示例包括 ToListAsync()、ToArrayAsync()、SingleAsync() 等。對於部分 LINQ 運算符（如 Where(...)、OrderBy(...) 等），沒有對應的非同步版本，因為這些方法僅用於構建 LINQ 表達式樹，而未將查詢發送到資料庫中執行。

　　下麵是一個示例，註意async必須搭配await，只有await完成後才會釋放當前EF上下文, async後面必須是Task（無返回值）或Task<T>(有返回值)：

    public async Task<List<Blog>> GetBlogsAsync()
    {return await context.Blogs.ToListAsync();
    }

三. 全局查詢篩選器

　　全局查詢篩選器是應用於元數據模型（通常為 OnModelCreating）中的實體類型的 LINQ 查詢謂詞（通常傳遞給 LINQ Where 查詢運算符的布爾表達式）。

　　下麵使用 HasQueryFilter API 在 OnModelCreating 中配置查詢篩選器。

      protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            //IsDeleted==1 沒有被邏輯刪除的Blog數據
            modelBuilder.Entity<Blog>().HasQueryFilter(b=>b.IsDeleted==1);
        }

         var blgos=  BloggingContext.Blogs
                .FromSql("select * from dbo.blogs")
                .Where(b=>b.BlogId>2)
                .OrderByDescending(b=>b.BlogId)
                 .Select(b=> new{ b.BlogId, b.Url})
                .ToList();

　　　　--通過sql server profiler監聽的sql，where後面加了IsDeleted==1，如下所示：
　　　　SELECT [b].[BlogId], [b].[Url]
　　　　FROM (
   　　　　 select * from dbo.blogs
　　　　) AS [b]
　　　　WHERE ([b].[IsDeleted] = 1) AND ([b].[BlogId] > 2)
　　　　ORDER BY [b].[BlogId] DESC

        //可以在Linq查詢語句中禁用全局查詢篩選器
        BloggingContext.Blogs.IgnoreQueryFilters().ToList();

參考文獻：

　　原生 SQL 查詢

　　非同步查詢

　　全局查詢篩選器