use dbName;
SELECT TOP(100) qt.text AS 'SP Name',
     qs.execution_count AS 'Execution Count',
     qs.execution_count / DATEDIFF(Second, qs.creation_time, GETDATE()) AS
     'Calls/Second',
     qs.total_worker_time / qs.execution_count AS 'AvgWorkerTime',
     qs.total_worker_time AS 'TotalWorkerTime',
     qs.total_elapsed_time / qs.execution_count AS 'AvgElapsedTime',
     qs.max_logical_reads,
     qs.max_logical_writes,
     qs.total_physical_reads,
     DATEDIFF(Minute, qs.creation_time, GETDATE()) AS 'Age in Cache'
FROM   sys.dm_exec_query_stats AS qs
     CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE  qt.dbid = DB_ID() -- Filter by current database
ORDER BY
     qs.execution_count DESC

select top 50
  sql_text.text,
  sql_handle,
  plan_generation_num,
  execution_count,
  dbid,
  objectid
from
  sys.dm_exec_query_stats a
  cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where
  plan_generation_num >1
order by plan_generation_num desc

SELECT NAME,
     create_date,
     modify_date
FROM   sys.objects
WHERE  TYPE = 'P'
ORDER BY
     Modify_Date DESC,
     NAME

System.InvalidOperationException: 'Lambda expression used inside Include is not valid.'

SELECT [b].[Id], [b].[Name], [t].[Id], [t].[BlogId], [t].[Description], [t].[Title]
FROM [Blogs] AS [b]
LEFT JOIN (
    SELECT [p].[Id], [p].[BlogId], [p].[Description], [p].[Title]
    FROM [Posts] AS [p]
    WHERE CHARINDEX(N'test title', [p].[Title]) > 0
) AS [t] ON [b].[Id] = [t].[BlogId]
ORDER BY [b].[Id], [t].[Id]

-- EF+ Query Future: 1 of 2
SELECT [b].[Id], [b].[Name]
FROM [Blogs] AS [b]
;

-- EF+ Query Future: 2 of 2
SELECT [t].[Id], [t].[BlogId], [t].[Description], [t].[Title]
FROM [Blogs] AS [b]
INNER JOIN (
    SELECT [p].[Id], [p].[BlogId], [p].[Description], [p].[Title]
    FROM [Posts] AS [p]
    WHERE CHARINDEX(N'test title', [p].[Title]) > 0
) AS [t] ON [b].[Id] = [t].[BlogId]
;

• همانطور که می‌بینید این دستور، 2 کوئری را اجرا میکند. سرعت آن از روش قبلی کمی کند‌تر است و memory allocation بیشتری را انجام میدهد.
• در صورت عدم استفاده از AsNoTracking، اشیاء Blog را نیز ثبت میکند؛ درنتیجه تعداد 101 آبجکت (100 Blog و 1 Post) به ChangeTracker اضافه خواهند شد.
• کد نویسی تمیزتر و راحت‌تری در سمت سی شارپ دارد. 
• این روش در EF6 نیز قابل استفاده است.

using System;
using Microsoft.Extensions.Logging;

namespace CoreIocServices
{
    public interface ITestService
    {
        void Run();
    }

    public class TestService : ITestService
    {
        private readonly ILogger<TestService> _logger;

        public TestService(ILogger<TestService> logger)
        {
            _logger = logger ?? throw new ArgumentNullException(nameof(logger));
        }

        public void Run()
        {
            _logger.LogWarning("A Warning from the TestService!");
        }
    }
}

public string Roles { get; set; } // comma-separated list of role names
public string Users { get; set; } // comma-separated list of usernames

[Authorize(Roles="Admins")]
public class AdminController : Controller
{
    [Authorize(Users="Vahid")]
    public ActionResult DoSomethingSecure()
    {
    }
}

<authentication mode="Forms">
      <forms loginUrl="~/Account/LogOn" timeout="2880" />
</authentication>

<authorization>
     <deny users="?" />
</authorization>

<!-- we don't want to stop anyone seeing the css and images -->
<location path="Content">
    <system.web>
      <authorization>
        <allow users="*" />
      </authorization>
    </system.web>
</location>

<location path="secure">
  <system.web>
    <authorization>
      <allow roles="Administrators" />
      <deny users="*" />
    </authorization>
  </system.web>
</location>

[System.Web.Mvc.AllowAnonymous]
public ActionResult Login()
{
    return View();
}

public class LogonAuthorize : AuthorizeAttribute {
     public override void OnAuthorization(AuthorizationContext filterContext) {
         if (!(filterContext.Controller is AccountController))
             base.OnAuthorization(filterContext);
     }
}

using System.Web.Mvc;

namespace MvcApplication15.Controllers
{
    public class HomeController : Controller
    {
        [Authorize]
        public ActionResult Index()
        {
            return View();
        }
    }
}

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>
Current user: @User.Identity.Name

<authentication mode="Windows" />

<authentication mode="Forms">
      <!--one month ticket-->
      <forms name=".403MyApp" 
             cookieless="UseCookies" 
             loginUrl="~/Account/LogOn" 
             defaultUrl="~/Home" 
             slidingExpiration="true" 
             protection="All" 
             path="/" 
             timeout="43200"/>
</authentication>

using System.ComponentModel.DataAnnotations;

namespace MvcApplication15.Models
{
    public class Account
    {
        [Required(ErrorMessage = "Username is required to login.")]
        [StringLength(20)]
        public string Username { get; set; }

        [Required(ErrorMessage = "Password is required to login.")]
        [DataType(DataType.Password)]
        public string Password { get; set; }

        public bool RememberMe { get; set; }
    }
}

using System.Web.Mvc;
using MvcApplication15.Models;

namespace MvcApplication15.Controllers
{
    public class AccountController : Controller
    {
        [HttpGet]
        public ActionResult LogOn()
        {
            return View();
        }

        [HttpPost]
        public ActionResult LogOn(Account loginInfo, string returnUrl)
        {
            return View();
        }
    }
}

http://localhost/Account/LogOn?ReturnUrl=something

using System.Web.Mvc;
using System.Web.Security;
using MvcApplication15.Models;

namespace MvcApplication15.Controllers
{
    public class AccountController : Controller
    {
        [HttpGet]
        public ActionResult LogOn(string returnUrl)
        {
            if (User.Identity.IsAuthenticated) //remember me
            {
                if (shouldRedirect(returnUrl))
                {
                    return Redirect(returnUrl);
                }
                return Redirect(FormsAuthentication.DefaultUrl);
            }

            return View(); // show the login page
        }

        [HttpGet]
        public void LogOut()
        {
            FormsAuthentication.SignOut();
        }

        private bool shouldRedirect(string returnUrl)
        {
            // it's a security check
            return !string.IsNullOrWhiteSpace(returnUrl) &&
                                Url.IsLocalUrl(returnUrl) &&
                                returnUrl.Length > 1 &&
                                returnUrl.StartsWith("/") &&
                                !returnUrl.StartsWith("//") &&
                                !returnUrl.StartsWith("/\\");
        }

        [HttpPost]
        public ActionResult LogOn(Account loginInfo, string returnUrl)
        {
            if (this.ModelState.IsValid)
            {
                if (loginInfo.Username == "Vahid" && loginInfo.Password == "123")
                {
                    FormsAuthentication.SetAuthCookie(loginInfo.Username, loginInfo.RememberMe);
                    if (shouldRedirect(returnUrl))
                    {
                        return Redirect(returnUrl);
                    }
                    FormsAuthentication.RedirectFromLoginPage(loginInfo.Username, loginInfo.RememberMe);
                }
            }
            this.ModelState.AddModelError("", "The user name or password provided is incorrect.");
            ViewBag.Error = "Login faild! Make sure you have entered the right user name and password!";
            return View(loginInfo);
        }
    }
}

[Authorize(Users="Vahid")]

using System;
using System.Web.Security;

namespace MvcApplication15.Helper
{
    public class CustomRoleProvider : RoleProvider
    {
        public override bool IsUserInRole(string username, string roleName)
        {
            if (username.ToLowerInvariant() == "ali" && roleName.ToLowerInvariant() == "User")
                return true;
            // blabla ...
            return false;
        }

        public override string[] GetRolesForUser(string username)
        {
            if (username.ToLowerInvariant() == "ali")
            {
                return new[] { "User", "Helpdesk" };
            }

            if(username.ToLowerInvariant()=="vahid")
            {
                return new [] { "Admin" };
            }

            return new string[] { };
        }

        public override void AddUsersToRoles(string[] usernames, string[] roleNames)
        {
            throw new NotImplementedException();
        }

        public override string ApplicationName
        {
            get
            {
                throw new NotImplementedException();
            }
            set
            {
                throw new NotImplementedException();
            }
        }

        public override void CreateRole(string roleName)
        {
            throw new NotImplementedException();
        }

        public override bool DeleteRole(string roleName, bool throwOnPopulatedRole)
        {
            throw new NotImplementedException();
        }

        public override string[] FindUsersInRole(string roleName, string usernameToMatch)
        {
            throw new NotImplementedException();
        }

        public override string[] GetAllRoles()
        {
            throw new NotImplementedException();
        }        

        public override string[] GetUsersInRole(string roleName)
        {
            throw new NotImplementedException();
        }        

        public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
        {
            throw new NotImplementedException();
        }

        public override bool RoleExists(string roleName)
        {
            throw new NotImplementedException();
        }
    }
}

<roleManager>
      <providers>
        <clear />
        <add name="CustomRoleProvider" type="MvcApplication15.Helper.CustomRoleProvider"/>
      </providers>
    </roleManager>

[Authorize(Roles = "Admin")]
public class HomeController : Controller

HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.Initialize();

using System.Collections.Generic;

namespace EF_Sample06.Models
{
    public class Department
    {
        public int DepartmentId { get; set; }
        public string Name { get; set; }

        //Creates Employee navigation property for Lazy Loading (1:many)
        public virtual ICollection<Employee> Employees { get; set; }
    }
}

namespace EF_Sample06.Models
{
    public class Employee
    {
        public int EmployeeId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }        

        //Creates Department navigation property for Lazy Loading
        public virtual Department Department { get; set; }
    }
}

using System.Data.Entity;
using EF_Sample06.Models;

namespace EF_Sample06.DataLayer
{
    public class Sample06Context : DbContext
    {
        public DbSet<Department> Departments { set; get; }
        public DbSet<Employee> Employees { set; get; }
    }
}

using System.Collections.Generic;
using System.Data.Entity.Migrations;
using EF_Sample06.Models;

namespace EF_Sample06.DataLayer
{
    public class Configuration : DbMigrationsConfiguration<Sample06Context>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = true;
            AutomaticMigrationDataLossAllowed = true;
        }

        protected override void Seed(Sample06Context context)
        {
            var employee1 = new Employee { FirstName = "f name1", LastName = "l name1" };
            var employee2 = new Employee { FirstName = "f name2", LastName = "l name2" };
            var employee3 = new Employee { FirstName = "f name3", LastName = "l name3" };
            var employee4 = new Employee { FirstName = "f name4", LastName = "l name4" };

            var dept1 = new Department { Name = "dept 1", Employees = new List<Employee> { employee1, employee2 } };
            var dept2 = new Department { Name = "dept 2", Employees = new List<Employee> { employee3 } };
            var dept3 = new Department { Name = "dept 3", Employees = new List<Employee> { employee4 } };

            context.Departments.Add(dept1);
            context.Departments.Add(dept2);
            context.Departments.Add(dept3);
            base.Seed(context);
        }
    }
}

void ExportMappings(DbContext context, string edmxFile)
{
     var settings = new XmlWriterSettings { Indent = true };
     using (XmlWriter writer = XmlWriter.Create(edmxFile, settings))
     {
         System.Data.Entity.Infrastructure.EdmxWriter.WriteEdmx(context, writer);
     }
}

using (var db = new Sample06Context())
{
     ExportMappings(db, "mappings.edmx");
}

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using EF_Sample06.DataLayer;
using EF_Sample06.Models;

namespace EF_Sample06
{
    class Program
    {
        static IList<Employee> FindEmployees(string fName, string lName, bool byName, bool byLName)
        { 
            using (var db = new Sample06Context())
            {
                IQueryable<Employee> query = db.Employees.AsQueryable();

                if (byLName)
                {
                    query = query.Where(x => x.LastName == lName);
                }

                if (byName)
                {
                    query = query.Where(x => x.FirstName == fName);
                }

                return query.ToList();
            }
        }

        static void Main(string[] args)
        {
            // note: remove this line if you received : create database is not supported by this provider.
            HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.Initialize();

            Database.SetInitializer(new MigrateDatabaseToLatestVersion<Sample06Context, Configuration>());

            var list = FindEmployees("f name1", "l name1", true, true);
            foreach (var item in list)
            {
                Console.WriteLine(item.FirstName);
            }
        }
    }
}

var list = FindEmployees("f name1", "l name1", true, true);

SELECT [Extent1].[EmployeeId]              AS [EmployeeId],
       [Extent1].[FirstName]               AS [FirstName],
       [Extent1].[LastName]                AS [LastName],
       [Extent1].[Department_DepartmentId] AS [Department_DepartmentId]
FROM   [dbo].[Employees] AS [Extent1]
WHERE  ([Extent1].[LastName] = 'l name1' /* @p__linq__0 */)
       AND ([Extent1].[FirstName] = 'f name1' /* @p__linq__1 */)

using (var db = new Sample06Context())
{
    var dept1 = db.Departments.Find(1);
    if (dept1 != null)
    {
        Console.WriteLine(dept1.Name);
        foreach (var item in dept1.Employees)
        {
             Console.WriteLine(item.FirstName);
        }
    }
}

SELECT [Limit1].[DepartmentId] AS [DepartmentId],
       [Limit1].[Name]         AS [Name]
FROM   (SELECT TOP (2) [Extent1].[DepartmentId] AS [DepartmentId],
                       [Extent1].[Name]         AS [Name]
        FROM   [dbo].[Departments] AS [Extent1]
        WHERE  [Extent1].[DepartmentId] = 1 /* @p0 */) AS [Limit1]


SELECT [Extent1].[EmployeeId]              AS [EmployeeId],
       [Extent1].[FirstName]               AS [FirstName],
       [Extent1].[LastName]                AS [LastName],
       [Extent1].[Department_DepartmentId] AS [Department_DepartmentId]
FROM   [dbo].[Employees] AS [Extent1]
WHERE  ([Extent1].[Department_DepartmentId] IS NOT NULL)
       AND ([Extent1].[Department_DepartmentId] = 1 /* @EntityKeyValue1 */)

using (var db = new Sample06Context())
{
      foreach (var dept in db.Departments)
      {
           Console.WriteLine(dept.Name);
           foreach (var item in dept.Employees)
           {
                Console.WriteLine(item.FirstName);
            }
        }
}

There is already an open DataReader associated with this Command which must be closed first

<connectionStrings>
    <clear/>
    <add
       name="Sample06Context"
       connectionString="Data Source=(local);Initial Catalog=testdb2012;Integrated Security = true;MultipleActiveResultSets=True;"
       providerName="System.Data.SqlClient"
      />
</connectionStrings>

using (var db = new Sample06Context())
{
      foreach (var dept in db.Departments.Include(x => x.Employees))
      {
           Console.WriteLine(dept.Name);
           foreach (var item in dept.Employees)
           {
              Console.WriteLine(item.FirstName);
           }
       }
}

SELECT [Project1].[DepartmentId]            AS [DepartmentId],
       [Project1].[Name]                    AS [Name],
       [Project1].[C1]                      AS [C1],
       [Project1].[EmployeeId]              AS [EmployeeId],
       [Project1].[FirstName]               AS [FirstName],
       [Project1].[LastName]                AS [LastName],
       [Project1].[Department_DepartmentId] AS [Department_DepartmentId]
FROM   (SELECT [Extent1].[DepartmentId]            AS [DepartmentId],
               [Extent1].[Name]                    AS [Name],
               [Extent2].[EmployeeId]              AS [EmployeeId],
               [Extent2].[FirstName]               AS [FirstName],
               [Extent2].[LastName]                AS [LastName],
               [Extent2].[Department_DepartmentId] AS [Department_DepartmentId],
               CASE
                 WHEN ([Extent2].[EmployeeId] IS NULL) THEN CAST(NULL AS int)
                 ELSE 1
               END                                 AS [C1]
        FROM   [dbo].[Departments] AS [Extent1]
               LEFT OUTER JOIN [dbo].[Employees] AS [Extent2]
                 ON [Extent1].[DepartmentId] = [Extent2].[Department_DepartmentId]) AS [Project1]
ORDER  BY [Project1].[DepartmentId] ASC,
          [Project1].[C1] ASC

public class Sample06Context : DbContext
{
        public Sample06Context()
        {
            this.Configuration.LazyLoadingEnabled = false;
        }

public class Department
{
     public int DepartmentId { get; set; }
     public string Name { get; set; }

     public  ICollection<Employee> Employees { get; set; }
     public Department()
     {
         Employees = new HashSet<Employee>();
     }
}