public async Task Can_select_limit_on_Table_with_References_Async()
{
if (Dialect == Dialect.MySql)
{
return; //= This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
}
#if NETCORE
if (Dialect == Dialect.SqlServer || Dialect == Dialect.SqlServer2012)
{
return; // generates "... WHERE CustomerId IN (SELECT * FROM ...)"
}
// when should generate "... WHERE CustomerId IN (SELECT Id FROM ...)"
// both on .NET and .NET Core
#endif
using (var db = OpenDbConnection())
{
CustomerOrdersUseCase.DropTables(db); //Has conflicting 'Order' table
db.DropAndCreateTable <Order>();
db.DropAndCreateTable <Customer>();
db.DropAndCreateTable <CustomerAddress>();
var customer1 = LoadReferencesTests.GetCustomerWithOrders("1");
db.Save(customer1, references: true);
var customer2 = LoadReferencesTests.GetCustomerWithOrders("2");
db.Save(customer2, references: true);
var results = await db.LoadSelectAsync(db.From <Customer>()
.OrderBy(x => x.Id)
.Limit(1, 1));
//db.GetLastSql().Print();
Assert.That(results.Count, Is.EqualTo(1));
Assert.That(results[0].Name, Is.EqualTo("Customer 2"));
Assert.That(results[0].PrimaryAddress.AddressLine1, Is.EqualTo("2 Humpty Street"));
Assert.That(results[0].Orders.Count, Is.EqualTo(2));
results = await db.LoadSelectAsync(db.From <Customer>()
.Join <CustomerAddress>()
.OrderBy(x => x.Id)
.Limit(1, 1));
db.GetLastSql().Print();
Assert.That(results.Count, Is.EqualTo(1));
Assert.That(results[0].Name, Is.EqualTo("Customer 2"));
Assert.That(results[0].PrimaryAddress.AddressLine1, Is.EqualTo("2 Humpty Street"));
Assert.That(results[0].Orders.Count, Is.EqualTo(2));
}
}
public void Can_select_limit_on_Table_with_References()
{
//This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
if (Dialect == Dialect.AnyMySql)
{
return;
}
//Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
if ((Dialect & Dialect.AnySqlServer) == Dialect)
{
return;
}
using (var db = OpenDbConnection())
{
CustomerOrdersUseCase.DropTables(db); //Has conflicting 'Order' table
db.DropAndCreateTable <Order>();
db.DropAndCreateTable <Customer>();
db.DropAndCreateTable <CustomerAddress>();
var customer1 = LoadReferencesTests.GetCustomerWithOrders("1");
db.Save(customer1, references: true);
var customer2 = LoadReferencesTests.GetCustomerWithOrders("2");
db.Save(customer2, references: true);
var results = db.LoadSelect(db.From <Customer>()
.OrderBy(x => x.Id)
.Limit(1, 1));
//db.GetLastSql().Print();
Assert.That(results.Count, Is.EqualTo(1));
Assert.That(results[0].Name, Is.EqualTo("Customer 2"));
Assert.That(results[0].PrimaryAddress.AddressLine1, Is.EqualTo("2 Humpty Street"));
Assert.That(results[0].Orders.Count, Is.EqualTo(2));
results = db.LoadSelect(db.From <Customer>()
.Join <CustomerAddress>()
.OrderBy(x => x.Id)
.Limit(1, 1));
db.GetLastSql().Print();
Assert.That(results.Count, Is.EqualTo(1));
Assert.That(results[0].Name, Is.EqualTo("Customer 2"));
Assert.That(results[0].PrimaryAddress.AddressLine1, Is.EqualTo("2 Humpty Street"));
Assert.That(results[0].Orders.Count, Is.EqualTo(2));
}
}
public void Can_select_limit_on_Table_with_References()
{
using (var db = OpenDbConnection())
{
CustomerOrdersUseCase.DropTables(db); //Has conflicting 'Order' table
db.DropAndCreateTable <Order>();
db.DropAndCreateTable <Customer>();
db.DropAndCreateTable <CustomerAddress>();
var customer1 = LoadReferencesTests.GetCustomerWithOrders("1");
db.Save(customer1, references: true);
var customer2 = LoadReferencesTests.GetCustomerWithOrders("2");
db.Save(customer2, references: true);
var results = db.LoadSelect <Customer>(q => q
.OrderBy(x => x.Id)
.Limit(1, 1));
//db.GetLastSql().Print();
Assert.That(results.Count, Is.EqualTo(1));
Assert.That(results[0].Name, Is.EqualTo("Customer 2"));
Assert.That(results[0].PrimaryAddress.AddressLine1, Is.EqualTo("2 Humpty Street"));
Assert.That(results[0].Orders.Count, Is.EqualTo(2));
results = db.LoadSelect <Customer>(q => q
.Join <CustomerAddress>()
.OrderBy(x => x.Id)
.Limit(1, 1));
db.GetLastSql().Print();
Assert.That(results.Count, Is.EqualTo(1));
Assert.That(results[0].Name, Is.EqualTo("Customer 2"));
Assert.That(results[0].PrimaryAddress.AddressLine1, Is.EqualTo("2 Humpty Street"));
Assert.That(results[0].Orders.Count, Is.EqualTo(2));
}
}
