最近学习LINQ资料不是很多,从CSHARPSAMPLE写一比较有用的例子方便大家学习。
1.INSERT/UPDATE/DELETE
insert Simple
public void LinqToSqlInsert01() {
var q =
from c in db.Customers
where c.Region == "WA"
select c;
Console.WriteLine("*** BEFORE ***");
ObjectDumper.Write(q);
Console.WriteLine();
Console.WriteLine("*** INSERT ***");
var newCustomer = new Customer { CustomerID = "MCSFT",
CompanyName = "Microsoft",
ContactName = "John Doe",
ContactTitle = "Sales Manager",
Address = "1 Microsoft Way",
City = "Redmond",
Region = "WA",
PostalCode = "98052",
Country = "USA",
Phone = "(425) 555-1234",
Fax = null
};
db.Customers.InsertOnSubmit(newCustomer);
db.SubmitChanges();
Console.WriteLine();
Console.WriteLine("*** AFTER ***");
ObjectDumper.Write(q);
Cleanup64(); // Restore previous database state
}
public void LinqToSqlInsert01() {
var q =
from c in db.Customers
where c.Region == "WA"
select c;
Console.WriteLine("*** BEFORE ***");
ObjectDumper.Write(q);
Console.WriteLine();
Console.WriteLine("*** INSERT ***");
var newCustomer = new Customer { CustomerID = "MCSFT",
CompanyName = "Microsoft",
ContactName = "John Doe",
ContactTitle = "Sales Manager",
Address = "1 Microsoft Way",
City = "Redmond",
Region = "WA",
PostalCode = "98052",
Country = "USA",
Phone = "(425) 555-1234",
Fax = null
};
db.Customers.InsertOnSubmit(newCustomer);
db.SubmitChanges();
Console.WriteLine();
Console.WriteLine("*** AFTER ***");
ObjectDumper.Write(q);
Cleanup64(); // Restore previous database state
}
insert one to Many
public void LinqToSqlInsert02() {
Northwind db2 = new Northwind(connString);
DataLoadOptions ds = new DataLoadOptions();
ds.LoadWith<nwind.Category>(p => p.Products);
db2.LoadOptions = ds;
var q = (
from c in db2.Categories
where c.CategoryName == "Widgets"
select c);
Console.WriteLine("*** BEFORE ***");
ObjectDumper.Write(q, 1);
Console.WriteLine();
Console.WriteLine("*** INSERT ***");
var newCategory = new Category { CategoryName = "Widgets",
Description = "Widgets are the customer-facing analogues " +
"to sprockets and cogs."
};
var newProduct = new Product { ProductName = "Blue Widget",
UnitPrice = 34.56M,
Category = newCategory
};
db2.Categories.InsertOnSubmit(newCategory);
db2.SubmitChanges();
Console.WriteLine();
Console.WriteLine("*** AFTER ***");
ObjectDumper.Write(q, 1);
Cleanup65(); // Restore previous database state
}
public void LinqToSqlInsert02() {
Northwind db2 = new Northwind(connString);
DataLoadOptions ds = new DataLoadOptions();
ds.LoadWith<nwind.Category>(p => p.Products);
db2.LoadOptions = ds;
var q = (
from c in db2.Categories
where c.CategoryName == "Widgets"
select c);
Console.WriteLine("*** BEFORE ***");
ObjectDumper.Write(q, 1);
Console.WriteLine();
Console.WriteLine("*** INSERT ***");
var newCategory = new Category { CategoryName = "Widgets",
Description = "Widgets are the customer-facing analogues " +
"to sprockets and cogs."
};
var newProduct = new Product { ProductName = "Blue Widget",
UnitPrice = 34.56M,
Category = newCategory
};
db2.Categories.InsertOnSubmit(newCategory);
db2.SubmitChanges();
Console.WriteLine();
Console.WriteLine("*** AFTER ***");
ObjectDumper.Write(q, 1);
Cleanup65(); // Restore previous database state
}
public
void
LinqToSqlInsert03()
{
Northwind db2 = new Northwind(connString);
DataLoadOptions ds = new DataLoadOptions();
ds.LoadWith<nwind.Employee>(p => p.EmployeeTerritories);
ds.LoadWith<nwind.EmployeeTerritory>(p => p.Territory);
db2.LoadOptions = ds;
var q = (
from e in db.Employees
where e.FirstName == "Nancy"
select e);
Console.WriteLine("*** BEFORE ***");
ObjectDumper.Write(q, 1);
Console.WriteLine();
Console.WriteLine("*** INSERT ***");
var newEmployee = new Employee { FirstName = "Kira",
LastName = "Smith"
};
var newTerritory = new Territory { TerritoryID = "12345",
TerritoryDescription = "Anytown",
Region = db.Regions.First()
};
var newEmployeeTerritory = new EmployeeTerritory { Employee = newEmployee,
Territory = newTerritory
};
db.Employees.InsertOnSubmit(newEmployee);
db.Territories.InsertOnSubmit(newTerritory);
db.EmployeeTerritories.InsertOnSubmit(newEmployeeTerritory);
db.SubmitChanges();
Console.WriteLine();
Console.WriteLine("*** AFTER ***");
ObjectDumper.Write(q, 2);
Cleanup66(); // Restore previous database state
}
Northwind db2 = new Northwind(connString);
DataLoadOptions ds = new DataLoadOptions();
ds.LoadWith<nwind.Employee>(p => p.EmployeeTerritories);
ds.LoadWith<nwind.EmployeeTerritory>(p => p.Territory);
db2.LoadOptions = ds;
var q = (
from e in db.Employees
where e.FirstName == "Nancy"
select e);
Console.WriteLine("*** BEFORE ***");
ObjectDumper.Write(q, 1);
Console.WriteLine();
Console.WriteLine("*** INSERT ***");
var newEmployee = new Employee { FirstName = "Kira",
LastName = "Smith"
};
var newTerritory = new Territory { TerritoryID = "12345",
TerritoryDescription = "Anytown",
Region = db.Regions.First()
};
var newEmployeeTerritory = new EmployeeTerritory { Employee = newEmployee,
Territory = newTerritory
};
db.Employees.InsertOnSubmit(newEmployee);
db.Territories.InsertOnSubmit(newTerritory);
db.EmployeeTerritories.InsertOnSubmit(newEmployeeTerritory);
db.SubmitChanges();
Console.WriteLine();
Console.WriteLine("*** AFTER ***");
ObjectDumper.Write(q, 2);
Cleanup66(); // Restore previous database state
}
update simple
public void LinqToSqlInsert04() {
var q =
from c in db.Customers
where c.CustomerID == "ALFKI"
select c;
Console.WriteLine("*** BEFORE ***");
ObjectDumper.Write(q);
Console.WriteLine();
Console.WriteLine("*** UPDATE ***");
Customer cust = db.Customers.First(c => c.CustomerID == "ALFKI");
cust.ContactTitle = "Vice President";
db.SubmitChanges();
Console.WriteLine();
Console.WriteLine("*** AFTER ***");
ObjectDumper.Write(q);
Cleanup67(); // Restore previous database state
}
public void LinqToSqlInsert04() {
var q =
from c in db.Customers
where c.CustomerID == "ALFKI"
select c;
Console.WriteLine("*** BEFORE ***");
ObjectDumper.Write(q);
Console.WriteLine();
Console.WriteLine("*** UPDATE ***");
Customer cust = db.Customers.First(c => c.CustomerID == "ALFKI");
cust.ContactTitle = "Vice President";
db.SubmitChanges();
Console.WriteLine();
Console.WriteLine("*** AFTER ***");
ObjectDumper.Write(q);
Cleanup67(); // Restore previous database state
}
update mutiple
public void LinqToSqlInsert05() {
var q = from p in db.Products
where p.CategoryID == 1
select p;
Console.WriteLine("*** BEFORE ***");
ObjectDumper.Write(q);
Console.WriteLine();
Console.WriteLine("*** UPDATE ***");
foreach (var p in q)
{
p.UnitPrice += 1.00M;
}
//
ChangeSet cs = db.GetChangeSet();
Console.WriteLine("*** CHANGE SET ***");
Console.WriteLine("Number of entities inserted: {0}", cs.Inserts.Count);
Console.WriteLine("Number of entities updated: {0}", cs.Updates.Count);
Console.WriteLine("Number of entities deleted: {0}", cs.Deletes.Count);
Console.WriteLine();
Console.WriteLine("*** GetOriginalEntityState ***");
foreach (object o in cs.Updates)
{
Product p = o as Product;
if (p != null)
{
Product oldP = db.Products.GetOriginalEntityState(p);
Console.WriteLine("** Current **");
ObjectDumper.Write(p);
Console.WriteLine("** Original **");
ObjectDumper.Write(oldP);
Console.WriteLine();
}
}
Console.WriteLine();
Console.WriteLine("*** GetModifiedMembers ***");
foreach (object o in cs.Updates)
{
Product p = o as Product;
if (p != null)
{
foreach (ModifiedMemberInfo mmi in db.Products.GetModifiedMembers(p))
{
Console.WriteLine("Member {0}", mmi.Member.Name);
Console.WriteLine("\tOriginal value: {0}", mmi.OriginalValue);
Console.WriteLine("\tCurrent value: {0}", mmi.CurrentValue);
}
}
}
db.SubmitChanges();
Console.WriteLine();
Console.WriteLine("*** AFTER ***");
ObjectDumper.Write(q);
Cleanup68(); // Restore previous database state
}
public void LinqToSqlInsert05() {
var q = from p in db.Products
where p.CategoryID == 1
select p;
Console.WriteLine("*** BEFORE ***");
ObjectDumper.Write(q);
Console.WriteLine();
Console.WriteLine("*** UPDATE ***");
foreach (var p in q)
{
p.UnitPrice += 1.00M;
}
//
ChangeSet cs = db.GetChangeSet();
Console.WriteLine("*** CHANGE SET ***");
Console.WriteLine("Number of entities inserted: {0}", cs.Inserts.Count);
Console.WriteLine("Number of entities updated: {0}", cs.Updates.Count);
Console.WriteLine("Number of entities deleted: {0}", cs.Deletes.Count);
Console.WriteLine();
Console.WriteLine("*** GetOriginalEntityState ***");
foreach (object o in cs.Updates)
{
Product p = o as Product;
if (p != null)
{
Product oldP = db.Products.GetOriginalEntityState(p);
Console.WriteLine("** Current **");
ObjectDumper.Write(p);
Console.WriteLine("** Original **");
ObjectDumper.Write(oldP);
Console.WriteLine();
}
}
Console.WriteLine();
Console.WriteLine("*** GetModifiedMembers ***");
foreach (object o in cs.Updates)
{
Product p = o as Product;
if (p != null)
{
foreach (ModifiedMemberInfo mmi in db.Products.GetModifiedMembers(p))
{
Console.WriteLine("Member {0}", mmi.Member.Name);
Console.WriteLine("\tOriginal value: {0}", mmi.OriginalValue);
Console.WriteLine("\tCurrent value: {0}", mmi.CurrentValue);
}
}
}
db.SubmitChanges();
Console.WriteLine();
Console.WriteLine("*** AFTER ***");
ObjectDumper.Write(q);
Cleanup68(); // Restore previous database state
}
public
void
LinqToSqlInsert06()
{
Console.WriteLine("*** BEFORE ***");
ObjectDumper.Write(from c in db.OrderDetails where c.OrderID == 10255 select c);
Console.WriteLine();
Console.WriteLine("*** DELETE ***");
//Beverages
OrderDetail orderDetail = db.OrderDetails.First(c => c.OrderID == 10255 && c.ProductID == 36);
db.OrderDetails.DeleteOnSubmit(orderDetail);
db.SubmitChanges();
Console.WriteLine();
Console.WriteLine("*** AFTER ***");
ClearDBCache();
ObjectDumper.Write(from c in db.OrderDetails where c.OrderID == 10255 select c);
Cleanup69(); // Restore previous database state
}
Console.WriteLine("*** BEFORE ***");
ObjectDumper.Write(from c in db.OrderDetails where c.OrderID == 10255 select c);
Console.WriteLine();
Console.WriteLine("*** DELETE ***");
//Beverages
OrderDetail orderDetail = db.OrderDetails.First(c => c.OrderID == 10255 && c.ProductID == 36);
db.OrderDetails.DeleteOnSubmit(orderDetail);
db.SubmitChanges();
Console.WriteLine();
Console.WriteLine("*** AFTER ***");
ClearDBCache();
ObjectDumper.Write(from c in db.OrderDetails where c.OrderID == 10255 select c);
Cleanup69(); // Restore previous database state
}
delete one to many
public void LinqToSqlInsert07() {
var orderDetails =
from o in db.OrderDetails
where o.Order.CustomerID == "WARTH" && o.Order.EmployeeID == 3
select o;
Console.WriteLine("*** BEFORE ***");
ObjectDumper.Write(orderDetails);
Console.WriteLine();
Console.WriteLine("*** DELETE ***");
var order =
(from o in db.Orders
where o.CustomerID == "WARTH" && o.EmployeeID == 3
select o).First();
foreach (OrderDetail od in orderDetails)
{
db.OrderDetails.DeleteOnSubmit(od);
}
db.Orders.DeleteOnSubmit(order);
db.SubmitChanges();
Console.WriteLine();
Console.WriteLine("*** AFTER ***");
ObjectDumper.Write(orderDetails);
Cleanup70(); // Restore previous database state
}
public void LinqToSqlInsert07() {
var orderDetails =
from o in db.OrderDetails
where o.Order.CustomerID == "WARTH" && o.Order.EmployeeID == 3
select o;
Console.WriteLine("*** BEFORE ***");
ObjectDumper.Write(orderDetails);
Console.WriteLine();
Console.WriteLine("*** DELETE ***");
var order =
(from o in db.Orders
where o.CustomerID == "WARTH" && o.EmployeeID == 3
select o).First();
foreach (OrderDetail od in orderDetails)
{
db.OrderDetails.DeleteOnSubmit(od);
}
db.Orders.DeleteOnSubmit(order);
db.SubmitChanges();
Console.WriteLine();
Console.WriteLine("*** AFTER ***");
ObjectDumper.Write(orderDetails);
Cleanup70(); // Restore previous database state
}
where
public void LinqToSqlWhere01() {
var q =
from c in db.Customers
where c.City == "London"
select c;
ObjectDumper.Write(q);
}
public void LinqToSqlWhere01() {
var q =
from c in db.Customers
where c.City == "London"
select c;
ObjectDumper.Write(q);
}
public
void
LinqToSqlWhere02()
{
var q =
from e in db.Employees
where e.HireDate >= new DateTime(1994, 1, 1)
select e;
ObjectDumper.Write(q);
}
var q =
from e in db.Employees
where e.HireDate >= new DateTime(1994, 1, 1)
select e;
ObjectDumper.Write(q);
}
first condition
public void LinqToSqlWhere08() {
Order ord = db.Orders.First(o => o.Freight > 10.00M);
ObjectDumper.Write(ord, 0);
}
public void LinqToSqlWhere08() {
Order ord = db.Orders.First(o => o.Freight > 10.00M);
ObjectDumper.Write(ord, 0);
}
public
void
LinqToSqlSelect01()
{
var q =
from c in db.Customers
select c.ContactName;
ObjectDumper.Write(q);
}
var q =
from c in db.Customers
select c.ContactName;
ObjectDumper.Write(q);
}
public
void
LinqToSqlSelect04()
{
var q =
from p in db.Products
select new {p.ProductID, HalfPrice = p.UnitPrice / 2};
ObjectDumper.Write(q, 1);
}
var q =
from p in db.Products
select new {p.ProductID, HalfPrice = p.UnitPrice / 2};
ObjectDumper.Write(q, 1);
}
public
void
LinqToSqlSelect05()
{
var q =
from p in db.Products
select new {p.ProductName, Availability = p.UnitsInStock - p.UnitsOnOrder < 0 ? "Out Of Stock": "In Stock"};
ObjectDumper.Write(q, 1);
}
var q =
from p in db.Products
select new {p.ProductName, Availability = p.UnitsInStock - p.UnitsOnOrder < 0 ? "Out Of Stock": "In Stock"};
ObjectDumper.Write(q, 1);
}
public
void
LinqToSqlSelect10()
{
var q = (
from c in db.Customers
select c.City )
.Distinct();
ObjectDumper.Write(q);
}
var q = (
from c in db.Customers
select c.City )
.Distinct();
ObjectDumper.Write(q);
}
public
void
LinqToSqlCount01()
{
var q = db.Customers.Count();
Console.WriteLine(q);
}
public void LinqToSqlCount02() {
var q = db.Products.Count(p => !p.Discontinued);
Console.WriteLine(q);
}
public void LinqToSqlCount03() {
var q = db.Orders.Select(o => o.Freight).Sum();
Console.WriteLine(q);
}
public void LinqToSqlCount05() {
var q = db.Products.Select(p => p.UnitPrice).Min();
Console.WriteLine(q);
}
public void LinqToSqlCount07() {
var categories =
from p in db.Products
group p by p.CategoryID into g
select new {
CategoryID = g.Key,
CheapestProducts =
from p2 in g
where p2.UnitPrice == g.Min(p3 => p3.UnitPrice)
select p2
};
ObjectDumper.Write(categories, 1);
}
public void LinqToSqlCount08() {
var q = db.Employees.Select(e => e.HireDate).Max();
Console.WriteLine(q);
}
public void LinqToSqlCount11() {
var q = db.Orders.Select(o => o.Freight).Average();
Console.WriteLine(q);
}
public void LinqToSqlCount12() {
var q = db.Products.Average(p => p.UnitPrice);
Console.WriteLine(q);
}
public void LinqToSqlCount13() {
var categories =
from p in db.Products
group p by p.CategoryID into g
select new {
g.Key,
ExpensiveProducts =
from p2 in g
where p2.UnitPrice > g.Average(p3 => p3.UnitPrice)
select p2
};
ObjectDumper.Write(categories, 1);
}
var q = db.Customers.Count();
Console.WriteLine(q);
}
public void LinqToSqlCount02() {
var q = db.Products.Count(p => !p.Discontinued);
Console.WriteLine(q);
}
public void LinqToSqlCount03() {
var q = db.Orders.Select(o => o.Freight).Sum();
Console.WriteLine(q);
}
public void LinqToSqlCount05() {
var q = db.Products.Select(p => p.UnitPrice).Min();
Console.WriteLine(q);
}
public void LinqToSqlCount07() {
var categories =
from p in db.Products
group p by p.CategoryID into g
select new {
CategoryID = g.Key,
CheapestProducts =
from p2 in g
where p2.UnitPrice == g.Min(p3 => p3.UnitPrice)
select p2
};
ObjectDumper.Write(categories, 1);
}
public void LinqToSqlCount08() {
var q = db.Employees.Select(e => e.HireDate).Max();
Console.WriteLine(q);
}
public void LinqToSqlCount11() {
var q = db.Orders.Select(o => o.Freight).Average();
Console.WriteLine(q);
}
public void LinqToSqlCount12() {
var q = db.Products.Average(p => p.UnitPrice);
Console.WriteLine(q);
}
public void LinqToSqlCount13() {
var categories =
from p in db.Products
group p by p.CategoryID into g
select new {
g.Key,
ExpensiveProducts =
from p2 in g
where p2.UnitPrice > g.Average(p3 => p3.UnitPrice)
select p2
};
ObjectDumper.Write(categories, 1);
}
join
public void LinqToSqlJoin03() {
var q =
from e in db.Employees
from et in e.EmployeeTerritories
where e.City == "Seattle"
select new {e.FirstName, e.LastName, et.Territory.TerritoryDescription};
ObjectDumper.Write(q);
}
//self jon
public void LinqToSqlJoin04() {
var q =
from e1 in db.Employees
from e2 in e1.Employees
where e1.City == e2.City
select new {
FirstName1 = e1.FirstName, LastName1 = e1.LastName,
FirstName2 = e2.FirstName, LastName2 = e2.LastName,
e1.City
};
ObjectDumper.Write(q);
}
//group join
public void LinqToSqlJoin05() {
var q =
from c in db.Customers
join o in db.Orders on c.CustomerID equals o.CustomerID into orders
select new {c.ContactName, OrderCount = orders.Count()};
ObjectDumper.Write(q);
}
//left join
public void LinqToSqlJoin07() {
var q =
from e in db.Employees
join o in db.Orders on e equals o.Employee into ords
from o in ords.DefaultIfEmpty()
select new {e.FirstName, e.LastName, Order = o};
ObjectDumper.Write(q);
}
public void LinqToSqlJoin03() {
var q =
from e in db.Employees
from et in e.EmployeeTerritories
where e.City == "Seattle"
select new {e.FirstName, e.LastName, et.Territory.TerritoryDescription};
ObjectDumper.Write(q);
}
//self jon
public void LinqToSqlJoin04() {
var q =
from e1 in db.Employees
from e2 in e1.Employees
where e1.City == e2.City
select new {
FirstName1 = e1.FirstName, LastName1 = e1.LastName,
FirstName2 = e2.FirstName, LastName2 = e2.LastName,
e1.City
};
ObjectDumper.Write(q);
}
//group join
public void LinqToSqlJoin05() {
var q =
from c in db.Customers
join o in db.Orders on c.CustomerID equals o.CustomerID into orders
select new {c.ContactName, OrderCount = orders.Count()};
ObjectDumper.Write(q);
}
//left join
public void LinqToSqlJoin07() {
var q =
from e in db.Employees
join o in db.Orders on e equals o.Employee into ords
from o in ords.DefaultIfEmpty()
select new {e.FirstName, e.LastName, Order = o};
ObjectDumper.Write(q);
}
public
void
LinqToSqlPaging01()
{
var q = (
from c in db.Customers
orderby c.ContactName
select c)
.Skip(50)
.Take(10);
ObjectDumper.Write(q);
}
var q = (
from c in db.Customers
orderby c.ContactName
select c)
.Skip(50)
.Take(10);
ObjectDumper.Write(q);
}