public void Scalar_Function_Let_Nested() { using (var context = CreateContext()) { var employeeId = 5; var starCount = 3; var emp = (from e in context.Employees let orderCount = NorthwindDbFunctionContext.StarValue(starCount, NorthwindDbFunctionContext.EmployeeOrderCount(employeeId)) where e.EmployeeID == employeeId select new { e.FirstName, OrderCount = orderCount }).Single(); Assert.Equal("Steven", emp.FirstName); Assert.Equal("***42", emp.OrderCount); AssertSql( @"@__starCount_0='3' @__employeeId_1='5' @__employeeId_2='5' SELECT TOP(2) [e].[FirstName], [dbo].StarValue(@__starCount_0, [dbo].EmployeeOrderCount(@__employeeId_1)) AS [OrderCount] FROM [Employees] AS [e] WHERE [e].[EmployeeID] = @__employeeId_2"); } }
public void Scalar_Function_Let_Not_Parameter() { var employeeId = 5; using (var context = CreateContext()) { var emp = (from e in context.Employees let orderCount = NorthwindDbFunctionContext.EmployeeOrderCount(employeeId) where e.EmployeeID == employeeId select new { e.FirstName, OrderCount = orderCount }).Single(); Assert.Equal("Steven", emp.FirstName); Assert.Equal(42, emp.OrderCount); AssertSql( @"@__employeeId_0='5' @__employeeId_1='5' SELECT TOP(2) [e].[FirstName], [dbo].EmployeeOrderCount(@__employeeId_0) AS [OrderCount] FROM [Employees] AS [e] WHERE [e].[EmployeeID] = @__employeeId_1"); } }
public void Scalar_Function_ClientEval_Method_As_Translateable_Method_Parameter() { using (var context = CreateContext()) { Assert.Throws <NotImplementedException>(() => (from e in context.Employees where e.EmployeeID == 5 select new { e.FirstName, OrderCount = NorthwindDbFunctionContext.EmployeeOrderCount(AddFive(e.EmployeeID - 5)) }).Single()); } }
public void Scalar_Nested_Function_UDF_Client() { using (var context = CreateContext()) { var results = (from e in context.Employees where 127 == NorthwindDbFunctionContext.EmployeeOrderCountWithClient(AddOne(e.EmployeeID)) select e.EmployeeID).Single(); Assert.Equal(2, results); AssertSql( @"SELECT [e].[EmployeeID] FROM [Employees] AS [e]"); } }
public void Scalar_Nested_Function_UDF_BCL() { using (var context = CreateContext()) { var results = (from e in context.Employees where 127 == NorthwindDbFunctionContext.EmployeeOrderCountWithClient(Math.Abs(e.EmployeeID)) select e.EmployeeID).Single(); Assert.Equal(3, results); AssertSql( @"SELECT TOP(2) [e].[EmployeeID] FROM [Employees] AS [e] WHERE 127 = [dbo].EmployeeOrderCount(ABS([e].[EmployeeID]))"); } }
public void Scalar_Function_Where_Correlated() { using (var context = CreateContext()) { var emp = (from e in context.Employees where NorthwindDbFunctionContext.IsTopEmployee(e.EmployeeID) select e.EmployeeID.ToString().ToLower()).ToList(); Assert.Equal(3, emp.Count); AssertSql( @"SELECT LOWER(CONVERT(VARCHAR(11), [e].[EmployeeID])) FROM [Employees] AS [e] WHERE [dbo].IsTopEmployee([e].[EmployeeID]) = 1"); } }
public void Scalar_Function_Constant_Parameter() { using (var context = CreateContext()) { var employeeId = 5; var emps = context.Employees.Select(e => NorthwindDbFunctionContext.EmployeeOrderCount(employeeId)).ToList(); Assert.Equal(9, emps.Count); AssertSql( @"@__employeeId_0='5' SELECT [dbo].EmployeeOrderCount(@__employeeId_0) FROM [Employees] AS [e]"); } }
public void Scalar_Function_Where_Nested() { using (var context = CreateContext()) { var emp = (from e in context.Employees where e.EmployeeID == NorthwindDbFunctionContext.GetEmployeeWithMostOrdersAfterDate( NorthwindDbFunctionContext.GetReportingPeriodStartDate( NorthwindDbFunctionContext.ReportingPeriod.Winter)) select e).SingleOrDefault(); Assert.NotNull(emp); Assert.True(emp.EmployeeID == 4); AssertSql( @"SELECT TOP(2) [e].[EmployeeID], [e].[City], [e].[Country], [e].[FirstName], [e].[ReportsTo], [e].[Title] FROM [Employees] AS [e] WHERE [e].[EmployeeID] = [dbo].GetEmployeeWithMostOrdersAfterDate([dbo].GetReportingPeriodStartDate(0))"); } }
public void Scalar_Function_Where_Not_Correlated() { using (var context = CreateContext()) { var startDate = DateTime.Parse("1/1/1998"); var emp = (from e in context.Employees where NorthwindDbFunctionContext.GetEmployeeWithMostOrdersAfterDate(startDate) == e.EmployeeID select e).SingleOrDefault(); Assert.NotNull(emp); Assert.True(emp.EmployeeID == 4); AssertSql( @"@__startDate_0='01/01/1998 00:00:00' SELECT TOP(2) [e].[EmployeeID], [e].[City], [e].[Country], [e].[FirstName], [e].[ReportsTo], [e].[Title] FROM [Employees] AS [e] WHERE [dbo].GetEmployeeWithMostOrdersAfterDate(@__startDate_0) = [e].[EmployeeID]"); } }
public void Scalar_Function_Anonymous_Type_Select_Not_Correlated() { using (var context = CreateContext()) { var emp = (from e in context.Employees where e.EmployeeID == 5 select new { e.FirstName, OrderCount = NorthwindDbFunctionContext.EmployeeOrderCount(5) }).Single(); Assert.Equal("Steven", emp.FirstName); Assert.Equal(42, emp.OrderCount); AssertSql( @"SELECT TOP(2) [e].[FirstName], [dbo].EmployeeOrderCount(5) AS [OrderCount] FROM [Employees] AS [e] WHERE [e].[EmployeeID] = 5"); } }
void Scalar_Function_With_Translator_Translates() { using (var context = CreateContext()) { var employeeId = 5; var len = context.Employees.Where(e => e.EmployeeID == employeeId).Select(e => NorthwindDbFunctionContext.MyCustomLength(e.FirstName)).Single(); Assert.Equal(6, len); AssertSql( @"@__employeeId_0='5' SELECT TOP(2) len([e].[FirstName]) FROM [Employees] AS [e] WHERE [e].[EmployeeID] = @__employeeId_0"); } }