var searchModel = new Pupil
{
    FirstName = "Ben",
    LastName = null,
    City = null,
    PostalZipCode = null
};

List<Pupil> pupils = db.Pupils.Where(p =>
        (searchModel.FirstName == null || p.FirstName == searchModel.FirstName)
        && (searchModel.LastName == null || p.LastName == searchModel.LastName)
        && (searchModel.City == null || p.LastName == searchModel.City)
        && (searchModel.PostalZipCode == null || p.PostalZipCode == searchModel.PostalZipCode))
    .Take(100)
    .ToList()

 USE [EFSchoolSystem]
DECLARE @p__linq__0 NVarChar(4000) SET @p__linq__0 = 'Ben'
DECLARE @p__linq__1 NVarChar(4000) SET @p__linq__1 = 'Ben'
DECLARE @p__linq__2 NVarChar(4000) SET @p__linq__2 = ''
DECLARE @p__linq__3 NVarChar(4000) SET @p__linq__3 = ''
DECLARE @p__linq__4 NVarChar(4000) SET @p__linq__4 = ''
DECLARE @p__linq__5 NVarChar(4000) SET @p__linq__5 = ''
DECLARE @p__linq__6 NVarChar(4000) SET @p__linq__6 = ''
DECLARE @p__linq__7 NVarChar(4000) SET @p__linq__7 = ''

-- Executed query
SELECT TOP (100)
        [Extent1].[PupilId] AS [PupilId] ,
        [Extent1].[FirstName] AS [FirstName] ,
        [Extent1].[LastName] AS [LastName] ,
        [Extent1].[Address1] AS [Address1] ,
        [Extent1].[Adderss2] AS [Adderss2] ,
        [Extent1].[PostalZipCode] AS [PostalZipCode] ,
        [Extent1].[City] AS [City] ,
        [Extent1].[PhoneNumber] AS [PhoneNumber] ,
        [Extent1].[SchoolId] AS [SchoolId] ,
        [Extent1].[Picture] AS [Picture]
FROM    [dbo].[Pupils] AS [Extent1]
WHERE   (@p__linq__0 IS NULL OR [Extent1].[FirstName] = @p__linq__1)
        AND (@p__linq__2 IS NULL OR [Extent1].[LastName] = @p__linq__3)
        AND (@p__linq__4 IS NULL OR [Extent1].[LastName] = @p__linq__5)
        AND (@p__linq__6 IS NULL OR [Extent1].[PostalZipCode] = @p__linq__7)

 if (searchModel.City == null)
{
    pupils = db.Pupils.Where(p =>
        (searchModel.FirstName == null || p.FirstName == searchModel.FirstName)
        && (searchModel.LastName == null || p.LastName == searchModel.LastName)
        && (searchModel.PostalZipCode == null || p.PostalZipCode == searchModel.PostalZipCode))
        .Take(100)
        .ToList();
}
else
{
    pupils = db.Pupils.Where(p =>
        (searchModel.FirstName == null || p.FirstName == searchModel.FirstName)
        && (searchModel.LastName == null || p.LastName == searchModel.LastName)
        && (searchModel.City == null || p.LastName == searchModel.City)
        && (searchModel.PostalZipCode == null || p.PostalZipCode == searchModel.PostalZipCode))
        .Take(100)
        .ToList();
}

public class RecompileDbCommandInterceptor : IDbCommandInterceptor
{
    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        if(!command.CommandText.EndsWith(" option(recompile)"))
        {
            command.CommandText += " option(recompile)";
        }
    }
    //and implement other interface members
}

 var interceptor = new RecompileDbCommandInterceptor();
DbInterception.Add(interceptor);
var pupils = db.Pupils.Where(p => p.City = city).ToList();
DbInterception.Remove(interceptor);

var result = db.Pupils.AsQueryable();

if(searchModel.FirstName != null )
     result = result.Where(p => p.FirstName == searchModel.FirstName);

if(searchModel.LastName != null )
     result = result.Where(p => p.LastName == searchModel.LastName);

if(searchModel.PostalZipCode != null )
     result = result.Where(p => p.PostalZipCode == searchModel.PostalZipCode);

if(searchModel.City != null )
     result = result.Where(p => p.City == searchModel.City);

var schools = db.Schools
    .OrderBy(s => s.PostalZipCode)
    .Skip(model.Page * model.ResultsPerPage)
    .Take(model.ResultsPerPage)
    .ToList();

SELECT  text, query_plan
FROM    sys.dm_exec_cached_plans
        CROSS APPLY sys.dm_exec_query_plan(plan_handle)
        CROSS APPLY sys.dm_exec_sql_text(plan_handle)

int resultsToSkip = model.Page * model.ResultsPerPage;
var schools = db.Schools
    .OrderBy(s => s.PostalZipCode)
    .Skip(() => resultsToSkip) //must pre-calculate this value
    .Take(() => model.ResultsPerPage)
    .ToList();

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

        public virtual ICollection<ProjectIssue> ProjectIssues { set; get; }
    }

    public class ProjectIssue
    {
        public int Id { set; get; }
        public string Body { set; get; }

        [ForeignKey("ProjectStatusId")]
        public virtual ProjectIssueStatus ProjectIssueStatus { set; get; }
        public int ProjectStatusId { set; get; }

        [ForeignKey("ProjectId")]
        public virtual Project Project { set; get; }
        public int ProjectId { set; get; }
    }

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

        public virtual ICollection<ProjectIssue> ProjectIssues { set; get; }
    }

    public class MyContext : DbContext
    {
        public DbSet<ProjectIssueStatus> ProjectStatus { get; set; }
        public DbSet<ProjectIssue> ProjectIssues { get; set; }
        public DbSet<Project> Projects { get; set; }
    }

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

        protected override void Seed(MyContext context)
        {
            var project1 = new Project { Name = "پروژه جدید" };
            context.Projects.Add(project1);

            var stat1 = new ProjectIssueStatus { Name = "درحال انجام" };
            var stat2 = new ProjectIssueStatus { Name = "انجام شد" };
            context.ProjectStatus.Add(stat1);
            context.ProjectStatus.Add(stat2);

            var issue1 = new ProjectIssue
            {
                Body = "تغییر قلم گزارش",
                ProjectIssueStatus = stat1,
                Project = project1
            };
            var issue2 = new ProjectIssue
            {
                Body = "تغییر لوگوی گزارش",
                ProjectIssueStatus = stat1,
                Project = project1
            };
            context.ProjectIssues.Add(issue1);
            context.ProjectIssues.Add(issue2);

            base.Seed(context);
        }
    }

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

            using (var ctx = new MyContext())
            {
                var projectId = 1;
                var list = ctx.ProjectStatus.Select(x => new
                                                    {
                                                        Id = x.Id,
                                                        Name = x.Name,
                                                        Count = x.ProjectIssues.Count(p => p.ProjectId == projectId)
                                                    }).ToList();
                foreach (var item in list)
                    Console.WriteLine("{0}:{1}",item.Name, item.Count);                
            }
        }
    }

SELECT [Project1].[Id] AS [Id],
       [Project1].[Name] AS [Name],
       [Project1].[C1] AS [C1]
FROM   (
           SELECT [Extent1].[Id] AS [Id],
                  [Extent1].[Name] AS [Name],
                  (
                      SELECT COUNT(1) AS [A1]
                      FROM   [dbo].[ProjectIssues] AS [Extent2]
                      WHERE  ([Extent1].[Id] = [Extent2].[ProjectStatusId])
                             AND ([Extent2].[ProjectId] = 1 /*@p__linq__0*/)
                  ) AS [C1]
           FROM   [dbo].[ProjectIssueStatus] AS [Extent1]
       ) AS [Project1]

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)
            {
            }
        }
    }

اما در مدل EAV، خواص داینامیک را به درون جدول دومی منتقل خواهیم کرد:

create table EmployeeEav
(
   Id int auto_increment
   primary key
);

create table EmployeeAttributes
(
  Id int auto_increment
  primary key,
  EmployeeId int not null,
  AttributeName text null,
  AttributeValue text null,
  constraint FK_EmployeeAttributes_EmployeeEav_EmployeeId
  foreign key (EmployeeId) references EmployeeEav (Id)
  on delete cascade
);

create index IX_EmployeeAttributes_EmployeeId
on EmployeeAttributes (EmployeeId);

تعریف جداول فوق نیز در Entity Framework به اینصورت خواهند بود:

public class EmployeeEav
{
    public int Id { get; set; }
    public virtual ICollection<EmployeeAttribute> Attributes { get; set; }
}

public class EmployeeAttribute
{
    public int Id { get; set; }
    public virtual EmployeeEav Employee { get; set; }
    public int EmployeeId { get; set; }
    public string AttributeName { get; set; }
    public string AttributeValue { get; set; }
}

public class MyDbContext : DbContext
{

    public DbSet<EmployeeEav> EmployeeEav { get; set; }
    public DbSet<EmployeeAttribute> EmployeeAttributes { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.UseMySQL(_configuration.GetConnectionString("DataConnection"));
    }
}

SELECT `e`.`Id`, `e`.`DateOfBirth`, `e`.`FirstName`, `e`.`LastName`
FROM `Employees` AS `e`
WHERE `e`.`DateOfBirth` > @__endDate_0