SELECT   facid,
         DATEPART(month, [StartTime]) AS month,
         sum(slots) AS slots
FROM     bookings
WHERE    starttime >= '2012-01-01'
         AND starttime < '2013-01-01'
GROUP BY ROLLUP(facid, DATEPART(month, [StartTime]))
ORDER BY facid, month;

var date1 = new DateTime(2012, 01, 01);
var date2 = new DateTime(2013, 01, 01);

var facilities = context.Bookings
                                    .Where(booking => booking.StartTime >= date1
                                                        && booking.StartTime < date2)
                                    .GroupBy(booking => new { booking.FacId, booking.StartTime.Month })
                                    .Select(group => new
                                    {
                                        group.Key.FacId,
                                        group.Key.Month,
                                        TotalSlots = group.Sum(booking => booking.Slots)
                                    })
                                    .OrderBy(result => result.FacId)
                                        .ThenBy(result => result.Month)
                                    .ToList()
                            //This is new
                            .GroupByWithRollup(
                                item => item.FacId,
                                item => item.Month,
                                (primaryGrouping, secondaryGrouping) => new
                                {
                                    FacId = primaryGrouping.Key,
                                    Month = secondaryGrouping.Key,
                                    TotalSlots = secondaryGrouping.Sum(item => item.TotalSlots)
                                },
                                item => new
                                {
                                    FacId = item.Key,
                                    Month = -1,
                                    TotalSlots = item.SubTotal(subItem => subItem.TotalSlots)
                                },
                                items => new
                                {
                                    FacId = -1,
                                    Month = -1,
                                    TotalSlots = items.GrandTotal(subItem => subItem.TotalSlots)
                                });

var items = context.Bookings
                                    .GroupBy(booking => new { booking.FacId, booking.Facility.Name })
                                    .Select(group => new
                                    {
                                        group.Key.FacId,
                                        group.Key.Name,
                                        TotalHours = group.Sum(booking => booking.Slots) / 2M
                                    })
                                    .OrderBy(result => result.FacId)
                                    .ToList();

var date1 = new DateTime(2012, 09, 01);
var items = context.Bookings
                                    .Where(booking => booking.StartTime >= date1)
                                    .GroupBy(booking => new
                                    {
                                        booking.Member.Surname,
                                        booking.Member.FirstName,
                                        booking.Member.MemId
                                    })
                                    .Select(group => new
                                    {
                                        group.Key.Surname,
                                        group.Key.FirstName,
                                        group.Key.MemId,
                                        StartTime = group.Min(booking => booking.StartTime)
                                    })
                                    .OrderBy(result => result.MemId)
                                    .ToList();

var members = context.Members
                        .OrderBy(member => member.JoinDate)
                        .Select(member => new
                        {
                            Count = context.Members.Count(),
                            member.FirstName,
                            member.Surname
                        })
                        .ToList();

SELECT COUNT(*)
FROM   [Members] AS [m];

SELECT   [m].[FirstName],
         [m].[Surname],
         @__Count_0 AS [Count]
FROM     [Members] AS [m]
ORDER BY [m].[JoinDate];

SELECT   row_number() OVER (ORDER BY joindate) AS row_number,
         firstname,
         surname
FROM     members
ORDER BY joindate;

var members = context.Members
                        .OrderBy(member => member.JoinDate)
                        .Select(member => new
                        {
                            member.FirstName,
                            member.Surname
                        })
                        .ToList()
                        /*
                            SELECT [m].[FirstName], [m].[Surname]
                                FROM [Members] AS [m]
                                ORDER BY [m].[JoinDate]
                        */
                        // Now using LINQ to Objects
                        .Select((member, index) => new
                        {
                            RowNumber = index + 1,
                            member.FirstName,
                            member.Surname
                        })
                        .ToList();

SELECT facid,
       total
FROM   (SELECT   facid,
                 sum(slots) AS total,
                 rank() OVER (ORDER BY sum(slots) DESC) AS rank
        FROM     bookings
        GROUP BY facid) AS ranked
WHERE  rank = 1;

SELECT   mems.firstname,
         mems.surname,
         ((sum(bks.slots) + 10) / 20) * 10 AS hours,
         rank() OVER (ORDER BY ((sum(bks.slots) + 10) / 20) * 10 DESC) AS rank
FROM     bookings AS bks
         INNER JOIN
         members AS mems
         ON bks.memid = mems.memid
GROUP BY mems.firstname,
         mems.surname
ORDER BY rank, mems.surname, mems.firstname;

var itemsQuery = context.Bookings
                                    .GroupBy(booking => new
                                    {
                                        booking.Member.FirstName,
                                        booking.Member.Surname
                                    })
                                    .Select(group => new
                                    {
                                        group.Key.FirstName,
                                        group.Key.Surname,
                                        Hours = (group.Sum(booking => booking.Slots) + 10) / 20 * 10
                                    })
                                    .OrderByDescending(result => result.Hours)
                                        .ThenBy(result => result.Surname)
                                        .ThenBy(result => result.FirstName);
                var rankedItems = itemsQuery.Select(thisItem => new
                {
                    thisItem.FirstName,
                    thisItem.Surname,
                    thisItem.Hours,
                    Rank = itemsQuery.Count(mainItem => mainItem.Hours > thisItem.Hours) + 1
                })
                .ToList();

var facilitiesQuery =
                            context.Bookings.Select(booking =>
                                new
                                {
                                    booking.Facility.Name,
                                    Revenue = booking.MemId == 0 ?
                                            booking.Slots * booking.Facility.GuestCost
                                            : booking.Slots * booking.Facility.MemberCost
                                })
                                .GroupBy(b => b.Name)
                                .Select(group => new
                                {
                                    Name = group.Key,
                                    TotalRevenue = group.Sum(b => b.Revenue)
                                })
                                .OrderBy(result => result.TotalRevenue);

                var rankedFacilities = facilitiesQuery.Select(thisItem => new
                {
                    thisItem.Name,
                    thisItem.TotalRevenue,
                    Rank = facilitiesQuery.Count(mainItem => mainItem.TotalRevenue > thisItem.TotalRevenue) + 1
                })
                .Where(result => result.Rank <= 3)
                .OrderBy(result => result.Rank)
                .ToList();

SELECT   name,
         CASE WHEN class = 1 THEN 'high' WHEN class = 2 THEN 'average' ELSE 'low' END AS revenue
FROM     (SELECT   facs.name AS name,
                   ntile(3) OVER (ORDER BY sum(CASE WHEN memid = 0 THEN slots * facs.guestcost ELSE slots * membercost END) DESC) AS class
          FROM     bookings AS bks
                   INNER JOIN
                   facilities AS facs
                   ON bks.facid = facs.facid
          GROUP BY facs.name) AS subq
ORDER BY class, name;

var facilities =
                            context.Bookings.Select(booking =>
                                new
                                {
                                    booking.Facility.Name,
                                    Revenue = booking.MemId == 0 ?
                                            booking.Slots * booking.Facility.GuestCost
                                            : booking.Slots * booking.Facility.MemberCost
                                })
                                .GroupBy(b => b.Name)
                                .Select(group => new
                                {
                                    Name = group.Key,
                                    TotalRevenue = group.Sum(b => b.Revenue)
                                })
                                .OrderByDescending(result => result.TotalRevenue)
                                .ToList();

SELECT   [f].[Name],
         SUM(CASE WHEN [b].[MemId] = 0 THEN CAST ([b].[Slots] AS DECIMAL (18, 6)) * [f].[GuestCost] ELSE CAST ([b].[Slots] AS DECIMAL (18, 6)) * [f].[MemberCost] END) AS [TotalRevenue]
FROM     [Bookings] AS [b]
         INNER JOIN
         [Facilities] AS [f]
         ON [b].[FacId] = [f].[FacId]
GROUP BY [f].[Name]
ORDER BY SUM(CASE WHEN [b].[MemId] = 0 THEN CAST ([b].[Slots] AS DECIMAL (18, 6)) * [f].[GuestCost] ELSE CAST ([b].[Slots] AS DECIMAL (18, 6)) * [f].[MemberCost] END) DESC;

var n = 3;
var tiledFacilities = facilities.Select((item, index) =>
                                        new
                                        {
                                            Item = item,
                                            Index = (index / n) + 1
                                        })
                                        .GroupBy(x => x.Index)
                                        .Select(g =>
                                            g.Select(z =>
                                                new
                                                {
                                                    z.Item.Name,
                                                    z.Item.TotalRevenue,
                                                    Tile = g.Key,
                                                    GroupName = g.Key == 1 ? "High" : (g.Key == 2 ? "Average" : "Low")
                                                })
                                                .OrderBy(x => x.GroupName)
                                                    .ThenBy(x => x.Name)
                                        )
                                        .ToList();

var flatTiledFacilities = tiledFacilities.SelectMany(group => group)
                                        .Select(tile => new { tile.Name, Revenue = tile.GroupName })
                                        .ToList();

var facilities =
                            context.Bookings.Select(booking =>
                                new
                                {
                                    booking.Facility.Name,
                                    booking.Facility.InitialOutlay,
                                    booking.Facility.MonthlyMaintenance,
                                    Revenue = booking.MemId == 0 ?
                                            booking.Slots * booking.Facility.GuestCost
                                            : booking.Slots * booking.Facility.MemberCost
                                })
                                .GroupBy(b => new
                                {
                                    b.Name,
                                    b.InitialOutlay,
                                    b.MonthlyMaintenance
                                })
                                .Select(group => new
                                {
                                    group.Key.Name,
                                    RepayTime =
                                        group.Key.InitialOutlay /
                                                ((group.Sum(b => b.Revenue) / 3) - group.Key.MonthlyMaintenance)
                                })
                                .OrderBy(result => result.Name)
                                .ToList();

var startDate = new DateTime(2012, 08, 1);
var endDate = new DateTime(2012, 08, 31);
var period = 14;

var dailyRevenueQuery =
                        context.Bookings
                                .Select(booking =>
                                new
                                {
                                    StartDate = booking.StartTime.Date, // How to group by date (or TruncateTime) in EF-Core
                                    Revenue = booking.MemId == 0 ?
                                                           booking.Slots * booking.Facility.GuestCost
                                                           : booking.Slots * booking.Facility.MemberCost
                                })
                                .GroupBy(b => b.StartDate)
                                .Select(group =>
                                new
                                {
                                    Date = group.Key,
                                    TotalRevenue = group.Sum(b => b.Revenue)
                                });

var movingAvgs =
                        dailyRevenueQuery
                                .Select(dr1 =>
                                new
                                {
                                    dr1.Date,
                                    MovingAvg = dailyRevenueQuery
                                        .Where(dr2 => dr2.Date <= dr1.Date && dr2.Date >= dr1.Date.AddDays(-period))
                                        .Average(dr2 => dr2.TotalRevenue)
                                })
                                .Where(result => result.Date >= startDate && result.Date <= endDate)
                                .OrderBy(result => result.Date)
                                .ToList();

    public class Person
    {
        public int Id { get; set; }
        public string Name { get; set; }

        [ForeignKey("BornInCityId")]
        public virtual City BornInCity { get; set; }
        public int BornInCityId { get; set; }
    }

    public class City
    {
        public int Id { get; set; }
        public string Name { get; set; }

        public virtual ICollection<Person> People { get; set; }
    }

    public class MyContext : DbContext
    {
        public DbSet<City> Cities { get; set; }
        public DbSet<Person> People { get; set; }
    }

    public class Configuration : DbMigrationsConfiguration<MyContext>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = true;
            AutomaticMigrationDataLossAllowed = true;
        }

        protected override void Seed(MyContext context)
        {
            var city1 = new City { Name = "city-1" };
            var city2 = new City { Name = "city-2" };
            context.Cities.Add(city1);
            context.Cities.Add(city2);

            var person1 = new Person { Name = "user-1", BornInCity = city1 };
            var person2 = new Person { Name = "user-2", BornInCity = city1 };
            context.People.Add(person1);
            context.People.Add(person2);

            base.Seed(context);
        }
    }

    public static class Test
    {
        public static void RunTests()
        {
            Database.SetInitializer(new MigrateDatabaseToLatestVersion<MyContext, Configuration>());

            using (var context = new MyContext())
            {
                var peopleAndCitiesList = from person in context.People
                           join city in context.Cities
                           on person.BornInCityId equals city.Id
                           select new
                           {
                              PersonName = person.Name,
                              CityName = city.Name
                           };

                foreach (var item in peopleAndCitiesList)
                {
                    Console.WriteLine("{0}:{1}", item.PersonName, item.CityName);
                }
            }
        }
    }

SELECT 
          [Extent1].[BornInCityId] AS [BornInCityId], 
          [Extent1].[Name] AS [Name], 
          [Extent2].[Name] AS [Name1]
FROM  [dbo].[People] AS [Extent1]
INNER JOIN [dbo].[Cities] AS [Extent2] ON [Extent1].[BornInCityId] = [Extent2].[Id]

var peopleAndCitiesList = context.People
                                  .Select(person => new
                                                         {
                                                             PersonName = person.Name,
                                                             CityName = person.BornInCity.Name
                                                         });

var citiesList = context.Cities.OrderByDescending(x => x.People.Count());
foreach (var item in citiesList)
{
    Console.WriteLine("{0}", item.Name);
}

SELECT 
[Project1].[Id] AS [Id], 
[Project1].[Name] AS [Name]
FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Name] AS [Name], 
        (SELECT 
                COUNT(1) AS [A1]
                FROM [dbo].[People] AS [Extent2]
                WHERE [Extent1].[Id] = [Extent2].[BornInCityId]) AS [C1]
        FROM [dbo].[Cities] AS [Extent1]
)  AS [Project1]
ORDER BY [Project1].[C1] DESC

 var peopleAndCitiesList = context.Cities
                                     .Select(city => new
                                                 {
                                                     InUseCount = city.People.Count(),
                                                     CityName = city.Name
                                                 });

foreach (var item in peopleAndCitiesList)
{
     Console.WriteLine("{0}:{1}", item.CityName, item.InUseCount);
}

SELECT 
[Extent1].[Id] AS [Id], 
(SELECT 
        COUNT(1) AS [A1]
        FROM [dbo].[People] AS [Extent2]
        WHERE [Extent1].[Id] = [Extent2].[BornInCityId]) AS [C1], 
[Extent1].[Name] AS [Name]
FROM [dbo].[Cities] AS [Extent1]

public void OpenConnection()
{
   Database.OpenConnection();
}

public DbCommand Command()
{
   DbCommand cmd = Database.GetDbConnection().CreateCommand();
   return cmd;
}

void OpenConnection();
DbCommand Command();

public void ConfigureServices(IServiceCollection services)
{
     services.AddScoped<IUnitOfWork, ApplicationDbContext>();
     services.AddScoped<ISpReader, SpReader>();
}

public List<ViewModel> GetFromSp <ViewModel>(string[,] Parametr, string NameSp) where ViewModel  : new()

        {
            _uow.OpenConnection();
            DbCommand cmd = _uow.Command();
            cmd.CommandText = NameSp;
            cmd.CommandType = CommandType.StoredProcedure;
            var countParametr = Parametr.GetLength(0);

            for (int i = 0; i < countParame tr; i++)
            {
                cmd.Parameters.Add(new SqlParameter { ParameterName = Parametr[i, 0], Value = Parametr[i, 1] });
            }

            List<ViewModel> list = new List<ViewModel >();
            using (var reader = cmd.ExecuteReader())
            {
                if (reader != null && reader.HasRows)
                {
                    var entity = typeof(ViewModel);
                    var propDict = new Dictionary<string, PropertyInfo>();
                    var props = entity.GetProperties
           (BindingFlags.Instance | BindingFlags.Public);
                    propDict = props.ToDictionary(p => p.Name.ToUpper(), p => p);
                    while (reader.Read())
                    {
                        ViewModel  newobject = new ViewModel ();

                        for (int index = 0; index < reader.FieldCount; index++)
                        {
                            if (propDict.ContainsKey(reader.GetName(index).ToUpper()))
                            {
                                var info = propDict[reader.GetName(index).ToUpper()];
                                if ((info != null) && info.CanWrite)
                                {
                                    var val = reader.GetValue(index);
                                    info.SetValue(newobject, (val == DBNull.Value) ? null : val, null);
                                }
                            }
                        }
                        list.Add(newobject);
                    }

                }
                return list;
            }

var countParametr = Parametr.GetLength(0);
for (int i = 0; i < countParametr; i++)
{
     cmd.Parameters.Add(new SqlParameter { ParameterName = Parametr[i, 0], Value = Parametr[i, 1] });
}

CREATE PROCEDURE [dbo].[isRelation](
@TableName as varchar(50),
@FieldOfRelation as varchar(70),
@ValueOfField as int)

public class EntityServices : IEntityService
    {
        private ISpreader _Reader;
        public EntityServices( ISpreader reader)
        {
            _Reader = reader;
        }

        public List<StoreProcedureResultViewModel>  IsRelation(string tableName , int keyValue, string keyFieldName)
        {
            List<StoreProcedureResultViewModel> IsContact;
            try
            {
                string[,] Parametr = new string[3, 2];
                Parametr[0, 0] = "@TableName";
                Parametr[0, 1] = tableName ;
                Parametr[1, 0] = "@ValueOfField";
                Parametr[1, 1] = keyValue.ToString().Trim();
                Parametr[2, 0] = "@FieldOfRelation";
                Parametr[2, 1] = keyFieldName.Trim();
                IsContact = _Reader.GetSp<StoreProcedureResultViewModel>(Parametr, "IsRelation");
                return IsContact;
            }
            catch (Exception ex)
            {
            }
        }
    }

متد زیر را که یکی از اشتباهات رایج حین استفاده از LINQ خصوصا جهت Binding اطلاعات است، در نظر بگیرید:

این متد در حقیقت هیچ چیزی را Get نمی‌کند! نام اصلی آن GetQueryableCustomers و یا GetQueryObjectForCustomersاست.
IQueryable قلب LINQ است و تنها بیانگر یک عبارت (expression) از رکوردهایی می‌باشد که مد نظر شما است و نه بیشتر.
برای مثال زمانیکه یک IQueryable را همانند مثال فوق فیلتر می‌کنید نیز هنوز چیزی از بانک اطلاعاتی یا منبع داده‌ای دریافت نشده است. هنوز هیچ اتفاقی رخ نداده است و هنوز رفت و برگشتی به منبع داده‌ای صورت نگرفته است.
به آن باید به شکل یک expression builder نگاه کرد و نه لیستی از اشیاء فیلتر شده‌ی ما. به این مفهوم، deferred execution (اجرای به تاخیر افتاده) نیز گفته می‌شود (باید دقت داشت که IQueryable هم یک نوع IEnumerable است به علاوه expression trees که مهم‌ترین وجه تمایز آن نیز می‌باشد).
برای مثال در عبارت زیر تنها در زمانیکه متد ToList فراخوانی می‌شود، کل عبارت LINQ ساخته شده، به عبارت SQL متناظر با آن ترجمه شده، اطلاعات از دیتابیس اخذ گردیده و حاصل به صورت یک لیست بازگشت داده می‌شود:

در مورد IEnumerable ها چطور؟
دو سطر فوق به این معنا است:
لطفا ابتدا به بانک اطلاعاتی رجوع کن و تمام رکوردهای محصولات موجود را بازگشت بده. سپس بر روی این حجم بالای اطلاعات، محصولاتی را که قیمت بالای 25 دارند، فیلتر کن.

اگر همین دو سطر را با IQueryable بازنویسی کنیم چطور؟
در سطر اول تنها یک عبارت LINQ ساخته شده است و بس. در سطر دوم نیز به همین صورت. در طی این دو سطر حتی یک رفت و برگشت به بانک اطلاعاتی صورت نخواهد گرفت. در ادامه اگر این اطلاعات به نحوی Select شوند (یا ToList فراخوانی شود، یا در طی یک حلقه برای مثال Iteration ایی روی این حاصل صورت گیرد یا موارد مشابه دیگر)، آنگاه کوئری SQL متناظر با عبارت LINQ فوق ساخته شده و بر روی بانک اطلاعاتی اجرا خواهد شد.
بدیهی است این روش منابع کمتری را نسبت به حالتی که تمام اطلاعات ابتدا دریافت شده و سپس فیلتر می‌شوند، مصرف می‌کند (حالت بازگشت تمام اطلاعات ممکن است شامل 20000 رکورد باشد، اما حالت دوم شاید فقط 5 رکورد را بازگشت دهد).

سؤال: پس IQueryable بسیار عالی است و از این پس کلا از IEnumerable ها دیگر نباید استفاده کرد؟
خیر! توصیه اکید طراحان این است که لطفا تا حد امکان متدهایی که IQueryable بازگشت می‌دهند ایجاد نکنید! IQueryable یعنی اینکه این نقطه‌ی آغازین کوئری در اختیار شما، بعد برو هر کاری که دوست داشتی با آن در طی لایه‌های مختلف انجام بده و هر زمانیکه دوست داشتی از آن یک خروجی تهیه کن. خروجی IQueryable به معنای مشخص نبودن زمان اجرای نهایی کوئری و همچنین مبهم بودن نحوه‌ی استفاده از آن است. به همین جهت متدهایی را طراحی کنید که IEnumerable بازگشت می‌دهند اما در بدنه‌ی آن‌ها به نحو صحیح و مطلوبی از IQueryable استفاده شده است. به این صورت حد و مرز یک متد کاملا مشخص می‌شود. متدی که واقعا همان فیلتر کردن محصولات را انجام می‌دهد، همان 5 رکورد را بازگشت خواهد داد؛ اما با استفاده از یک لیست یا یک IEnumerable و نه یک IQueryable که پس از فراخوانی متد نیز به هر نحو دلخواهی قابل تغییر است.

- خیر. سطح اول کش در EF 6x فقط به معنای استفاده از متد FindAsync و Find آن است و نه هیچ متد دیگری.

- فراخوانی متد ToList بر روی یک IEnumerable، اتصال آن‌را از بانک اطلاعاتی قطع می‌کند. به همین جهت اگر list نوشته شده (var list = ctx.ProjectStatus.Select )، یک ToList را در انتها داشت، دو حلقه‌ی بعدی فقط LINQ to Objects می‌شدند (از حافظه خوانده می‌شدند)؛ اما در حالت فعلی آن، هر دو مورد LINQ to Entities هستند و متصل به بانک اطلاعاتی.

- البته این مورد متصل بودن به بانک اطلاعاتی، می‌تواند مزیت هم باشد. برای مثال ToList، ابتدا تمام رکوردها را به درون حافظه بارگذاری می‌کند؛ اما اگر صرفا با خروجی Select که از نوع IEnumerable هست کار کنید، هربار یک yield return را شاهد خواهید بود که سربار مصرف حافظه‌ی بسیار کمی را دارد و برای کار با تعداد رکورد بالا بسیار مناسب است.

- برای بررسی دقیق‌تر این موارد همیشه از یک پروفایلر استفاده کنید؛ مانند DNTProfiler و برای مثال بررسی کنید که چه تعدادی کوئری SQL تولید شده. 

حین کار با ORMهای پیشرفته، ویژگی‌های جالب توجهی در اختیار برنامه نویس‌ها قرار می‌گیرد که در زمان استفاده از کلاس‌های متداول SQLHelper از آن‌ها خبری نیست؛ مانند:
الف) Deferred execution
ب) Lazy loading
ج) Eager loading

نحوه بررسی SQL نهایی تولیدی توسط EF

برای توضیح موارد فوق، نیاز به مشاهده خروجی SQL نهایی حاصل از ORM است و همچنین شمارش تعداد بار رفت و برگشت به بانک اطلاعاتی. بهترین ابزاری را که برای این منظور می‌توان پیشنهاد داد، برنامه EF Profiler است. برای دریافت آن می‌توانید به این آدرس مراجعه کنید: (^) و (^)

پس از وارد کردن نام و آدرس ایمیل، یک مجوز یک ماهه آزمایشی، به آدرس ایمیل شما ارسال خواهد شد.
زمانیکه این فایل را در ابتدای اجرای برنامه به آن معرفی می‌کنید، محل ذخیره سازی نهایی آن جهت بازبینی بعدی، مسیر MyUserName\Local Settings\Application Data\EntityFramework Profiler خواهد بود.

استفاده از این برنامه هم بسیار ساده است:
الف) در برنامه خود، ارجاعی را به اسمبلی HibernatingRhinos.Profiler.Appender.dll که در پوشه برنامه EFProf موجود است، اضافه کنید.
ب) در نقطه آغاز برنامه، متد زیر را فراخوانی نمائید:

نقطه آغاز برنامه می‌تواند متد Application_Start برنامه‌های وب، در متد Program.Main برنامه‌های ویندوزی کنسول و WinForms و در سازنده کلاس App برنامه‌های WPF باشد.
ج) برنامه EFProf را اجرا کنید.

مزایای استفاده از این برنامه
1) وابسته به بانک اطلاعاتی مورد استفاده نیست. (برخلاف برای مثال برنامه معروف SQL Server Profiler که فقط به همراه SQL Server ارائه می‌شود)
2) خروجی SQL نمایش داده شده را فرمت کرده و به همراه Syntax highlighting نیز هست.
3) کار این برنامه صرفا به لاگ کردن SQL تولیدی خلاصه نمی‌شود. یک سری از Best practices را نیز به شما گوشزد می‌کند. بنابراین اگر نیاز دارید سیستم خود را بر اساس دیدگاه یک متخصص بررسی کنید (یک Code review ارزشمند)، این ابزار می‌تواند بسیار مفید باشد.
4) می‌تواند کوئری‌های سنگین و سبک را به خوبی تشخیص داده و گزارشات آماری جالبی را به شما ارائه دهد.
5) می‌تواند دقیقا مشخص کند، کوئری را که مشاهده می‌کنید از طریق کدام متد در کدام کلاس صادر شده است و دقیقا از چه سطری.
6) امکان گروه بندی خودکار کوئری‌های صادر شده را بر اساس DbContext مورد استفاده به همراه دارد.
و ...

استفاده از این برنامه حین کار با EF «الزامی» است! (البته نسخه‌های NH و سایر ORMهای دیگر آن نیز موجود است و این مباحث در مورد تمام ORMهای پیشرفته صادق است)
مدام باید بررسی کرد که صفحه جاری چه تعداد کوئری را به بانک اطلاعاتی ارسال کرده و به چه نحوی. همچنین آیا می‌توان با اعمال اصلاحاتی، این وضع را بهبود بخشید. بنابراین عدم استفاده از این برنامه حین کار با ORMs، همانند راه رفتن در خواب است! ممکن است تصور کنید برنامه دارد به خوبی کار می‌کند اما ... در پشت صحنه فقط صفحه جاری برنامه، 100 کوئری را به بانک اطلاعاتی ارسال کرده، در حالیکه شما تنها نیاز به یک کوئری داشته‌اید.


کلاس‌های مدل مثال جاری

کلاس‌های مدل مثال جاری از یک دپارتمان که دارای تعدادی کارمند می‌باشد، تشکیل شده است. ضمنا هر کارمند تنها در یک دپارتمان می‌تواند مشغول به کار باشد و رابطه many-to-many نیست :



نگاشت دستی این کلاس‌ها هم ضرورتی ندارد، زیرا قراردادهای توکار EF Code first را رعایت کرده و EF در اینجا به سادگی می‌تواند primary key و روابط one-to-many را بر اساس navigation properties تعریف شده، تشخیص دهد.

در اینجا کلاس Context برنامه به شرح زیر است:



و تنظیمات ابتدایی نحوه به روز رسانی و آغاز بانک اطلاعاتی نیز مطابق کدهای زیر می‌باشد:


نکته: تهیه خروجی XML از نگاشت‌های خودکار تهیه شده

اگر علاقمند باشید که پشت صحنه نگاشت‌های خودکار EF Code first را در یک فایل XML جهت بررسی بیشتر ذخیره کنید، می‌توان از متد کمکی زیر استفاده کرد:


بهتر است پسوند فایل XML تولیدی را edmx قید کنید تا بتوان آن‌را با دوبار کلیک بر روی فایل، در ویژوال استودیو نیز مشاهده کرد:




الف) بررسی Deferred execution یا بارگذاری به تاخیر افتاده

برای توضیح مفهوم Deferred loading/execution بهترین مثالی را که می‌توان ارائه داد، صفحات جستجوی ترکیبی در برنامه‌ها است. برای مثال یک صفحه جستجو را طراحی کرده‌اید که حاوی دو تکست باکس دریافت FirstName و LastName کاربر است. کنار هر کدام از این تکست باکس‌ها نیز یک چک‌باکس قرار دارد. به عبارتی کاربر می‌تواند جستجویی ترکیبی را در اینجا انجام دهد. نحوه پیاده سازی صحیح این نوع مثال‌ها در EF Code first به چه نحوی است؟


نحوه صحیح این نوع پیاده سازی ترکیبی را در متد FindEmployees مشاهده می‌کنید. نکته مهم آن، استفاده از نوع IQueryable و متد AsQueryable است و امکان ترکیب کوئری‌ها با هم.
به نظر شما با فراخوانی متد FindEmployees به نحو زیر که هر دو شرط آن توسط کاربر انتخاب شده است، چه تعداد کوئری به بانک اطلاعاتی ارسال می‌شود؟


شاید پاسخ دهید که سه بار : یکبار در متد db.Employees.AsQueryable و دوبار هم در حین ورود به بدنه شرط‌های یاد شده و اینجا است که کسانی که قبلا با رویه‌های ذخیره شده کار کرده باشند، شروع به فریاد و فغان می‌کنند که ما قبلا این مسایل رو با یک SP در یک رفت و برگشت مدیریت می‌کردیم!
پاسخ صحیح: «فقط یکبار»! آن‌هم تنها در زمان فراخوانی متد ToList و نه قبل از آن.
برای اثبات این مدعا نیاز است به خروجی SQL لاگ شده توسط EF Profiler مراجعه کرد:



IQueryable قلب LINQ است و تنها بیانگر یک عبارت (expression) از رکوردهایی می‌باشد که مد نظر شما است و نه بیشتر. برای مثال زمانیکه یک IQueryable را همانند مثال فوق فیلتر می‌کنید، هنوز چیزی از بانک اطلاعاتی یا منبع داده‌ای دریافت نشده است. هنوز هیچ اتفاقی رخ نداده است و هنوز رفت و برگشتی به منبع داده‌ای صورت نگرفته است. به آن باید به شکل یک expression builder نگاه کرد و نه لیستی از اشیاء فیلتر شده‌ی ما. به این مفهوم، deferred execution (اجرای به تاخیر افتاده) نیز گفته می‌شود.
کوئری LINQ شما تنها زمانی بر روی بانک اطلاعاتی اجرا می‌شود که کاری بر روی آن صورت گیرد مانند فراخوانی متد ToList، فراخوانی متد First یا FirstOrDefault و امثال آن. تا پیش از این فقط به شکل یک عبارت در برنامه وجود دارد و نه بیشتر.
اطلاعات بیشتر: «تفاوت بین IQueryable و IEnumerable در حین کار با ORMs»



ب) بررسی Lazy Loading یا واکشی در صورت نیاز

در مطلب جاری اگر به کلاس‌های مدل برنامه دقت کنید، تعدادی از خواص به صورت virtual تعریف شده‌اند. چرا؟
تعریف یک خاصیت به صورت virtual، پایه و اساس lazy loading است و به کمک آن، تا به اطلاعات شیءایی نیاز نباشد، وهله سازی نخواهد شد. به این ترتیب می‌توان به کارآیی بیشتری در حین کار با ORMs رسید. برای مثال در کلاس‌های فوق، اگر تنها نیاز به دریافت نام یک دپارتمان هست، نباید حین وهله سازی از شیء دپارتمان، شیء لیست کارمندان مرتبط با آن نیز وهله سازی شده و از بانک اطلاعاتی دریافت شوند. به این وهله سازی با تاخیر، lazy loading گفته می‌شود.
Lazy loading پیاده سازی ساده‌ای نداشته و مبتنی است بر بکارگیری AOP frameworks یا کتابخانه‌هایی که امکان تشکیل اشیاء Proxy پویا را در پشت صحنه فراهم می‌کنند. علت virtual تعریف کردن خواص رابط نیز به همین مساله بر می‌گردد، تا این نوع کتابخانه‌ها بتوانند در نحوه تعریف اینگونه خواص virtual در زمان اجرا، در پشت صحنه دخل و تصرف کنند. البته حین استفاده از EF یا انواع و اقسام ORMs دیگر با این نوع پیچیدگی‌ها روبرو نخواهیم شد و تشکیل اشیاء Proxy در پشت صحنه انجام می‌شوند.

یک مثال: قصد داریم اولین دپارتمان ثبت شده در حین آغاز برنامه را یافته و سپس لیست کارمندان آن‌را نمایش دهیم:



رفتار یک ORM جهت تعیین اینکه آیا نیاز است برای دریافت اطلاعات بین جداول Join صورت گیرد یا خیر، واکشی حریصانه و غیرحریصانه را مشخص می‌سازد.
در حالت واکشی حریصانه به ORM خواهیم گفت که لطفا جهت دریافت اطلاعات فیلدهای جداول مختلف، از همان ابتدای کار در پشت صحنه، Join های لازم را تدارک ببین. در حالت واکشی غیرحریصانه به ORM خواهیم گفت به هیچ عنوان حق نداری Join ایی را تشکیل دهی. هر زمانی که نیاز به اطلاعات فیلدی از جدولی دیگر بود باید به صورت مستقیم به آن مراجعه کرده و آن مقدار را دریافت کنی.
به صورت خلاصه برنامه نویس در حین کار با ORM های پیشرفته نیازی نیست Join بنویسد. تنها باید ORM را طوری تنظیم کند که آیا اینکار را حتما خودش در پشت صحنه انجام دهد (واکشی حریصانه)، یا اینکه خیر، به هیچ عنوان SQL های تولیدی در پشت صحنه نباید حاوی Join باشند (lazy loading).

در مثال فوق به صورت خودکار دو کوئری به بانک اطلاعاتی ارسال می‌گردد:


یکبار زمانیکه قرار است اطلاعات دپارتمان‌ یک (db.Departments.Find) دریافت شود. تا این لحظه خبری از جدول Employees نیست. چون lazy loading فعال است و فقط اطلاعاتی را که نیاز داشته‌ایم فراهم کرده است.
زمانیکه برنامه به حلقه می‌رسد، نیاز است اطلاعات dept1.Employees را دریافت کند. در اینجا است که کوئری دوم، به بانک اطلاعاتی صادر خواهد شد (بارگذاری در صورت نیاز).


ج) بررسی Eager Loading یا واکشی حریصانه

حالت lazy loading بسیار جذاب به نظر می‌رسد؛ برای مثال می‌توان خواص حجیم یک جدول را به جدول مرتبط دیگری منتقل کرد. مثلا فیلد‌های متنی طولانی یا اطلاعات باینری فایل‌های ذخیره شده، تصاویر و امثال آن. به این ترتیب تا زمانیکه نیازی به اینگونه اطلاعات نباشد، lazy loading از بارگذاری آن‌ها جلوگیری کرده و سبب افزایش کارآیی برنامه می‌شود.
اما ... همین lazy loading در صورت استفاده نا آگاهانه می‌تواند سرور بانک اطلاعاتی را در یک برنامه چندکاربره از پا درآورد! نیازی هم نیست تا شخصی به سایت شما حمله کند. مهاجم اصلی همان برنامه نویس کم اطلاع است!
اینبار مثال زیر را درنظر بگیرید که بجای دریافت اطلاعات یک شخص، مثلا قصد داریم، اطلاعات کلیه دپارتمان‌ها را توسط یک Grid نمایش دهیم (فرقی نمی‌کند برنامه وب یا ویندوز باشد؛ اصول یکی است):

یک نکته: اگر سعی کنیم کد فوق را اجرا کنیم به خطای زیر برخواهیم خورد:


برای رفع این مشکل نیاز است گزینه MultipleActiveResultSets=True را به کانکشن استرینگ اضافه کرد:


سؤال: به نظر شما در دو حلقه تو در توی فوق چندبار رفت و برگشت به بانک اطلاعاتی صورت می‌گیرد؟ با توجه به اینکه در متد Seed ذکر شده در ابتدای مطلب، تعداد رکوردها مشخص است.
پاسخ: 7 بار!


و اینجا است که عنوان شد استفاده از EF Profiler در حین توسعه برنامه‌های مبتنی بر ORM «الزامی» است! اگر از این نکته اطلاعی نداشتید، بهتر است یکبار تمام صفحات گزارش‌گیری برنامه‌های خود را که حاوی یک Grid هستند، توسط EF Profiler بررسی کنید. اگر در این برنامه پیغام خطای n+1 select را دریافت کردید، یعنی در حال استفاده ناصحیح از امکانات lazy loading می‌باشید.

آیا می‌توان این وضعیت را بهبود بخشید؟ زمانیکه کار ما گزارشگیری از اطلاعات با تعداد رکوردهای بالا است، استفاده ناصحیح از ویژگی Lazy loading می‌تواند به شدت کارآیی بانک اطلاعاتی را پایین بیاورد. برای حل این مساله در زمان‌های قدیم (!) بین جداول join می‌نوشتند؛ الان چطور؟
در EF متدی به نام Include جهت Eager loading اطلاعات موجودیت‌های مرتبط به هم درنظر گرفته شده است که در پشت صحنه همینکار را انجام می‌دهد:


همانطور که ملاحظه می‌کنید اینبار به کمک متد Include، نسبت به واکشی حریصانه Employees اقدام کرده‌ایم. اکنون اگر برنامه را اجرا کنیم، فقط یک رفت و برگشت به بانک اطلاعاتی انجام خواهد شد و کار Join نویسی به صورت خودکار توسط EF مدیریت می‌گردد:



متد Include در نگارش‌های اخیر EF پیشرفت کرده است و همانند مثال فوق، امکان کار با lambda expressions را جهت تعریف خواص مورد نظر به صورت strongly typed ارائه می‌دهد. در نگارش‌های قبلی این متد، تنها امکان استفاده از رشته‌ها برای معرفی خواص وجود داشت.
همچنین توسط متد Include امکان eager loading چندین سطح با هم نیز وجود دارد؛ مثلا x.Employees.Kids و همانند آن.


چند نکته در مورد نحوه خاموش کردن Lazy loading

امکان خاموش کردن Lazy loading در تمام کلاس‌های برنامه با تنظیم خاصیت Configuration.LazyLoadingEnabled کلاس Context برنامه به نحو زیر میسر است:


یا اگر تنها در مورد یک کلاس نیاز است این خاموش سازی صورت گیرد، کلمه کلیدی virtual را حذف کنید. برای مثال با نوشتن public ICollection<Employee> Employees بجای public virtual ICollection<Employee> Employees در اولین بار وهله سازی کلاس دپارتمان، لیست کارمندان آن به نال تنظیم می‌شود. البته در این حالت null object pattern را نیز فراموش نکنید (وهله سازی پیش فرض Employees در سازنده کلاس):


به این ترتیب به خطای null reference object بر نخواهیم خورد. همچنین وهله سازی، با مقدار دهی لیست دریافتی از بانک اطلاعاتی متفاوت است. در اینجا نیز باید از متد Include استفاده کرد.

بنابراین در صورت خاموش کردن lazy loading، حتما نیاز است از متد Include استفاده شود. اگرlazy loading فعال است، جهت تبدیل آن به eager loading از متد Include استفاده کنید (اما اجباری نیست).

INSERT INTO User_Table(Id,Username,Password,FirstName,SureName,PhoneNumber,x,Email)
VALUES (1,'VahidN','123456','Vahid','Nasiri','09120000000','vahid_xxx@example.com')

public void Insert(User user)
{
  _users.Insert(user);
  db.Save();
}

public ICollection<User> GetUsers(string firstName,string lastName,Func<User, bool> orderBy)
{
    var query = _users.where(u => u.LastName.StartsWith(lastName));
    query = query.where(u => u.FirstName.StartsWith(firstName));
    query = query.OrderBy(orderBy);
    return  query.ToList();
}

CREATE PROCEDURE DynamicWhere 
    @LastName varchar(50) = null,
    @FirstName varchar(50) = null,
    @Orderby varchar(50) = null
AS
BEGIN
    DECLARE @where nvarchar(max)
    SELECT @where = '1 = 1'
 
    IF @LastName IS NOT NULL
        SELECT @Where = @Where + " AND A.LastName LIKE @LastName + '%'"
 
    IF @FirstName IS NOT NULL
        SELECT @Where = @Where + " AND A.FirstName LIKE @FirstName + '%'"
 
    DECLARE @orderBySql nvarchar(max)
    SELECT @orderBySql = CASE
        WHEN @OrderBy = "LastName" THEN "A.LastName"
        ELSE @OrderBy = "FirstName" THEN "A.FirstName"
    END
 
    DECLARE @sql nvarchar(max)
    SELECT @sql = "
    SELECT A.Id , A.AccountNoId, A.LastName, A.FirstName, A.PostingDt, 
    A.BillingAmount
    FROM Users 
    WHERE " + @where + " 
    ORDER BY " + @orderBySql
 
    exec sp_executesql @sql,  N'@LastName varchar(50), @FirstName varchar(50)
        @LastName, @FirstName
END

namespace EFCorePgExercises.Entities
{
    public class Booking
    {
       // ...

        public int MemId { set; get; }
        public virtual Member Member { set; get; }

       // ...
    }
}

var startTimes = context.Bookings
                        .Where(booking => booking.Member.FirstName == "David"
                                            && booking.Member.Surname == "Farrell")
                        .Select(booking => new { booking.StartTime })
                        .ToList();

namespace EFCorePgExercises.Entities
{
    public class Booking
    {
       // ...

        public int FacId { set; get; }
        public virtual Facility Facility { set; get; }

       // ...
    }
}

int[] tennisCourts = { 0, 1 };
var date1 = new DateTime(2012, 09, 21);
var date2 = new DateTime(2012, 09, 22);
var startTimes = context.Bookings
                        .Where(booking => tennisCourts.Contains(booking.Facility.FacId)
                                && booking.StartTime >= date1
                                && booking.StartTime < date2)
                        .Select(booking => new { booking.StartTime, booking.Facility.Name })
                        .ToList();

namespace EFCorePgExercises.Entities
{
    public class Member
    {
       // ...

        public virtual ICollection<Member> Children { get; set; }
        public virtual Member Recommender { set; get; }
        public int? RecommendedBy { set; get; }

       // ...
    }
}

var members = context.Members
                        .Where(member => member.Recommender != null)
                        .Select(member => new { member.Recommender.FirstName, member.Recommender.Surname })
                        .Distinct()
                        .OrderBy(member => member.Surname).ThenBy(member => member.FirstName)
                        .ToList();

var members = context.Members
                        .Select(member => new
                        {
                            memFName = member.FirstName,
                            memSName = member.Surname,
                            recFName = member.Recommender.FirstName ?? "",
                            recSName = member.Recommender.Surname ?? ""
                        })
                        .OrderBy(member => member.memSName).ThenBy(member => member.memFName)
                        .ToList();

A LEFT JOIN B            A LEFT OUTER JOIN B
A RIGHT JOIN B           A RIGHT OUTER JOIN B
A FULL JOIN B            A FULL OUTER JOIN B
A INNER JOIN B           A JOIN B

namespace EFCorePgExercises.Entities
{
    public class Booking
    {
       // ...

        public int FacId { set; get; }
        public virtual Facility Facility { set; get; }

        public int MemId { set; get; }
        public virtual Member Member { set; get; }

       // ...
    }
}

namespace EFCorePgExercises.Entities
{
    public class Member
    {
       // ...

        public virtual ICollection<Booking> Bookings { set; get; }
    }
}

int[] tennisCourts = { 0, 1 };
var members = context.Members
                        .SelectMany(x => x.Bookings)
                        .Where(booking => tennisCourts.Contains(booking.Facility.FacId))
                        .Select(booking => new
                        {
                            Member = booking.Member.FirstName + " " + booking.Member.Surname,
                            Facility = booking.Facility.Name
                        })
                        .Distinct()
                        .OrderBy(x => x.Member)
                        .ToList();

var date1 = new DateTime(2012, 09, 14);
var date2 = new DateTime(2012, 09, 15);

var items = context.Members
                        .SelectMany(x => x.Bookings)
                        .Where(booking => booking.StartTime >= date1 && booking.StartTime < date2
                        && (
                            (((booking.Slots * booking.Facility.GuestCost) > 30) && (booking.MemId == 0)) ||
                            (((booking.Slots * booking.Facility.MemberCost) > 30) && (booking.MemId != 0))
                        ))
                        .Select(booking => new
                        {
                            Member = booking.Member.FirstName + " " + booking.Member.Surname,
                            Facility = booking.Facility.Name,
                            Cost = booking.MemId == 0 ?
                                        booking.Slots * booking.Facility.GuestCost
                                        : booking.Slots * booking.Facility.MemberCost
                        })
                        .Distinct()
                        .OrderByDescending(x => x.Cost)
                        .ToList();

var members = context.Members
                        .Select(member =>
                        new
                        {
                            Member = member.FirstName + " " + member.Surname,
                            Recommender = context.Members
                                .Where(recommender => recommender.MemId == member.RecommendedBy)
                                .Select(recommender => recommender.FirstName + " " + recommender.Surname)
                                .FirstOrDefault() ?? ""
                        })
                        .Distinct()
                        .OrderBy(member => member.Member)
                        .ToList();

var date1 = new DateTime(2012, 09, 14);
var date2 = new DateTime(2012, 09, 15);

var items = context.Members
                        .SelectMany(x => x.Bookings)
                        .Where(booking => booking.StartTime >= date1 && booking.StartTime < date2)
                        .Select(booking => new
                        {
                            Member = booking.Member.FirstName + " " + booking.Member.Surname,
                            Facility = booking.Facility.Name,
                            Cost = booking.MemId == 0 ?
                                        booking.Slots * booking.Facility.GuestCost
                                        : booking.Slots * booking.Facility.MemberCost
                        })
                        .Where(x => x.Cost > 30)
                        .Distinct()
                        .OrderByDescending(x => x.Cost)
                        .ToList();