بر این اساس، کلاسهای مدل دومین مساله به صورت زیر خواهند بود:
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; } }
اگر EF Code first را وادار به تهیه جداول و روابط معادل کلاسهای فوق کنیم:
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); } }
سابقا برای یافتن تعداد متناظر با هر IssueStatus خیلی سریع میشد چنین کوئری را نوشت:
اما اکنون معادل آن با EF Code first چیست؟
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]