internal static void DbTransaction()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
using (DbConnection connection = adventureWorks.Connection)
{
connection.Open();
using (DbTransaction transaction = connection.BeginTransaction())
{
try
{
adventureWorks.Transaction = transaction;
ProductCategory category = new ProductCategory()
{
Name = "Transaction"
};
adventureWorks.ProductCategories.InsertOnSubmit(category);
adventureWorks.SubmitChanges();
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "DELETE FROM [Production].[ProductCategory] WHERE [Name] = N'Transaction'";
command.Transaction = transaction;
Trace.WriteLine(command.ExecuteNonQuery()); // 1.
}
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw;
}
}
}
}
internal static int Create()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
ProductCategory category = new ProductCategory()
{
Name = "Category"
};
ProductSubcategory subcategory = new ProductSubcategory()
{
Name = "Subcategory"
};
category.ProductSubcategories.Add(subcategory);
adventureWorks.ProductCategories.InsertOnSubmit(category);
Trace.WriteLine(category.ProductCategoryID); // 0.
Trace.WriteLine(subcategory.ProductCategoryID); // null.
Trace.WriteLine(subcategory.ProductSubcategoryID); // 0.
adventureWorks.SubmitChanges();
Trace.WriteLine(category.ProductCategoryID); // 5.
Trace.WriteLine(subcategory.ProductCategoryID); // 5.
Trace.WriteLine(subcategory.ProductSubcategoryID); // 38.
return(subcategory.ProductSubcategoryID);
}
}
internal static void EntitiesFromSameDataContext()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
Product productById = adventureWorks.Products.Single(product => product.ProductID == 999);
Product productByName = adventureWorks.Products.Single(product => product.Name == "Road-750 Black, 52");
Trace.WriteLine(object.ReferenceEquals(productById, productByName)); // True.
}
}
internal static void Delete()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
ProductCategory category = adventureWorks.ProductCategories
.Single(entity => entity.Name == "Category");
adventureWorks.ProductCategories.DeleteOnSubmit(category);
adventureWorks.SubmitChanges();
}
}
internal static void DataContextLog()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
adventureWorks.Log = new CustomTextWriter(log => Trace.Write(log));
IQueryable <ProductCategory> source = adventureWorks.ProductCategories; // Define query.
source.ForEach(category => Trace.WriteLine(category.Name)); // Execute query.
// TODO.
}
}
internal static void UntrackedChanges()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
adventureWorks.ObjectTrackingEnabled = false;
IQueryable <Product> products = adventureWorks.Products.Take(10);
adventureWorks.Products.DeleteAllOnSubmit(products);
adventureWorks.SubmitChanges();
// InvalidOperationException: Object tracking is not enabled for the current data context instance.
}
}
internal static void UpdateWithNoChange()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
Product product = adventureWorks.Find <Product>(999);
product.ListPrice += product.ListPrice;
product.ListPrice /= 2; // Change tracked entity then change back.
Trace.WriteLine(adventureWorks.GetChangeSet().Updates.Any()); // False.
adventureWorks.SubmitChanges();
}
}
internal static void DisableDeferredLoading()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
adventureWorks.DeferredLoadingEnabled = false; // Default: true.
IQueryable <ProductSubcategory> subcategories = AdventureWorks.ProductSubcategories;
subcategories.ForEach(subcategory => Trace.WriteLine(
$@"{subcategory.ProductCategory?.Name}/{subcategory.Name}: {string.Join(
", ", subcategory.Products.Select(product => product.Name))}"));
}
}
internal static void DataContexGetCommand()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
IQueryable <ProductCategory> source = adventureWorks.ProductCategories; // Define query.
DbCommand command = adventureWorks.GetCommand(source);
Trace.WriteLine($@"{command.CommandText}{string.Concat(command.Parameters
.Cast<DbParameter>()
.Select(parameter => $", {parameter.ParameterName}={parameter.Value}"))}");
// TODO.
}
}
internal static void DisableObjectTracking()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
adventureWorks.ObjectTrackingEnabled = false;
Product product = adventureWorks.Products.First();
product.ListPrice += 100;
Trace.WriteLine(adventureWorks.GetChangeSet().Updates.Any()); // False
adventureWorks.ObjectTrackingEnabled = true;
// System.InvalidOperationException: Data context options cannot be modified after results have been returned from a query.
}
}
internal static void DeleteWithRelationship()
{
Create(); // Insert ProductCategory "Category" and ProductSubcategory "Subcategory".
using (AdventureWorks adventureWorks = new AdventureWorks())
{
ProductCategory category = adventureWorks.ProductCategories
.Single(entity => entity.Name == "Category");
ProductSubcategory subcategory = adventureWorks.ProductSubcategories
.Single(entity => entity.Name == "Subcategory");
adventureWorks.ProductCategories.DeleteOnSubmit(category);
adventureWorks.ProductSubcategories.DeleteOnSubmit(subcategory);
adventureWorks.SubmitChanges();
}
}
internal static void EagerLoadingWithRelationship()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
DataLoadOptions options = new DataLoadOptions();
options.LoadWith <ProductSubcategory>(subcategory => subcategory.Products);
options.LoadWith <ProductSubcategory>(subcategory => subcategory.ProductCategory);
adventureWorks.LoadOptions = options;
IQueryable <ProductSubcategory> subcategories = adventureWorks.ProductSubcategories;
subcategories.ForEach(subcategory => Trace.WriteLine(
$@"{subcategory.ProductCategory?.Name}/{subcategory.Name}: {string.Join(
", ", subcategory.Products.Select(product => product.Name))}"));
}
}
internal static void DataQueryToString()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
IQueryable <ProductCategory> source = adventureWorks.ProductCategories; // Define query.
string translatedSql = source.ToString();
Trace.WriteLine(translatedSql);
// SELECT[t0].[ProductID], [t0].[Name], [t0].[ListPrice], [t0].[ProductSubcategoryID]
// FROM[Production].[Product]
// AS[t0]
// WHERE[t0].[ListPrice] > @p0
source.ForEach(category => Trace.WriteLine(category.Name)); // Execute query.
}
}
internal static void DeleteWithNoQuery(int subcategoryId)
{
ProductSubcategory subcategory = new ProductSubcategory()
{
ProductSubcategoryID = subcategoryId
};
using (AdventureWorks adventureWorks = new AdventureWorks())
{
adventureWorks.ProductSubcategories.Attach(subcategory, false);
adventureWorks.ProductSubcategories.DeleteOnSubmit(subcategory);
adventureWorks.SubmitChanges();
}
}
internal static void ObjectsFromSameDataContext()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
var productById = adventureWorks.Products
.Where(product => product.ProductID == 999)
.Select(product => new { Name = product.Name, ListPrice = product.ListPrice })
.Single();
var productByName = adventureWorks.Products
.Where(product => product.Name == "Road-750 Black, 52")
.Select(product => new { Name = product.Name, ListPrice = product.ListPrice })
.Single();
Trace.WriteLine(object.ReferenceEquals(productById, productByName)); // False.
}
}
internal static void ConditionalEagerLoading()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
DataLoadOptions options = new DataLoadOptions();
options.LoadWith <ProductSubcategory>(subcategory => subcategory.Products);
options.AssociateWith <ProductSubcategory>(subcategory => subcategory.Products.Where(
product => product.ListPrice > 0));
adventureWorks.LoadOptions = options;
IQueryable <ProductSubcategory> subcategories = adventureWorks.ProductSubcategories;
subcategories.ForEach(subcategory => Trace.WriteLine(
$@"{subcategory.Name}: {string.Join(
", ", subcategory.Products.Select(product => product.Name))}"));
}
}
internal static void Attach()
{
Product product = new UniversalProduct()
{
Name = "On the fly", ListPrice = 1
};
using (AdventureWorks adventureWorks = new AdventureWorks())
{
product.ListPrice = 2;
Trace.WriteLine(adventureWorks.GetChangeSet().Updates.Any()); // False.
adventureWorks.Products.Attach(product);
product.ListPrice = 3;
adventureWorks.GetChangeSet().Updates.OfType <Product>().ForEach(attached => Trace.WriteLine(
$"{adventureWorks.Products.GetOriginalEntityState(attached).ListPrice} -> {attached.ListPrice}")); // 2 -> 3.
}
}
internal static void TransactionScope()
{
using (TransactionScope scope = new TransactionScope())
using (AdventureWorks adventureWorks = new AdventureWorks())
using (DbConnection connection = adventureWorks.Connection)
{
connection.Open();
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "INSERT INTO [Production].[ProductCategory] ([Name]) VALUES (N'Transaction')";
Trace.WriteLine(command.ExecuteNonQuery()); // 1.
}
ProductCategory category = adventureWorks.ProductCategories.Single(entity => entity.Name == "Transaction");
adventureWorks.ProductCategories.DeleteOnSubmit(category);
adventureWorks.SubmitChanges();
scope.Complete();
}
}
internal static void RelationshipChanges()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
DataLoadOptions loadOptions = new DataLoadOptions();
loadOptions.LoadWith <ProductCategory>(entity => entity.ProductSubcategories);
adventureWorks.LoadOptions = loadOptions;
ProductCategory category = adventureWorks.ProductCategories.First();
Trace.WriteLine(category.ProductSubcategories.Count); // 12.
ProductSubcategory[] subcategories = category.ProductSubcategories.ToArray();
Trace.WriteLine(subcategories.All(subcategory => subcategory.ProductCategory == category)); // True.
category.ProductSubcategories.Clear();
Trace.WriteLine(category.ProductSubcategories.Count); // 0.
Trace.WriteLine(adventureWorks.GetChangeSet().Updates.Count); // 12.
Trace.WriteLine(subcategories.All(subcategory => subcategory.ProductCategory == null)); // True.
}
}
internal static void Update()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
ProductCategory category = adventureWorks.ProductCategories.First();
ProductSubcategory subcategory = adventureWorks.ProductSubcategories
.Single(entity => entity.Name == "Subcategory");
Trace.WriteLine(subcategory.Name); // Subcategory.
Trace.WriteLine(subcategory.ProductCategoryID); // 5.
subcategory.Name = "Update"; // Update property.
subcategory.ProductCategory = category; // Update association.
adventureWorks.SubmitChanges();
Trace.WriteLine(subcategory.Name); // Subcategory update.
Trace.WriteLine(subcategory.ProductCategoryID); // 4.
}
}
internal static void EntityChanges()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
Product insert = new UniversalProduct()
{
Name = "Insert", ListPrice = 123
};
// Product insert = new Product() causes InvalidOperationException for InsertOnSubmit:
// InvalidOperationException: Instance of type 'Tutorial.LinqToSql.Product' could not be added. This type is not part of the mapped type system.
adventureWorks.Products.InsertOnSubmit(insert);
IQueryable <Product> update = adventureWorks.Products
.Where(product => product.Name.Contains("HL"));
update.ForEach(product => product.ListPrice += 50);
IQueryable <Product> delete = adventureWorks.Products
.Where(product => product.Name.Contains("ML"));
adventureWorks.Products.DeleteAllOnSubmit(delete);
ChangeSet changeSet = adventureWorks.GetChangeSet();
Trace.WriteLine(changeSet.Inserts.Any()); // True.
Trace.WriteLine(changeSet.Updates.Any()); // True.
Trace.WriteLine(changeSet.Deletes.Any()); // True.
changeSet.Inserts.OfType <Product>().ForEach(product => Trace.WriteLine(
$"{nameof(ChangeSet.Inserts)}: ({product.ProductID}, {product.Name}, {product.ListPrice})"));
changeSet.Updates.OfType <Product>().ForEach(product =>
{
Product original = adventureWorks.Products.GetOriginalEntityState(product);
Trace.WriteLine($"{nameof(ChangeSet.Updates)}: ({original.ProductID}, {original.Name}, {original.ListPrice}) -> ({product.ProductID}, {product.Name}, {product.ListPrice})");
});
changeSet.Deletes.OfType <Product>().ForEach(product => Trace.WriteLine(
$"{nameof(ChangeSet.Deletes)}: ({product.ProductID}, {product.Name}, {product.ListPrice})"));
// Inserts: (0, Insert, 123)
// Updates: (951, HL Crankset, 404.9900) -> (951, HL Crankset, 454.9900)
// Updates: (996, HL Bottom Bracket, 121.4900) -> (996, HL Bottom Bracket, 171.4900)
// Deletes: (950, ML Crankset, 256.4900)
// Deletes: (995, ML Bottom Bracket, 101.2400)
}
}
internal static void Default()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
ProductCategory category = adventureWorks.ProductCategories.First();
Trace.WriteLine(category.Name); // Accessories.
category.Name = "Update";
ProductSubcategory subcategory = adventureWorks.ProductSubcategories.First();
Trace.WriteLine(subcategory.ProductCategoryID); // 1.
subcategory.ProductCategoryID = -1;
try
{
adventureWorks.SubmitChanges();
}
catch (SqlException exception)
{
Trace.WriteLine(exception);
// SqlException: The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_ProductSubcategory_ProductCategory_ProductCategoryID". The conflict occurred in database "D:\ONEDRIVE\WORKS\DRAFTS\CODESNIPPETS\DATA\ADVENTUREWORKS_DATA.MDF", table "Production.ProductCategory", column 'ProductCategoryID'. The statement has been terminated.
adventureWorks.Refresh(RefreshMode.OverwriteCurrentValues, category, subcategory);
Trace.WriteLine(category.Name); // Accessories.
Trace.WriteLine(subcategory.ProductCategoryID); // 1.
}
}
}
internal static IQueryable <string> GetProductNames
(this AdventureWorks adventureWorks, decimal listPrice) =>
GetProductNamesCompiled(adventureWorks, listPrice);