public static void DeleteTable(string tableName)
{
using (var context = new EasyContext())
{
context.Database.ExecuteSqlCommand($"DELETE {tableName}");
}
}
public static void ClearRange <T>(this DbSet <T> dbSet) where T : class
{
using (var context = new EasyContext())
{
dbSet.RemoveRange(dbSet);
}
}
public static void ResetIdentity(string tableName)
{
using (var context = new EasyContext())
{
context.Database.ExecuteSqlCommand($"DBCC CHECKIDENT('{tableName}', RESEED, 0)");
}
}
public static void SeedingWithoutDatabaseDrop(EasyContext context)
{
//TODO V3: Do a more automated cleanup
var productOrders = context.ProductOrder.Include("Products").ToList();
productOrders.ForEach(x =>
{
x.Products.ToList().ForEach(y =>
{
context.Product.Remove(y);
});
});
context.ProductOrder.ClearRange();
ClearingHelper.ResetIdentity("dbo.ProductOrder");
context.Product.ClearRange();
ClearingHelper.ResetIdentity("dbo.Product");
context.Person.ClearRange();
ClearingHelper.ResetIdentity("dbo.Person");
//TODO V3: Do a manual clean up
//ClearingHelper.DeleteTable("dbo.ProductOrder_Product_Mapping");
//ClearingHelper.DeleteTable("dbo.PersonDescription");
//ClearingHelper.DeleteTableAndResetIdentity("dbo.ProductOrder");
//ClearingHelper.DeleteTableAndResetIdentity("dbo.Product");
//ClearingHelper.DeleteTableAndResetIdentity("dbo.Person");
BaseSeed(context);
}
private static void BaseSeed(EasyContext context)
{
IList<Person> persons = new List<Person>
{
new Person { FirstName = "Brett", LastName = "Morin" , OverlyLongDescriptionField = "OMG Look I have a bunch of text denormalizing a table by putting a bunch of stuff only side related to the primary table." },
new Person { FirstName = "Neil", LastName = "Carpenter"},
new Person { FirstName = "Ryan", LastName = "Johnson"},
new Person { FirstName = "Aaron", LastName = "Shaver"},
};
foreach (var person in persons)
context.Person.AddOrUpdate(person);
IList<Product> products = new List<Product>
{
new Product {ProductName = "Shirt"},
new Product {ProductName = "Pants"},
new Product {ProductName = "Shoes" },
new Product {ProductName = "Bike" },
};
foreach (var product in products)
context.Product.AddOrUpdate(product);
IList<ProductOrder> productOrders = new List<ProductOrder>
{
new ProductOrder { Person = persons[0], ProductOrderName = "BrettOrders", Products = new List<Product> { products[0], products[1], products[2]} },
new ProductOrder { Person = persons[1], ProductOrderName = "NeilOrders", Products = new List<Product> { products[0], products[1], products[2], products[3] } }
};
foreach (var productOrder in productOrders)
context.ProductOrder.AddOrUpdate(productOrder);
}
public static void SeedingWithoutDatabaseDrop(EasyContext context)
{
//TODO V3: Do a more automated cleanup
var productOrders = context.ProductOrder.Include("Products").ToList();
productOrders.ForEach(x =>
{
x.Products.ToList().ForEach(y =>
{
context.Product.Remove(y);
});
});
context.ProductOrder.ClearRange();
ClearingHelper.ResetIdentity("dbo.ProductOrder");
context.Product.ClearRange();
ClearingHelper.ResetIdentity("dbo.Product");
context.Person.ClearRange();
ClearingHelper.ResetIdentity("dbo.Person");
//TODO V3: Do a manual clean up
//ClearingHelper.DeleteTable("dbo.ProductOrder_Product_Mapping");
//ClearingHelper.DeleteTable("dbo.PersonDescription");
//ClearingHelper.DeleteTableAndResetIdentity("dbo.ProductOrder");
//ClearingHelper.DeleteTableAndResetIdentity("dbo.Product");
//ClearingHelper.DeleteTableAndResetIdentity("dbo.Person");
BaseSeed(context);
}
static void Main(string[] args)
{
/*
SQL FOR REFERENCE
USE SimpleCodeFirst;
IF Object_id('ProductOrder') IS NOT NULL
DROP TABLE ProductOrder
IF Object_id('Product') IS NOT NULL
DROP TABLE Product
IF Object_id('Person') IS NOT NULL
DROP TABLE Person
CREATE TABLE Person
(
PersonId int identity CONSTRAINT PK_PersonId PRIMARY Key
, FirstName varchar(256) NOT NULL
, LastName varchar(256) NOT NULL
)
CREATE TABLE ProductOrder
(
ProductOrderId int identity NOT NULL CONSTRAINT PK_ProductOrderId PRIMARY Key
, PersonId int CONSTRAINT FK_ProductOrder_PersonId FOREIGN Key(PersonId) REFERENCES Person(PersonId)
, OrderName varchar(256) NOT NULL
)
CREATE TABLE Product
(
ProductId int IDENTITY CONSTRAINT PK_ProductId PRIMARY Key
, OrderId int CONSTRAINT FK_Product_OrderId FOREIGN Key(OrderId) REFERENCES Order(OrderId)
, ProductName varchar(256) NOT NULL
)
INSERT INTO dbo.Person(FirstName, LastName) VALUES('Bart', 'Simpson'),('Lisa', 'Simpson');
INSERT INTO dbo.ProductOrder(PersonId, OrderName) VALUES (1, 'Clothing'),(2, 'Clothing'),(1, 'SpareTime');
INSERT INTO dbo.Product(OrderId, ProductName) VALUES(1, 'Shirt'),(1, 'Shoes'),(1, 'Shorts'),(1, 'Hat'),(2, 'Dress'),(2, 'Bow'),(3, 'SkateBoard');
Select
p.FirstName + ' ' + p.LastName AS Name
, po.OfficeName
, o.ProductName
from dbo.Person p
left JOIN dbo.Order o ON o.PersonId = p.PersonId
left JOIN dbo.Product po ON po.OrderId = o.OrderId
TO ENABLE AUTOMATIC MIGRATION:
First, open the package manager console from Tools → Library Package Manager → Package Manager Console and then
run "enable-migrations –EnableAutomaticMigration:$true" command
(make sure that the default project is the project where your context class is)
TO ENABLE ADD MIGRATION:
First, open the package manager console from Tools → Library Package Manager → Package Manager Console and then
run "add-migration "First Easy schema"" or similar.
UPDATE DATABASE AFTER MIGRATION:
First, open the package manager console from Tools → Library Package Manager → Package Manager Console and then
run "update-database -verbose". The verbose switch will show the SQL Statements that run as well.
FORCE DATABASE FORCE:
*/
using (var context = new EasyContext())
{
//var people = context.Person.ToList();
//people.ForEach(x => Console.WriteLine($"{x.PersonId} {x.FirstName} {x.OverlyLongDescriptionField}"));
var productOrders = context.ProductOrder.Include("Products").ToList();
var persons = context.Person.ToList();
persons.GroupJoin(productOrders,
p => p.PersonId,
o => o.Person.PersonId,
(p, g) => g
.Select(o => new { PersonId = p.PersonId, PersonName = p.FirstName + " " + p.LastName, p.OverlyLongDescriptionField, Orders = o })
.DefaultIfEmpty(new { PersonId = p.PersonId, PersonName = p.FirstName + " " + p.LastName, p.OverlyLongDescriptionField, Orders = new ProductOrder() })
)
.SelectMany(g => g)
.ToList()
.ForEach(item =>
{
Console.WriteLine($"{item.PersonId}: {item.PersonName}: {item.OverlyLongDescriptionField}");
if (!(item?.Orders?.Products?.Count > 0))
return;
Console.WriteLine($"\t {item.Orders.ProductOrderId}: {item.Orders.ProductOrderName}");
item.Orders.Products.ToList().ForEach(x => Console.WriteLine($"\t\t{x.ProductId}: {x.ProductName}"));
});
Console.ReadLine();
}
}
private static void BaseSeed(EasyContext context)
{
IList <Person> persons = new List <Person>
{
new Person {
FirstName = "Brett", LastName = "Morin", OverlyLongDescriptionField = "OMG Look I have a bunch of text denormalizing a table by putting a bunch of stuff only side related to the primary table."
},
new Person {
FirstName = "Neil", LastName = "Carpenter"
},
new Person {
FirstName = "Ryan", LastName = "Johnson"
},
new Person {
FirstName = "Aaron", LastName = "Shaver"
},
};
foreach (var person in persons)
{
context.Person.AddOrUpdate(person);
}
IList <Product> products = new List <Product>
{
new Product {
ProductName = "Shirt"
},
new Product {
ProductName = "Pants"
},
new Product {
ProductName = "Shoes"
},
new Product {
ProductName = "Bike"
},
};
foreach (var product in products)
{
context.Product.AddOrUpdate(product);
}
IList <ProductOrder> productOrders = new List <ProductOrder>
{
new ProductOrder {
Person = persons[0], ProductOrderName = "BrettOrders", Products = new List <Product> {
products[0], products[1], products[2]
}
},
new ProductOrder {
Person = persons[1], ProductOrderName = "NeilOrders", Products = new List <Product> {
products[0], products[1], products[2], products[3]
}
}
};
foreach (var productOrder in productOrders)
{
context.ProductOrder.AddOrUpdate(productOrder);
}
}
public static void SeedingForDatabaseDrop(EasyContext context)
{
BaseSeed(context);
}
public static void SeedingForDatabaseDrop(EasyContext context)
{
BaseSeed(context);
}
