برای مثال دو جدول شهرها و افراد را درنظر بگیرید. مقصود از تعریف جدول شهرها در اینجا، مشخص سازی محل تولد افراد است:
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); }
خروجی SQL کوئری فوق به نحو ذیل است:
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]