public void ShouldRetrieveAllVendorsWithRelatedAddresses()
{
SampleDataInitialization.InitializeData(ctx);
var vendors = (
from vTbl in ctx.Vendor
join beaTbl in ctx.BusinessEntityAddress on vTbl.BusinessEntityID equals beaTbl.BusinessEntityID
join aTbl in ctx.Address on beaTbl.AddressID equals aTbl.AddressID
join sTbl in ctx.StateProvince on aTbl.StateProvinceID equals sTbl.StateProvinceID
orderby vTbl.Name
select new
{
VendorID = vTbl.BusinessEntityID,
AccountNumber = vTbl.AccountNumber,
VendorName = vTbl.Name,
AddressLine1 = aTbl.AddressLine1,
AddressLine2 = aTbl.AddressLine2,
City = aTbl.City,
PostalCode = aTbl.PostalCode,
Region = sTbl.StateProvinceCode
}
).ToList();
int vendorsCount = vendors.Count();
Assert.NotNull(vendors);
Assert.Equal(6, vendorsCount);
}
public void ShouldAddNewPersonRecord()
{
SampleDataInitialization.InitializeData(ctx);
ExecuteInATransaction(RunTheTest);
void RunTheTest()
{
var numberOfEntities = ctx.BusinessEntity.Count();
var numberOfPeople = ctx.Person.Count();
var businessEntity = new BusinessEntity
{
PersonObj = new AdventureWorks.Models.Person.Person
{
PersonType = "EM",
IsEasternNameStyle = false,
Title = "Big Pimp",
FirstName = "Terri",
MiddleName = "J",
LastName = "Phide",
Suffix = "Mr.",
EmailPromotion = EmailPromoPreference.NoPromotions
}
};
ctx.BusinessEntity.Add(businessEntity);
ctx.SaveChanges();
Assert.Equal(numberOfEntities + 1, ctx.BusinessEntity.Count());
Assert.Equal(numberOfPeople + 1, ctx.Person.Count());
Assert.Equal(businessEntity.BusinessEntityID, businessEntity.PersonObj.BusinessEntityID);
}
}
public void ShouldRaiseErrorDuplicateDepartmentName()
{
SampleDataInitialization.InitializeData(ctx);
ExecuteInATransaction(RunTheTest);
void RunTheTest()
{
Action testCode = () =>
{
ctx.Department.Add
(
new Department
{
Name = "Engineering",
GroupName = "Research and Development"
}
);
ctx.SaveChanges();
};
var ex = Record.Exception(testCode);
Assert.NotNull(ex);
Assert.IsType <DbUpdateException>(ex);
Assert.True(ex.InnerException.Message.Contains("AK_Department_Name", StringComparison.OrdinalIgnoreCase));
}
}
public void ShouldCreateVendorRecord()
{
SampleDataInitialization.InitializeData(ctx);
ExecuteInATransaction(RunTheTest);
void RunTheTest()
{
var businessEntity = new BusinessEntity
{
VendorObj = new Vendor
{
AccountNumber = "OAKCLIFF0001",
Name = "Oak Cliff Bike Resellers",
CreditRating = CreditRating.Superior,
PreferredVendor = true,
IsActive = true
}
};
ctx.BusinessEntity.Add(businessEntity);
ctx.SaveChanges();
var vendor = ctx.Vendor
.AsNoTracking()
.Where(v => v.AccountNumber == "OAKCLIFF0001")
.Single <Vendor>();
Assert.NotNull(vendor);
}
}
public void ShouldRetrieveOnePersonOfTypeEmployeeWithAddress()
{
SampleDataInitialization.InitializeData(ctx);
var person = (
from pTbl in ctx.Person
join beaTbl in ctx.BusinessEntityAddress on pTbl.BusinessEntityID equals beaTbl.BusinessEntityID
join aTbl in ctx.Address on beaTbl.AddressID equals aTbl.AddressID
join sTbl in ctx.StateProvince on aTbl.StateProvinceID equals sTbl.StateProvinceID
where pTbl.PersonType == "EM" && pTbl.BusinessEntityID == 1
select new
{
PersonID = pTbl.BusinessEntityID,
FirstName = pTbl.FirstName,
MiddleName = pTbl.MiddleName,
LastName = pTbl.LastName,
AddressLine1 = aTbl.AddressLine1,
AddressLine2 = aTbl.AddressLine2,
City = aTbl.City,
PostalCode = aTbl.PostalCode,
Region = sTbl.StateProvinceCode
}
).Single();
Assert.NotNull(person);
Assert.Equal("Ken", person.FirstName);
Assert.Equal("4350 Minute Dr.", person.AddressLine1);
}
public void ShouldAddNewEmployeeWithDepartmentHistory()
{
SampleDataInitialization.InitializeData(ctx);
ExecuteInATransaction(RunTheTest);
void RunTheTest()
{
var businessEntity = new BusinessEntity
{
PersonObj = new AdventureWorks.Models.Person.Person
{
PersonType = "EM",
IsEasternNameStyle = false,
FirstName = "Jane",
MiddleName = "J",
LastName = "Doe",
Suffix = "Ms.",
EmailPromotion = EmailPromoPreference.NoPromotions,
EmployeeObj = new Employee
{
NationalIDNumber = "222222222",
LoginID = "adventure-works\\jdoe0",
JobTitle = "Software Engineer",
BirthDate = new DateTime(1991, 1, 29),
MaritalStatus = "S",
Gender = "F",
HireDate = new DateTime(2020, 6, 6),
VacationHours = 0,
SickLeaveHours = 0,
DepartmentHistories =
{
new EmployeeDepartmentHistory
{
DepartmentID = 5,
ShiftID = 1,
StartDate = new DateTime(2020, 6, 6)
}
}
}
}
};
ctx.BusinessEntity.Add(businessEntity);
ctx.SaveChanges();
var person = ctx.Person
.AsNoTracking()
.Where(p => p.FirstName == "Jane" && p.MiddleName == "J" && p.LastName == "Doe")
.SingleOrDefault <AdventureWorks.Models.Person.Person>();
Assert.NotNull(person);
var employee = ctx.Employee.Find(person.BusinessEntityID);
Assert.NotNull(employee);
Assert.Equal(5, employee.DepartmentHistories[0].DepartmentID);
Assert.Equal(new DateTime(2020, 6, 6), employee.DepartmentHistories[0].StartDate);
}
}
public void ShouldAddPersonWithNewBusinessEntityAddress()
{
SampleDataInitialization.InitializeData(ctx);
ExecuteInATransaction(RunTheTest);
void RunTheTest()
{
var businessEntity = new BusinessEntity
{
PersonObj = new AdventureWorks.Models.Person.Person
{
PersonType = "EM",
IsEasternNameStyle = false,
FirstName = "Jane",
MiddleName = "J",
LastName = "Doe",
Suffix = "Ms.",
EmailPromotion = EmailPromoPreference.NoPromotions
}
};
ctx.BusinessEntity.Add(businessEntity);
ctx.SaveChanges();
var address = new Address
{
AddressLine1 = "123 Main Street",
AddressLine2 = "Ste 111",
City = "Dallas",
StateProvinceID = 73,
PostalCode = "75231",
BusinessEntityAddressObj = new BusinessEntityAddress
{
BusinessEntityID = businessEntity.BusinessEntityID,
AddressTypeID = 3
}
};
ctx.Address.Add(address);
ctx.SaveChanges();
var person = ctx.Person
.AsNoTracking()
.Where(p => p.FirstName == "Jane" && p.MiddleName == "J" && p.LastName == "Doe")
.SingleOrDefault <AdventureWorks.Models.Person.Person>();
Assert.NotNull(person);
Assert.True(person.BusinessEntityID > 0);
var businessEntityAddress = ctx.BusinessEntityAddress
.AsNoTracking()
.Where(bea => bea.BusinessEntityID == person.BusinessEntityID && bea.AddressID == address.AddressID)
.Single <BusinessEntityAddress>();
Assert.NotNull(businessEntityAddress);
}
}
public void ShouldAddNewPersonWithNewEmailAddressAndEmailPassword()
{
SampleDataInitialization.InitializeData(ctx);
ExecuteInATransaction(RunTheTest);
void RunTheTest()
{
var businessEntity = new BusinessEntity
{
PersonObj = new AdventureWorks.Models.Person.Person
{
PersonType = "EM",
IsEasternNameStyle = false,
FirstName = "Jane",
MiddleName = "J",
LastName = "Doe",
Suffix = "Ms.",
EmailPromotion = EmailPromoPreference.NoPromotions,
EmailAddressObj = new EmailAddress
{
PersonEmailAddress = "*****@*****.**"
},
PasswordObj = new PersonPWord
{
PasswordHash = "pbFwXWE99vobT6g+vPWFy93NtUU/orrIWafF01hccfM=",
PasswordSalt = "bE3XiWw="
}
}
};
ctx.BusinessEntity.Add(businessEntity);
ctx.SaveChanges();
var person = ctx.Person
.AsNoTracking()
.Where(p => p.FirstName == "Jane" && p.MiddleName == "J" && p.LastName == "Doe")
.SingleOrDefault <AdventureWorks.Models.Person.Person>();
Assert.NotNull(person);
Assert.True(person.BusinessEntityID > 0);
var emailAddress = ctx.EmailAddress
.AsNoTracking()
.Where(a => a.BusinessEntityID == person.BusinessEntityID)
.SingleOrDefault <EmailAddress>();
Assert.Equal("*****@*****.**", emailAddress.PersonEmailAddress);
var emailPassword = ctx.Password
.AsNoTracking()
.Where(pword => pword.BusinessEntityID == person.BusinessEntityID)
.SingleOrDefault <PersonPWord>();
Assert.Equal("pbFwXWE99vobT6g+vPWFy93NtUU/orrIWafF01hccfM=", emailPassword.PasswordHash);
}
}
public void ShouldRetrieveOneEmployeeByBusinessEntityID()
{
SampleDataInitialization.InitializeData(ctx);
var bizEntityID = 18;
var employee = ctx.Employee.Find(bizEntityID);
Assert.Equal("Duffy", employee.PersonNavigation.LastName);
}
public void ShouldRetrieveOneDepartmentByName()
{
SampleDataInitialization.InitializeData(ctx);
var deptName = "Engineering";
var dept = ctx.Department.Where(d => d.Name == deptName).First();
Assert.Equal(15, dept.DepartmentID);
Assert.Equal("Research and Development", dept.GroupName);
}
public void ShouldRetrieveOneDepartmentByID()
{
SampleDataInitialization.InitializeData(ctx);
short deptID = 15;
var dept = ctx.Department.Find(deptID);
Assert.NotNull(dept);
Assert.Equal("Engineering", dept.Name);
}
public void ShouldRetrieveOneEmployeeByFirstAndLastName()
{
SampleDataInitialization.InitializeData(ctx);
var employee = ctx.Employee
.Where(e => e.PersonNavigation.FirstName == "Terri" && e.PersonNavigation.LastName == "Duffy")
.First();
Assert.Equal(18, employee.BusinessEntityID);
}
public void ShouldRetrieveCompleteEmployeeObjectGraphByLoginID()
{
SampleDataInitialization.InitializeData(ctx);
var employeeObj = ctx.Person
.Where(person => person.PersonType == "EM" && person.EmployeeObj.LoginID == "adventure-works\\freddyk")
.Single <AdventureWorks.Models.Person.Person>();
Assert.NotNull(employeeObj);
}
public void ShouldRetrieveCompleteEmployeeObjectGraphByNationalID()
{
SampleDataInitialization.InitializeData(ctx);
var employeeObj = ctx.Person
.AsNoTracking()
.Where(person => person.PersonType == "EM" && person.EmployeeObj.NationalIDNumber == "811994146")
.Single <AdventureWorks.Models.Person.Person>();
Assert.NotNull(employeeObj);
}
public void ShouldRetrieveAllEmployeeViewModels()
{
SampleDataInitialization.InitializeData(ctx);
var employees = ctx.EmployeeViewModel.ToList();
Assert.NotNull(employees);
var count = employees.Count;
Assert.Equal(6, count);
}
public void ShouldRetrieveOneAddressViewModelByAddressID()
{
SampleDataInitialization.InitializeData(ctx);
var addressID = 1;
var result = ctx.AddressViewModel
.Where(a => a.AddressID == addressID)
.FirstOrDefault();
Assert.NotNull(result);
Assert.Equal("28 San Marino Ct", result.AddressLine1);
}
public void ShouldRetrieveOnePhoneViewModelByBusinessEntityID()
{
SampleDataInitialization.InitializeData(ctx);
var bizEntityID = 13;
var result = ctx.PhoneViewModel
.Where(ph => ph.BusinessEntityID == bizEntityID)
.FirstOrDefault();
Assert.NotNull(result);
Assert.Equal("273-555-0100", result.PhoneNumber);
}
public void ShouldRetrieveAllVendorViewModels()
{
SampleDataInitialization.InitializeData(ctx);
var results = ctx.VendorViewModel
.OrderBy(r => r.BusinessEntityID)
.ToList();
Assert.NotNull(results);
var count = results.Count;
Assert.Equal(6, count);
}
public void ShouldRetrieveOneVendorViewModel()
{
SampleDataInitialization.InitializeData(ctx);
var bizEntityID = 2;
var result = ctx.VendorViewModel
.Where(v => v.BusinessEntityID == bizEntityID)
.FirstOrDefault();
Assert.NotNull(result);
Assert.Equal(CreditRating.Superior, result.CreditRating);
Assert.Equal("Superior", result.CreditRatingText);
}
public void ShouldAddNewBusinessEntityRecord()
{
SampleDataInitialization.InitializeData(ctx);
Assert.Equal(19, ctx.BusinessEntity.Count());
var businessEntity = new BusinessEntity {
};
ctx.BusinessEntity.Add(businessEntity);
ctx.SaveChanges();
Assert.True(businessEntity.BusinessEntityID > 0);
Assert.Equal(20, ctx.BusinessEntity.Count());
}
public void ShouldRetrieveOneVendorContactViewModel()
{
SampleDataInitialization.InitializeData(ctx);
var bizEntityID = 13;
var result = ctx.VendorContactViewModel
.Where(r => r.BusinessEntityID == bizEntityID)
.FirstOrDefault();
Assert.NotNull(result);
Assert.Equal("*****@*****.**", result.EmailAddress);
Assert.Equal("VC", result.PersonType);
Assert.Equal("Vendor Contact", result.PersonTypeLong);
}
public void ShouldRetrieveOneEmployeeViewModelByID()
{
SampleDataInitialization.InitializeData(ctx);
var eeID = 18;
var employee = ctx.EmployeeViewModel
.Where(e => e.BusinessEntityID == eeID)
.FirstOrDefault();
Assert.NotNull(employee);
Assert.Equal("Terri", employee.FirstName);
Assert.Equal("Duffy", employee.LastName);
Assert.Equal(1, employee.VacationHours);
}
public void ShouldAddPersonWithNewPhoneNumber()
{
SampleDataInitialization.InitializeData(ctx);
ExecuteInATransaction(RunTheTest);
void RunTheTest()
{
var businessEntity = new BusinessEntity
{
PersonObj = new AdventureWorks.Models.Person.Person
{
PersonType = "EM",
IsEasternNameStyle = false,
FirstName = "Jane",
MiddleName = "J",
LastName = "Doe",
Suffix = "Ms.",
EmailPromotion = EmailPromoPreference.NoPromotions,
Phones =
{
new PersonPhone {
PhoneNumber = "888-555-0142", PhoneNumberTypeID = 1
}
}
}
};
ctx.BusinessEntity.Add(businessEntity);
ctx.SaveChanges();
var person = ctx.Person
.AsNoTracking()
.Where(p => p.FirstName == "Jane" && p.MiddleName == "J" && p.LastName == "Doe")
.SingleOrDefault <AdventureWorks.Models.Person.Person>();
Assert.NotNull(person);
Assert.True(person.BusinessEntityID > 0);
var phoneNumber = ctx.PersonPhone
.AsNoTracking()
.Where(phone => phone.BusinessEntityID == person.BusinessEntityID)
.SingleOrDefault <PersonPhone>();
Assert.Equal("888-555-0142", phoneNumber.PhoneNumber);
}
}
public void ShouldRetrievePersonWithPhoneEmailAndPassword()
{
SampleDataInitialization.InitializeData(ctx);
var personObj = ctx.Person
.AsNoTracking()
.Where(person => person.PersonType == "EM" && person.FirstName == "Diane" && person.MiddleName == "L" && person.LastName == "Margheim")
.Include(person => person.Phones)
.Include(person => person.EmailAddressObj)
.Include(Person => Person.PasswordObj)
.Single <AdventureWorks.Models.Person.Person>();
Assert.NotNull(personObj);
Assert.Equal("815-555-0138", personObj.Phones[0].PhoneNumber);
Assert.Equal("*****@*****.**", personObj.EmailAddressObj.PersonEmailAddress);
Assert.Equal("s+FUWADIZzXBKpcbxe4OwL2uiJmjLogJNYXXHvc1X/k=", personObj.PasswordObj.PasswordHash);
}
public void ShouldRetrieveAllVendorContactViewModelsByVendorID()
{
SampleDataInitialization.InitializeData(ctx);
var vendorID = 7;
var results = ctx.VendorContactViewModel
.Where(vc => vc.VendorID == vendorID)
.OrderBy(vc => vc.LastName)
.ThenBy(vc => vc.FirstName)
.ToList();
Assert.NotNull(results);
var count = results.Count;
Assert.Equal(3, count);
Assert.Equal("Moberly", results[0].LastName);
Assert.Equal("Zimmerman", results[2].LastName);
}
public void ShouldRetrieveCompleteEmployeeObjectGraphByID()
{
SampleDataInitialization.InitializeData(ctx);
var eeID = 18;
var employeeObj = ctx.Person
.Where(person => person.BusinessEntityID == eeID)
.Include(person => person.EmployeeObj)
.ThenInclude(employee => employee.DepartmentHistories)
.Include(person => person.EmployeeObj)
.ThenInclude(employee => employee.PayHistories)
.Single <AdventureWorks.Models.Person.Person>();
var payHistoryCount = employeeObj.EmployeeObj.PayHistories.Count;
var deptHistoryCount = employeeObj.EmployeeObj.DepartmentHistories.Count;
Assert.NotNull(employeeObj);
Assert.Equal(2, payHistoryCount);
Assert.Equal(2, deptHistoryCount);
Assert.Equal("245797967", employeeObj.EmployeeObj.NationalIDNumber);
}
public void ShouldAddVendorAndContactThenLinkContactToVendor()
{
SampleDataInitialization.InitializeData(ctx);
ExecuteInATransaction(RunTheTest);
void RunTheTest()
{
// Create the vendor
var vendorEntity = new BusinessEntity
{
VendorObj = new Vendor
{
AccountNumber = "OAKCLIFF0001",
Name = "Oak Cliff Bike Resellers",
CreditRating = CreditRating.Superior,
PreferredVendor = true,
IsActive = true
}
};
// Create the vendor point of contact with phone, email, and email password
var contactEntity = new BusinessEntity
{
PersonObj = new AdventureWorks.Models.Person.Person
{
PersonType = "VC",
IsEasternNameStyle = false,
Title = "Big Pimp",
FirstName = "Terri",
MiddleName = "J",
LastName = "Phide",
Suffix = "Mr.",
EmailPromotion = EmailPromoPreference.NoPromotions,
EmailAddressObj = new EmailAddress
{
PersonEmailAddress = "*****@*****.**"
},
PasswordObj = new PersonPWord
{
PasswordHash = "pbFwXWE99vobT6g+vPWFy93NtUU/orrIWafF01hccfM=",
PasswordSalt = "bE3XiWw="
},
Phones =
{
new PersonPhone {
PhoneNumber = "214-555-0142", PhoneNumberTypeID = 1
}
}
}
};
// Insert vendor and point of contact into the database
ctx.BusinessEntity.AddRange(new List <BusinessEntity> {
vendorEntity, contactEntity
});
ctx.SaveChanges();
// Create the vendor's address
var address = new Address
{
AddressLine1 = "123 MLK Blvd",
AddressLine2 = "Ste 111",
City = "Dallas",
StateProvinceID = 73,
PostalCode = "75231",
BusinessEntityAddressObj = new BusinessEntityAddress
{
BusinessEntityID = vendorEntity.BusinessEntityID,
AddressTypeID = 3
}
};
// Create the link between the vendor's address and the vendor
var vendorContactLink = new BusinessEntityContact
{
BusinessEntityID = vendorEntity.BusinessEntityID,
PersonID = contactEntity.BusinessEntityID,
ContactTypeID = 17
};
// Insert the vendor address and address linking record
ctx.Address.Add(address);
ctx.BusinessEntityContact.Add(vendorContactLink);
ctx.SaveChanges();
/*
* Adding a vendor and vendor contact results in records being inserted into
* the following eight tables:
* Purchasing.Vendor
* Person.Person
* Person.PersonPhone
* Person.EmailAddress
* Person.Password
* Person.Address
* Person.BusinessEntityAddress
* Person.BusinessEntityContact
*/
var vendor = ctx.Vendor
.AsNoTracking()
.Where(v => v.AccountNumber == "OAKCLIFF0001")
.Single <Vendor>();
var contact = ctx.Person
.AsNoTracking()
.Where(c => c.FirstName == "Terri" && c.MiddleName == "J" && c.LastName == "Phide")
.Single <AdventureWorks.Models.Person.Person>();
var contactLink = ctx.BusinessEntityContact
.AsNoTracking()
.Where(bec => bec.BusinessEntityID == vendor.BusinessEntityID && bec.PersonID == contact.BusinessEntityID)
.Single <BusinessEntityContact>();
var addressLink = ctx.BusinessEntityAddress
.AsNoTracking()
.Where(a => a.BusinessEntityID == vendor.BusinessEntityID && a.AddressID == address.AddressID && a.AddressTypeID == 3)
.Single <BusinessEntityAddress>();
var vendorAddress = ctx.Address
.AsNoTracking()
.Where(a => a.AddressLine1 == "123 MLK Blvd" && a.AddressLine2 == "Ste 111" && a.City == "Dallas" && a.StateProvinceID == 73)
.Single <Address>();
var vendorContactEmail = ctx.EmailAddress
.AsNoTracking()
.Where(e => e.BusinessEntityID == contactEntity.BusinessEntityID && e.PersonEmailAddress == "*****@*****.**")
.Single <EmailAddress>();
var vendorContactPhone = ctx.PersonPhone
.AsNoTracking()
.Where(ph => ph.BusinessEntityID == contactEntity.BusinessEntityID && ph.PhoneNumber == "214-555-0142" && ph.PhoneNumberTypeID == 1)
.Single <PersonPhone>();
var contactPassword = ctx.Password
.AsNoTracking()
.Where(pw => pw.BusinessEntityID == contactEntity.BusinessEntityID && pw.PasswordHash == "pbFwXWE99vobT6g+vPWFy93NtUU/orrIWafF01hccfM=")
.Single <PersonPWord>();
Assert.NotNull(vendor);
Assert.NotNull(contact);
Assert.NotNull(contactLink);
Assert.NotNull(addressLink);
Assert.NotNull(vendorAddress);
Assert.NotNull(vendorContactEmail);
Assert.NotNull(vendorContactPhone);
Assert.NotNull(contactPassword);
}
}
public RepoTestsBase()
{
SampleDataInitialization.InitializeData(ctx);
}
protected void ResetDatabase()
{
SampleDataInitialization.InitializeData(new AdventureWorksContextFactory().CreateDbContext(null));
}