دوره آموزشی Entity FrameWork Core - قسمت 29

دوره آموزشی Entity FrameWork Core - قسمت 28

خوب Entity Framework Core به شما امکان می دهد هنگام کار با یک پایگاه داده رابطه ای مستقیما از SQL queries استفاده کنید. پرس و جوهای SQL در صورتی مفید هستند که پرس و جوی مورد نظر شما را نتوان با استفاده از LINQ بیان کرد، یا اگر یک کوئری LINQ باعث شود EF SQL ناکارآمد تولید کند. پرس و جوهای SQL می توانند انواع موجودیت معمولی یا انواع موجودیت های بدون کلید را که بخشی از مدل شما هستند، برگردانند.

پرس و جوهای Basic SQL :

می توانید از FromSql برای شروع یک پرس و جوی LINQ بر اساس پرس و جوی SQL استفاده کنید:

var blogs = context.Blogs
    .FromSql($&quotSELECT * FROM dbo.Blogs&quot)
    .ToList();

البته توجه داشته باشید FromSql در EF Core 7.0 معرفی می شود. هنگام استفاده از نسخه های قدیمی تر، به جای آن از FromSqlInterpolated استفاده کنید. همچنین FromSql فقط می تواند به طور مستقیم در یک DbSet استفاده شود. نمی توان آن را روی یک query دلخواه LINQ تشکیل داد.

پرس و جوهای SQL را می توان برای اجرای یک stored procedure که داده های موجودیت را برمی گرداند استفاده کرد:

var blogs = context.Blogs
    .FromSql($&quotEXECUTE dbo.GetMostPopularBlogs&quot)
    .ToList();

ارسال پارامترها (Passing parameters) :

هنگام استفاده از پرس و جوهای SQL به پارامترسازی توجه زیادی داشته باشید

هنگام وارد کردن مقادیر ارائه شده توسط کاربر در پرس و جوی SQL، باید مراقب بود تا از حملات SQL injection جلوگیری شود. SQL injection زمانی اتفاق می‌افتد که یک برنامه یک مقدار رشته ارائه‌شده توسط کاربر را در یک کوئری SQL ادغام می‌کند، و مقدار ارائه‌شده توسط کاربر برای پایان دادن به رشته و اجرای یک عملیات مخرب SQL ایجاد می‌شود.

متد های FromSql و FromSqlInterpolated در برابر SQL injection ایمن هستند و همیشه داده های پارامتر را به عنوان یک پارامتر SQL جداگانه ادغام می کنند. با این حال، متد FromSqlRaw در صورت استفاده نادرست
می تواند در برابر حملات تزریق SQL آسیب پذیر باشد.

مثال زیر یک پارامتر را به یک stored procedure ارسال می کند:

var user = &quotjoh&quot
var blogs = context.Blogs
    .FromSql($&quotEXECUTE dbo.GetMostPopularBlogsForUser {user}&quot)
    .ToList();

این syntax باعث می‌شود که FromSql از حملات SQL injection در امان باشد و مقدار را به‌طور مؤثر و صحیح به پایگاه داده ارسال کند.

هنگام اجرای رویه های ذخیره شده، استفاده از پارامترهای نامگذاری شده در رشته پرس و جو SQL می تواند مفید باشد، به خصوص زمانی که رویه ذخیره شده دارای پارامترهای اختیاری باشد:

var user = new SqlParameter(&quotuser&quot, &quotjoh&quot);
var blogs = context.Blogs
    .FromSql($&quotEXECUTE dbo.GetMostPopularBlogsForUser @filterByUser={user}&quot)
    .ToList();

پارامترهایی که پاس می کنید باید دقیقاً با تعریف رویه ذخیره شده مطابقت داشته باشند. به ترتیب پارامترها توجه ویژه ای داشته باشید و مراقب باشید که هیچ یک از آنها را از دست ندهید یا به اشتباه درج نکنید.

همچنین، مطمئن شوید که parameter types مطابقت دارند و وجوه آنها (اندازه، دقت، مقیاس) در صورت نیاز تنظیم شده است.

ارسال پارامترها و Dynamic SQL :

از FromSql و پارامترسازی آن باید تا حد امکان استفاده شود. با این حال سناریوهای خاصی وجود دارد که در آن SQL باید به صورت پویا با هم ترکیب شود و پارامترهای پایگاه داده نمی توانند استفاده شوند. به عنوان مثال، فرض کنید یک متغیر #C نام خاصیت a را که باید فیلتر شود را در خود دارد. ممکن است استفاده از پرس و جوی SQL مانند موارد زیر وسوسه انگیز باشد:

var propertyName = &quotUser&quot
var propertyValue = &quotjohndoe&quot
var blogs = context.Blogs
    .FromSql($&quotSELECT * FROM [Blogs] WHERE {propertyName} = {propertyValue}&quot)
    .ToList();

این کد کار نمی کند، زیرا پایگاه های داده اجازه پارامترسازی نام ستون ها (یا هر بخش دیگری از schema) را نمی دهند.
اول، مهم است که مفاهیم ساخت پویا یک پرس و جو - از طریق SQL یا موارد دیگر را در نظر بگیرید. پذیرش نام ستون از سوی کاربر ممکن است به آنها اجازه دهد ستونی را انتخاب کنند که index نشده است، و باعث می شود پرس و جو بسیار کند اجرا شود و پایگاه داده شما بیش از حد بارگذاری شود. یا ممکن است به آن‌ها اجازه دهد ستونی را انتخاب کنند که حاوی داده‌هایی باشد که نمی‌خواهید در معرض آن قرار گیرند. به جز سناریوهای واقعاً پویا، معمولاً بهتر است دو پرس و جو برای نام دو ستون داشته باشید، به جای استفاده از پارامترسازی برای جمع کردن آنها در یک پرس و جو.

اگر تصمیم گرفته اید که می خواهید به صورت پویا SQL خود را بسازید، باید از FromSqlRaw استفاده کنید که به جای استفاده از پارامتر پایگاه داده اجازه می دهد تا داده های متغیر را مستقیماً در رشته SQL درونیابی کند:

var columnName = &quotUrl&quot
var columnValue = new SqlParameter(&quotcolumnValue&quot, &quothttp://SomeURL&quot);
var blogs = context.Blogs
    .FromSqlRaw($&quotSELECT * FROM [Blogs] WHERE {columnName} = @columnValue&quot, columnValue)
    .ToList();

در کد بالا، نام ستون به طور مستقیم در SQL وارد می شود. این مسئولیت شماست که مطمئن شوید این مقدار رشته ایمن است و اگر منشأ ناامن دارد آن را پاک سازی کنید.

هنگام استفاده از FromSqlRaw بسیار مراقب باشید و همیشه مطمئن شوید که مقادیر یا از مبدا امن هستند یا به درستی پاکسازی شده اند. حملات SQL injection می تواند عواقب فاجعه باری برای برنامه شما داشته باشد.

کوئری Sql به همراه Linq :

var searchTerm = &quottext&quot
var blogs = context.Blogs
    .FromSql($&quotSELECT * FROM dbo.SearchBlogs({searchTerm})&quot)
    .Where(b => b.Rating > 3)
    .OrderByDescending(b => b.Rating)
    .ToList();

کوئری فوق SQL زیر را تولید می کند:

SELECT [b].[BlogId], [b].[OwnerId], [b].[Rating], [b].[Url]
FROM (
    SELECT * FROM dbo.SearchBlogs(@p0)
) AS [b]
WHERE [b].[Rating] > 3
ORDER BY [b].[Rating] DESC

عملگر Include می تواند برای بارگیری داده های مرتبط استفاده شود، درست مانند هر پرس و جوی LINQ دیگر:

var searchTerm = &quottext&quot
var blogs = context.Blogs
    .FromSql($&quotSELECT * FROM dbo.SearchBlogs({searchTerm})&quot)
    .Include(b => b.Posts)
    .ToList();

این ترکیب به عنوان یک پرس و جو در نظر گرفته می شود و مستلزم آن است که پرس و جوی SQL شما قابل ترکیب باشد، زیرا EF Core در اینجا SQL ارائه شده را به عنوان یک subquery در نظر می گیرد. subquery ها معمولاً با کلمه کلیدی SELECT شروع می شوند.

یک subquery یک پرس و جوی SQL است که درون یک پرس و جو بزرگتر قرار گرفته است.

توجه داشته باشید SQL Server در این شیوه اجازه ترکیب و فراخوانی‌های stored procedure را نمی‌دهد، بنابراین هرگونه تلاش برای اعمال عملگرهای پرس و جو اضافی برای چنین تماسی منجر به invalid SQL می‌شود. از AsEnumerable یا AsAsyncEnumerable درست بعد از FromSql یا FromSqlRaw استفاده کنید تا مطمئن شوید که EF Core سعی در ترکیب روی یک stored procedure ندارد.

ردیابی تغییرات (Change Tracking) :

کوئری هایی که از FromSql یا FromSqlRaw استفاده می کنند دقیقاً از همان قوانین ردیابی تغییرات پیروی
می کنند که هر کوئری LINQ دیگر در EF Core.

مثال زیر از یک پرس و جوی SQL استفاده می کند که یک Table-Valued Function (TVF) را select می کند، سپس ردیابی تغییر را با فراخوانی AsNoTracking غیرفعال می کند:

var searchTerm = &quottext&quot
var blogs = context.Blogs
    .FromSql($&quotSELECT * FROM dbo.SearchBlogs({searchTerm})&quot)
    .AsNoTracking()
    .ToList();

یک Table-Valued Function (TVF)، یک تابع تعریف شده توسط کاربر است که یک جدول را برمی گرداند. شما می توانید از TVF در هر جایی که می توانید از جدول استفاده کنید، آن را نیز بکار برید. TVFها مشابه viewها رفتار می کنند، اما یک TVF می تواند پارامترهایی داشته باشد.

پرس و جو از scalar (non-entity) types :

این ویژگی از EF Core 7.0 معرفی شده است.

در حالی که FromSql برای جستجوی موجودیت های تعریف شده در مدل شما مفید است، SqlQuery به شما اجازه می دهد تا به راحتی انواع اسکالر و غیر موجودیت را از طریق SQL جستجو کنید، به عنوان مثال، پرس و جو زیر همه شناسه ها را از جدول blog واکشی می کند:

var ids = context.Database
    .SqlQuery<int>($&quotSELECT [BlogId] FROM [Blogs]&quot)
    .ToList();

از آنجایی که SQL شما به یک subquery تبدیل می‌شود باید ستون خروجی را value نامگذاری کنید. به عنوان مثال، کوئری زیر شناسه هایی را که بالاتر از میانگین ID هستند برمی گرداند:

var overAverageIds = context.Database
    .SqlQuery<int>($&quotSELECT [BlogId] AS [Value] FROM [Blogs]&quot)
    .Where(id => id > context.Blogs.Average(b => b.BlogId))
    .ToList();

اجرای non-querying SQL :

در برخی از سناریوها، ممکن است نیاز به اجرای SQL باشد که هیچ داده ای را بر نمی گرداند، معمولاً برای اصلاح داده ها در پایگاه داده یا فراخوانی یک رویه ذخیره شده که هیچ مجموعه نتیجه ای را بر نمی گرداند. این را
می توان از طریق ExecuteSql انجام داد:

using (var context = new BloggingContext())
{
    var rowsModified = context.Database.ExecuteSql($&quotUPDATE [Blogs] SET [Url] = NULL&quot);
}

این SQL ارائه شده را اجرا می کند و تعداد ردیف های اصلاح شده را برمی گرداند. ExecuteSql با استفاده از پارامترسازی ایمن از SQL injection محافظت می کند، درست مانند FromSql، و ExecuteSqlRaw امکان ساخت پویا پرس و جوهای SQL را می دهد، درست همانطور که FromSqlRaw برای پرس و جوها انجام
می دهد.

قبل از EF Core 7.0، گاهی اوقات لازم بود که از APIهای ExecuteSql برای انجام
"به روز رسانی انبوه/bulk update" در پایگاه داده، مانند بالا استفاده شود. این به طور قابل توجهی کارآمدتر از پرس و جو برای همه ردیف های منطبق و سپس استفاده از SaveChanges برای اصلاح آنها است. EF Core 7.0 ExecuteUpdate و ExecuteDelete را معرفی کرد که امکان بیان عملیات به روز رسانی انبوه کارآمد را از طریق LINQ فراهم کرد. توصیه می شود در صورت امکان به جای ExecuteSql از آن API ها استفاده کنید.

محدودیت ها :

هنگام برگرداندن entity typeها از پرس و جوهای SQL، باید از چند محدودیت آگاه بود:

• پرس و جوی SQL باید داده ها را برای تمام پراپرتی های ntity type برگرداند.
• نام ستون‌ها در مجموعه نتایج باید با نام ستون‌هایی که پراپرتی ها به آن نگاشت شده‌اند مطابقت داشته باشد.
• پرس و جوی SQL نمی تواند حاوی داده های مرتبط باشد. با این حال، در بسیاری از موارد می‌توانید با استفاده از عملگر Include برای بازگرداندن داده‌های مرتبط، استفاده کنید همانطور که در این پست به آن اشاره شده است.

بیشتر بخوانید : دوره آموزشی Entity FrameWork Core - قسمت 30

بیشتر بخوانید : دوره آموزشی Entity FrameWork Core

بیشتر بخوانید : نقشه راه توسعه دهندگان Asp.NET Core

https://zarinp.al/farshidazizi