وحید نصیری وحید نصیری
شروع به کار با EF Core 1.0 - قسمت 10 - استفاده از امکانات بومی بانک‌های اطلاعاتی
اندازه‌ی قلم متن
تخمین مدت زمان مطالعه‌ی مطلب: پنج دقیقه
در قسمت بعد، ارتباطات self referencing را بررسی خواهیم کرد و چون EF Core هیچ راه حل بهینه‌ای را برای کوئری گرفتن از این نوع روابط سلسله مراتبی ارائه نمی‌دهد (درEF 6.x نیز به همین ترتیب)، نیاز است مستقیما SQL نویسی کرد. به همین جهت در این قسمت نحوه‌ی نوشتن کوئری‌های مستقیم SQL و اجرای آن‌ها را در EF Core بررسی می‌کنیم.


اجرای کوئری‌های خام SQL بر روی بانک اطلاعاتی، توسط EF Core

گاهی از اوقات نیاز به استفاده‌ی قابلیت خاصی از بانک اطلاعاتی مدنظر وجود دارد که توسط LINQ پشتیبانی نمی‌شود و یا کوئری SQL حاصل از LINQ to Entities آنچنان بهینه نیست. در یک چنین حالاتی راهی بجز نوشتن کوئر‌ی‌های خام SQL وجود ندارد. امکان اجرای یک چنین کوئری‌هایی توسط EF Core پیش بینی شده‌است؛ اما با این محدودیت‌ها:
 - خروجی کوئری SQL، تنها باید معادل یکی از کلاس‌های موجودیت‌های شما باشد. قرار است این محدودیت در نگارش 1.1 برطرف شود.
 - کوئری SQL نوشته شده باید تمام خواص موجودیتی را که قرار است به آن نگاشت شود، بازگشت دهد.
 - نام ستون‌های بازگشت داده شده‌ی توسط کوئری SQL باید با نام خواص موجودیت در حال کار، یکی باشند و برخلاف EF 6.x، از یک چنین عدم تطابق‌هایی صرفنظر نخواهد شد.
 -  کوئری SQL نوشته شده نباید به همراه اطلاعات ارتباطات موجودیت‌ها باشد.

در اینجا برای نوشتن کوئری‌های خام SQL می‌توان از متد FromSql مرتبط با یکی از DbSetهای برنامه استفاده کرد:
var blogs = context.Blogs
    .FromSql("SELECT * FROM dbo.Blogs")
    .ToList();
و یا حتی می‌توان از رویه‌ی ذخیره شده‌ای استفاده کرد که خروجی ستون‌های آن، معادل تمام خواص کلاس Blog باشد:
var blogs = context.Blogs
  .FromSql("EXECUTE dbo.GetMostPopularBlogs")
  .ToList();

بنابراین رفتار EF Core اندکی متفاوت است با EF 6.x. در اینجا اگر می‌خواهید از عبارت SQL خود خروجی بگیرید، باید از یکی از DbSetهای خود شروع کنید و متد FromSql را بر روی آن فراخوانی نمائید. همچنین کوئری نوشته شده باید اولا تمام ستون‌های آن DbSet رابازگشت دهد و به علاوه این ستون‌ها دقیقا با نام‌های خواص آن کلاس، تطابق داشته باشند.
علت این مسایل نیز به این دلیل است که بتوان نتیجه‌ی کوئری را به صورت خودکار وارد سیستم change tracking کرد و همچنین کوئری‌های ترکیبی LINQ را نیز در اینجا فعال کرد.


ارسال پارامترها به کوئری‌های خام SQL

تنها حالتی در EF Core که مستعد به حملات تزریق SQL است، دقیقا همین مورد دور شدن از LINQ و نوشتن عبارات مستقیم SQL است. در اینجا برای نوشتن کوئری‌های پارامتری دو حالت پیش بینی شده‌است:
الف) روش parameter place holders
در اینجا متد FromSql، بسیار شبیه به متد String.Format است، اما در عمل اینطور نیست و تمام place holders آن به صورت خودکار تبدیل به پارامتر می‌شوند:
var user = "johndoe";

var blogs = context.Blogs
  .FromSql("EXECUTE dbo.GetMostPopularBlogsForUser {0}", user)
  .ToList();
ب) روش ساخت دستی DbParameterها
اگر می‌خواهید از پارامترهای نام دار استفاده کنید، با وهله‌ای از SqlParameter شروع کرده و سپس آن‌را به متد FromSql ارسال کنید:
var user = new SqlParameter("user", "johndoe");
var blogs = context.Blogs
  .FromSql("EXECUTE dbo.GetMostPopularBlogsForUser @user", user)
  .ToList();
و یا این حالت را به شکل ساده شده‌ی ذیل نیز می‌توان مورد استفاده قرار داد:
 var results = _context.Contacts.FromSql(
@"SELECT Id, Name Address, City, State, Zip 
    FROM Contacts 
    WHERE Name IN (@p0, @p1)", name1, name2);
که در اینجا p0@ به name1 و p1@ به name2 نگاشت خواهد شد.
مزیت کار کردن با SqlParameter این است که می‌توان برای مثال Direction و SqlDbType را نیز صریحا ذکر کرد (بسته به نوع پارامترهای رویه‌ی ذخیره شده):
var nameParameter = new SqlParameter
{
  ParameterName = "@name",
  Value = "doc",
  Direction = ParameterDirection.Input,
  SqlDbType = SqlDbType.NVarChar
};


امکان ترکیب کوئری‌های SQL و LINQ نیز پیش بینی شده‌است

در کوئری ذیل، قسمت select از جدولی به صورت SQL و قسمت where و order by آن توسط LINQ تهیه شده‌اند که در نهایت به یک کوئری ترجمه شده و بر روی بانک اطلاعاتی اجرا می‌شوند.
یک مثال جالب آن، امکان کوئری گرفتن از Table Value Function‌ها و سپس ترکیب آن‌ها با LINQ است (این ترکیب، تنها یک کوئری SQL نهایی را تولید می‌کند):
var posts = context.Posts
  .FromSql("SELECT * FROM dbo.GetMatchingPostByTitle({0})", searchTerm)
  .Where(p => p.BlogId == 1)
  .OrderByDescending(p => p.CreateDate)
  .ToList();


واکشی ارتباطات یک موجودیت توسط SQL و LINQ

در ابتدای بحث در قسمت محدودیت‌های کوئری‌های SQL نوشته شده، ذکر شد «کوئری SQL نوشته شده نباید به همراه اطلاعات ارتباطات موجودیت‌ها باشد». برای رفع این محدودیت می‌توان از ترکیب SQL و LINQ به صورت ذیل استفاده کرد:
var searchTerm = ".NET";
var blogs = context.Blogs
  .FromSql("SELECT * FROM dbo.SearchBlogs {0}", searchTerm)
  .Include(b => b.Posts)
  .ToList();
در اینجا برای واکشی ارتباطات یک موجودیت از متد Include استفاده شده‌است.


اجرای عبارات SQL، بدون بازگشت مقداری

تا اینجا در مورد عبارات SQL از نوع Select و یا اجرای رویه‌های ذخیره شده، بحث شد. برای اجرای عبارات SQL ایی مانند update و delete می‌توان از متد ExecuteSqlCommand مربوط به  context.Database استفاده کرد:
  context.Database.ExecuteSqlCommand("UPDATE dbo.People SET FirstName = 'Jane' WHERE PersonId = 30");
و یا برای ارسال پارامترها به آن می‌توان به این صورت عمل کرد (اجرای یک رویه‌ی ذخیره شده با دو پارامتر ارسالی به آن):
context.Database.ExecuteSqlCommand("usp_CreateShipper @p0, @p1",
  parameters: new[] { "hello", "world" });


اجرای عبارات SQL و دریافت خروجی‌هایی به غیر از موجودیت‌های برنامه

در ابتدا بحث عنوان شد که محدودیت فعلی کوئری‌های FromSQL که می‌توانند خروجی را نیز ارائه دهند، مقید بودن آن‌ها به DbSet در حال استفاده است و محدود بودن آن‌ها به خواص کلاس متناظر تعریف شده. در این حالت اگر بخواهیم یک محاسبه‌ی عددی را بازگشت دهیم چه باید کرد؟
متد ExecuteSqlCommand تنها وضعیت نهایی اجرای عملیات را بازگشت می‌دهد و FromSQL مقید است به DbSet متناظر. برای رفع این محدودیت‌ها می‌توان مستقیما به DbConnection دسترسی یافت و سپس کوئری گرفت؛ به نحو ذیل:
using (var connection = context.Database.GetDbConnection())
{
    connection.Open();
 
    using (var command = connection.CreateCommand())
    {
        command.CommandText = "SELECT COUNT(*) FROM Contacts";
        var result = command.ExecuteScalar().ToString();
    }
}
به عبارتی در اینجا امکان بازگشت به حالت ADO.NET خام نیز پیش بینی شده‌است.
‫۸ سال و ۱ ماه قبل، یکشنبه ۷ شهریور ۱۳۹۵، ساعت ۱۹:۰۰
۴۲۸۰
۱۲
ASP.NET CoreEntity framework Core
مطالب مشابه
  • #
    م شاکری م شاکری
    ‫۷ سال و ۵ ماه قبل، چهارشنبه ۲۳ فروردین ۱۳۹۶، ساعت ۱۵:۵۸
    ممنون بابت این دوره. شما اشاره کردین که قرار است محدودیت "خروجی کوئری SQL، تنها باید معادل یکی از کلاس‌های موجودیت‌های شما باشد" در نسخه‌ی 1.1 EF Core برطرف شود  و با توجه به اینکه در حال حاضر نسخه نسخه 1.1.1 EF Core منتشر شده چطور میتوان از این امکان استفاده کرد؟
  • #
    وحید نصیری وحید نصیری
    ‫۷ سال قبل، یکشنبه ۲۶ شهریور ۱۳۹۶، ساعت ۰۰:۰۴
    یک نکته‌ی تکمیلی: اهمیت دقت داشتن به امضای متد FromSql در EF Core 2.0

    در EF Core 2.0، اگر از String Interpolation معرفی شده‌ی در C# 6.0 استفاده شود، متد FromSql این نوع متغیرها را به صورت خودکار تبدیل به پارامترهای کوئری‌های SQL می‌کند. برای مثال کوئری ذیل که در آن userName از طریق String Interpolation معرفی شده‌است:
    var userName = "user 1";
var user = context.Users.FromSql($"select top 1 * from Users where name = {userName} ").FirstOrDefault();
if (user != null)
{
   Console.WriteLine(user.Name);
}
    یک چنین خروجی SQL ایی را تولید می‌کند:
    SELECT TOP(1) [u].[UserId], [u].[IsAdmin], [u].[Name]
FROM (
    select top 1 * from Users where name = @p0
) AS [u]
    همانطور که ملاحظه می‌کنید پارامتر p0@ به صورت خودکار بجای {userName} درج شده‌است.

    اما ... اگر این کوئری را به نحو ذیل اجرا کنیم:
    var sql = $"select top 1 * from Users where name = {userName} ";
user = context.Users.FromSql(sql).FirstOrDefault();
if (user != null)
{
   Console.WriteLine(user.Name);
}
    برنامه کرش می‌کند:
     .SqlException: Incorrect syntax near '1'
    علت اینجا است که زمانیکه رشته‌ی Interpolation را مستقیما داخل متد FromSql درج می‌کنیم از overload زیر استفاده می‌کند:
     public static IQueryable<TEntity> FromSql<TEntity>(this IQueryable<TEntity> source, FormattableString sql) where TEntity : class;
    در اینجا sql از نوع FormattableString معرفی شده‌است.

    اما زمانیکه از var استفاده می‌کنیم، یعنی این رشته به نوع string تبدیل می‌شود. در این حالت دیگر از overload فوق استفاده نشده و عملیات تهیه‌ی کوئری‌های پارامتری انجام نخواهد شد. علت کرش برنامه هم همین مورد است. اگر در این حالت بخواهیم از userName استفاده کنیم، باید آن‌را داخل '' محصور کنیم:
     var sql = $"select top 1 * from Users where name = '{userName}' ";
    و در نهایت کوئری تولید شده نیز پارامتری نیست:
    SELECT TOP(1) [u].[UserId], [u].[IsAdmin], [u].[Name]
FROM (
   select top 1 * from Users where name = 'user 1'
) AS [u]
    به عبارتی این نوع کوئری، مستعد به حملات تزریق اس کیوال است. چون بجای user 1 هر نوع ورودی دیگری را نیز می‌توان درج کرد و به علت پارامتری نبودن، این نوع ورودی‌ها می‌توانند ساختار عبارت SQL نوشته شده را تغییر دهند.
    بنابراین در حین کار با متد FromSql به overload در حال استفاده دقت داشته باشید. فقط حالت FormattableString آن است که کار تبدیل String Interpolation را به کوئری‌های پارامتری انجام می‌دهد.
  • #
    م شاکری م شاکری
    ‫۶ سال و ۱۱ ماه قبل، چهارشنبه ۲۶ مهر ۱۳۹۶، ساعت ۱۲:۴۵
    در متد ExecuteSqlCommand امکان تعریف output parameter همانند متد FromSQL وجود دارد؟
    • #
      وحید نصیری وحید نصیری
      ‫۶ سال و ۱۱ ماه قبل، چهارشنبه ۲۶ مهر ۱۳۹۶، ساعت ۱۳:۰۲
      بله. در اینجا هم امکان استفاده از new SqlParameter وجود دارد.
      var outParam = new SqlParameter("@ParamOut", DbType.Int32)
    { Direction = ParameterDirection.Output };
ctx.Database.ExecuteSqlCommand("EXEC dbo.MyTest @ParamOut OUTPUT", outParam);
  • #
    وحید نصیری وحید نصیری
    ‫۶ سال و ۳ ماه قبل، یکشنبه ۲۰ خرداد ۱۳۹۷، ساعت ۱۳:۲۷
    رفع محدودیت «خروجی کوئری SQL، تنها باید معادل یکی از کلاس‌های موجودیت‌های شما باشد» در نگارش 2.1
    در نگارش 2.1 مفهوم جدیدی به نام Query Types ارائه شده‌است که امکان نگاشت به خروجی‌های خاص بانک اطلاعاتی مانند Viewها و یا رویه‌های ذخیره شده را میسر می‌کند که این خروجی‌ها عموما مستقل از فیلدهای جداول و موجودیت‌های تعریف شده‌ی در برنامه هستند.
    برای مثال فرض کنید یک View ویژه را بر اساس جدول و یا جداول بانک اطلاعاتی خود طراحی کرده‌اید:
    using (var db = new BloggingContext())
{
   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");
}
    خروجی این View که دو ستون name و PostCount را به همراه دارد، متناظر با موجودیت‌های اصلی برنامه نیست. برای تهیه نگاشتی به آن، ابتدا کلاس مدل متناظر با این ستون‌های بازگشتی را تهیه می‌کنیم: 
    public class BlogPostsCount
{
    public string BlogName { get; set; }
    public int PostCount { get; set; }
}

    سپس برای معرفی آن به Context باید دو مرحله انجام شود:
    الف) این کلاس به صورت DbQuery در Context معرفی می‌شود:
    public class BloggingContext : DbContext
{
    public DbQuery<BlogPostsCount> BlogPostCounts { get; set; }
    ب) در متد OnModelCreating همین Context، نگاشت این DbQuery به View یاد شده توسط متد ToView انجام می‌شود:
       protected override void OnModelCreating(ModelBuilder modelBuilder)
   {
      modelBuilder
           .Query<BlogPostsCount>().ToView("View_BlogPostCounts")
           .Property(v => v.BlogName).HasColumnName("Name");
    }
    متد ToView الزاما نیازی به یک view ندارد. مفهوم آن صرفا یک خروجی فقط خواندنی است. برای مثال حتی در اینجا یک جدول بانک اطلاعاتی را هم می‌توانید ذکر کنید. اما مفهوم آن غیرقابل تغییر بودن خروجی کوئری‌های آن است. بنابراین باید دقت داشت که در اینجا مهم نیست که کلاس نگاشت تعریف شده دارای کلید هست یا خیر و ارجاعی از این کلاس را نمی‌توان در کلاس‌های موجودیت‌های اصلی مورد استفاده قرار داد.
    از متد Property به این جهت استفاده شده‌است که در کلاس BlogPostsCount، خاصیت BlogName، متناظر با هیچکدام از ستون‌های بازگشتی View تعریف شده نیست. به همین جهت با استفاده از این متد مشخص کرده‌ایم که این خاصیت باید به کدام ستون بازگشتی، نگاشت شود.
    و در آخر کوئری گرفتن از این DbQuery تعریف شده به صورت زیر است:
    using (var db = new BloggingContext())
{
   var postCounts = db.BlogPostCounts.ToList();

    مثال کامل این نکته
    • #
      محمد دلیری محمد دلیری
      ‫۵ سال و ۳ ماه قبل، یکشنبه ۱۹ خرداد ۱۳۹۸، ساعت ۱۵:۳۳
      مثال گفته شده پیغام 404 می‌دهد.
    • #
      محمد دلیری محمد دلیری
      ‫۵ سال و ۳ ماه قبل، یکشنبه ۱۹ خرداد ۱۳۹۸، ساعت ۱۷:۰۴
      چگونه می‌توان در تزریق وابستگی به استفاده از unitofwork ویو BlogPostCounts را صدا زد؟ و مقدار آن را خواند
      شبیه این روند       
              private readonly IUnitOfWork _uow;
        private readonly DbSet<Student> _student;

        public StudentService(IUnitOfWork uow)
        {
            _uow = uow;
            _uow.CheckArgumentIsNull(nameof(_uow));

            _student = _uow.Set<Student>();
        }

      • #
        وحید نصیری وحید نصیری
        ‫۵ سال و ۳ ماه قبل، یکشنبه ۱۹ خرداد ۱۳۹۸، ساعت ۱۷:۲۱
        شبیه به همان خاصیت DbSet ای که در اینترفیس IUnitOfWork دارید، یکی هم برای DbQuery ایجاد کنید.
  • #
    وحید نصیری وحید نصیری
    ‫۵ سال و ۳ ماه قبل، دوشنبه ۲۰ خرداد ۱۳۹۸، ساعت ۱۲:۵۵
    ارتقاء به EF Core 3.0
    Query types که کمی بالاتر معرفی شد، در EF Core 3.0 منسوخ شده اعلام و با همان DbSet ادغام می‌شود (هدف آن کاهش سردرگمی بین DbQuery و DbSet است). در اینجا query type چیزی بجز یک entity بدون کلید نیست. بنابراین بجای تعریف ModelBuilder.Query، تنها از متد HasNoKey استفاده کنید؛ مانند ()ModelBuilder.Entity<>().HasNoKey. از این پس بجای DbQuery، از همان DbSet استفاده کنید. معادل DbContext.Query نیز DbContext.Set است.
  • #
    وحید نصیری وحید نصیری
    ‫۵ سال و ۳ ماه قبل، دوشنبه ۲۰ خرداد ۱۳۹۸، ساعت ۱۵:۳۷
    ارتقاء به EF Core 3.0
    کمی بالاتر در مورد «یک نکته‌ی تکمیلی: اهمیت دقت داشتن به امضای متد FromSql در EF Core 2.0 » بحث شد. به صورت خلاصه مشکل آن چنین چیزی است:
    v1 = context.Customers.FromSql($"SELECT * FROM Customers WHERE City = {city}");

var sql = $"SELECT * FROM Customers WHERE City = {city}";
v2 = context.Customers.FromSql(sql);
    حالت v1 یک کوئری پارامتری نهایی را ایجاد می‌کند و حالت v2 خیر که مستعد به حملات تزریق اس‌کیوال است. برای رفع این مشکل، متدهای FromSql ،ExecuteSql و ExecuteSqlAsync در EF Core 3.0 تغییر نام یافته‌اند و دیگر وجود خارجی ندارند. اینبار متدهای FromSqlRaw، ExecuteSqlRaw و ExecuteSqlRawAsync و همچنین FromSqlInterpolated، ExecuteSqlInterpolated و ExecuteSqlInterpolatedAsync معرفی شده‌اند تا هدف از کاربرد آن‌ها دقیقا مشخص شود: 
    context.Products.FromSqlRaw(
    "SELECT * FROM Products WHERE Name = {0}",
    product.Name);

context.Products.FromSqlInterpolated(
    $"SELECT * FROM Products WHERE Name = {product.Name}");
    یعنی جائیکه Raw است، نباید از Interpolated strings استفاده کنید.