強悍的Linq
在使用Linq轉化XML,ActiveDirectory,Datatable,Array,List,Dictionary后意識到Linq的強大。VS居然還提供專門的LINQ Explorer，不覺明厲啊~
好用的 Linq Samples and the Sample Query Explorer
http://blog.miniasp.com/post/2008/05/05/About-Linq-Samples-and-the-Sample-Query-Explorer.aspx
該solution是直接使用MDF文件的，自己裝個Northwind，把connection string改下
AttachDBFileName='C:\Program Files (x86)\Microsoft Visual Studio 10.0\Samples\1033\CSharpSamples\LinqSamples\SampleQueries\Data\NORTHWND.MDF';Server='.\SQLEXPRESS';user instance=true;Integrated Security=SSPI;Connection Timeout=60
Data Source=Localhost;Initial Catalog=Northwind;Integrated Security=True
1.查詢Datatbale的值
var q = from dr in ds.Tables[0].AsEnumerable()
select new {Name = dr["Name"].ToString().Trim(),Age=dr["Age"].ToString().Trim()};
2.Join multiple string for group by rows
var employees = new List<Employee>();
employees.Add(new Employee { Name = "A", Age = 30, ContactNo = "New Contact Number A", Salary = 20000 });
employees.Add(new Employee { Name = "A", Age = 30, ContactNo = "New Contact Number A", Salary = 20000 });
employees.Add(new Employee { Name = "B", Age = 31, ContactNo = "New Contact Number B", Salary = 30000 });
employees.Add(new Employee { Name = "C", Age = 32, ContactNo = "New Contact Number C", Salary = 40000 });
employees.Add(new Employee { Name = "C", Age = 32, ContactNo = "New Contact Number C", Salary = 40000 });
var customers = new List<Customer>();
customers.Add(new Customer { Name = "A", Age = 30, ContactNo = "Old Contact Number A" });
customers.Add(new Customer { Name = "B", Age = 31, ContactNo = "New Contact Number B" });
var q2 = from e in employees
group e by new { name = e.Name, age = e.Age } into pg
select new Customer
{
Name = pg.Key.name,
Age = pg.Key.age,
ContactNo = String.Join(",", pg.Select(o => o.ContactNo).ToList())
};
3.對List<T>的distinct操作必須定義GetHashCode() 和Equals方法才能成功
不然的話，就只能用group by了
public partial class Person
{
public String Name { get; set; }
public Int AGe { get; set; }
public override bool Equals(object obj)
{
if (obj == null)
return false;
if (this.GetType() != obj.GetType())
return false;
return Equals(obj as Person);
}
private bool Equals(Person p)
{
return (this.Name == =p.Name && this.Age == p.Age);
}
public override int GetHashCode()
{
return this.GetHashCode();
}
}
4.Left join
var q4 = from e in employees
join c in customers on e.Name equals c.Name into g
from lg in g.DefaultIfEmpty()
select new { Name = e.Name, Age = e.Age, ContactNo1 = e.ContactNo, ContactNo2 =(lg==null)?"NULL":lg.ContactNo };
Console.WriteLine("Left Join Results:");
foreach (var c in q4)
{
Console.WriteLine(string.Format("Name:{0} Age:{1} ContactNo1:{2} ContactNo2:{3}", c.Name, c.Age, c.ContactNo1, c.ContactNo2));
}
How to: Perform Left Outer Joins (C# Programming Guide)
http://msdn.microsoft.com/en-us/library/bb397895.aspx
5.Update
我比較意外Method1可以成功。。。
// Method1
employees.Where(w => w.Name == "A" || w.Name == "C").ToList().ForEach(f => f.Salary = 5000);
Console.WriteLine("Method1:");
foreach (Employee e in employees)
{
Console.WriteLine(string.Format("Name:{0} Age:{1} ContactNo:{2} Salary:{3}", e.Name, e.Age, e.ContactNo , e.Salary));
}
// Method2
var updateEmps = employees.Where(w => w.Name == "A" || w.Name == "C");
foreach(Employee e in updateEmps){
e.Salary = 6000;
}
Console.WriteLine("Method2:");
foreach (Employee e in employees)
{
Console.WriteLine(string.Format("Name:{0} Age:{1} ContactNo:{2} Salary:{3}", e.Name, e.Age, e.ContactNo, e.Salary));
}
6.Multiple fields join
var q3 = from e in employees
join c in customers on new { e.Name, e.Age } equals new { c.Name, c.Age }
select e;
7.Multiple insert linq result int database
.Net Framework 4.5 似乎有AsDatatable()和CopyToDataTable,如果沒有自己寫一個也可以
Converting a List to Datatable
http://social.msdn.microsoft.com/Forums/vstudio/en-US/6ffcb247-77fb-40b4-bcba-08ba377ab9db/converting-a-list-to-datatable
.NET Framework 4.5 CopyToDataTable Method
http://msdn.microsoft.com/en-us/library/system.data.datatableextensions.copytodatatable(v=vs.110).aspx
public static DataTable ConvertToDataTable<T>(IList<T> data)
{
PropertyDescriptorCollection properties =
TypeDescriptor.GetProperties(typeof(T));
DataTable table = new DataTable();
foreach (PropertyDescriptor prop in properties)
table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
foreach (T item in data)
{
DataRow row = table.NewRow();
foreach (PropertyDescriptor prop in properties)
row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
table.Rows.Add(row);
}
return table;
}
DataTable dt = new DataTable();
String[] colStrs = new string[] { 。。。};
String[] colDts = new string[] {。。。};
String[] colInts = new string[] { 。。。};
foreach (string col in colStrs) { dt.Columns.Add(new DataColumn(col, typeof(string))); }
foreach (string col in colDts) { dt.Columns.Add(new DataColumn(col, typeof(DateTime))); }
foreach (string col in colInts) { dt.Columns.Add(new DataColumn(col, typeof(Int32))); }
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString))
{
var q = from e in db.TestTable
where e.Id > 10
select e;
DataTable dt2 = ConvertToDataTable( q.ToList());// q.CopyToDataTable();
bulkCopy.DestinationTableName = "TestTable";
foreach (string col in colStrs) { bulkCopy.ColumnMappings.Add(col, col); }
foreach (string col in colDts) { bulkCopy.ColumnMappings.Add(col, col); }
foreach (string col in colInts) { bulkCopy.ColumnMappings.Add(col, col); }
bulkCopy.WriteToServer(dt2);
}
8. Create XML document
public void LinqToSqlLocalMethodCall02()
{
XDocument doc = new XDocument(
new XElement("Customers", from c in db.Customers
where c.Country == "UK" || c.Country == "USA"
select (new XElement("Customer",
new XAttribute("CustomerID", c.CustomerID),
new XAttribute("CompanyName", c.CompanyName),
new XAttribute("InterationalPhone", PhoneNumberConverter(c.Country, c.Phone))
))));
Console.WriteLine(doc.ToString());
}
9. Sum()
public void LinqToSqlCount03() {
var q = db.Orders.Select(o => o.Freight).Sum();
Console.WriteLine(q);
}
public void LinqToSqlCount04() {
var q = db.Products.Sum(p => p.UnitsOnOrder);
Console.WriteLine(q);
}
10.group by count()
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);
}
11.union
public void LinqToSqlUnion01() {
var q = (
from c in db.Customers
select c.Phone
).Concat(
from c in db.Customers
select c.Fax
).Concat(
from e in db.Employees
select e.HomePhone
);
ObjectDumper.Write(q);
}
public void LinqToSqlUnion04() {
var q = (
from c in db.Customers
select c.Country
).Intersect(
from e in db.Employees
select e.Country
);
ObjectDumper.Write(q);
}
public void LinqToSqlUnion05() {
var q = (
from c in db.Customers
select c.Country
).Except(
from e in db.Employees
select e.Country
);
ObjectDumper.Write(q);
}
12.Like
public void LinqToSqlSqlMethods01()
{
var q = from c in db.Customers
where SqlMethods.Like(c.CustomerID, "C%")
select c;
ObjectDumper.Write(q);
}
13.DateDiffDay
public void LinqToSqlSqlMethods02()
{
var q = from o in db.Orders
where SqlMethods.DateDiffDay(o.OrderDate, o.ShippedDate) < 10
select o;
ObjectDumper.Write(q);
}
14.CompiledQuery
public void LinqToSqlCompileQuery01()
{
//Create compiled query
var fn = CompiledQuery.Compile((Northwind db2, string city) =>
from c in db2.Customers
where c.City == city
select c);
Console.WriteLine("****** Call compiled query to retrieve customers from London ******");
var LonCusts = fn(db, "London");
ObjectDumper.Write(LonCusts);
Console.WriteLine();
Console.WriteLine("****** Call compiled query to retrieve customers from Seattle ******");
var SeaCusts = fn(db, "Seattle");
ObjectDumper.Write(SeaCusts);
}
15.linq to dictionary
public void LinqToSqlConversion04() {
var q =
from p in db.Products
where p.UnitsInStock <= p.ReorderLevel && !p.Discontinued
select p;
Dictionary<int, Product> qDictionary = q.ToDictionary(p => p.ProductID);
foreach (int key in qDictionary.Keys) {
Console.WriteLine("Key {0}:", key);
ObjectDumper.Write(qDictionary[key]);
Console.WriteLine();
}
}
var db = new EmpServiceDataContext();
var q = (from s in db.ChineseCheckInfos
select new { s.EmployerCode ,s.CheckCode,s.CheckName})
.ToDictionary(s=>s.EmployerCode +s.CheckCode,s=>s.CheckName);
Dictionary<string, string> chnCheckNames = q;
16.Direct SQL
public void LinqToSqlDirect01() {
var products = db.ExecuteQuery<Product>(
"SELECT [Product List].ProductID, [Product List].ProductName " +
"FROM Products AS [Product List] " +
"WHERE [Product List].Discontinued = 0 " +
"ORDER BY [Product List].ProductName;"
);
ObjectDumper.Write(products);
}
Console.WriteLine("*** UPDATE ***");
db.ExecuteCommand("UPDATE Products SET UnitPrice = UnitPrice + 1.00");
16. Linq to Store procedure
public void LinqToSqlStoredProc02() {
ISingleResult<CustomersByCityResult> result = db.CustomersByCity("London");
ObjectDumper.Write(result);
}
public void LinqToSqlStoredProc04() {
IMultipleResults result = db.GetCustomerAndOrders("SEVES");
Console.WriteLine("********** Customer Result-set ***********");
IEnumerable<CustomerResultSet> customer = result.GetResult<CustomerResultSet>();
ObjectDumper.Write(customer);
Console.WriteLine();
Console.WriteLine("********** Orders Result-set ***********");
IEnumerable<OrdersResultSet> orders = result.GetResult<OrdersResultSet>();
ObjectDumper.Write(orders);
}
17.Linq to string array compare the length and value
public void Linq5() {
string[] digits = { "zero", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine" };
var shortDigits = digits.Where((digit, index) => digit.Length < index);
Console.WriteLine("Short digits:");
foreach (var d in shortDigits)
{
Console.WriteLine("The word {0} is shorter than its value.", d);
}
}
Short digits:
The word five is shorter than its value.
The word six is shorter than its value.
The word seven is shorter than its value.
The word eight is shorter than its value.
The word nine is shorter than its value.
18.Table to Dictionary
connDbConnection_forTemp = new System.Data.OleDb.OleDbConnection();
connDbConnection_forTemp.ConnectionString = conString;
connDbConnection_forTemp.Open();
System.Data.OleDb.OleDbCommand objCommand = new System.Data.OleDb.OleDbCommand();
string sqlStr = "select * from AMS_MailSetting where MailSetting like '" + prefix + "%'";
objCommand.CommandText = sqlStr;
objCommand.Connection = connDbConnection_forTemp;
DataSet ds = new DataSet();
System.Data.OleDb.OleDbDataAdapter objAdaptor = new System.Data.OleDb.OleDbDataAdapter();
objAdaptor.SelectCommand = objCommand;
objAdaptor.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
{
var q = from dr in ds.Tables[0].AsEnumerable()
select (new KeyValuePair<string, string>(dr["MailSetting"].ToString(), dr["SettingParam"].ToString()));
values = q.ToDictionary(s =>s.Key,s=>s.Value);
// value = ds.Tables[0].Rows[0][0].ToString();
}
19.居然还可以动态列名排序！
public static IQueryable<TEntity> OrderBy<TEntity>(this IQueryable<TEntity> source, string propertyStr) where TEntity : class
{
ParameterExpression param = Expression.Parameter(typeof(TEntity), "c");
PropertyInfo property = typeof(TEntity).GetProperty(propertyStr);
Expression propertyAccessExpression = Expression.MakeMemberAccess(param, property);
LambdaExpression le = Expression.Lambda(propertyAccessExpression, param);
Type type = typeof(TEntity);
MethodCallExpression resultExp = Expression.Call(typeof(Queryable), "OrderBy", new Type[] { type, property.PropertyType }, source.Expression, Expression.Quote(le));
return source.Provider.CreateQuery<TEntity>(resultExp);
}
