// 用于加载重写示例
private IEnumerable<Product> LoadProducts(Category category)
{
// 此分部方法将调用 LinqToSql 查询加载某个类别的产品
// 如果不调用 LinqToSQL 查询，这里还可以调用存储过程以加载产品
Console.WriteLine("******** Using LinqToSQL query to load products belong to category that are not discontinued. ******");
return this.Products.Where(p => p.CategoryID == category.CategoryID).Where(p=>!p.Discontinued);
}
// For Load Override Sample
private IEnumerable<Product> LoadProducts(Category category)
{
// This partial method is calling a LinqToSql query to load the products for a category
// Instead of the LinqToSQL query, A stored procedure can also be also be called here to load products
Console.WriteLine("******** Using LinqToSQL query to load products belong to category that are not discontinued. ******");
return this.Products.Where(p => p.CategoryID == category.CategoryID).Where(p=>!p.Discontinued);
}
public void LinqToSqlAdvanced05()
{
Northwind db = CreateDB();
//PK Column should be autogenerated
var con = new Category() { CategoryName = "New Era", Description= "(123)-456-7890" };
db.Categories.InsertOnSubmit(con);
db.SubmitChanges();
Console.WriteLine();
Console.WriteLine("The Category of the new record is {0}", con.CategoryID);
Category customerReloaded=db.Categories.First(c=>c.CategoryID==con.CategoryID);
Assert.AreEqual(customerReloaded.CategoryName, con.CategoryName);
Assert.AreEqual(customerReloaded.Description, con.Description);
// cleanup
db.Categories.DeleteOnSubmit(con);
db.SubmitChanges();
}
public void LinqToSqlInsert02()
{
Northwind db = CreateDB();
var ds = new DataLoadOptions();
ds.LoadWith<Category>(c => c.Products);
db.LoadOptions = ds;
var q = from c in db.Categories
where c.CategoryName == "Temp Widgets"
select c;
var newCategory = new Category
{
CategoryName = "Temp Widgets",
Description = "Widgets are the customer-facing analogues to sprockets and cogs."
};
var newProduct = new Product
{
ProductName = "temp Blue Widget",
UnitPrice = 34.56m,
Category = newCategory
};
db.Categories.InsertOnSubmit(newCategory);
db.SubmitChanges();
var reloadedProduct = db.Products.First(p => p.ProductID == newProduct.ProductID);
Assert.AreEqual(reloadedProduct.ProductName, newProduct.ProductName);
Assert.AreEqual(reloadedProduct.UnitPrice, newProduct.UnitPrice);
Assert.AreEqual(reloadedProduct.Category.CategoryID, newProduct.CategoryID);
var reloadedCategory = reloadedProduct.Category;
Assert.AreEqual(reloadedCategory.CategoryName, newCategory.CategoryName);
Assert.AreEqual(reloadedCategory.Description, reloadedCategory.Description);
db.Products.DeleteOnSubmit(newProduct);
db.Categories.DeleteOnSubmit(newCategory);
db.SubmitChanges();
}
partial void DeleteCategory(Category instance);
partial void UpdateCategory(Category instance);
partial void InsertCategory(Category instance);
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 InsertAndDeleteWithDependencies()
{
const string newCategoryName = "temp Category";
const string newProduct1 = "temp First Test Product";
const string newProduct2 = "temp Second Test Product";
var db = CreateDB();
var product = new Product
{
#if INGRES
Discontinued = "Y",
#else
Discontinued = true,
#endif
ProductName = newProduct1,
};
var category = new Category
{
CategoryName = newCategoryName,
Description = "Insert Description Here",
};
category.Products.Add(product);
Assert.AreEqual(0, category.CategoryID);
Assert.AreEqual(0, product.CategoryID.Value);
db.Categories.InsertOnSubmit(category);
db.SubmitChanges();
Assert.AreEqual(1, db.Categories.Where(c => c.CategoryName == newCategoryName).Count());
Assert.AreNotEqual(0, category.CategoryID);
Assert.AreEqual(1, db.Products.Where(p => p.ProductName == newProduct1).Count());
Assert.AreEqual(category.CategoryID, product.CategoryID.Value);
var p2 = new Product
{
#if INGRES
Discontinued = "Y",
#else
Discontinued = true,
#endif
ProductName = newProduct2
};
category.Products.Add(p2);
db.SubmitChanges();
Assert.AreEqual(1, db.Products.Where(p => p.ProductName == newProduct2).Count());
db.Products.DeleteOnSubmit(product);
db.Products.DeleteOnSubmit(p2);
db.Categories.DeleteOnSubmit(category);
db.SubmitChanges();
Assert.AreEqual(0, db.Categories.Where(c => c.CategoryName == newCategoryName).Count());
Assert.AreEqual(0, db.Products.Where(p => p.ProductName == newProduct1).Count());
Assert.AreEqual(0, db.Products.Where(p => p.ProductName == newProduct2).Count());
}
public void G13_ProvidedAutoGeneratedColumn()
{
Northwind db = CreateDB();
Category newCat = new Category();
newCat.CategoryID = 999;
newCat.CategoryName = "test";
db.Categories.InsertOnSubmit(newCat);
db.SubmitChanges();
// CategoryID is [Column(AutoSync=AutoSync.OnInsert)], so it's
// value is ignored on insert and will be updated
Assert.AreNotEqual(999, newCat.CategoryID);
// then, load our object
var checkCat = (from c in db.Categories where c.CategoryID == newCat.CategoryID select c).Single();
Assert.AreEqual(newCat.CategoryID, checkCat.CategoryID);
// remove the whole thing
db.Categories.DeleteOnSubmit(newCat);
db.SubmitChanges();
}
public void F18_ByteArrayAssignmentTest()
{
var db = CreateDB();
var picture = new byte[] { 1, 2, 3, 4 };
var nc = new Category { CategoryName = "test", Picture = picture };
db.Categories.InsertOnSubmit(nc);
db.SubmitChanges();
var q = from c in db.Categories
where c.CategoryName == "test"
select new { c.Picture };
var l = q.ToList();
Assert.IsTrue(l.Count > 0);
Assert.IsTrue(picture.SequenceEqual(l[0].Picture.ToArray()));
db.Categories.DeleteOnSubmit(nc);
db.SubmitChanges();
}
