public void Join_OnToMany()
{
var mappingSource = new FluentMappingSource(delegate
{
var mapping = new DataContextMapping <DataContext>();
mapping.Provider = new ProviderAttribute(typeof(AccessDbProvider));
mapping.Map <MyCustomer>()
.Table("Customers")
.Column(o => (string)o["City"]);
mapping.Map <MyOrder>()
.Table("Orders")
.Column(o => o["OrderID"]);
return(mapping);
});
var db = new DataContext("C:/Northwind.mdb", mappingSource)
{
Log = Console.Out
};
var customers = db.GetTable <MyCustomer>();
var orders = db.GetTable <MyOrder>();
var q = from c in customers
from o in orders
where (string)c["City"] == "London"
select o;
var items = q.ToList();
Assert.IsTrue(items.Count > 0);
}
public void Delete()
{
var mappingSource = new FluentMappingSource(delegate
{
var mapping = new DataContextMapping <DataContext>();
mapping.Provider = new ProviderAttribute(typeof(AccessDbProvider));
mapping.Map <MyEmployee>()
.Table("Employees")
.Column(o => (int)o["EmployeeID"])
.Column(o => (DateTime)o["BirthDate"])
.Column(o => (string)o["FirstName"])
.Column(o => (string)o["LastName"]);
mapping.Map <MyOrder>()
.Table("Orders")
.Column(o => (int)o["OrderID"])
.Column(o => (DateTime)o["OrderDate"]);
return(mapping);
});
var db = new DataContext("C:/Northwind.mdb", mappingSource);
var table = db.GetTable <MyEmployee>();
table.Delete(o => (int)o["EmployeeID"] == -1);
}
public void DynamicColumJoin()
{
var mappingSource = new FluentMappingSource(delegate
{
var mapping = new DataContextMapping <DataContext>();
mapping.Provider = new ProviderAttribute(typeof(AccessDbProvider));
mapping.Map <MyEmployee>()
.Table("Employees")
.Column(o => o["EmployeeID"])
.Column(o => o["BirthDate"])
.Column(o => o["FirstName"])
.Column(o => o["LastName"]);
mapping.Map <MyOrder>()
.Table("Orders")
.Column(o => o["OrderID"])
.Column(o => o["OrderDate"]);
return(mapping);
});
var db = new DataContext("C:/Northwind.mdb", mappingSource)
{
Log = Console.Out
};
var employees = db.GetTable <MyEmployee>();
var orders = db.GetTable <MyOrder>();
employees.Join(orders, o => o["EmployeeID"], o => o["OrderID"],
(a, b) => new { FirstName = a["FirstName"], OrderDate = b["OrderDate"] }).ToArray();
}
public void Join_CompositeKey()
{
var mappingSource = new FluentMappingSource(delegate(Type contextType)
{
var mapping = new DataContextMapping(contextType);
mapping.Provider = new ProviderAttribute(typeof(MySqlProvider));
mapping.Map <MyOrder>()
.Table("Orders")
.Column(o => o["OrderID"]);
mapping.Map <MyProduct>()
.Table("Products")
.Column(o => o["ProductID"]);
mapping.Map <MyOrderDetail>()
.Table("OrderDetails")
.Column(o => o["OrderID"])
.Column(o => o["ProductID"])
.Column(o => o["UnitPrice"]);
return(mapping);
});
string conn = "server=localhost;User Id=root;Password=test;Persist Security Info=True;database=northwind";
var db = new DataContext(conn, mappingSource)
{
Log = Console.Out
};
var orders = db.GetTable <MyOrder>();
var products = db.GetTable <MyProduct>();
var orderDetails = db.GetTable <MyOrderDetail>();
(from o in orders
from p in products
join d in orderDetails
on new
{
OrderID = o["OrderID"],
ProductID = p["ProductID"],
}
equals new
{
OrderID = d["OrderID"],
ProductID = d["ProductID"]
} into details
from d in details
select new
{
OrderID = o["OrderID"],
ProductID = p["ProductID"],
UnitPrice = d["UnitPrice"]
}).ToList();
}
public void Where_Drilldown()
{
//Where - Drilldown
//This sample prints a list of customers from the state of Washington along with their orders.
//A sequence of customers is created by selecting customers where the region is 'WA'. The sample
//uses doubly nested foreach statements to print the order numbers for each customer in the sequence.
var mappingSource = new FluentMappingSource(delegate(Type contextType)
{
var mapping = new DataContextMapping(contextType);
mapping.Provider = new ProviderAttribute(typeof(AccessDbProvider));
mapping.Map <MyCustomer>()
.Table("Customers")
.Column(o => (string)o["Region"]);
return(mapping);
});
var db = new DataContext("C:/Northwind.mdb", mappingSource)
{
Log = Console.Out
};
var table = db.GetTable <MyCustomer>();
var waCustomers = table.Where(o => (string)o["Region"] == "WA").ToList();
Assert.IsTrue(waCustomers.Count > 0);
}
public void Where_Simple4()
{
var mappingSource = new FluentMappingSource(delegate(Type contextType)
{
var mapping = new DataContextMapping(contextType);
mapping.Provider = new ProviderAttribute(typeof(AccessDbProvider));
mapping.Map <MyProduct>()
.Table("Products")
.Column(o => (decimal)o["UnitPrice"])
.Column(o => (bool)o["Discontinued"]);
return(mapping);
});
var db = new DataContext("C:/Northwind.mdb", mappingSource)
{
Log = Console.Out
};
//下面这个例子是调用两次where以筛选出UnitPrice大于10且已停产的产品
var table = db.GetTable <MyProduct>();
var q = table.Where(o => (decimal)o["UnitPrice"] > 10m);
var products = q.Where(o => !(bool)o["Discontinued"]).ToList();
Assert.IsTrue(products.Count > 0);
}
public void Where_Simple3()
{
var mappingSource = new FluentMappingSource(delegate(Type contextType)
{
var mapping = new DataContextMapping(contextType);
mapping.Provider = new ProviderAttribute(typeof(AccessDbProvider));
mapping.Map <MyProduct>()
.Table("Products")
.Column(o => (int)o["UnitsInStock"])
.Column(o => (bool)o["Discontinued"])
.Column(o => (int)o["ReorderLevel"]);
return(mapping);
});
var db = new DataContext("C:/Northwind.mdb", mappingSource)
{
Log = Console.Out
};
//筛选库存量在订货点水平之下但未断货的产品
var table = db.GetTable <MyProduct>();
var products = table.Where(o => (short?)o["UnitsInStock"] <= (short?)o["ReorderLevel"] &&
!(bool)o["Discontinued"]).ToList();
Assert.IsTrue(products.Count > 0);
}
public void InsertWithSubmit()
{
var mappingSource = new FluentMappingSource(delegate
{
var mapping = new DataContextMapping <DataContext>();
mapping.Provider = new ProviderAttribute(typeof(AccessDbProvider));
mapping.Map <MyEmployee>()
.Table("Employees")
.Column(o => (int)o["EmployeeID"], o => o.PrimaryKey().AutoSyncOnInsert().DbGenerated())
.Column(o => (string)o["FirstName"])
.Column(o => (string)o["LastName"]);
return(mapping);
});
var db = new DataContext("C:/Northwind.mdb", mappingSource)
{
Log = Console.Out
};
var item = new MyEmployee();
item["FirstName"] = "CCC";
item["LastName"] = "DDD";
var table = db.GetTable <MyEmployee>();
table.InsertOnSubmit(item);
db.SubmitChanges();
}
public void Where_Simple2()
{
var mappingSource = new FluentMappingSource(delegate(Type contextType)
{
var mapping = new DataContextMapping(contextType);
mapping.Provider = new ProviderAttribute(typeof(AccessDbProvider));
mapping.Map <MyEmployee>()
.Table("Employees")
.Column(o => (DateTime)o["HireDate"]);
return(mapping);
});
var db = new DataContext("C:/Northwind.mdb", mappingSource)
{
Log = Console.Out
};
//筛选1994 年或之后雇用的雇员：
//var employees = (from e in db.Employees
// where e.HireDate >= new DateTime(1994, 1, 1)
// select e).ToList();
//Assert.IsTrue(employees.Count > 0);
var table = db.GetTable <MyEmployee>();
var employees = table.Where(o => (DateTime?)o["HireDate"] >= new DateTime(1994, 1, 1)).ToList();
Assert.IsTrue(employees.Count > 0);
}
public void Join_LetAssignment()
{
var mappingSource = new FluentMappingSource(delegate(Type contextType)
{
var mapping = new DataContextMapping(contextType);
mapping.Provider = new ProviderAttribute(typeof(MySqlProvider));
mapping.Map <MyCustomer>()
.Table("Customers")
.Column(o => (int)o["CustomerID"])
.Column(o => (int)o["OrderID"])
.Column(o => (int)o["CustomerID"])
.Column(o => (string)o["Country"])
.Column(o => (string)o["City"])
.Column(o => (string)o["ContactName"]);
mapping.Map <MyOrder>()
.Table("Orders")
.Column(o => (int)o["OrderID"])
.Column(o => (int)o["CustomerID"]);
return(mapping);
});
string conn = "server=localhost;User Id=root;Password=test;Persist Security Info=True;database=northwind";
var db = new DataContext(conn, mappingSource)
{
Log = Console.Out
};
var customers = db.GetTable <MyCustomer>();
var orders = db.GetTable <MyOrder>();
var q = (from c in customers
join o in orders on c["CustomerID"]
equals o["CustomerID"] into ords
let z = (string)c["City"] + (string)c["Country"]
from o in ords
select new
{
ContactName = c["ContactName"],
OrderID = o["OrderID"],
z
}).ToList();
Assert.IsTrue(q.Count > 0);
}
public void Join_ThreeWay()
{
var mappingSource = new FluentMappingSource(delegate(Type contextType)
{
var mapping = new DataContextMapping(contextType);
mapping.Provider = new ProviderAttribute(typeof(ALinq.MySQL.MySqlProvider));
mapping.Map <MyCustomer>()
.Table("Customers")
.Column(o => o["CustomerID"])
.Column(o => o["City"])
.Column(o => o["ContactName"]);
mapping.Map <MyOrder>()
.Table("Orders")
.Column(o => o["CustomerID"])
.Column(o => o["OrderID"]);
mapping.Map <MyEmployee>()
.Table("Employees")
.Column(o => o["City"]);
return(mapping);
});
string conn = "server=localhost;User Id=root;Password=test;Persist Security Info=True;database=northwind";
var db = new DataContext(conn, mappingSource)
{
Log = Console.Out
};
var customers = db.GetTable <MyCustomer>();
var orders = db.GetTable <MyOrder>();
var employees = db.GetTable <MyEmployee>();
var items = (from c in customers
join o in orders on c["CustomerID"]
equals o["CustomerID"] into ords
join e in employees on c["City"]
equals e["City"] into emps
select new
{
ContactName = c["ContactName"],
ords = ords.Count(),
emps = emps.Count(),
}).ToList();
}
public void Join_NullableNonnullableKeyRelationship()
{
var mappingSource = new FluentMappingSource(delegate(Type contextType)
{
var mapping = new DataContextMapping(contextType);
mapping.Provider = new ProviderAttribute(typeof(MySqlProvider));
mapping.Map <MyOrder>()
.Table("Orders")
.Column(o => o["OrderID"])
.Column(o => o["EmployeeID"]);
mapping.Map <MyEmployee>()
.Table("Employees")
.Column(o => o["EmployeeID"])
.Column(o => o["FirstName"]);
return(mapping);
});
string conn = "server=localhost;User Id=root;Password=test;Persist Security Info=True;database=northwind";
var db = new DataContext(conn, mappingSource)
{
Log = Console.Out
};
var orders = db.GetTable <MyOrder>();
var employees = db.GetTable <MyEmployee>();
var q = (from o in orders
join e in employees
on o["EmployeeID"] equals
e["EmployeeID"] into emps
from e in emps
select new
{
OrderID = o["OrderID"],
FirstName = e["FirstName"]
}).ToList();
Assert.IsTrue(q.Count > 0);
}
public void Join_TowWay()
{
var mappingSource = new FluentMappingSource(delegate(Type contextType)
{
var mapping = new DataContextMapping(contextType);
mapping.Provider = new ProviderAttribute(typeof(AccessDbProvider));
mapping.Map <MyOrder>()
.Table("Orders")
.Column(o => o["OrderID"])
.Column(o => o["CustomerID"]);
mapping.Map <MyCustomer>()
.Table("Customers")
.Column(o => o["CustomerID"])
.Column(o => o["ContactName"]);
return(mapping);
});
var db = new DataContext("C:/Northwind.mdb", mappingSource)
{
Log = Console.Out
};
var customers = db.GetTable <MyCustomer>();
var orders = db.GetTable <MyOrder>();
var items = (from c in customers
join o in orders on c["CustomerID"]
equals o["CustomerID"] into q
select new
{
ContactName = c["ContactName"],
OrderCount = q.Count()
}).ToList();
Assert.IsTrue(items.Count > 0);
}
public void Insert()
{
var mappingSource = new FluentMappingSource(delegate(Type contextType)
{
var mapping = new DataContextMapping(contextType);
mapping.Provider = new ProviderAttribute(typeof(AccessDbProvider));
mapping.Map <MyEmployee>()
.Table("Employees")
.Column(o => (int)o["EmployeeID"]);
return(mapping);
});
var db = new DataContext("C:/Northwind.mdb", mappingSource)
{
Log = Console.Out
};
var table = db.GetTable <MyEmployee>();
var id = table.Insert(o => new { FirstName = "AAA", LastName = "BBB" });
}
public void SelectWhere()
{
var mappingSource = new FluentMappingSource(delegate(Type contextType)
{
var mapping = new DataContextMapping(contextType);
mapping.Provider = new ProviderAttribute(typeof(AccessDbProvider));
mapping.Map <MyEmployee>()
.Table("Employees")
.Column(o => (DateTime)o["BirthDate"]);
return(mapping);
});
var db = new DataContext("C:/Northwind.mdb", mappingSource)
{
Log = Console.Out
};
var table = db.GetTable <MyEmployee>().Where(o => (DateTime?)o["BirthDate"] < DateTime.Now);
table.Where(o => (DateTime?)o["BirthDate"] < DateTime.Now).ToArray();
}
public void Aggregate_Null()
{
var mappingSource = new FluentMappingSource(delegate
{
var mapping = new DataContextMapping <DataContext>();
mapping.Provider = new ProviderAttribute(typeof(AccessDbProvider));
mapping.Map <MyProduct>()
.Table("Products")
.Column(o => (int)o["ProductID"]);
return(mapping);
});
var db = new DataContext("C:/Northwind.mdb", mappingSource);
var table = db.GetTable <MyProduct>();
table.Select(o => (int)o["ProductID"]).ToArray();
var min = table.Min(o => (int)o["ProductID"]);
var max = table.Max(o => (int)o["ProductID"]);
}
public void SelectNew1()
{
var mappingSource = new FluentMappingSource(delegate
{
var mapping = new DataContextMapping <DataContext>();
mapping.Provider = new ProviderAttribute(typeof(AccessDbProvider));
mapping.Map <MyEmployee>()
.Table("Employees")
.Column(o => o["LastName"]);
return(mapping);
});
var db = new DataContext("C:/Northwind.mdb", mappingSource)
{
Log = Console.Out
};
db.GetTable <MyEmployee>().Select(o => new { Item = o })
.Select(o => o.Item["LastName"])
.ToArray();
}
public void Where_Simple1()
{
var mappingSource = new FluentMappingSource(delegate(Type contextType)
{
var mapping = new DataContextMapping(contextType);
mapping.Provider = new ProviderAttribute(typeof(AccessDbProvider));
mapping.Map <MyCustomer>()
.Table("Customers")
.Column(o => (string)o["City"]);
return(mapping);
});
var db = new DataContext("C:/Northwind.mdb", mappingSource)
{
Log = Console.Out
};
//使用where筛选在伦敦的客户
var table = db.GetTable <MyCustomer>();
var customers = table.Where(o => (string)o["City"] == "London").ToArray();
Assert.IsTrue(customers.Length > 0);
}
public void ConvertTest()
{
var mappingSource = new FluentMappingSource(delegate(Type contextType)
{
var mapping = new DataContextMapping(contextType);
mapping.Provider = new ProviderAttribute(typeof(AccessDbProvider));
mapping.Map <MyProduct>().Table("Products").Column(o => o["ProductID"]);
return(mapping);
});
var db = new DataContext("C:/Northwind.mdb", mappingSource)
{
Log = Console.Out
};
var table = db.GetTable <MyProduct>();
table.Select(o => o["ProductID"]).ToArray();
table.Select(o => (int)o["ProductID"]).ToArray();
table.Select(o => Convert.ToString((int)o["ProductID"])).ToArray();
//var min = table.Min(o => (int)o["ProductID"]);
//var max = table.Max(o => (int)(o["ProductID"]));
}
public void GroupWhere4()
{
var mappingSource = new FluentMappingSource(delegate
{
var mapping = new DataContextMapping <DataContext>();
mapping.Provider = new ProviderAttribute(typeof(AccessDbProvider));
mapping.Map <MyEmployee>()
.Table("Employees")
.Column(o => (DateTime)o["BirthDate"])
.Column(o => o["FirstName"])
.Column(o => o["LastName"]);
return(mapping);
});
var db = new DataContext("C:/Northwind.mdb", mappingSource);
var table = db.GetTable <MyEmployee>();
var keys3 = table.Where(o => (DateTime)o["BirthDate"] < DateTime.Now)
.GroupBy(o => o["FirstName"])
.Select(o => o.Key);
keys3.ToList().ForEach(Console.WriteLine);
}
