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); } } }