public void DateTimeOffest_Day_properly_translated_to_function()
{
using (var context = new ArubaContext())
{
var query = context.AllTypes.Select(a => a.c30_datetimeoffset.Day);
Assert.Contains("DATEPART", query.ToString().ToUpperInvariant());
Assert.Contains("DAY", query.ToString().ToUpperInvariant());
}
}
public void Substring_properly_translated_to_function()
{
using (var context = new ArubaContext())
{
var query1 = context.Owners.Select(o => o.LastName.Substring(1));
var query2 = context.Owners.Select(o => o.LastName.Substring(1, 2));
Assert.Contains("SUBSTRING", query1.ToString().ToUpperInvariant());
Assert.Contains("SUBSTRING", query2.ToString().ToUpperInvariant());
}
}
public void Include_propagation_over_sort()
{
using (var context = new ArubaContext())
{
((IObjectContextAdapter)context).ObjectContext.ContextOptions.LazyLoadingEnabled = false;
var query = context.Owners.Include(o => o.OwnedRun).OrderBy(o => o.Id == 1);
var results = query.ToList();
Assert.NotNull(results.First().OwnedRun);
}
}
public void Include_propagation_over_type_filter()
{
using (var context = new ArubaContext())
{
((IObjectContextAdapter)context).ObjectContext.ContextOptions.LazyLoadingEnabled = false;
var query = context.Configs.Include(o => o.Failures).OfType <ArubaMachineConfig>();
var results = query.ToList();
Assert.True(results.Any(r => r.Failures.Count > 0));
}
}
public void Include_propagation_over_first_or_default()
{
using (var context = new ArubaContext())
{
((IObjectContextAdapter)context).ObjectContext.ContextOptions.LazyLoadingEnabled = false;
var results = context.Configs.Include(o => o.Failures).FirstOrDefault();
Assert.NotNull(results.Failures);
Assert.True(results.Failures.Count > 0);
}
}
public void Materialize_ObjectQuery_throws()
{
using (var context = new ArubaContext())
{
var objectContext = ((IObjectContextAdapter)context).ObjectContext;
var owners = objectContext.CreateQuery <ArubaOwner>("Owners");
Assert.Throws <InvalidOperationException>(() => context.Runs.Select(r => owners).ToList());
context.Runs.Select(r => owners.AsEnumerable()).ToList();
}
}
public void SqlFunctions_scalar_function_translated_properly_to_sql_function()
{
using (var context = new ArubaContext())
{
var query1 = context.AllTypes.Select(a => SqlFunctions.Acos(a.c7_decimal_28_4));
var query2 = context.AllTypes.Select(a => SqlFunctions.Acos(a.c10_float));
Assert.Contains("ACOS", query1.ToString().ToUpperInvariant());
Assert.Contains("ACOS", query2.ToString().ToUpperInvariant());
}
}
public void DateTimeOffset_NullableUtcDateTimeConstant_properly_to_function()
{
using (var context = new ArubaContext())
{
DateTimeOffset?x = DateTimeOffset.Now;
var query = context.AllTypes.Where(a => a.c29_datetime2 < x.Value.UtcDateTime);
Assert.Contains("CONVERT (DATETIME2,", query.ToString().ToUpperInvariant());
Assert.Contains(", 1)", query.ToString().ToUpperInvariant());
}
}
public void Include_from_except_takes_span_information_from_first_collection_ignores_from_second()
{
using (var context = new ArubaContext())
{
((IObjectContextAdapter)context).ObjectContext.ContextOptions.LazyLoadingEnabled = false;
var query = context.Failures.Include(f => f.Bugs).Except(context.Failures.Where(f => f.Id == 1).Include(f => f.Configs));
var results = query.ToList();
Assert.True(results.Any(r => r.Bugs.Count > 0));
Assert.True(results.All(r => r.Configs == null));
}
}
public void Include_from_union_combined()
{
using (var context = new ArubaContext())
{
((IObjectContextAdapter)context).ObjectContext.ContextOptions.LazyLoadingEnabled = false;
var query = context.Failures.Include(f => f.Bugs).Union(context.Failures.Include(f => f.Configs));
var results = query.ToList();
Assert.True(results.Any(r => r.Bugs.Count > 0));
Assert.True(results.Any(r => r.Configs.Count > 0));
}
}
public void Include_one_to_one_relationship()
{
using (var context = new ArubaContext())
{
((IObjectContextAdapter)context).ObjectContext.ContextOptions.LazyLoadingEnabled = false;
var query = context.Owners.Include(o => o.OwnedRun);
var results = query.ToList();
var runsForOwners = context.Owners.Select(r => r.OwnedRun).ToList();
Enumerable.SequenceEqual(runsForOwners, results.Select(r => r.OwnedRun));
}
}
public void IndexOf_properly_translated_to_function()
{
using (var context = new ArubaContext())
{
var query = context.Owners.Select(o => o.LastName.IndexOf("N"));
// translated to charindex() - 1
Assert.True(query.ToString().ToLowerInvariant().Contains("charindex"));
Assert.Contains("CHARINDEX", query.ToString().ToUpperInvariant());
Assert.True(query.ToString().Contains("- 1"));
}
}
public void OrderBy_ThenBy_Skip_Take_lifted_above_type_filter()
{
using (var context = new ArubaContext())
{
var query = context.Configs.OrderByDescending(p => p.Arch).ThenBy(p => p.Id).Skip(5).Take(10).OfType <ArubaMachineConfig>();
QueryTestHelpers.VerifyDbQuery(query, _orderBy_ThenBy_Skip_Take_lifted_above_type_filter_expectedSql);
var results = query.ToList();
var expected = context.Configs.ToList().OrderByDescending(p => p.Arch).ThenBy(p => p.Id).Skip(5).Take(10).OfType <ArubaMachineConfig>().ToList();
QueryTestHelpers.VerifyQueryResult(expected, results, (o, i) => o.Id == i.Id);
}
}
public void Can_materialize_collection()
{
using (var context = new ArubaContext())
{
var query = context.Failures.Where(f => f.Id == 1).Select(f => f.Bugs);
var results = query.ToList();
var bugs = context.Bugs.Where(b => b.Failure.Id == 1).ToList();
Assert.Equal(1, results.Count);
Assert.Equal(bugs.Count, results[0].Count);
}
}
public void Skip_limit_with_duplicates()
{
var query = @"
SELECT i
FROM {1,1,1,2,2} as i
ORDER BY i SKIP 2 LIMIT 4";
var expectedSql = @"
SELECT TOP (4)
[UnionAll4].[C1] AS [C1]
FROM ( SELECT [UnionAll4].[C1] AS [C1], row_number() OVER (ORDER BY [UnionAll4].[C1] ASC) AS [row_number]
FROM (SELECT
[UnionAll3].[C1] AS [C1]
FROM (SELECT
[UnionAll2].[C1] AS [C1]
FROM (SELECT
[UnionAll1].[C1] AS [C1]
FROM (SELECT
1 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable1]
UNION ALL
SELECT
1 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable2]) AS [UnionAll1]
UNION ALL
SELECT
1 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable3]) AS [UnionAll2]
UNION ALL
SELECT
2 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable4]) AS [UnionAll3]
UNION ALL
SELECT
2 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable5]) AS [UnionAll4]
) AS [UnionAll4]
WHERE [UnionAll4].[row_number] > 2
ORDER BY [UnionAll4].[C1] ASC";
// verifying that there are 3 results returned and that they match the expected output
using (var db = new ArubaContext())
{
using (var reader = QueryTestHelpers.EntityCommandSetup(db, query, expectedSql))
{
var values = new List <int> {
1, 2, 2
};
VerifyAgainstBaselineResults(reader, values);
}
}
}
public void Nested_skip_limits_in_select()
{
var query = @"
SELECT (
SELECT o.Id
FROM ArubaContext.Owners AS o
ORDER BY o.Id SKIP 1 LIMIT 2) AS TOP, oo.Id
FROM ArubaContext.Owners as oo
WHERE oo.Id > 3
ORDER BY oo.Id SKIP 2 LIMIT 3";
var expectedSql = @"
SELECT
[Limit1].[Id] AS [Id],
[Project1].[C1] AS [C1],
[Project1].[Id] AS [Id1]
FROM (SELECT TOP (3) [Filter1].[Id] AS [Id]
FROM ( SELECT [Extent1].[Id] AS [Id], row_number() OVER (ORDER BY [Extent1].[Id] ASC) AS [row_number]
FROM [dbo].[ArubaOwners] AS [Extent1]
WHERE [Extent1].[Id] > 3
) AS [Filter1]
WHERE [Filter1].[row_number] > 2
ORDER BY [Filter1].[Id] ASC ) AS [Limit1]
LEFT OUTER JOIN (SELECT TOP (2)
[Extent2].[Id] AS [Id],
1 AS [C1]
FROM ( SELECT [Extent2].[Id] AS [Id], row_number() OVER (ORDER BY [Extent2].[Id] ASC) AS [row_number]
FROM [dbo].[ArubaOwners] AS [Extent2]
) AS [Extent2]
WHERE [Extent2].[row_number] > 1
ORDER BY [Extent2].[Id] ASC ) AS [Project1] ON 1 = 1
ORDER BY [Limit1].[Id] ASC, [Project1].[C1] ASC";
// verifying that there are 3 results returned
using (var db = new ArubaContext())
{
using (var reader = QueryTestHelpers.EntityCommandSetup(db, query, expectedSql))
{
var count = 0;
while (reader.Read())
{
var nestedReader = (DbDataReader)reader.GetValue(0);
while (nestedReader.Read())
{
count++;
}
}
Assert.Equal(3 * 2, count);
}
}
}
public void Truncates_properly_translated_to_function()
{
using (var context = new ArubaContext())
{
var query1 = context.AllTypes.Select(a => Math.Truncate(a.c7_decimal_28_4));
Assert.Contains("ROUND", query1.ToString().ToUpperInvariant());
Assert.Contains("0", query1.ToString().ToUpperInvariant());
var query2 = context.AllTypes.Select(a => Math.Truncate(a.c10_float));
Assert.Contains("ROUND", query2.ToString().ToUpperInvariant());
Assert.Contains("0", query2.ToString().ToUpperInvariant());
}
}
public void String_Insert_properly_translated_to_function()
{
using (var context = new ArubaContext())
{
var expectedSql =
@"SELECT
SUBSTRING([Extent1].[LastName], 1, 2) + N'Foo' + SUBSTRING([Extent1].[LastName], 2 + 1, ( CAST(LEN([Extent1].[LastName]) AS int)) - 2) AS [C1]
FROM [dbo].[ArubaOwners] AS [Extent1]";
var query = context.Owners.Select(o => o.LastName.Insert(2, "Foo"));
QueryTestHelpers.VerifyDbQuery(query, expectedSql);
}
}
public void OrderBy_ThenBy_Skip_lifted_above_projection()
{
using (var context = new ArubaContext())
{
var query = context.Owners.OrderByDescending(p => p.FirstName).ThenBy(p => p.Id).Skip(5).Select(p => new { p.FirstName, p.Id, Foo = p.Id * 5 });
var baseline = context.Owners.Select(p => new { p.FirstName, p.Id, Foo = p.Id * 5 }).OrderByDescending(p => p.FirstName).ThenBy(p => p.Id).Skip(5);
Assert.Equal(baseline.ToString(), query.ToString());
var results = query.ToList();
var expected = context.Owners.ToList().OrderByDescending(p => p.FirstName).ThenBy(p => p.Id).Skip(5).Select(p => new { p.FirstName, p.Id, Foo = p.Id * 5 }).ToList();
QueryTestHelpers.VerifyQueryResult(expected, results, (o, i) => o.Id == i.Id);
}
}
public void Can_materialize_null_complex_type()
{
using (var context = new ArubaContext())
{
var query = context.Runs.Select(r => r.Tasks.Where(t => t.Id < 0).Select(t => t.TaskInfo).FirstOrDefault());
var results = query.ToList();
Assert.IsType <List <ArubaTaskInfo> >(results);
foreach (var result in results)
{
Assert.Null(result);
}
}
}
public void IsNullOrEmpty_translated_properly_to_expression()
{
using (var context = new ArubaContext())
{
var expectedSql =
@"SELECT
CASE WHEN (([Extent1].[LastName] IS NULL) OR (( CAST(LEN([Extent1].[LastName]) AS int)) = 0)) THEN cast(1 as bit) WHEN ( NOT (([Extent1].[LastName] IS NULL) OR (( CAST(LEN([Extent1].[LastName]) AS int)) = 0))) THEN cast(0 as bit) END AS [C1]
FROM [dbo].[ArubaOwners] AS [Extent1]";
var query = context.Owners.Select(o => string.IsNullOrEmpty(o.LastName));
QueryTestHelpers.VerifyDbQuery(query, expectedSql);
}
}
public void OrderBy_ThenBy_lifted_above_type_filter()
{
using (var context = new ArubaContext())
{
var query = context.Configs.OrderByDescending(p => p.Arch).ThenBy(p => p.Id).OfType <ArubaMachineConfig>();
var baseline = context.Configs.OfType <ArubaMachineConfig>().OrderByDescending(p => p.Arch).ThenBy(p => p.Id);
Assert.Equal(baseline.ToString(), query.ToString());
var results = query.ToList();
var expected = context.Configs.ToList().OrderByDescending(p => p.Arch).ThenBy(p => p.Id).OfType <ArubaMachineConfig>().ToList();
QueryTestHelpers.VerifyQueryResult(expected, results, (o, i) => o.Id == i.Id);
}
}
public void Materializing_empty_list_throws()
{
using (var context = new ArubaContext())
{
var query = context.Runs.Select(r => new List <int> {
});
Assert.Throws <NotSupportedException>(() => query.ToList()).
ValidateMessage(
typeof(DbContext).Assembly(),
"ELinq_UnsupportedEnumerableType",
null,
typeof(List <int>).FullName);
}
}
public void OrderBy_ThenBy_Skip_lifted_above_filter_without_clr_null_semantics()
{
using (var context = new ArubaContext())
{
((IObjectContextAdapter)context).ObjectContext.ContextOptions.UseCSharpNullComparisonBehavior = false;
var query = context.Owners.OrderByDescending(p => p.FirstName).ThenBy(p => p.Id).Skip(5).Where(p => p.Id % 2 == 0);
QueryTestHelpers.VerifyDbQuery(query, _orderBy_ThenBy_Skip_lifted_above_filter_without_clr_null_semantics_expectedSql);
var results = query.ToList();
var expected = context.Owners.ToList().OrderByDescending(p => p.FirstName).ThenBy(p => p.Id).Skip(5).Where(p => p.Id % 2 == 0).ToList();
QueryTestHelpers.VerifyQueryResult(expected, results, (o, i) => o.Id == i.Id);
}
}
public void Materializing_empty_list_throws()
{
using (var context = new ArubaContext())
{
var query = context.Runs.Select(r => new List <int> {
});
Assert.Throws <NotSupportedException>(() => query.ToList()).
ValidateMessage(
typeof(DbContext).Assembly,
"ELinq_UnsupportedEnumerableType",
null,
"System.Collections.Generic.List`1[[System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]");
}
}
public void As_on_enum_not_supported()
{
using (var context = new ArubaContext())
{
Assert.Throws <NotSupportedException>(
() => context.AllTypes.Select(a => a.c33_enum as ArubaEnum?).ToList()).ValidateMessage(
typeof(DbContext).Assembly(),
"ELinq_UnsupportedIsOrAs",
null,
"TypeAs",
"System.Data.Entity.TestModels.ArubaModel.ArubaEnum",
"System.Nullable`1[[" + typeof(ArubaEnum).AssemblyQualifiedName + "]]");
}
}
public void As_on_enum_not_supported()
{
using (var context = new ArubaContext())
{
Assert.Throws <NotSupportedException>(
() => context.AllTypes.Select(a => a.c33_enum as ArubaEnum?).ToList()).ValidateMessage(
typeof(DbContext).Assembly(),
"ELinq_UnsupportedIsOrAs",
null,
"TypeAs",
"System.Data.Entity.TestModels.ArubaModel.ArubaEnum",
"System.Nullable`1[[System.Data.Entity.TestModels.ArubaModel.ArubaEnum, EntityFramework.FunctionalTests, Version=0.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a]]");
}
}
public void OrderBy_ThenBy_Skip_Take_lifted_above_filter_with_clr_null_semantics()
{
using (var context = new ArubaContext())
{
((IObjectContextAdapter)context).ObjectContext.ContextOptions.UseCSharpNullComparisonBehavior = true;
var query = context.Owners.OrderByDescending(p => p.FirstName).ThenBy(p => p.Id).Skip(5).Take(10).Where(p => p.Id % 2 == 0);
var baseline = context.Owners.OrderByDescending(p => p.FirstName).ThenBy(p => p.Id).Skip(5).Take(10).Where(p => p.Id % 2 == 0).OrderByDescending(p => p.FirstName).ThenBy(p => p.Id);
Assert.Equal(baseline.ToString(), query.ToString());
var results = query.ToList();
var expected = context.Owners.ToList().OrderByDescending(p => p.FirstName).ThenBy(p => p.Id).Skip(5).Take(10).Where(p => p.Id % 2 == 0).ToList();
QueryTestHelpers.VerifyQueryResult(expected, results, (o, i) => o.Id == i.Id);
}
}
public void GeographyDistanceFromMultiPolygon()
{
var query = @"
select Edm.Distance(g.c31_geography,
CAST(Edm.GeographyFromText(""MULTIPOLYGON (((-136.34518219919187 -45.444057174306, 100.08107983924452 0.029396673640468753, -12.771444237628261 0.029396673640468753, -136.34518219919187 -45.444057174306), (-136.34518219919187 -59.574853846584816, -6.6232329320655019 -12.718185214660565, 93.93286853368177 -12.718185214660565, -136.34518219919187 -59.574853846584816)))"", 4326) AS Edm.Geography))
from ArubaContext.AllTypes as g
where (Edm.Distance(g.c31_geography,
CAST(Edm.GeographyFromText(""MULTIPOLYGON (((-136.34518219919187 -45.444057174306, 100.08107983924452 0.029396673640468753, -12.771444237628261 0.029396673640468753, -136.34518219919187 -45.444057174306), (-136.34518219919187 -59.574853846584816, -6.6232329320655019 -12.718185214660565, 93.93286853368177 -12.718185214660565, -136.34518219919187 -59.574853846584816)))"", 4326) AS Edm.Geography)) <= 600000)";
// verifying that all of the results are less than or equal to 600000 as specified in the query
using (var db = new ArubaContext())
using (var reader = QueryTestHelpers.EntityCommandSetup(db, query, "STDistance"))
{
VerifyValueCondition(reader, a => (double)a <= 600000);
}
}
public void Can_materialize_properties_into_non_mapped_type()
{
using (var context = new ArubaContext())
{
var query = context.Owners.Where(o => o.Id == 1).Select(o => new MyNonMappedType {
Id = o.Id, Name = o.FirstName
});
var results = query.ToList();
var id = context.Owners.Select(o => o.Id).First(r => r == 1);
var name = context.Owners.Where(o => o.Id == 1).Select(o => o.FirstName).First();
Assert.Equal(1, results.Count);
Assert.Equal(id, results[0].Id);
Assert.Equal(name, results[0].Name);
}
}
public void Can_generate_delete_tree_when_ia_required_to_many()
{
DbModel model;
using (var context = new ArubaContext())
{
model
= context
.InternalContext
.CodeFirstModel
.CachedModelBuilder
.BuildDynamicUpdateModel(ProviderRegistry.Sql2008_ProviderInfo);
}
var commandTreeGenerator
= new ModificationCommandTreeGenerator(model);
var commandTrees
= commandTreeGenerator
.GenerateDelete(GetType().Namespace + ".ArubaTask")
.ToList();
Assert.Equal(1, commandTrees.Count());
}
