Skip to content

Instantly share code, notes, and snippets.

@FransBouma
Last active July 11, 2016 14:30
Show Gist options
  • Save FransBouma/8ea09b01c6d2d9f9035ec3f985289c8c to your computer and use it in GitHub Desktop.
Save FransBouma/8ea09b01c6d2d9f9035ec3f985289c8c to your computer and use it in GitHub Desktop.
Temporal table predicate specification test for LLBLGen Pro v5.1 on SQL Server 2016
SELECT [LPA_L1].[Address], [LPA_L1].[AnnualSalary], [LPA_L1].[Department],
[LPA_L1].[EmployeeID] AS [EmployeeId], [LPA_L1].[Name], [LPA_L1].[Position],
[LPA_L1].[ValidFrom], [LPA_L1].[ValidTo]
FROM [TestDB].[dbo].[Employee]
FOR SYSTEM_TIME BETWEEN @p2 AND @p4 [LPA_L1]
WHERE ( ( [LPA_L1].[EmployeeID] = @p5))
[Test]
public void SimpleHistoryQueryBetweenTest()
{
var fromDate = new DateTime(2016, 1, 1);
var toDate = new DateTime(2016, 7, 1);
using(var adapter = new TemporalTableTestDataAccessAdapter())
{
var metaData = new LinqMetaData(adapter);
var q = from e in metaData.Employee
.ForSystemTime("BETWEEN {0} AND {1}", fromDate, toDate)
where e.EmployeeId == 1
select e;
var results = q.ToList();
// should give 3 rows: 1 from the real table and 2 from the history
Assert.AreEqual(3, results.Count);
var expectedQuery = "SELECT [LPA_L1].[Address], [LPA_L1].[AnnualSalary], [LPA_L1].[Department], [LPA_L1].[EmployeeID] AS [EmployeeId], [LPA_L1].[Name], [LPA_L1].[Position], [LPA_L1].[ValidFrom], [LPA_L1].[ValidTo] FROM [TestDB].[dbo].[Employee] FOR SYSTEM_TIME BETWEEN @p2 AND @p4 [LPA_L1] WHERE ( ( [LPA_L1].[EmployeeID] = @p5))";
Assert.AreEqual(expectedQuery, adapter.LastGeneratedCommand.CommandText);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment