DBdan biror modelga mos bitta ob’ektni aniqlash uchun Find() metodidan foydalanishimiz mumkin. Ushbu metod Linq metodi hisoblanmaydi. U DbSet klassida aniqlangan:
// id=3 bo’lgan elementni aniqlash
Phone myphone = db.Phones.Find(3);
Console.WriteLine("{0}.{1} - {2}", myphone.Id, myphone.Name, myphone.Price);
Ammo alternativ sifatida Linq First()/FirstOrDefault() metodlaridan foydalanishimiz mumkin. Ular shart asosida aniqlangan elementlar ro'yxatidan birinchi elementi qaytaradi.
FirstOrDefault() metodini ishlatish qulay bo’lib, agar natija bo’sh to’plam bo’lgan holda u null qiymatni qaytaradi. First() metodi esa ushbu holda xatolikni qaytaradi. Buning uchun quyidagi koddan foydalanish zarur:
Phone myphone = db.Phones.FirstOrDefault(p => p.Id == 3);
if (myphone != null)
Console.WriteLine(myphone.Name);
Endi proeksiyani amalga oshiramiz. Biz natijaga kompaniya nomini qo’shishimiz lozim bo’lsin. U holda Include metodidan foydalanib, ob’ektni boshqa jadvaldagi ob’ekt bilan bog‘lash mumkin:
var phones = db.Phones.Include(p => p.Company);
foreach (Phone p in newphones)
Console.WriteLine("{0}.{1} - {2}", p.Id, p.Company.Name, p.Price);
Ammo ko’p hollarda tanlanadigan ob’ektning barcha xususiyatlari zarur emas. Ushbu holda Select metodi orqali olinayotgan ma’lumotlardan yangi tip hosil qilish mumkin:
using (PhoneContext db = new PhoneContext())
{
var phones = db.Phones.Select(p => new
{
Name = p.Name,
Price = p.Price,
Company = p.Company.Name
});
foreach (var p in phones)
Console.WriteLine("{0} ({1}) - {2}", p.Name, p.Company, p.Price);
}
Natijada Select metodi olingan ma’lumotlardan yangi tipni shakllantiradi. Ushbu holda yangi anonim tip hosil qilinayotgan bo’lib, uning o’rniga foydalanuvchi hosil qilgan tipdan foydalanish ham mumkin. Masalan,
bizda Model klassi aniqlangan bo’lsin:
class Model
{
public string Name { get; set; }
public string Company { get; set; }
public int Price { get; set; }
}
Biz yuqoridagi misoldagi so’rov natijasini ushbu tipga mos ob’ekt sifatida shakllantiramiz:
using (PhoneContext db = new PhoneContext())
{
var phones = db.Phones.Select(p => new Model
{
Name = p.Name,
Price = p.Price,
Company = p.Company.Name
});
foreach (Model p in phones)
Console.WriteLine("{0} ({1}) - {2}", p.Name, p.Company, p.Price);
}
Tartiblash
DBdan olingan ma’lumotlarni o'sish tartibida tartiblash uchun, OrderBy metodi yoki orderby operatori xizmat qiladi. Name xususiyati bo’yicha tartiblashni ko’rib chiqamiz:
using (PhoneContext db = new PhoneContext())
{
var phones = db.Phones.OrderBy(p => p.Name);
foreach (Phone p in phones)
Console.WriteLine("{0}.{1} - {2}", p.Id, p.Name, p.Price);
}
Natijada Entity Framework quyidagi SQL ifodani generatsiya qiladi:
SELECT [Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name],
[Extent1].[Price] AS [Price],
[Extent1].[CompanyId] AS [CompanyId]
FROM [dbo].[Phones] AS [Extent1]
ORDER BY [Extent1].[Name] ASC
OrderBy metodiga alternativ tarzda orderby operatoridan foydalanish mumkin:
using (PhoneContext db = new PhoneContext())
{
var phones = from p in db.Phones
orderby p.Name
select p;
foreach (Phone p in phones)
Console.WriteLine("{0}.{1} - {2}", p.Id, p.Name, p.Price);
}
Teskari tartibda tartiblash uchun OrderByDescending() metodidan foydalanish zarur:
using (PhoneContext db = new PhoneContext())
{
var phones = db.Phones.OrderByDescending(p => p.Name);
foreach (Phone p in phones)
Console.WriteLine("{0}.{1} - {2}", p.Id, p.Name, p.Price);
}
Agar bizga ma’lumotlarni bir qancha parametrlar bo’yicha tartiblash lozim bo’lsa, ThenBy() va ThenByDescending() metodlaridan foydalanish lozim. So’rovda ikkita xususiyat bo’yicha tartiblash uchun quyidagi koddan foydalanish mumkin:
var tphones = db.Phones.Select(p => new { Id=p.Id, Name = p.Name, Company = p.Company.Name, Price = p.Price }).OrderBy(p => p.Price).ThenBy(p => p.Company);
foreach (var k in tphones)
Console.WriteLine("{0}.{1} - {2}", k.Id, k.Name, k.Price);
Jadval hosil qilish
Muayyan shartlar asosida jadvallarni birlashtirish uchun Join metodi ishlatiladi. Bizning misolda telefonlar va kompaniyalar jadvallari umumiy kompaniya Id siga ega. Shuning uchun ushbu jadvallarni birlashtirish mumkin:
using (PhoneContext db = new PhoneContext())
{
var phones = db.Phones.Join(db.Companies, // ikkinchi to’plam
p => p.CompanyId, // birinchi to’plamdagi xususiyat
c => c.Id, // ikkinchi to’plamdagi xususiyat
(p, c) => new // natija
{
Name = p.Name,
Company = c.Name,
Price = p.Price
});
foreach (var p in phones)
Console.WriteLine("{0} ({1}) - {2}", p.Name, p.Company, p.Price);
}
Join metodi to’rtta parametr qabul qiladi:
• Joriy jadval bilan bog‘lash lozim bo’lgan ikkinchi jadval;
• ob’ekta xususiyati – birinchi jadvaldagi ustun. Ushbu ustun orqali ulanish amalga oshiriladi;
• ob’ekt xususiyati - ikkinchi jadvaldagi ustun. Ushbu ustun orqali ulanish amalga oshiriladi;
• bog‘lanish natijasida hosil qilinadigan yangi ob’ekt.
Natijada quyidagi SQL ifoda generatsiya qilinadi:
SELECT [Extent1].[Price] AS [Price],
[Extent1].[Name] AS [Name],
[Extent2].[Name] AS [Name1]
FROM [dbo].[Phones] AS [Extent1]
INNER JOIN [dbo].[Companies] AS [Extent2]
ON [Extent1].[CompanyId] = [Extent2].[Id]
Yuqoridagi natijaga join operatorini qo’llash orqali erishish mumkin:
using (PhoneContext db = new PhoneContext())
{
var phones = from p in db.Phones
join c in db.Companies on p.CompanyId equals c.Id
select new { Name = p.Name, Company = c.Name, Price = p.Price };
foreach (var p in phones)
Console.WriteLine("{0} ({1}) - {2}", p.Name, p.Company, p.Price);
}
Guruhlash
Ma’lumotlarni biror parametr bo’yicha guruhlash uchun group by operatori yoki GroupBy() metodidan foydalanish zarur. Telefon modellarini ishlab chiqargan tashkilot bo’yicha guruhlashtiramiz:
using (PhoneContext db = new PhoneContext())
{
var groups = from p in db.Phones
group p by p.Company.Name;
foreach (var g in groups)
{
Console.WriteLine(g.Key);
foreach (var p in g)
Console.WriteLine("{0} - {1}", p.Name, p.Price);
Console.WriteLine();
}
}
Ushbu holda birlashtirish sharti sifatida kompaniya nomi ishlatildi. Ya’ni bog‘lovchi Companies jadvalidagi Name ustuni. Guruhlashtirish sharti sifatida kelgan ustun kalit hisoblanadi. Ushbu kalitga biz guruhdagi Key xususiyati orqali ega bo’lishimiz mumkin.
Natijada biz bir qancha guruhlarga ega bo’lamiz. Ushbu guruhlar o’zida bir qancha elementlarni saqlashi mumkin. Bizning misolda quyidagi natija hosil qilinadi:
|
