public void Verify_Delete()
{
using (var transaction = new TransactionScope())
{
// PrepareDb
InsertCustomer();
using (var northwindContext = new NorthwindEntities())
{
var customer = northwindContext
.Customers
.Single(c => c.CustomerID == customerId);
northwindContext.Customers.DeleteObject(customer);
northwindContext.SaveChanges();
}
// Verify the customer was updated
Assert.Equal(
0,
ExecuteScalar(
string.Format(
"SELECT COUNT(*) FROM Customers WHERE CustomerID = '{0}' AND CompanyName = '{1}'",
customerId,
companyName)));
}
}
public void Verify_Update()
{
const string newCompanyName = "New Company Name";
using(var transaction = new TransactionScope())
{
// PrepareDb
InsertCustomer();
using (var northwindContext = new NorthwindEntities())
{
var customer = northwindContext
.Customers
.Single(c => c.CustomerID == customerId);
customer.CompanyName = newCompanyName;
customer.Location = SpatialServices.Instance.GeographyFromText("POINT (-122.191667 47.685833)");
northwindContext.SaveChanges();
}
// Verify the customer was updated
Assert.Equal(
1,
ExecuteScalar(
string.Format(
"SELECT COUNT(*) " +
"FROM Customers " +
"WHERE CustomerID = '{0}' AND CompanyName = '{1}' AND Location.STAsText() = 'POINT (-122.191667 47.685833)'",
customerId,
newCompanyName)));
}
}
static void Main(string[] args)
{
using (NorthwindEntities context = new NorthwindEntities())
{
context.Customers.ToList();
}
}
public void Verify_TVFs_returning_entities_work()
{
using(var context = new NorthwindEntities())
{
var inTransitOrders = context.fx_OrdersForShippingStatus(ShippingStatus.InTransit);
// because TVFs are composable, we can query over the TVF results on the server instead of in memory.
var orders = inTransitOrders.Where(o => o.ShipCountry == "Poland").ToList();
Assert.Equal(3, orders.Count);
Assert.Contains(10611, orders.Select(o => o.OrderID));
Assert.Contains(10870, orders.Select(o => o.OrderID));
Assert.Contains(10998, orders.Select(o => o.OrderID));
}
}
public void Verify_TVFs_returning_scalar_values_work()
{
using(var context = new NorthwindEntities())
{
var customerLocations = context.fx_CustomerLocationForCountry("Portugal").ToList();
Assert.Equal(2, customerLocations.Count);
Assert.Contains("POINT (-9.19968872070313 38.7638671875)", customerLocations.Select(s => s.AsText()));
Assert.Contains( "POINT (-9.13509541581515 38.7153290459515)", customerLocations.Select(s => s.AsText()));
}
}
public void Verify_stored_procedures_with_multiple_resultsets_work()
{
using (var context = new NorthwindEntities())
{
var query = context.CustomerWithRecentOrders("ALFKI");
Assert.Equal("ALFKI", query.Single().CustomerID);
var orders = query
.GetNextResult<CustomerWithRecentOrders_OrderInfo>()
.ToList();
var expectedOrderIds = new int[] { 11011, 10952, 10835, 10702, 10692, 10643 };
var actualResult = expectedOrderIds.Zip(orders, (oid, order) => oid == order.OrderID).ToList();
Assert.True(expectedOrderIds.Length == actualResult.Count && actualResult.All(r => r));
}
}
public void Verify_store_DbGeometry_method_works()
{
using(var context = new NorthwindEntities())
{
var query = from o in context.Orders
where SampleSqlFunctions.Astextzm(o.ContainerSize) == "POLYGON ((0 0, 9 0, 9 9, 0 9, 0 0))"
select o;
Assert.Equal(73, query.Count());
}
}
public void Verify_DbGeometry_static_method_translated_correctly()
{
using (var context = new NorthwindEntities())
{
var query = from o in context.Orders
where o.ContainerSize.SpatialEquals(DbGeometry.FromText("POLYGON ((0 0, 9 0, 9 9, 0 9, 0 0))", 0))
select o;
Assert.Equal(73, query.Count());
}
}
public void Verify_static_store_DbGeography_method_translated_correctly()
{
var seattleLocation = SpatialServices.Instance.GeographyFromText("POINT(-122.333056 47.609722)");
var expectedResults = new string[]
{
"BOTTM",
"LAUGB",
"LONEP",
"THEBI",
"TRAIH",
"WHITC",
};
using (var context = new NorthwindEntities())
{
var query = from c in context.Customers
where c.Location.Distance(SampleSqlFunctions.Pointgeography(47.609722, -122.333056, 4326)) < 250000 // 250 km
select c;
var caseIdx = 0;
foreach (var customer in query)
{
Assert.Equal(expectedResults[caseIdx++], customer.CustomerID);
}
}
}
private static void InsertCustomer()
{
using (var northwindContext = new NorthwindEntities())
{
northwindContext.Customers.AddObject(
new Customer()
{
CustomerID = customerId,
CompanyName = companyName,
Location = SpatialServices.Instance.GeographyFromText("POINT (17.038333 51.107778)")
});
northwindContext.SaveChanges();
}
}
public void Verify_DbGeometry_can_be_materialized()
{
using (var context = new NorthwindEntities())
{
var order = context.Orders.OrderBy(o => o.OrderID).First();
Assert.Equal(10248, order.OrderID);
Assert.Equal("POLYGON ((0 0, 1 0, 1 1, 0 1, 0 0))", order.ContainerSize.AsText());
}
}
public void Verify_query_containing_StartsWith_works()
{
var expected =
new string[]
{
"La corne d'abondance",
"La maison d'Asie",
"Laughing Bacchus Wine Cellars",
"Lazy K Kountry Store"
};
using (var context = new NorthwindEntities())
{
var query = from c in context.Customers
where c.CompanyName.StartsWith("La")
select c;
var caseIdx = 0;
foreach (var customer in query)
{
Assert.Equal(expected[caseIdx], customer.CompanyName);
caseIdx++;
}
Assert.Equal(4, caseIdx);
}
}
public void Verify_query_with_provider_store_function_works()
{
var expected =
new string[]
{
"Aroun... - Company",
"B's B... - Company",
"Conso... - Company",
"Easte... - Company",
"North... - Company",
"Seven... - Company"
};
using (var context = new NorthwindEntities())
{
var query =
from c in context.Customers
where c.Address.City == "London"
select SampleSqlFunctions.Stuff(c.CompanyName, 6, c.CompanyName.Length - 5, "... - Company");
var caseIdx = 0;
foreach (var result in query)
{
Assert.Equal(expected[caseIdx], result);
caseIdx++;
}
Assert.Equal(6, caseIdx);
}
}
public void Verify_parametrized_Linq_query_works()
{
using (var context = new NorthwindEntities())
{
var query = from c in context.Customers
where c.CustomerID == "ALFKI"
select c;
var customer = query.Single();
Assert.Equal(expectedResults[0].Name, customer.CompanyName);
Assert.Equal(expectedResults[0].Location, customer.Location.AsText());
}
}
public void Verify_TVFs_returning_complex_values_work()
{
DbGeography londonLocation = DbGeography.FromText("POINT(-0.5 51.50)");
using(var context = new NorthwindEntities())
{
var suppliersNearLondon = context.fx_SuppliersWithinRange(500, londonLocation).ToList();
Assert.Equal(7, suppliersNearLondon.Count);
Assert.Contains(1, suppliersNearLondon.Select(s => s.SupplierID));
Assert.Contains(12, suppliersNearLondon.Select(s => s.SupplierID));
Assert.Contains(13, suppliersNearLondon.Select(s => s.SupplierID));
Assert.Contains(18, suppliersNearLondon.Select(s => s.SupplierID));
Assert.Contains(22, suppliersNearLondon.Select(s => s.SupplierID));
Assert.Contains(27, suppliersNearLondon.Select(s => s.SupplierID));
Assert.Contains(28, suppliersNearLondon.Select(s => s.SupplierID));
}
}
public void Verify_DbGeography_instance_property_translated_correctly()
{
using (var context = new NorthwindEntities())
{
var query = from c in context.Customers
where c.Location.Latitude < 0
select c;
Assert.Equal(9, query.Count());
}
}
public void Verify_Update_DbGeography()
{
using (var transaction = new TransactionScope())
{
int orderID;
using (var northwindContext = new NorthwindEntities())
{
var order = northwindContext
.Orders
.OrderBy(o => o.OrderID)
.First();
orderID = order.OrderID;
order.ContainerSize = SpatialServices.Instance.GeometryFromText("LINESTRING (100 100, 20 180, 180 180)");
northwindContext.SaveChanges();
}
// Verify the customer was updated
Assert.Equal(
1,
ExecuteScalar(
string.Format(
"SELECT COUNT(*) FROM Orders WHERE OrderID = '{0}' AND ContainerSize.STAsText() = 'LINESTRING (100 100, 20 180, 180 180)'",
orderID)));
}
}
