Introduction
LINQ is one of the most important features in .NET Framework 3.5 (Visual Studio 2008). It's the new way to mapping database tables to classes, and as we know, we call this O/R Mapping. An article on how to write LINQ code quickly is always welcome for beginners, and I think that reading samples is the best way to learn a new technique.
These are samples created while I was learning and using LINQ, and I want to share them now. Hope they will be helpful. I will use Northwind database as a sample, which you can download from the link at the top of this article.
I recommend that you read 101 LINQ Samples if you would like to learn more.
Samples
// Basic
// Select * From Products
var query1 = from p in db.Products
select p;
// Select ProductID, ProductName, UnitPrice From Products
var query2 = from p in db.Products
select new {
p.ProductID,
p.ProductName,
p.UnitPrice
};
Note: query2 will create a new class which contains three properties that map the ProductId, ProductName, and UnitPrice.
// Where
// Select * From Products Where ProductID = 1
var query3 = from p in db.Products
where p.ProductID == 1
select p;
// Select * From Products Where SupplierId =5 and UnitPrice > 20
var query4 = from p in db.Products
where p.SupplierID == 5 && p.UnitPrice > 20
select p;
// Select * From Products Where SupplierId =5 Or SupplierId=6
var query5 = from p in db.Products
where p.SupplierID == 5 || p.SupplierID == 6
select p;
Note: The condition in the where block is a logical express, a boolean value is returned just like in if().
// Order By
// Select * From Products Order By ProductId
var query6 = from p in db.Products
orderby p.ProductID
select p;
// Select * From Products Order By ProductId Desc
var query7 = from p in db.Products
orderby p.ProductID descending
select p;
// Select * From Products Order By CategoryId, UnitPrice Desc
var query8 = from p in db.Products
orderby p.CategoryID, p.UnitPrice descending
select p;
Note: The default order is ascending, the order by p.ProductID is same as order by p.ProductID ascending, just like in T-SQL.
// Top
// Select Top 10 * From Products
var query9 = (from p in db.Products
select p).Take(10);
// Select Top 1 * From Products
var query10 = (from p in db.Products
select p).Take(1);
// or
var query11 = (from p in db.Products
select p).First();
Note: If it just returns one record, I recommend using First instead of Take(1).
// Top with Order By
// Select Top 10 * From Products Order By ProductId
var query12 = (from p in db.Products
orderby p.ProductID
select p).Take(10);
// Distinct
// Select Distinct CategoryId From Products
var query13 = (from p in db.Products
select p.CategoryID).Distinct();
// Group By
// Select CategoryId, Count(CategoryID) As NewField
// From Products Group By CategoryId
var query14 = from p in db.Products
group p by p.CategoryID into g
select new {
CategoryId = g.Key,
NewField = g.Count()
};
// Select CategoryId, Avg(UnitPrice) As NewField From Products Group By CategoryId
var query15 = from p in db.Products
group p by p.CategoryID into g
select new {
CategoryId = g.Key,
NewField = g.Average(k => k.UnitPrice)
};
// Select CategoryId, Sum(UnitPrice) As NewField From Products Group By CategoryId
var query16 = from p in db.Products
group p by p.CategoryID into g
select new {
CategoryId = g.Key,
NewField = g.Sum(k => k.UnitPrice )
};
// Union
// Select * From Products Where CategoryId =1 union Select *
// From Products Where CategoryId = 2
var query17 = (from p in db.Products
where p.CategoryID == 1
select p).Union(
from m in db.Products
where m.CategoryID == 2
select m
);
// Two tables
// Select A.ProductId, A.ProductName, B.CategoryId, B.CategoryName
// From Products A, Categories B
// Where A.CategoryID = B.CategoryID and A.SupplierId =1
var query18 = from p in db.Products
from m in db.Categories
where p.CategoryID == m.CategoryID && p.SupplierID == 1
select new {
p.ProductID,
p.ProductName,
m.CategoryID,
m.CategoryName
};
History
- Ver 1.0 - 2008-03-18: Article created
