مهاجرت از SQL به Linq
پنجشنبه 18 تیر 1394در این مقاله ابتدا توضیحاتی درباره فناوری Linq و سپس نحوه نوشتن Query های SQL در Linq با ذکر مثال ارائه می گردد .
Linq اختصار یافته Language Integrated Query می باشد . Linq یک فناوری Microsoft .NET است برای Query نوشتن و دریافت اطلاعات استفاده می شود . در حالی که SQL می تواند برای بروز رسانی یا حذف اطلاعات استفاده شود . در SQL ، برای جداول بانک اطلاعاتی و در Linq برای مجموعه NET. دستوراتی (Query) می نویسیم .
اکنون به نمونه ای که ارائه می شود توجه کنید . دو نوع شی (Object) داریم که شامل Products و Orders می باشند . یک Order با یک یا بیشتر Product در ارتباط است .
در #C کلاس ها بصورت زیر می باشند :
public class Product
{
public int Id {get; set;}
public string Name {get; set;}
public string Description {get; set;}
public decimal Price {get; set;}
public int StockLevel {get; set;}
}
public class Order
{
public int Id {get; set;}
public IList ProductIds {get; set;}
}
در SQL جداول را به این صورت تعریف می کنیم :
CREATE TABLE Products ( Id INT, Name VARCHAR(50), Description VARCHAR(2000), Price DECIMAL, StockLevel INT ) CREATE TABLE Orders ( Id INT, DatePlaced DATETIME ) CREATE TABLE OrderProducts ( Id INT, OrderId INT, ProductId INT ) /* primary and foreign keys would also be defined, of course */
توجه داشته باشید چگونه می توانیم یک ارتباط چند به چند (many-to-many) داشته باشیم ، می بایست 3 جدول در SQL و فقط 2 شی در #C تعریف کنیم.
#C
public static IList<Product> GetProducts()
{
var products = new List<Product>();
products.Add(new Product
{
Id = 1,
Name = "Sausages",
Description = "Succulent pork sausages, from the finest farms in the land",
Price = 4.99M,
StockLevel = 12
});
products.Add(new Product
{
Id = 2,
Name = "Bacon",
Description = "Delicious bacon, fry it or grill it!",
Price = 3.50M,
StockLevel = 5
});
products.Add(new Product
{
Id = 3,
Name = "Chicken Fillets",
Description = "Our chickens are treated well, and our fillets will treat you well",
Price = 8.99M,
StockLevel = 4
});
products.Add(new Product
{
Id = 4,
Name = "Fishcakes",
Description = "If you love fish and you love cakes then you'll love our fish cakes",
Price = 3.99M,
StockLevel = 22
});
products.Add(new Product
{
Id = 5,
Name = "Lamb Chops",
Description = "Lovely with mint sauce",
Price = 12.00M,
StockLevel = 0
});
return products;
}
public static IList<Orders> GetOrders()
{
var orders = new List<Order>();
orders.Add(new Order
{
Id = 1,
ProductIds = new List(){ 1, 4 }
});
orders.Add(new Order
{
Id = 2,
ProductIds = new List(){ 2, 3, 5 }
});
orders.Add(new Order
{
Id = 3,
ProductIds = new List(){ 3, 1 }
});
orders.Add(new Order
{
Id = 4,
ProductIds = new List(){ 4 }
});
orders.Add(new Order
{
Id = 5,
ProductIds = new List(){ 2, 4, 3, 1 }
});
return orders;
}
SQL
INSERT INTO Products(Id, Name, Description, Price, StockLevel) VALUES (1, 'Sausages', 'Succulent pork sausages, from the finest farms in the land', 4.99, 12) INSERT INTO Products(Id, Name, Description, Price, StockLevel) VALUES (2, 'Bacon', 'Delicious bacon, fry it or grill it!', 3.50, 5) INSERT INTO Products(Id, Name, Description, Price, StockLevel) VALUES (3, 'Chicken Fillets', 'Our chickens are treated well, _ and our fillets will treat you well', 8.99, 4) INSERT INTO Products(Id, Name, Description, Price, StockLevel) VALUES (4, 'Fishcakes', _ 'If you love fish and you love cakes then you''ll love our fish cakes', 3.99, 22) INSERT INTO Products(Id, Name, Description, Price, StockLevel) VALUES (5, 'Lamb Chops', 'Lovely with mint sauce', 12.00, 0) INSERT INTO Orders (Id, DatePlaced) VALUES (1, '1 January 2015') INSERT INTO Orders (Id, DatePlaced) VALUES (2, '1 February 2015') INSERT INTO Orders (Id, DatePlaced) VALUES (3, '12 February 2015') INSERT INTO Orders (Id, DatePlaced) VALUES (4, '29 March 2015') INSERT INTO Orders (Id, DatePlaced) VALUES (5, '1 July 2015') INSERT INTO OrderProducts(Id, OrderId, ProductId) VALUES (1, 1, 4) INSERT INTO OrderProducts(Id, OrderId, ProductId) VALUES (2, 1, 1) INSERT INTO OrderProducts(Id, OrderId, ProductId) VALUES (3, 2, 2) INSERT INTO OrderProducts(Id, OrderId, ProductId) VALUES (4, 2, 3) INSERT INTO OrderProducts(Id, OrderId, ProductId) VALUES (5, 2, 5) INSERT INTO OrderProducts(Id, OrderId, ProductId) VALUES (6, 3, 3) INSERT INTO OrderProducts(Id, OrderId, ProductId) VALUES (7, 3, 1) INSERT INTO OrderProducts(Id, OrderId, ProductId) VALUES (8, 4, 4) INSERT INTO OrderProducts(Id, OrderId, ProductId) VALUES (9, 5, 2) INSERT INTO OrderProducts(Id, OrderId, ProductId) VALUES (10, 5, 4) INSERT INTO OrderProducts(Id, OrderId, ProductId) VALUES (11, 5, 3) INSERT INTO OrderProducts(Id, OrderId, ProductId) VALUES (12, 5, 1)
اکنون به نحوه استفاده از یک مجموعه ProductIds بجای مجموعه Productsدر کلاس Order توجه کنید . همچنین اجازه می دهد قابلیت Join در Linq بازیابی گردد .
انتخاب همه چیز
SQL
SELECT * FROM Products
#C
var allProducts = from p in products
select p;
در Linq ، قسمت select بیشتر در آخر Query می آید .
انتخاب یک ستون خاص
SQL
SELECT Id, Name FROM Products
#C
var idAndNameOnly = from p in products
select new { p.Id, p.Name };
در اینجا برخی از تفاوت های ذاتی بین SQL و Linq ارائه می گردد . Linq با تصور شی گرا ایجاد شده است. در SQL ، می توانید بسادگی ستون مورد نظر را مشخص کنید ف بطوریکه اغلب نتیجه با یک dataset برابر است . به دلیل اینکه Linq بخشی از NET. که یک framework شی گرا است ، می باشد Query ها مجموعه از شی ها را باز می گردانند .بنابراین اگر همه مواردی که می خواهیم Product Ids و Name باشند ، مجبر به انجام کاری مانند استفاده از انواع ناشناس هستیم . در حقیقت ، استفاده از این روش در #C ناخوشایند است ، بجای بازگرداندن کامل شی product به هرکدام از خواص مورد نیاز دسترسی داشته باشید . همچنین اگر شی بازگردانده شده بزرگ و پیچیده باشد می تواند مشکلات اجرایی را نشان دهد، و فقط نیاز به دسترسی به یک تا دو مشخصه دارید .
شرط ( Where )
SQL
SELECT * FROM Products WHERE StockLevel < 5
#C
var lowStock = from p in products
where p.StockLevel < 5
select p;
مرتب سازی (Ordering)
SQL
SELECT * FROM Products ORDER BY Price DESC
#C
var byPrice = from p in products
orderby p.Price descending
select p;
توابع Aggregate
SQL
SELECT SUM(Price) FROM Products SELECT COUNT(*) FROM Products SELECT MIN(StockLevel) FROM Products
#C
var priceSum = products.Sum(p => p.Price); var productCount = products.Count(); var minStockLevel = products.Min(p => p.StockLevel);
با توجه به توابع Aggregate ، هیچ راهی برای دسترسی به نتیجه مورد نظر بدون استفاده از متد های extension وجود ندارد . به دلیل اینکه عبارات کاملا از دستورات که یک مجموعه را باز می گردانند ایجاد می شوند ، و از زمانی که توابع Aggregate تنها یک مقدار بازمی گردانند ، به یک متد extension نیاز داریم . در SQL ، همیشه Query ها یک dataset باز می گردانند . اگر در SQL یک Query مقداری واحد برگرداند در واقع یک dataset که شامل یک ستون و یک ردیف است بازگردانده است .
Join
SQL
SELECT * FROM Products p INNER JOIN OrderProducts op ON p.Id = op.ProductId INNER JOIN Orders o ON o.Id = op.OrderId
#C
var orderProducts = from o in orders
select new
{
Order = o,
Products = from p in products
join pid in o.ProductIds
on p.Id equals pid
select p
};
به دلیل ارتباط چند به چند بین Orders و Products می بایست یک Query داخلی به منظور دستیابی به همه فیلد های Products مربوط به یک Order ایجاد کنید . زمانی که از کلمه کلیدی Join در linq استفاده می کنید ، باید از عملگر Equals نیز استفاده کنید .
انتخاب مقادیر منحصر به فرد (متفاوت )
SQL
SELECT DISTINCT Price FROM Products
#C
var distinctPrices = (from p in products
select p.Price).Distinct();
اکنون می بایست مجددا از متد extension برای بازیابی مقادیر متفاوت استفاده کنید.
به دست آوردن n رکورد اول خروجی
SQL
SELECT TOP 3 * FROM Products ORDER BY Price DESC
#C
var top3Expensive = (from p in products
orderby p.Price descending
select p).Take(3);
