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 Delete_Returns_Number_Of_Deleted_Records() { 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(6) } )); var dataSet = new SqlDataSet <Flat_Entity>(sqlDataModel, dataProvider); Assert.AreEqual(2, dataSet.Delete().Execute()); } }
public void Delete_Can_Delete_From_Non_Default_Table() { var tableName = "Delete_Can_Delete_From_Non_Default_Table"; 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 {tableName} ( [Id] INTEGER PRIMARY KEY AUTOINCREMENT, [Value] INTEGER )" )); dataProvider.ExecuteNonQuery(QueryExpression.Insert( QueryExpression.Table(tableName), new[] { QueryExpression.Column("Value") }, new[] { QueryExpression.Value(5) }, new[] { QueryExpression.Value(6) } )); var dataSet = new SqlDataSet <Flat_Entity>(sqlDataModel, dataProvider); // no need to test the results of this operation, if it fails it'll throw an exception dataSet.Delete().Table(tableName).Execute(); } }
protected override void WriteFunctionToSql(QueryExpression queryExpression) { switch (queryExpression) { case ConcatenateQueryExpression concatenateQueryExpression: for (var i = 0; i < concatenateQueryExpression.Expressions.Length; i++) { ExpressionWriter.Visit(concatenateQueryExpression.Expressions[i]); if (i < concatenateQueryExpression.Expressions.Length - 1) { Sql.Append(" || "); } } return; case LastInsertIdFunctionExpression lastInsertIdExpression: Sql.Append("last_insert_rowid()"); return; case TableExistsVirtualFunctionExpression tableExistsExpression: ExpressionWriter.Visit( QueryExpression.Select( new[] { QueryExpression.Value(1) }, from: QueryExpression.Table("sqlite_master"), where : QueryExpression.AndAlso( QueryExpression.Compare(QueryExpression.Column("type"), ComparisonOperator.AreEqual, QueryExpression.Value("table")), QueryExpression.Compare(QueryExpression.Column("name"), ComparisonOperator.AreEqual, QueryExpression.Value(tableExistsExpression.Table.TableName)) ) ) ); return; } base.WriteFunctionToSql(queryExpression); }
public void SetAll <TView>(TView view) where TView : class { var reader = new ViewReader <TView>(view); var entityView = EntityModel.GetEntityView <TView>(); foreach (var intersectedFields in entityView.ClassToEntityIntersection.IntersectedFields) { if (!intersectedFields.RightField.IsEntityLocalField) { continue; } if (intersectedFields.RightField.IsPrimaryKey && intersectedFields.RightField.IsSeverGenerated) { continue; } if (reader.CheckPath(intersectedFields.LeftPath)) { var value = reader.Read <object>(intersectedFields.LeftPath); Set( QueryExpression.Column(intersectedFields.RightField.Column.Name), value ); } } }
public void Manual_Delete_Operation_Deletes_Records() { 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(6) } )); var dataSet = new SqlDataSet <Flat_Entity>(sqlDataModel, dataProvider); dataSet.Delete().AndWhere(q => q.Value == 5).Execute(); using (var queryResult = dataProvider.ExecuteReader(QueryExpression.Select(new[] { QueryExpression.Column(nameof(Flat_Entity.Value)) }, from: QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName)))) { Assert.IsTrue(queryResult.HasRows); Assert.IsTrue(queryResult.Read()); Assert.AreEqual(6, queryResult.GetInt32(0)); } } }
public void Can_Update_With_Value() { 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); dataSet.Update().Set(q => q.Value, "World").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("World", result.GetString(1)); } } }
public void Can_Select_Computed_Field_With_Expression() { var schema = new DataSchemaBuilder() .AddSqlEntity <Entity_With_Computed_Field>() .Build(); var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Entity_With_Computed_Field> >().First(); using (var dataProvider = DataProvider.CreateTestProvider()) { dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@" CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName} ( [Id] INTEGER PRIMARY KEY AUTOINCREMENT, [A] INTEGER, [B] INTEGER, [C] INTEGER )" )); dataProvider.ExecuteNonQuery(QueryExpression.Insert( QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName), new[] { QueryExpression.Column("A"), QueryExpression.Column("B"), QueryExpression.Column("C") }, new[] { QueryExpression.Value(1), QueryExpression.Value(1), QueryExpression.Value(3) } // note C isn't A + B as defined on the entity class itself to prove it's read direct from the db )); var dataSet = new SqlDataSet <Entity_With_Computed_Field>(sqlDataModel, dataProvider); var value = dataSet.Select(q => q.C).ToSingle(); Assert.AreEqual(3, value); } }
public void Can_Select_HasFlag_With_Expression() { var schema = new DataSchemaBuilder() .AddSqlEntity <Enum_Entity>() .Build(); var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Enum_Entity> >().First(); using (var dataProvider = DataProvider.CreateTestProvider()) { dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@" CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName} ( [Id] INTEGER PRIMARY KEY AUTOINCREMENT, [Value] INTEGER, [Requirement] INTEGER )" )); dataProvider.ExecuteNonQuery(QueryExpression.Insert( QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName), new[] { QueryExpression.Column("Value"), QueryExpression.Column("Requirement") }, new[] { QueryExpression.Value(0x1), QueryExpression.Value(0x1) }, new[] { QueryExpression.Value(0x2), QueryExpression.Value(0x1) }, new[] { QueryExpression.Value(0x1 | 0x4), QueryExpression.Value(0x1) } )); var dataSet = new SqlDataSet <Enum_Entity>(sqlDataModel, dataProvider); var valueSet = dataSet.Select(q => dataSet.ServerFunctions.HasFlag(q.Value, q.Requirement)).ToList(); Assert.IsTrue(new[] { true, false, true }.SequenceEqual(valueSet)); } }
public void Can_Select_IsLike() { var schema = new DataSchemaBuilder() .AddSqlEntity <Flat_Entity <string> >(q => q.DefaultTableName = "TestTable") .Build(); var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Flat_Entity <string> > >().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("This is like") }, new[] { QueryExpression.Value("Not so much") } )); var dataSet = new SqlDataSet <Flat_Entity <string> >(sqlDataModel, dataProvider); var valueSet = dataSet.Select(q => dataSet.ServerFunctions.IsLike(q.Value, "%is%")).ToList(); Assert.IsTrue(new[] { true, false }.SequenceEqual(valueSet)); } }
public void Can_Select_IsIn_With_SubQuery() { 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(1) }, new[] { QueryExpression.Value(2) }, new[] { QueryExpression.Value(3) } )); var dataSet = new SqlDataSet <Flat_Entity>(sqlDataModel, dataProvider); var subQuery = dataSet.Select(q => q.Id).AndWhere(q => q.Id == 1 || q.Id == 3); var valueSet = dataSet.Select(q => dataSet.ServerFunctions.IsIn(q.Id, subQuery)).ToList(); Assert.IsTrue(new[] { true, false, true }.SequenceEqual(valueSet)); } }
public override JoinExpression GetJoinExpression() { var rightIdentifier = AliasIdentifierExpression; var leftIdentifier = Left.AliasIdentifierExpression; var onCondition = default(QueryExpression); foreach (var columnPair in _joinColumnPairs) { var newCondition = QueryExpression.Compare( QueryExpression.Column(columnPair.LeftColumnName, leftIdentifier), ComparisonOperator.AreEqual, QueryExpression.Column(columnPair.RightColumnName, rightIdentifier) ); onCondition = QueryExpression.CombineConditions(onCondition, ConditionType.AndAlso, newCondition); } return(QueryExpression.Join( QueryExpression.Alias( Right.AliasIdentifierExpression, rightIdentifier.Identifier ), onCondition, JoinDirection.Left )); }
public virtual async Task Crud_Update() { using (var tempTable = await DataTestHelpers.CreateAutoIncrementTable(DataProvider)) using (var queryResult = await DataProvider.ExecuteReaderAsync( new CompositeQueryExpression( QueryExpression.Insert( tempTable.TableName, new[] { "Data" }, new object[] { 1 } ), QueryExpression.Update( QueryExpression.Table(tempTable.TableName), QueryExpression.Compare(QueryExpression.Column("Id"), ComparisonOperator.AreEqual, QueryExpression.Value(1)), QueryExpression.Assign(QueryExpression.Column("Data"), QueryExpression.Value(5)) ), QueryExpression.Select( new[] { QueryExpression.Column("Data") }, from: QueryExpression.Table(tempTable.TableName), where : QueryExpression.Compare(QueryExpression.Column("Id"), ComparisonOperator.AreEqual, QueryExpression.Value(1)) ) ))) { Assert.IsTrue(queryResult.HasRows); Assert.IsTrue(await queryResult.ReadAsync()); Assert.AreEqual(5, queryResult.GetInt32(0)); } }
public void Select_View_Returns_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] INTEGER )" )); dataProvider.ExecuteNonQuery(QueryExpression.Insert( QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName), new[] { QueryExpression.Column("Value") }, new[] { QueryExpression.Value(1) }, new[] { QueryExpression.Value(2) } )); var dataSet = new SqlDataSet <Flat_Entity>(sqlDataModel, dataProvider); var valueSet = dataSet.Select <Flat_Entity_Value_View>().ToList(); Assert.IsTrue( valueSet.Select(q => q.Value).SequenceEqual(new[] { 1, 2 }) ); } }
public void Can_Select_Count() { 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(1) }, new[] { QueryExpression.Value(2) } )); var dataSet = new SqlDataSet <Flat_Entity>(sqlDataModel, dataProvider); var id = dataSet.Select(q => dataSet.ServerFunctions.Count()).ToSingle(); Assert.AreEqual(2, id); } }
public void Set(EntityField <T> schemaField, IQueryBuilder subQuery) { AddFieldAssignment( QueryExpression.Column(schemaField.Column.Name), subQuery.BuildQuery() ); }
public void Execute_Returns_Affected_Row_Count() { 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 result = dataSet.Update().Set(q => q.Value, "World").Execute(); Assert.AreEqual(2, result); } }
public virtual async Task Functions_CountDistinct() { using (var tempTable = await DataTestHelpers.CreateAutoIncrementTable(DataProvider)) { await DataProvider.ExecuteNonQueryAsync(QueryExpression.Insert( tempTable.TableName, new[] { "Data" }, new object[] { 10 }, new object[] { 10 }, new object[] { 15 }, new object[] { 20 } )); using (var queryResult = await DataProvider.ExecuteReaderAsync( QueryExpression.Select( new[] { QueryExpression.CountFunction( QueryExpression.Distinct(QueryExpression.Column("Data")) ) }, from: QueryExpression.Table(tempTable.TableName) ))) { Assert.IsTrue(queryResult.HasRows); Assert.IsTrue(await queryResult.ReadAsync()); Assert.AreEqual(3, queryResult.GetInt32(0)); } } }
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 IResultReader <TView> AddView <TView>() where TView : class { var entityView = EntitySchema.GetEntityView <TView>(); foreach (var intersectedFields in entityView.EntityToClassIntersection.IntersectedFields) { if (!intersectedFields.RightField.CanWrite) { continue; } var entityField = intersectedFields.LeftField; if (!_projectionExpressions.ContainsKey(entityField.ProjectionAlias)) { _projectionExpressions.Add( entityField.ProjectionAlias, new AliasExpression( QueryExpression.Column(entityField.Column.Name, entityField.Source.AliasIdentifierExpression), entityField.ProjectionAlias ) ); var requiredJoin = entityField.Source as Join; if (requiredJoin != null) { _requiredJoins.AddJoin(requiredJoin); } } } return(new MappingReader <TView>(entityView.Mapping)); }
public void Can_Query_For_Last_Id() { using (var dataProvider = Sqlite3DataProvider.InMemory()) { dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw( "CREATE TABLE TestTable (Id INT, Value TEXT, CONSTRAINT [PK] PRIMARY KEY (Id))" )); var table = QueryExpression.Table("TestTable"); using (var queryResult = dataProvider.ExecuteReader(QueryExpression.Many( QueryExpression.Insert( table, new[] { QueryExpression.Column("Value") }, new[] { QueryExpression.Value("Hello World") }, new[] { QueryExpression.Value("Hello World") } ), QueryExpression.Select( projection: new[] { QueryExpression.LastInsertedId() }, from: table ) ))) { Assert.IsTrue(queryResult.HasRows); Assert.IsTrue(queryResult.Read()); Assert.AreEqual(2, queryResult.GetInt32(0)); } } }
public void Can_Extend_Server_Method_Support() { var schema = new DataSchemaBuilder() .AddSqlMethodCallConverter(new ServerFunctionConverter()) .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(1) }, new[] { QueryExpression.Value(2) } )); var dataSet = new SqlDataSet <Flat_Entity>(sqlDataModel, dataProvider); var resultSet = dataSet.Select().AndWhere(q => dataSet.ServerFunctions.ValueIsGreaterThanOne(q)).ToList(); Assert.AreEqual(1, resultSet.Count); Assert.AreEqual(2, resultSet[0].Value); } }
public void Does_Not_Select_Computed_Field_On_Entity() { var schema = new DataSchemaBuilder() .AddSqlEntity <Entity_With_Computed_Field>() .Build(); var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Entity_With_Computed_Field> >().First(); using (var dataProvider = DataProvider.CreateTestProvider()) { // leave "C" off the schema, if we try to query for "C" it'll throw an exception and fail the test dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@" CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName} ( [Id] INTEGER PRIMARY KEY AUTOINCREMENT, [A] INTEGER, [B] INTEGER )" )); dataProvider.ExecuteNonQuery(QueryExpression.Insert( QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName), new[] { QueryExpression.Column("A"), QueryExpression.Column("B") }, new[] { QueryExpression.Value(1), QueryExpression.Value(1) } )); var dataSet = new SqlDataSet <Entity_With_Computed_Field>(sqlDataModel, dataProvider); var result = dataSet.Select().ToSingle(); } }
public void Select_Expression_With_Complex_Join_Returns_Value() { var schema = new DataSchemaBuilder() .AddSqlEntity <Really_Complex_Entity>() .AddSqlEntity <Complex_Entity>() .AddSqlEntity <Flat_Entity>() .Build(); var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Really_Complex_Entity> >().First(); using (var dataProvider = DataProvider.CreateTestProvider()) { dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@" CREATE TABLE Flat_Entity ( [Id] INTEGER PRIMARY KEY AUTOINCREMENT, [Value] INTEGER )" )); dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@" CREATE TABLE Complex_Entity ( [Id] INTEGER PRIMARY KEY AUTOINCREMENT, [Value] INTEGER, [Flat_Id] INTEGER )" )); dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@" CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName} ( [Id] INTEGER PRIMARY KEY AUTOINCREMENT, [Value] INTEGER, [Complex_Id] INTEGER, [Flat_Id] INTEGER )" )); dataProvider.ExecuteNonQuery(QueryExpression.Insert( QueryExpression.Table("Flat_Entity"), new[] { QueryExpression.Column("Value") }, new[] { QueryExpression.Value(7) }, new[] { QueryExpression.Value(8) } )); dataProvider.ExecuteNonQuery(QueryExpression.Insert( QueryExpression.Table("Complex_Entity"), new[] { QueryExpression.Column("Value"), QueryExpression.Column("Flat_Id") }, new[] { QueryExpression.Value(6), QueryExpression.Value(1) } )); dataProvider.ExecuteNonQuery(QueryExpression.Insert( QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName), new[] { QueryExpression.Column("Value"), QueryExpression.Column("Flat_Id"), QueryExpression.Column("Complex_Id") }, new[] { QueryExpression.Value(5), QueryExpression.Value(2), QueryExpression.Value(1) } )); var dataSet = new SqlDataSet <Really_Complex_Entity>(sqlDataModel, dataProvider); var valueSet = dataSet.Select(q => q.Complex.Flat.Value).ToList(); Assert.AreEqual(7, valueSet[0]); } }
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 OrElse(EntityField <T> schemaField, ComparisonOperator @operator, T entity) { OrElse(QueryExpression.Compare( QueryExpression.Column(schemaField.Column.Name, schemaField.Source.AliasIdentifierExpression), @operator, GetValueExpression(schemaField, entity) )); AddJoin(schemaField.Source); }
public void Can_Write_Max_Function_With_Args() { var queryExpression = QueryExpression.Select( new[] { QueryExpression.Max(QueryExpression.Column("Id")) } ); var sql = TestHelpers.ConvertToSql(queryExpression); Assert.AreEqual("SELECT MAX([Id]);", sql); }
public void Can_Write_Concat_Function() { var queryExpression = QueryExpression.Select( new[] { QueryExpression.Concat(QueryExpression.Column("Column1"), QueryExpression.Column("Column2")) } ); var sql = TestHelpers.ConvertToSql(queryExpression); Assert.AreEqual("SELECT CONCAT([Column1], [Column2]);", sql); }
public void OrElse(EntityField <T> schemaField, ComparisonOperator @operator, IQueryBuilder subQuery) { OrElse(QueryExpression.Compare( QueryExpression.Column(schemaField.Column.Name, schemaField.Source.AliasIdentifierExpression), @operator, subQuery.BuildQuery() )); AddJoin(schemaField.Source); }
public void Set <TValue>(EntityField <T> schemaField, Expression <Func <T, TValue> > valueExpression) { var valueExpressionResult = ExpressionConverter.Convert(valueExpression); AddFieldAssignment( QueryExpression.Column(schemaField.Column.Name), valueExpressionResult.QueryExpression ); }