public virtual async Task Select_WithOrderBy()
{
using (var tempTable = await Select_CreatePopulatedSelectTable())
using (var queryResult = await DataProvider.ExecuteReaderAsync(
QueryExpression.Select(
new[] { QueryExpression.All() },
from: QueryExpression.Table(tempTable.TableName),
orderBy: new[] { QueryExpression.Descending(QueryExpression.Column("Data")) }
)))
{
Assert.IsTrue(queryResult.HasRows);
Assert.IsTrue(await queryResult.ReadAsync());
Assert.AreEqual(3, queryResult.GetInt32(1));
Assert.IsTrue(await queryResult.ReadAsync());
Assert.AreEqual(3, queryResult.GetInt32(1));
Assert.IsTrue(await queryResult.ReadAsync());
Assert.AreEqual(2, queryResult.GetInt32(1));
Assert.IsTrue(await queryResult.ReadAsync());
Assert.AreEqual(2, queryResult.GetInt32(1));
Assert.IsTrue(await queryResult.ReadAsync());
Assert.AreEqual(1, queryResult.GetInt32(1));
Assert.IsTrue(await queryResult.ReadAsync());
Assert.AreEqual(1, queryResult.GetInt32(1));
}
}
public void Insert_Complex_Entity_With_Relationship_View()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Flat_Entity>()
.AddSqlEntity <Complex_Entity>()
.Build();
var flatDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Flat_Entity> >().First();
var complexDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Complex_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {flatDataModel.StorageModel.DefaultTableName}
(
[Value] INTEGER,
[Id] INTEGER PRIMARY KEY AUTOINCREMENT
)" ));
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {complexDataModel.StorageModel.DefaultTableName}
(
[Value] INTEGER,
[Flat_Id] INTEGER
)" ));
var flatDataSet = new SqlDataSet <Flat_Entity>(flatDataModel, dataProvider);
var complexDataSet = new SqlDataSet <Complex_Entity>(complexDataModel, dataProvider);
// the ID here isn't written to the INSERT query because the ORM see's it as server generated (which it should be)
var flatEntity = new Flat_Entity {
Id = 1, Value = 5
};
var complexEntityView = new { Value = 6, FlatId = flatEntity.Id };
flatDataSet.Insert(flatEntity).Execute();
complexDataSet.Insert(complexEntityView).Execute();
using (var result = dataProvider.ExecuteReader(QueryExpression.Select(
new[] { QueryExpression.All() },
from: QueryExpression.Table(complexDataModel.StorageModel.DefaultTableName),
joins: new[]
{
QueryExpression.Join(
QueryExpression.Table(flatDataModel.StorageModel.DefaultTableName),
QueryExpression.AreEqual(
QueryExpression.Column("Flat_Id", QueryExpression.Table(complexDataModel.StorageModel.DefaultTableName)),
QueryExpression.Column("Id", QueryExpression.Table(flatDataModel.StorageModel.DefaultTableName))
)
)
})))
{
Assert.IsTrue(result.HasRows);
Assert.IsTrue(result.Read());
Assert.AreEqual(complexEntityView.Value, result.GetInt32(0));
Assert.AreEqual(complexEntityView.FlatId, result.GetInt32(1));
Assert.AreEqual(flatEntity.Value, result.GetInt32(2));
Assert.AreEqual(flatEntity.Id, result.GetInt32(3));
}
}
}
public void Convert_QueryExpression_Returns_Same_Reference()
{
var expressionConverter = CreateConverter <int, int>();
var queryExpression = QueryExpression.Select(QueryExpression.All(), QueryExpression.Table("TestTable"));
var condition = expressionConverter.Convert(q => queryExpression);
var checkExpression = condition.QueryExpression as SelectExpression;
Assert.IsNotNull(checkExpression);
Assert.ReferenceEquals(queryExpression, checkExpression);
}
public ExpressionResult Convert(MethodInfo methodInfo, MethodCallExpression node,
ExpressionConverter expressionConverter)
{
var expressionResult = expressionConverter.Convert(node.Arguments[0]);
var maxExpression = expressionResult.QueryExpression;
if (maxExpression is TableExpression)
{
maxExpression = QueryExpression.All();
}
return(new ExpressionResult(
QueryExpression.Max(maxExpression),
expressionResult.RequiredJoins
));
}
public virtual async Task Functions_Count()
{
// some database engines require the subquery to be aliased
using (var tempTable = await Select_CreatePopulatedSelectTable())
{
using (var queryResult = await DataProvider.ExecuteReaderAsync(
QueryExpression.Select(
new[] { QueryExpression.CountFunction(QueryExpression.All()) },
from: QueryExpression.Table(tempTable.TableName)
)))
{
Assert.IsTrue(queryResult.HasRows);
Assert.IsTrue(queryResult.Read());
Assert.AreEqual(6, queryResult.GetInt32(0));
}
}
}
public virtual async Task Select_WithWhere()
{
using (var tempTable = await Select_CreatePopulatedSelectTable())
using (var queryResult = await DataProvider.ExecuteReaderAsync(
QueryExpression.Select(
new[] { QueryExpression.All() },
from: QueryExpression.Table(tempTable.TableName),
where : QueryExpression.Compare(QueryExpression.Column("Data"), ComparisonOperator.AreEqual, QueryExpression.Value(3)),
limit: QueryExpression.Value(1)
)))
{
Assert.IsTrue(queryResult.HasRows);
Assert.IsTrue(await queryResult.ReadAsync());
Assert.AreEqual(3, queryResult.GetInt32(1));
Assert.IsFalse(await queryResult.ReadAsync());
}
}
public virtual async Task Select_FromSubSelect()
{
// some database engines require the subquery to be aliased
var subQueryAlias = QueryExpression.Alias(
QueryExpression.Select(
new[] { QueryExpression.Alias(QueryExpression.Value(1), "value") }
),
"subQuery"
);
using (var queryResult = await DataProvider.ExecuteReaderAsync(
QueryExpression.Select(
new[] { QueryExpression.All() },
from: subQueryAlias
)))
{
Assert.IsTrue(queryResult.HasRows);
Assert.IsTrue(await queryResult.ReadAsync());
Assert.AreEqual(1, queryResult.GetInt32(0));
}
}
public void Insert_Flat_Entity_Into_NonDefault_Table()
{
var tableName = "MyCustomTable";
var schema = new DataSchemaBuilder()
.AddSqlEntity <Flat_Entity>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Flat_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE MyCustomTable
(
[Value] INTEGER
)" ));
var dataSet = new SqlDataSet <Flat_Entity>(sqlDataModel, dataProvider);
var value = 5;
dataSet.Insert(new Flat_Entity {
Value = value
}).Table(tableName).Execute();
using (var result = dataProvider.ExecuteReader(QueryExpression.Select(new[] { QueryExpression.All() }, from: QueryExpression.Table(tableName))))
{
Assert.IsTrue(result.HasRows);
Assert.IsTrue(result.Read());
Assert.AreEqual(value, result.GetInt32(0));
}
}
}
public void Entity_Delete_Operation_Deletes_Only_Entity_Record()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Flat_Entity>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Flat_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] INTEGER
)" ));
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName),
new[] { QueryExpression.Column("Value") },
new[] { QueryExpression.Value(5) },
new[] { QueryExpression.Value(5) }
));
var dataSet = new SqlDataSet <Flat_Entity>(sqlDataModel, dataProvider);
var entity = new Flat_Entity {
Id = 1
};
dataSet.Delete(entity).Execute();
using (var queryResult = dataProvider.ExecuteReader(QueryExpression.Select(new[] { QueryExpression.All() }, from: QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName))))
{
Assert.IsTrue(queryResult.HasRows);
}
}
}
public void Insert_Default_Values()
{
var defaultValue = 4;
var schema = new DataSchemaBuilder()
.AddSqlEntity <Flat_Entity>(cfg => cfg.Field(q => q.Value, cfg => cfg.DefaultValue(() => defaultValue)))
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Flat_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Value] INTEGER
)" ));
var dataSet = new SqlDataSet <Flat_Entity>(sqlDataModel, dataProvider);
dataSet.Insert().Execute();
using (var result = dataProvider.ExecuteReader(QueryExpression.Select(new[] { QueryExpression.All() }, from: QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName))))
{
Assert.IsTrue(result.HasRows);
Assert.IsTrue(result.Read());
Assert.AreEqual(defaultValue, result.GetInt32(0));
}
}
}
public void Can_Commit_In_Transaction()
{
using (var dataProvider = Sqlite3DataProvider.InMemory())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw(
"CREATE TABLE TestTable (Value TEXT)"
));
using (var transaction = dataProvider.CreateTransaction())
{
transaction.ExecuteNonQuery(Sqlite3QueryExpression.Raw(
"INSERT INTO TestTable VALUES ('Hello World')"
));
transaction.Commit();
}
using (var queryResult = dataProvider.ExecuteReader(QueryExpression.Select(
projection: new[] { QueryExpression.All() },
from: QueryExpression.Table("TestTable")
)))
{
Assert.IsTrue(queryResult.HasRows);
Assert.IsTrue(queryResult.Read());
Assert.AreEqual("Hello World", queryResult.GetString(0));
}
}
}
public void Can_Query_With_Values()
{
using (var dataProvider = Sqlite3DataProvider.InMemory())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw(
"CREATE TABLE TestTable (Value TEXT)"
));
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw(
"INSERT INTO TestTable VALUES ('Hello World')"
));
using (var queryResult = dataProvider.ExecuteReader(QueryExpression.Select(
projection: new[] { QueryExpression.All() },
from: QueryExpression.Table("TestTable"),
where : QueryExpression.AreEqual(QueryExpression.Column("Value"), QueryExpression.Value("Hello World"))
)))
{
Assert.IsTrue(queryResult.HasRows);
Assert.IsTrue(queryResult.Read());
Assert.AreEqual("Hello World", queryResult.GetString(0));
}
}
}
public void Can_Rollback_By_Disposing_Transaction()
{
using (var dataProvider = Sqlite3DataProvider.InMemory())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw(
"CREATE TABLE TestTable (Value TEXT)"
));
using (var transaction = dataProvider.CreateTransaction())
{
transaction.ExecuteNonQuery(Sqlite3QueryExpression.Raw(
"INSERT INTO TestTable VALUES ('Hello World')"
));
}
using (var queryResult = dataProvider.ExecuteReader(QueryExpression.Select(
projection: new[] { QueryExpression.All() },
from: QueryExpression.Table("TestTable")
)))
{
Assert.IsFalse(queryResult.HasRows);
}
}
}
public void Insert_Flat_View_With_Binding_And_Transform()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Flat_Entity>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Flat_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Value] INTEGER
)" ));
var dataSet = new SqlDataSet <Flat_Entity>(sqlDataModel, dataProvider);
var value = 5;
var view = new { Custom = value.ToString() };
var transformWasCalled = false;
dataSet.Insert(view, binding =>
binding.Bind(t => t.Value, s => s.Custom, value =>
{
transformWasCalled = true;
return(int.Parse(value));
})).Execute();
Assert.IsTrue(transformWasCalled);
using (var result = dataProvider.ExecuteReader(QueryExpression.Select(new[] { QueryExpression.All() }, from: QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName))))
{
Assert.IsTrue(result.HasRows);
Assert.IsTrue(result.Read());
Assert.AreEqual(value, result.GetInt32(0));
}
}
}
public void Can_Update_Field_To_Null_With_View()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Flat_Entity>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Flat_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] TEXT
)" ));
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName),
new[] { QueryExpression.Column("Value") },
new[] { QueryExpression.Value("Hello") }
));
var dataSet = new SqlDataSet <Flat_Entity>(sqlDataModel, dataProvider);
var view = new { Value = default(string) };
dataSet.Update(view).AndWhere(q => q.Id == 1).Execute();
using (var result = dataProvider.ExecuteReader(QueryExpression.Select(new[] { QueryExpression.All() }, from: QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName))))
{
Assert.IsTrue(result.HasRows);
Assert.IsTrue(result.Read());
Assert.IsTrue(result.IsDBNull(1));
}
}
}
public void Can_Insert_Entity_With_Null_Member()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Complex_Entity>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Complex_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Value] INTEGER,
[Flat_Value] INTEGER,
[Flat_Id] INTEGER
)" ));
var dataSet = new SqlDataSet <Complex_Entity>(sqlDataModel, dataProvider);
var entity = new Complex_Entity
{
Value = 5
};
dataSet.Insert(entity).Execute();
using (var result = dataProvider.ExecuteReader(QueryExpression.Select(new[] { QueryExpression.All() }, from: QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName))))
{
Assert.IsTrue(result.HasRows);
Assert.IsTrue(result.Read());
Assert.AreEqual(entity.Value, result.GetInt32(0));
Assert.AreEqual(0, result.GetInt32(1));
}
}
}
public void Can_Update_Entity_Instance()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Flat_Entity>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Flat_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] TEXT
)" ));
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName),
new[] { QueryExpression.Column("Value") },
new[] { QueryExpression.Value("Hello") },
new[] { QueryExpression.Value("Hello") }
));
var dataSet = new SqlDataSet <Flat_Entity>(sqlDataModel, dataProvider);
var entity = new Flat_Entity {
Id = 2, Value = "World"
};
dataSet.Update(entity).Execute();
using (var result = dataProvider.ExecuteReader(QueryExpression.Select(new[] { QueryExpression.All() }, from: QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName))))
{
Assert.IsTrue(result.HasRows);
Assert.IsTrue(result.Read());
Assert.AreEqual("Hello", result.GetString(1));
Assert.IsTrue(result.Read());
Assert.AreEqual("World", result.GetString(1));
}
}
}
public void Insert_Complex_Embedded_View_With_Complex_Type_Binding()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Complex_Entity>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Complex_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Value] INTEGER,
[Flat_Value] INTEGER,
[Flat_Id] INTEGER
)" ));
var dataSet = new SqlDataSet <Complex_Entity>(sqlDataModel, dataProvider);
var view = new
{
TopValue = 5,
DeepValue = 6
};
dataSet.Insert(view, binding =>
{
binding
.Bind(t => t.Value, s => s.TopValue)
.Bind(t => t.Flat, s => s.DeepValue, value => new Flat_Entity {
Value = value
});
}).Execute();
using (var result = dataProvider.ExecuteReader(QueryExpression.Select(new[] { QueryExpression.All() }, from: QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName))))
{
Assert.IsTrue(result.HasRows);
Assert.IsTrue(result.Read());
Assert.AreEqual(view.TopValue, result.GetInt32(0));
Assert.AreEqual(view.DeepValue, result.GetInt32(1));
}
}
}
protected virtual async Task Data_TestStoreDataType <T>(SqlDataType dataType, T value)
{
using (var dataTable = await DataTestHelpers.CreateDataTable(dataType, DataProvider))
{
await DataProvider.ExecuteNonQueryAsync(QueryExpression.Insert(
dataTable.TableName, new[] { "Data" }, new object[] { value }
));
using (var queryResult = await DataProvider.ExecuteReaderAsync(QueryExpression.Select(
new[] { QueryExpression.All() },
QueryExpression.Table(dataTable.TableName)
)))
{
Assert.IsTrue(queryResult.HasRows);
Assert.IsTrue(await queryResult.ReadAsync());
var retrievedValue = DataTestHelpers.ReadFromResult <T>(queryResult, 0);
Assert.AreEqual(value, retrievedValue);
}
}
}
public void Insert_Custom_Embedded_ColumnName()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Complex_Entity>(cfg => cfg.Field(q => q.Flat.Value, cfg => cfg.ColumnName("Custom")).AutoModel())
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Complex_Entity> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Value] INTEGER,
[Custom] INTEGER,
[Flat_Id] INTEGER
)" ));
var dataSet = new SqlDataSet <Complex_Entity>(sqlDataModel, dataProvider);
var entity = new Complex_Entity {
Value = 5, Flat = new Flat_Entity {
Value = 6
}
};
dataSet.Insert(entity).Execute();
using (var result = dataProvider.ExecuteReader(QueryExpression.Select(new[] { QueryExpression.All() }, from: QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName))))
{
Assert.IsTrue(result.HasRows);
Assert.IsTrue(result.Read());
Assert.AreEqual(entity.Value, result.GetInt32(0));
Assert.AreEqual(entity.Flat.Value, result.GetInt32(1));
}
}
}
public virtual async Task Transactions_Rollback()
{
using (var testTable = await DataTestHelpers.CreateAutoIncrementTable(DataProvider))
{
using (var transaction = await DataProvider.CreateTransactionAsync())
{
await transaction.ExecuteNonQueryAsync(QueryExpression.Insert(
DataTestHelpers.AUTOINC_TABLE_NAME,
new[] { "Data" },
new object[] { 10 }
));
transaction.Rollback();
}
using (var queryResult = await DataProvider.ExecuteReaderAsync(QueryExpression.Select(
new[] { QueryExpression.All() },
QueryExpression.Table(DataTestHelpers.AUTOINC_TABLE_NAME)
)))
{
Assert.IsFalse(queryResult.HasRows);
}
}
}