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 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 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 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();
}
}
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 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 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 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 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 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 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_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 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 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 Insert_Flat_View_With_Binding()
{
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 = 5 };
dataSet.Insert(view, binding => binding.Bind(t => t.Value, s => s.Custom)).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(value, result.GetInt32(0));
}
}
}
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 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 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 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 static SqlDataModelBuilder Create(Type entityType, DataSchemaBuilder schemaBuilder, SqlEntityConfiguration entityConfiguration)
{
return(Activator.CreateInstance(
typeof(SqlDataModelBuilder <>).MakeGenericType(entityType),
schemaBuilder,
entityConfiguration
) as SqlDataModelBuilder);
}
public static void ConfigureSchema(DataSchemaBuilder builder)
{
ConfigureInvokeCount++;
if (ConfigureEntity)
{
builder.ConfigureSqlEntity <EntityType>(config =>
{
config.DefaultTableName = "ChangedTableName";
});
}
}
public void Select_Entity_With_Join_Returns_Entity()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Complex_Entity>()
.AddSqlEntity <Flat_Entity>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <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 {sqlDataModel.StorageModel.DefaultTableName}
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] INTEGER,
[Flat_Id] INTEGER
)" ));
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table("Flat_Entity"),
new[] { QueryExpression.Column("Value") },
new[] { QueryExpression.Value(5) },
new[] { QueryExpression.Value(6) }
));
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName),
new[] { QueryExpression.Column("Value"), QueryExpression.Column("Flat_Id") },
new[] { QueryExpression.Value(3), QueryExpression.Value(1) },
new[] { QueryExpression.Value(4), QueryExpression.Value(2) }
));
var dataSet = new SqlDataSet <Complex_Entity>(sqlDataModel, dataProvider);
var entitySet = dataSet.Select().ToList();
Assert.IsTrue(
entitySet.Select(q => q.Value).SequenceEqual(new[] { 3, 4 })
);
Assert.IsTrue(
entitySet.Select(q => q.Flat.Id).SequenceEqual(new[] { 1, 2 })
);
Assert.IsTrue(
entitySet.Select(q => q.Flat.Value).SequenceEqual(new[] { 5, 6 })
);
}
}
private (SqlDataModel <T1>, SqlDataModel <T2>) BuildDataSet <T1, T2>()
where T1 : class
where T2 : class
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <T1>()
.AddSqlEntity <T2>()
.Build();
return(
schema.Sql.SqlEntities.OfType <SqlDataModel <T1> >().First(),
schema.Sql.SqlEntities.OfType <SqlDataModel <T2> >().First()
);
}
private static void AttemptCallConfigure(Type dataContextType, DataSchemaBuilder dataSchemaBuilder)
{
var method = dataContextType.GetMethod(CONFIGURE_METHOD_NAME, BindingFlags.Public | BindingFlags.Static);
if (method == null)
{
return;
}
var parameters = method.GetParameters();
if (parameters.Length != 1 || parameters[0].ParameterType != typeof(DataSchemaBuilder))
{
return;
}
method.Invoke(null, new object[] { dataSchemaBuilder });
}
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));
}
}
}
public void Insert_Returns_Number_Of_Inserted_Rows()
{
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;
Assert.AreEqual(1, dataSet.Insert(new Flat_Entity {
Value = value
}).Execute());
}
}
public static DataSchema CreateSchemaFromDataContextType(Type dataContextType)
{
if (dataContextType.IsAbstract)
{
throw new InvalidOperationException("Cannot create schema from abstract data context type.");
}
var schemaBuilder = new DataSchemaBuilder();
foreach (var property in dataContextType.GetDataSetProperties())
{
if (property.PropertyType.IsGenericType)
{
var genericArgs = property.PropertyType.GetGenericArguments();
if (property.PropertyType.IsSqlDataSetWithEntityAndBusinessTypes())
{
var sqlEntityConfiguration = SqlEntityConfiguration.Create(
genericArgs[0], genericArgs[1]);
sqlEntityConfiguration.DefaultTableName = property.Name;
AutoConfigureEntityType(sqlEntityConfiguration);
schemaBuilder.AddSqlEntity(sqlEntityConfiguration);
}
else if (property.PropertyType.IsSqlDataSetWithEntityType())
{
var sqlEntityConfiguration = SqlEntityConfiguration.Create(
genericArgs[0]);
sqlEntityConfiguration.DefaultTableName = property.Name;
AutoConfigureEntityType(sqlEntityConfiguration);
schemaBuilder.AddSqlEntity(sqlEntityConfiguration);
}
}
}
AttemptCallConfigure(dataContextType, schemaBuilder);
return(schemaBuilder.Build());
}
public void Can_Select_Bound_Complex_Type_With_Transform()
{
var schema = new DataSchemaBuilder()
.AddSqlEntity <Has_Complex_Type>()
.Build();
var sqlDataModel = schema.Sql.SqlEntities.OfType <SqlDataModel <Has_Complex_Type> >().First();
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {sqlDataModel.StorageModel.DefaultTableName}
(
[Complex_A] INTEGER,
[Complex_B] INTEGER
)" ));
var a = 2;
var b = 4;
dataProvider.ExecuteNonQuery(QueryExpression.Insert(
QueryExpression.Table(sqlDataModel.StorageModel.DefaultTableName),
new[] { QueryExpression.Column("Complex_A"), QueryExpression.Column("Complex_B") },
new[] { QueryExpression.Value(a), QueryExpression.Value(b) }
));
var binding = new TypeBindingBuilder <Has_Complex_Type, Custom_View>()
.Bind(q => q.Sum, q => q.Complex, o => o.A + o.B)
.Bind(q => q.Product, q => q.Complex, o => o.A * o.B)
.BuildBinding();
var dataSet = new SqlDataSet <Has_Complex_Type>(sqlDataModel, dataProvider);
var result = dataSet.Select <Custom_View>(binding).ToSingle();
Assert.AreEqual(a + b, result.Sum);
Assert.AreEqual(a * b, result.Product);
}
}
