EF Code First #13

استفاده مستقيم از عبارات SQL در EF Code first

طراحي اكثر ORMهاي موجود به نحوي است كه برنامه نهايي شما را مستقل از بانك اطلاعاتي كنند و اين پروايدر نهايي است كه معادل‌هاي صحيح بسياري از توابع توكار بانك اطلاعاتي مورد استفاده را در اختيار EF قرار مي‌دهد. براي مثال در يك بانك اطلاعاتي تابعي به نام substr تعريف شده، در بانك اطلاعاتي ديگري همين تابع substring نام دارد. اگر برنامه را به كمك كوئري‌هاي LINQ تهيه كنيم، نهايتا پروايدر نهايي مخصوص بانك اطلاعاتي مورد استفاده است كه اين معادل‌ها را در اختيار EF قرار مي‌دهد و برنامه بدون مشكل كار خواهد كرد. اما يك سري از موارد شايد معادلي در ساير بانك‌هاي اطلاعاتي نداشته باشند؛ براي مثال رويه‌هاي ذخيره شده يا توابع تعريف شده توسط كاربر. امكان استفاده از يك چنين توانايي‌هايي نيز با اجراي مستقيم عبارات SQL در EF Code first پيش بيني شده و بديهي است در اين حالت برنامه به يك بانك اطلاعاتي خاص گره خواهد خورد؛ همچنين مزيت استفاده از كوئري‌هاي Strongly typed تحت نظر كامپايلر را نيز از دست خواهيم داد. به علاوه بايد به يك سري مسايل امنيتي نيز دقت داشت كه در ادامه بررسي خواهند شد.


كلاس‌هاي مدل مثال جاري

در مثال جاري قصد داريم نحوه استفاده از رويه‌هاي ذخيره شده و توابع تعريف شده توسط كاربر مخصوص SQL Server را بررسي كنيم. در اينجا كلاس‌هاي پزشك و بيماران او، كلاس‌هاي مدل برنامه را تشكيل مي‌دهند:

using System.Collections.Generic;

namespace EF_Sample08.DomainClasses
{
    public class Doctor
    {
        public int Id { set; get; }
        public string Name { set; get; }

        public virtual ICollection<Patient> Patients { set; get; }
    }
}

namespace EF_Sample08.DomainClasses
{
    public class Patient
    {
        public int Id { set; get; }
        public string Name { set; get; }

        public virtual Doctor Doctor { set; get; }
    }
}

كلاس Context برنامه به نحو زير تعريف شده:

using System.Data.Entity;
using EF_Sample08.DomainClasses;

namespace EF_Sample08.DataLayer.Context
{
    public class Sample08Context : DbContext
    {
        public DbSet<Doctor> Doctors { set; get; }
        public DbSet<Patient> Patients { set; get; }
    }
}

و اينبار كلاس DbMigrationsConfiguration تعريف شده اندكي با مثال‌هاي قبلي متفاوت است:

using System.Data.Entity.Migrations;
using EF_Sample08.DomainClasses;
using System.Collections.Generic;

namespace EF_Sample08.DataLayer.Context
{
    public class Configuration : DbMigrationsConfiguration<Sample08Context>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = true;
            AutomaticMigrationDataLossAllowed = true;
        }

        protected override void Seed(Sample08Context context)
        {
            addData(context);
            addSP(context);
            addFn(context);
            base.Seed(context);
        }

        private static void addData(Sample08Context context)
        {
            var patient1 = new Patient { Name = "p1" };
            var patient2 = new Patient { Name = "p2" };
            var doctor1 = new Doctor { Name = "doc1", Patients = new List<Patient> { patient1, patient2 } };
            context.Doctors.Add(doctor1);
        }

        private static void addFn(Sample08Context context)
        {
            context.Database.ExecuteSqlCommand(
                @"IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FindDoctorPatientsCount]') 
                   AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
                      DROP FUNCTION [dbo].[FindDoctorPatientsCount]");
            context.Database.ExecuteSqlCommand(
                @"CREATE FUNCTION FindDoctorPatientsCount(@Doctor_Id INT)
                    RETURNS INT
                    BEGIN
                     RETURN 
                     (
                         SELECT COUNT(*)
                         FROM   Patients
                         WHERE  Doctor_Id = @Doctor_Id
                     );
                    END");
        }

        private static void addSP(Sample08Context context)
        {
            context.Database.ExecuteSqlCommand(
                @"IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FindDoctorsStartWith]')
                  AND type in (N'P', N'PC'))
                       DROP PROCEDURE [dbo].[FindDoctorsStartWith]
                 ");
            context.Database.ExecuteSqlCommand(
                @"CREATE PROCEDURE FindDoctorsStartWith(@name NVARCHAR(400))
                  AS
                 SELECT *
                 FROM   Doctors
                 WHERE  [Name] LIKE @name + '%'");
        }
    }
}

در اينجا از متد Seed علاوه بر مقدار دهي اوليه جداول، براي تعريف يك رويه ذخيره شده به نام FindDoctorsStartWith و يك تابع سفارشي به نام FindDoctorPatientsCount نيز استفاده شده است. متد context.Database.ExecuteSqlCommand مستقيما يك عبارت SQL را بر روي بانك اطلاعاتي اجرا مي‌كند.

در ادامه كدهاي كامل برنامه نهايي را ملاحظه مي‌كنيد:

using System;
using System.Data;
using System.Data.Entity;
using System.Data.Objects.SqlClient;
using System.Data.SqlClient;
using System.Linq;
using EF_Sample08.DataLayer.Context;
using EF_Sample08.DomainClasses;

namespace EF_Sample08
{
    class Program
    {
        static void Main(string[] args)
        {
            Database.SetInitializer(new MigrateDatabaseToLatestVersion<Sample08Context, Configuration>());

            using (var db = new Sample08Context())
            {
                runSp(db);
                runFn(db);
                usingSqlFunctions(db);
            }
        }

        private static void usingSqlFunctions(Sample08Context db)
        {
            var doctorsWithNumericNameList = db.Doctors.Where(x => SqlFunctions.IsNumeric(x.Name) == 1).ToList();
            if (doctorsWithNumericNameList.Any())
            {
                //do something
            }
        }

        private static void runFn(Sample08Context db)
        {
            var doctorIdParameter = new SqlParameter
            {
                ParameterName = "@doctor_id",
                Value = 1,
                SqlDbType = SqlDbType.Int
            };
            var patientsCount = db.Database.SqlQuery<int>("select dbo.FindDoctorPatientsCount(@doctor_id)", doctorIdParameter).FirstOrDefault();
            Console.WriteLine(patientsCount);
        }

        private static void runSp(Sample08Context db)
        {
            var nameParameter = new SqlParameter
            {
                ParameterName = "@name",
                Value = "doc",
                Direction = ParameterDirection.Input,
                SqlDbType = SqlDbType.NVarChar
            };
            var doctors = db.Database.SqlQuery<Doctor>("exec FindDoctorsStartWith @name", nameParameter).ToList();
            if (doctors.Any())
            {
                foreach (var item in doctors)
                {
                    Console.WriteLine(item.Name);
                }
            }
        }
    }
}

توضيحات

همانطور كه ملاحظه مي‌كنيد، براي اجراي مستقيم يك عبارت SQL صرفنظر از اينكه يك رويه ذخيره شده است يا يك تابع و يا يك كوئري معمولي، بايد از متد db.Database.SqlQuery استفاده كرد. خروجي اين متد از نوع IEnumerable است و اين توانايي را دارد كه ركوردهاي بازگشت داده شده از بانك اطلاعاتي را به خواص يك كلاس به صورت خودكار نگاشت كند.
پارامتر اول متد db.Database.SqlQuery، عبارت SQL مورد نظر است. پارامتر دوم آن بايد توسط وهله‌هايي از كلاس SqlParameter مقدار دهي شود. به كمك SqlParameter نام پارامتر مورد استفاده، مقدار و نوع آن مشخص مي‌گردد. همچنين Direction آن نيز براي استفاده از رويه‌هاي ذخيره شده ويژه‌اي كه داراي پارامتري از نوع out هستند درنظر گرفته شده است.

چند نكته

- در متد runSp فوق، متد الحاقي ToList را حذف كرده و برنامه را اجرا كنيد. بلافاصله پيغام خطاي «The SqlParameter is already contained by another SqlParameterCollection.» ظاهر خواهد شد. علت هم اين است كه با بكارگيري متد ToList، تمام عمليات يكبار انجام شده و نتيجه بازگشت داده مي‌شود اما اگر به صورت مستقيم از خروجي IEnumerable آن استفاده كنيم، در حلقه foreach تعريف شده، ممكن است اين فراخواني چندبار انجام شود. به همين جهت ذكر متد ToList در اينجا ضروري است.

- عنوان شد كه در اينجا بايد به مسايل امنيتي دقت داشت. بديهي است امكان نوشتن يك چنين كوئري‌هايي نيز وجود دارد:

db.Database.SqlQuery<Doctor>("exec FindDoctorsStartWith "+ txtName.Text, nameParameter).ToList()

در اين حالت به ظاهر مشغول به استفاده از رويه‌هاي ذخيره شده‌اي هستيم كه عنوان مي‌شود در برابر حملات تزريق SQL در امان هستند، اما چون در كدهاي ما به نحو ناصحيحي با جمع زدن رشته‌ها مقدار دهي شده است، برنامه و بانك اطلاعاتي ديگر در امان نخواهند بود. بنابراين در اين حالت استفاده از پارامترها را نبايد فراموش كرد.
زمانيكه از كوئري‌هاي LINQ استفاده مي‌شود تمام اين مسايل توسط EF مديريت خواهد شد. اما اگر قصد داريد مستقيما عبارات SQL را فراخواني كنيد، تامين امنيت برنامه به عهده خودتان خواهد بود.

- در متد usingSqlFunctions از SqlFunctions.IsNumeric استفاده شده است. اين مورد مختص به SQL Server است و امكان استفاده از توابع توكار ويژه SQL Server را در كوئري‌هاي LINQ برنامه فراهم مي‌سازد. براي مثال متدالحاقي از پيش تعريف شده‌اي به نام IsNumeric به صورت مستقيم در دسترس نيست، اما به كمك كلاس SqlFunctions اين تابع و بسياري از توابع ديگر توكار SQL Server قابل استفاده خواهند بود.
اگر علاقمند هستيد كه ليست اين توابع را مشاهده كنيد، در ويژوال استوديو بر روي SqlFunctions كليك راست كرده و گزينه Go to definition را انتخاب كنيد.