public async Task ShouldReturnReport()
{
var context = GetDbContext(useSqlLite: true);
NorthwindInitializer.Initialize(context);
context.Database.GetDbConnection().Execute(@"
CREATE VIEW viewEmployeesWithManagers(
EmployeeFirstName, EmployeeLastName, EmployeeTitle,
ManagerFirstName, ManagerLastName, ManagerTitle)
AS
SELECT e.FirstName as EmployeeFirstName, e.LastName as EmployeeLastName, e.Title as EmployeeTitle,
m.FirstName as ManagerFirstName, m.LastName as ManagerLastName, m.Title as ManagerTitle
FROM employees AS e
JOIN employees AS m ON e.ReportsTo = m.EmployeeID
WHERE e.ReportsTo is not null");
var query = new EmployeesWithManagersViewQuery();
var queryHandler = new EmployeesWithManagersViewQueryHandler(context);
var result = await queryHandler.Handle(query, CancellationToken.None);
Assert.NotEmpty(result);
Assert.Equal(8, result.Count());
Assert.Contains(result, r => r.ManagerTitle == "Vice President, Sales");
Assert.DoesNotContain(result, r => r.EmployeeTitle == "Vice President, Sales");
}
public Task <IEnumerable <EmployeeManagerModel> > Handle(EmployeesWithManagersViewQuery request, CancellationToken cancellationToken)
{
var sql = "select * from viewEmployeesWithManagers";
return(_context.Database.GetDbConnection()
.QueryAsync <EmployeeManagerModel>(sql));
}
public async Task ShouldReturnReport()
{
UseSqlite();
using (var context = GetDbContext())
{
// Arrange
// This view only exists on DB, so we need to create it before querying.
context.Database.GetDbConnection().Execute(EmployeesWithManagersViewSql);
NorthwindInitializer.Initialize(context);
var query = new EmployeesWithManagersViewQuery(context);
// Act
var result = await query.Execute();
// Assert
Assert.NotEmpty(result);
Assert.Equal(8, result.Count());
Assert.Contains(result, r => r.ManagerTitle == "Vice President, Sales");
Assert.DoesNotContain(result, r => r.EmployeeTitle == "Vice President, Sales");
}
}
