public void GeometryJoinWhereContains()
{
var query = @"
select
i.c36_geometry_linestring,
o.Geometry
from
ArubaContext.Runs as o
left outer join
ArubaContext.AllTypes as i
on
Edm.SpatialContains(
i.c36_geometry_linestring,
o.Geometry)
where
Edm.IsClosedSpatial(i.c36_geometry_linestring)" ;
// verifying that the correct sql is generated and the linestrings returned are closed per the query
using (var db = new ArubaContext())
using (var reader = QueryTestHelpers.EntityCommandSetup(db, query, "STContains"))
{
VerifyValueCondition(reader, o =>
{
var g = (DbGeometry)o;
var isClosed = DbSpatialServices.Default.GetIsClosed(g);
return(isClosed != null && (bool)isClosed);
});
}
}
public void Skip_limit_distinct()
{
var query = @"
SELECT DISTINCT o.FirstName as a
FROM ArubaContext.Owners as o
ORDER BY a SKIP 1 LIMIT 1";
var expectedSql = @"
SELECT TOP (1)
[Distinct1].[C1] AS [C1],
[Distinct1].[FirstName] AS [FirstName]
FROM ( SELECT [Distinct1].[FirstName] AS [FirstName], [Distinct1].[C1] AS [C1], row_number() OVER (ORDER BY [Distinct1].[FirstName] ASC) AS [row_number]
FROM ( SELECT DISTINCT
[Extent1].[FirstName] AS [FirstName],
1 AS [C1]
FROM [dbo].[ArubaOwners] AS [Extent1]
) AS [Distinct1]
) AS [Distinct1]
WHERE [Distinct1].[row_number] > 1
ORDER BY [Distinct1].[FirstName] ASC";
// verifying that there is 1 result returned
using (var db = new ArubaContext())
{
using (var reader = QueryTestHelpers.EntityCommandSetup(db, query, expectedSql))
{
VerifySortDescAndCountString(reader, 1);
}
}
}
public void Multiple_sort_keys()
{
var query = @"
SELECT o.id, o.FirstName, o.LastName
FROM ArubaContext.Owners as o
ORDER BY o.FirstName ASC, o.LastName DESC SKIP 3 LIMIT 4";
var expectedSql = @"
SELECT TOP (4)
[Extent1].[Id] AS [Id],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName]
FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[FirstName] AS [FirstName], [Extent1].[LastName] AS [LastName], row_number() OVER (ORDER BY [Extent1].[FirstName] ASC, [Extent1].[LastName] DESC) AS [row_number]
FROM [dbo].[ArubaOwners] AS [Extent1]
) AS [Extent1]
WHERE [Extent1].[row_number] > 3
ORDER BY [Extent1].[FirstName] ASC, [Extent1].[LastName] DESC";
// verifying that there are 2 results returned
using (var db = new ArubaContext())
{
using (var db2 = new ArubaContext())
{
using (var reader = QueryTestHelpers.EntityCommandSetup(db2, query, expectedSql))
{
var expectedResults = db.Owners.ToList().OrderBy(o => o.FirstName).ThenByDescending(o => o.LastName)
.Skip(3).Take(4).Select(o => o.Id);
VerifyAgainstBaselineResults(reader, expectedResults);
}
}
}
}
public void Skip_limit_group_by()
{
var query = @"
SELECT o.FirstName as c
FROM ArubaContext.Owners as o
GROUP BY o.FirstName
ORDER BY c DESC SKIP 1 LIMIT 2";
var expectedSql = @"
SELECT TOP (2)
[Project2].[C1] AS [C1],
[Project2].[FirstName] AS [FirstName]
FROM ( SELECT [Project2].[FirstName] AS [FirstName], [Project2].[C1] AS [C1], row_number() OVER (ORDER BY [Project2].[FirstName] DESC) AS [row_number]
FROM ( SELECT
[Distinct1].[FirstName] AS [FirstName],
1 AS [C1]
FROM ( SELECT DISTINCT
[Extent1].[FirstName] AS [FirstName]
FROM [dbo].[ArubaOwners] AS [Extent1]
) AS [Distinct1]
) AS [Project2]
) AS [Project2]
WHERE [Project2].[row_number] > 1
ORDER BY [Project2].[FirstName] DESC";
// verifying that there are 2 results returned and they are sorted in Descending order
using (var db = new ArubaContext())
{
using (var reader = QueryTestHelpers.EntityCommandSetup(db, query, expectedSql))
{
VerifySortDescAndCountString(reader, 2);
}
}
}
public void GeometryClosestDistance()
{
var query = @"
select
Edm.Distance(
i.[Geometry],
o.[c32_geometry]
) as [Distance]
from
ArubaContext.[Runs] as [i],
ArubaContext.[AllTypes] as [o]
where
i.[Geometry] is not null
order by
Edm.Distance(
i.[Geometry],
o.[c32_geometry]
) asc" ;
// verifying that the distances returned match the distances returned by a matching linq query
using (var db = new ArubaContext())
using (var db2 = new ArubaContext())
using (var reader = QueryTestHelpers.EntityCommandSetup(db2, query, "STDistance"))
{
var allTypes = db.AllTypes.Select(s => s.c32_geometry).ToList();
var runs = db.Runs.Select(s => s.Geometry);
var expectedResults = from atGeometry in allTypes
from rGeometry in runs
orderby DbSpatialServices.Default.Distance(atGeometry, rGeometry) ascending
select DbSpatialServices.Default.Distance(atGeometry, rGeometry);
VerifyAgainstBaselineResults(reader, expectedResults);
}
}
public void Nested_limit()
{
var query = @"
SELECT C.Id
FROM (
SELECT o.Id AS Id
FROM ArubaContext.Owners as o
ORDER BY o.Id LIMIT 5
) AS C
ORDER BY C.Id DESC LIMIT 3";
var expectedSql = @"
SELECT TOP (3)
[Limit1].[Id] AS [Id]
FROM ( SELECT TOP (5) [Extent1].[Id] AS [Id]
FROM [dbo].[ArubaOwners] AS [Extent1]
ORDER BY [Extent1].[Id] ASC
) AS [Limit1]
ORDER BY [Limit1].[Id] DESC";
// verifying that there are 3 results returned and they are sorted in descending order
using (var db = new ArubaContext())
{
using (var reader = QueryTestHelpers.EntityCommandSetup(db, query, expectedSql))
{
VerifySortDescAndCountInt(reader, 3);
}
}
}
public void Basic_skip_limit()
{
var query = @"
SELECT C.Id, C.Address
FROM OFTYPE (ArubaContext.Configs, CodeFirstNamespace.ArubaMachineConfig) AS C
ORDER BY C.Id DESC SKIP 3 LIMIT 2";
var expectedSql = @"
SELECT TOP (2)
[Filter1].[Id] AS [Id],
[Filter1].[Address] AS [Address]
FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[Address] AS [Address], row_number() OVER (ORDER BY [Extent1].[Id] DESC) AS [row_number]
FROM [dbo].[ArubaConfigs] AS [Extent1]
WHERE [Extent1].[Discriminator] = N'ArubaMachineConfig'
) AS [Filter1]
WHERE [Filter1].[row_number] > 3
ORDER BY [Filter1].[Id] DESC";
// verifying that there are 2 results returned and they are sorted in descending order
using (var db = new ArubaContext())
{
using (var db2 = new ArubaContext())
{
using (var reader = QueryTestHelpers.EntityCommandSetup(db2, query, expectedSql))
{
var expectedResults =
db.Configs.ToList().OfType <ArubaMachineConfig>().OrderByDescending(o => o.Id).Skip(3).Take(2)
.Select(o => o.Id).ToList();
Assert.Equal(expectedResults.Count(), 2);
VerifyAgainstBaselineResults(reader, expectedResults);
}
}
}
}
public void Select_desc_skip_limit_with_params()
{
var query = @"
select o.Id
from ArubaContext.Owners as o
order by o.Id desc skip @pInt16 LIMIT @pInt64";
var prm1 = new EntityParameter("pInt16", DbType.Int16);
var prm2 = new EntityParameter("pInt64", DbType.Int64);
prm1.Value = 5;
prm2.Value = 2;
// verifying that there are 2 results returned and they are sorted in descending order
using (var db = new ArubaContext())
{
using (var db2 = new ArubaContext())
{
using (var reader = QueryTestHelpers.EntityCommandSetup(db2, query, null, prm1, prm2))
{
var expectedResults = db.Owners.ToList().OrderByDescending(o => o.Id).Skip(5).Take(2).Select(o => o.Id).ToList();
Assert.Equal(expectedResults.Count, 2);
VerifyAgainstBaselineResults(reader, expectedResults);
}
}
}
}
public void Select_desc_skip_limit_with_params_and_literal()
{
var query = @"
select o.Id
from ArubaContext.Owners as o
order by o.Id desc skip @pInt16 LIMIT 5";
var expectedSql = @"
SELECT TOP (5)
[Extent1].[Id] AS [Id]
FROM ( SELECT [Extent1].[Id] AS [Id], row_number() OVER (ORDER BY [Extent1].[Id] DESC) AS [row_number]
FROM [dbo].[ArubaOwners] AS [Extent1]
) AS [Extent1]
WHERE [Extent1].[row_number] > @pInt16
ORDER BY [Extent1].[Id] DESC";
var prm1 = new EntityParameter("pInt16", DbType.Int16);
prm1.Value = 5;
// verifying that there are 5 results returned and they are sorted in descending order
using (var db = new ArubaContext())
{
using (var reader = QueryTestHelpers.EntityCommandSetup(db, query, expectedSql, prm1))
{
VerifySortDescAndCountInt(reader, 5);
}
}
}
public void Select_top_nested_asc_and_desc_with_params()
{
var query = @"
SELECT TOP (@pInt64) C.OwnerId
FROM (
SELECT TOP (@pInt16) o.Id AS OwnerId
FROM ArubaContext.Owners as o
ORDER BY o.Id ASC
) AS C
ORDER BY C.OwnerId DESC";
string expectedSql = @"
SELECT TOP (@pInt64)
[Limit1].[Id] AS [Id]
FROM ( SELECT TOP (@pInt16) [Extent1].[Id] AS [Id]
FROM [dbo].[ArubaOwners] AS [Extent1]
ORDER BY [Extent1].[Id] ASC
) AS [Limit1]
ORDER BY [Limit1].[Id] DESC";
var prm1 = new EntityParameter("pInt16", DbType.Int16);
var prm2 = new EntityParameter("pInt64", DbType.Int64);
prm1.Value = 5;
prm2.Value = 3;
// verifying that there are 3 results returned and they are sorted in descending order
using (var db = new ArubaContext())
{
using (var reader = QueryTestHelpers.EntityCommandSetup(db, query, expectedSql, prm1, prm2))
{
VerifySortDescAndCountInt(reader, 3);
}
}
}
public void Select_top_nested_with_params()
{
var query = @"
SELECT TOP (@pInt64) C.OwnerId
FROM (
SELECT TOP (@pInt16) o.Id AS OwnerId
FROM ArubaContext.Owners as o
ORDER BY o.Id
) AS C
ORDER BY C.OwnerId DESC";
var prm1 = new EntityParameter("pInt16", DbType.Int16);
var prm2 = new EntityParameter("pInt64", DbType.Int64);
prm1.Value = 5;
prm2.Value = 2;
// verifying that there are 2 results returned and they are sorted in descending order
using (var db = new ArubaContext())
{
using (var reader = QueryTestHelpers.EntityCommandSetup(db, query, null, prm1, prm2))
{
VerifySortDescAndCountInt(reader, 2);
}
}
}
public void Select_top_order_by_reduced_with_inheritance()
{
var query = @"
SELECT TOP (3) C.Id, C.Address
FROM OFTYPE (ArubaContext.Configs, CodeFirstNamespace.ArubaMachineConfig) AS C
ORDER BY C.Id DESC";
var expectedSql = @"
SELECT TOP (3)
[Extent1].[Id] AS [Id],
[Extent1].[Address] AS [Address]
FROM [dbo].[ArubaConfigs] AS [Extent1]
WHERE [Extent1].[Discriminator] = N'ArubaMachineConfig'
ORDER BY [Extent1].[Id] DESC";
// verifying that there are 3 results returned and they are sorted in descending order
// using a model with inheritance
using (var db = new ArubaContext())
{
using (var reader = QueryTestHelpers.EntityCommandSetup(db, query, expectedSql))
{
VerifySortDescAndCountInt(reader, 3);
}
}
}
public void Select_top_distinct()
{
var query = @"
SELECT DISTINCT TOP (2) o.FirstName AS name
FROM ArubaContext.Owners as o
ORDER BY name DESC";
var expectedSql = @"
SELECT TOP (2)
[Distinct1].[C1] AS [C1],
[Distinct1].[FirstName] AS [FirstName]
FROM ( SELECT DISTINCT
[Extent1].[FirstName] AS [FirstName],
1 AS [C1]
FROM [dbo].[ArubaOwners] AS [Extent1]
) AS [Distinct1]
ORDER BY [Distinct1].[FirstName] DESC";
// verifying that there are 2 results returned, that they are sorted in descending order
// and they are distinct
using (var db = new ArubaContext())
{
using (var reader = QueryTestHelpers.EntityCommandSetup(db, query, expectedSql))
{
VerifySortDescAndCountString(reader, 2, distinct: true);
}
}
}
public void GeometryPointsWithinPolygon()
{
var query = @"
select value
g.c32_geometry
from
ArubaContext.AllTypes as g
where
Edm.SpatialWithin(
g.c32_geometry,
GeometryFromText(""MULTILINESTRING ((10 20, 15 20, 15 25, 10 25, 10 20), (12 22, 13 22, 13 23, 12 23, 12 22))"", 32768))";
// verifying that the points returned are within the specified polygon
using (var db = new ArubaContext())
using (var reader = QueryTestHelpers.EntityCommandSetup(db, query, "STWithin"))
{
var shape = DbGeometry.MultiLineFromText(
"MULTILINESTRING ((10 20, 15 20, 15 25, 10 25, 10 20), (12 22, 13 22, 13 23, 12 23, 12 22))", 32768);
VerifyValueCondition(reader, o =>
{
var g = (DbGeometry)o;
return(DbSpatialServices.Default.Within(g, shape));
});
}
}
public void Nested_skip_limits_in_from()
{
var query = @"
SELECT TOP.Id
FROM (
SELECT o.Alias, o.Id
FROM ArubaContext.Owners AS o
ORDER BY o.Id desc SKIP 2 LIMIT 5
) AS TOP
ORDER BY TOP.Id SKIP 1 LIMIT 2";
var expectedSql = @"
SELECT TOP (2)
[Limit1].[Id] AS [Id]
FROM ( SELECT [Limit1].[Id] AS [Id], row_number() OVER (ORDER BY [Limit1].[Id] ASC) AS [row_number]
FROM ( SELECT TOP (5) [Extent1].[Id] AS [Id]
FROM ( SELECT [Extent1].[Id] AS [Id], row_number() OVER (ORDER BY [Extent1].[Id] DESC) AS [row_number]
FROM [dbo].[ArubaOwners] AS [Extent1]
) AS [Extent1]
WHERE [Extent1].[row_number] > 2
ORDER BY [Extent1].[Id] DESC
) AS [Limit1]
) AS [Limit1]
WHERE [Limit1].[row_number] > 1
ORDER BY [Limit1].[Id] ASC";
// verifying that there are 2 integer results returned
using (var db = new ArubaContext())
{
using (var reader = QueryTestHelpers.EntityCommandSetup(db, query, expectedSql))
{
VerifyTypeAndCount(reader, 2, "Int32");
}
}
}
public void Skip_no_limit_with_inheritance()
{
var query = @"
SELECT Config.Id, Config.Address
FROM OFTYPE (ArubaContext.Configs, CodeFirstNamespace.ArubaMachineConfig) AS Config
ORDER BY Config.Id DESC SKIP 1";
var expectedSql = @"
SELECT
[Filter1].[Id] AS [Id],
[Filter1].[Address] AS [Address]
FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[Address] AS [Address], row_number() OVER (ORDER BY [Extent1].[Id] DESC) AS [row_number]
FROM [dbo].[ArubaConfigs] AS [Extent1]
WHERE [Extent1].[Discriminator] = N'ArubaMachineConfig'
) AS [Filter1]
WHERE [Filter1].[row_number] > 1
ORDER BY [Filter1].[Id] DESC";
// verify that the first 1 is skipped and that the results are sorted in descending order
using (var db = new ArubaContext())
{
using (var db2 = new ArubaContext())
{
using (var reader = QueryTestHelpers.EntityCommandSetup(db2, query, expectedSql))
{
var expectedResults = db.Configs.OfType <ArubaMachineConfig>().ToList().OrderByDescending(c => c.Id).Skip(1)
.Select(c => c.Id);
VerifyAgainstBaselineResults(reader, expectedResults);
}
}
}
}
public void GeometryPointsIntersectWithBufferOfPolygon()
{
var query = @"
select value
g.c32_geometry
from
ArubaContext.AllTypes as g
where
Edm.SpatialIntersects(
GeometryFromText(""POLYGON ((11 20, 10 20, 10 21, 11 20))"", 32768),
Edm.SpatialBuffer(g.c32_geometry, 5)
)" ;
var polygon = DbGeometry.PolygonFromText("POLYGON ((11 20, 10 20, 10 21, 11 20))", 32768);
// verifying that the results returned intersect the buffer (5)
using (var db = new ArubaContext())
using (var reader = QueryTestHelpers.EntityCommandSetup(db, query, "STIntersects"))
{
VerifyValueCondition(reader, o =>
{
var g = (DbGeometry)o;
return(DbSpatialServices.Default.Intersects(polygon, DbSpatialServices.Default.Buffer(g, 5)));
});
}
}
public void GeographyPointsIntersectsPolygon()
{
var query = @"
select value
g.c31_geography
from
ArubaContext.AllTypes as g
where
Edm.SpatialIntersects(
g.c31_geography,
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))";
// verifying that the results that are returned intersect the specified polygons
using (var db = new ArubaContext())
using (var reader = QueryTestHelpers.EntityCommandSetup(db, query, "STIntersect"))
{
var polygon = DbGeography.MultiPolygonFromText(
"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);
VerifyValueCondition(reader, o =>
{
var g = (DbGeography)o;
return(DbSpatialServices.Default.Intersects(polygon, g));
});
}
}
public void Edge_case_column_name()
{
var query = @"
SELECT c.Id as [row_number]
FROM ArubaContext.Owners as c
ORDER BY [row_number] skip 2 limit 3";
var expectedSql = @"
SELECT TOP (3)
[Extent1].[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]
) AS [Extent1]
WHERE [Extent1].[row_number] > 2
ORDER BY [Extent1].[Id] ASC";
// verifying that there are 3 results returned and they are sorted in ascending order
using (var db = new ArubaContext())
{
using (var reader = QueryTestHelpers.EntityCommandSetup(db, query, expectedSql))
{
VerifySortAscAndCountInt(reader, 3);
}
}
}
public void GeometryPolygonsIntersectionAndDisjointAnother()
{
var query = @"
select value
g.c32_geometry
from
ArubaContext.AllTypes as g
where
Edm.SpatialDisjoint(
GeometryFromText(""POLYGON ((13 22, 12 22, 12 23, 13 22))"", 32768),
Edm.SpatialIntersection(
g.c32_geometry,
GeometryFromText(""MULTILINESTRING ((12 22, 15 22, 15 25, 12 25, 12 22), (13 23, 14 23, 14 24, 13 24, 13 23))"", 32768)
)
)" ;
var polygon = DbGeometry.PolygonFromText("POLYGON ((13 22, 12 22, 12 23, 13 22))", 32768);
var multiString =
DbGeometry.MultiLineFromText(
"MULTILINESTRING ((12 22, 15 22, 15 25, 12 25, 12 22), (13 23, 14 23, 14 24, 13 24, 13 23))", 32768);
// verifying that the results that are returned, when intersected with the given lineStrings are disjoint from the
// specified polygon as stated in the query
using (var db = new ArubaContext())
using (var reader = QueryTestHelpers.EntityCommandSetup(db, query, "STDisjoint"))
{
VerifyValueCondition(reader, o =>
{
var g = (DbGeometry)o;
return(DbSpatialServices.Default.Disjoint(polygon, DbSpatialServices.Default.Intersection(g, multiString)));
});
}
}
public void Skip_with_no_limit_and_multiset()
{
var query = @"
SELECT c.Id
FROM ArubaContext.Owners AS c
ORDER BY c.Id DESC SKIP 2";
var expectedSql = @"
SELECT
[Extent1].[Id] AS [Id]
FROM ( SELECT [Extent1].[Id] AS [Id], row_number() OVER (ORDER BY [Extent1].[Id] DESC) AS [row_number]
FROM [dbo].[ArubaOwners] AS [Extent1]
) AS [Extent1]
WHERE [Extent1].[row_number] > 2
ORDER BY [Extent1].[Id] DESC";
using (var db = new ArubaContext())
{
using (var db2 = new ArubaContext())
{
using (var reader = QueryTestHelpers.EntityCommandSetup(db2, query, expectedSql))
{
var expectedResults = db.Owners.ToList().OrderByDescending(o => o.Id).Skip(2).Select(o => o.Id);
VerifyAgainstBaselineResults(reader, expectedResults);
}
}
}
}
public void Select_top_nested_order_by()
{
var query = @"
SELECT TOP (4) C.Id
FROM ( SELECT TOP (14) o.Id AS Id
FROM ArubaContext.Owners as o
ORDER BY o.Id
) AS C
ORDER BY C.Id DESC";
var expectedSql = @"
SELECT TOP (4)
[Limit1].[Id] AS [Id]
FROM ( SELECT TOP (14) [Extent1].[Id] AS [Id]
FROM [dbo].[ArubaOwners] AS [Extent1]
ORDER BY [Extent1].[Id] ASC
) AS [Limit1]
ORDER BY [Limit1].[Id] DESC";
// verifying that there are 4 results returned and they are sorted in descending order
// with nested TOP statements
using (var db = new ArubaContext())
{
using (var reader = QueryTestHelpers.EntityCommandSetup(db, query, expectedSql))
{
VerifySortDescAndCountInt(reader, 4);
}
}
}
public void Complicated_order_by()
{
var query = @"
SELECT 1, d.Id
FROM ArubaContext.Owners as c, ArubaContext.Owners as d
WHERE c.Id == d.Id
ORDER BY anyelement(select value c.Id * (-1) from {d} as c) SKIP 4 LIMIT 3";
var expectedSql = @"
SELECT TOP (3)
[Project1].[Id] AS [Id],
[Project1].[C2] AS [C1]
FROM ( SELECT [Project1].[C1] AS [C1], [Project1].[Id] AS [Id], [Project1].[C2] AS [C2], row_number() OVER (ORDER BY [Project1].[C1] ASC) AS [row_number]
FROM ( SELECT
[Extent1].[Id] * -1 AS [C1],
[Extent1].[Id] AS [Id],
1 AS [C2]
FROM [dbo].[ArubaOwners] AS [Extent1]
) AS [Project1]
) AS [Project1]
WHERE [Project1].[row_number] > 4
ORDER BY [Project1].[C1] ASC";
// verifying that there are 3 results returned and they are sorted in descending order
using (var db = new ArubaContext())
{
using (var reader = QueryTestHelpers.EntityCommandSetup(db, query, expectedSql))
{
VerifySortDescAndCountInt(reader, 3);
}
}
}
public void Anyelement_over_skip_limit()
{
var query = @"
ANYELEMENT (
SELECT C.Id
FROM ArubaContext.Owners AS C
ORDER BY C.Id SKIP 3 LIMIT 2
)";
var expectedSql = @"
SELECT
[Element1].[Id] AS [Id]
FROM ( SELECT 1 AS X ) AS [SingleRowTable1]
LEFT OUTER JOIN (SELECT TOP (1) [element].[Id] AS [Id]
FROM ( SELECT TOP (2)
[Extent1].[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]
) AS [Extent1]
WHERE [Extent1].[row_number] > 3
ORDER BY [Extent1].[Id] ASC
) AS [element] ) AS [Element1] ON 1 = 1" ;
// verifying that there is 1 result returned and that it is an integer
using (var db = new ArubaContext())
{
using (var reader = QueryTestHelpers.EntityCommandSetup(db, query, expectedSql))
{
VerifyTypeAndCount(reader, 1, "Int32");
}
}
}
public void Nested_projections_list()
{
var query = @"
SELECT VALUE (
SELECT VALUE TOP (2) d.Id
FROM ArubaContext.Owners AS d
WHERE d.Id > c.Id
ORDER BY d.Id
) AS top
FROM ArubaContext.Owners AS c
ORDER BY c.Id skip 5 limit 2";
var expectedSql = @"
SELECT
[Project2].[Id] AS [Id],
[Project2].[C1] AS [C1],
[Project2].[Id1] AS [Id1]
FROM ( SELECT
[Limit1].[Id] AS [Id],
[Limit2].[Id] AS [Id1],
CASE WHEN ([Limit2].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
FROM (SELECT TOP (2) [Extent1].[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]
) AS [Extent1]
WHERE [Extent1].[row_number] > 5
ORDER BY [Extent1].[Id] ASC ) AS [Limit1]
OUTER APPLY (SELECT TOP (2) [Project1].[Id] AS [Id]
FROM ( SELECT
[Extent2].[Id] AS [Id]
FROM [dbo].[ArubaOwners] AS [Extent2]
WHERE [Extent2].[Id] > [Limit1].[Id]
) AS [Project1]
ORDER BY [Project1].[Id] ASC ) AS [Limit2]
) AS [Project2]
ORDER BY [Project2].[Id] ASC, [Project2].[C1] ASC";
// verifying that there are 4 results returned and they are integers
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())
{
Assert.Equal("Int32", ((CollectionType)reader.DataRecordInfo.RecordType.EdmType).TypeUsage.EdmType.Name);
count++;
}
}
Assert.Equal(4, count);
}
}
}
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 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 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 Table_self_referential_except()
{
var query = @"
ArubaContext.Owners
EXCEPT
ArubaContext.Owners";
// verifying that there are no results returned
using (var db = new ArubaContext())
{
using (var reader = QueryTestHelpers.EntityCommandSetup(db, query))
{
VerifySortAscAndCountInt(reader, 0);
}
}
}
public void Except_with_nulls()
{
var query = @"
{1, null, null, 5} except {5, null, 7}";
// verifying that { 1 } is returned
using (var db = new ArubaContext())
{
using (var reader = QueryTestHelpers.EntityCommandSetup(db, query))
{
VerifyAgainstBaselineResults(reader, new List <int> {
1
});
}
}
}
