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_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 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_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 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 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_Parameters()
{
using (var dataProvider = Sqlite3DataProvider.InMemory())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw(
"CREATE TABLE TestTable (Value TEXT)"
));
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw(
"INSERT INTO TestTable VALUES ('Hello World')"
));
var parameters = new ParameterBag
{
{ "valueParameter", "Hello World" }
};
using (var queryResult = dataProvider.ExecuteReader(QueryExpression.Select(
projection: new[] { QueryExpression.All() },
from: QueryExpression.Table("TestTable"),
where : QueryExpression.AreEqual(QueryExpression.Column("Value"), QueryExpression.Parameter("valueParameter"))
), parameters))
{
Assert.IsTrue(queryResult.HasRows);
Assert.IsTrue(queryResult.Read());
Assert.AreEqual("Hello World", queryResult.GetString(0));
}
}
}
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_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 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 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 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 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 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_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_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_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_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 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 DataContext_Operates_On_DataSets()
{
lock (TestDataContext.Lock)
{
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE [Entities]
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] INTEGER
)" ));
TestDataContext.ConfigureEntity = false;
var context = DataContext.Create <TestDataContext>(dataProvider);
var testEntityInstance = new EntityType
{
Value = 1
};
context.Entities.Insert(testEntityInstance)
.Execute();
var retrievedInstance = context.Entities.Select()
.AndWhere(q => q.Value == testEntityInstance.Value)
.ToSingle();
Assert.AreEqual(testEntityInstance.Value, retrievedInstance.Value);
}
}
}
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_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 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 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 })
);
}
}
public void Can_Insert_Relationship_And_Query_Entity()
{
using (var dataProvider = DataProvider.CreateTestProvider())
{
var context = DataContext.Create <TestDataContext>(dataProvider);
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {context.Entities.DataModel.StorageModel.DefaultTableName}
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] INTEGER
)" ));
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE {context.RelatedEntities.DataModel.StorageModel.DefaultTableName}
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] INTEGER,
[Related_Id] INTEGER
)" ));
var newEntity = new EntityType
{
Value = 2
};
var relatedEntity = new EntityTypeWithRelationship
{
Value = 3
};
Batch.Create()
.Insert(context.Entities.Insert(newEntity))
.Insert(
context.RelatedEntities.Insert(relatedEntity)
.Set(q => q.Related.Id, q => context.SqlServerFunctions.LastInsertId())
)
.Single(context.RelatedEntities.Select(), out var entityResult)
.Execute();
var entity = entityResult.Result;
Assert.AreEqual(1, entity.Id);
Assert.AreEqual(1, entity.Related.Id);
Assert.AreEqual(newEntity.Value, entity.Related.Value);
Assert.AreEqual(relatedEntity.Value, entity.Value);
}
}
public void Can_Select_With_Custom_Binding()
{
using (var dataProvider = TestHelpers.CreateTestProvider())
{
var container = TestHelpers.CreateContainer(services =>
{
services.AddDataContext <TestContext>(dataProvider);
services.Configure <MappingOptions>(options =>
{
options.AddBindingOverride <TestEntity, TestView>(binding =>
{
binding.Bind(t => t.TheId, s => s.Id);
binding.Bind(t => t.TheValue, s => s.Value, value => value * 2);
});
});
});
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE Entities
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] INTEGER
)" ));
using (var scope = container.CreateScope())
{
var dataContext = scope.ServiceProvider.GetRequiredService <TestContext>();
var entity = new TestEntity
{
Value = 2
};
Batch.Create()
.Insert(dataContext.Entities.Insert(entity))
.Inject(dataContext.Entities.Select().LastInsertId(), entity)
.Single(dataContext.Entities.Select <TestView>(), out var fetchResult)
.Execute();
var fetchedEntity = fetchResult.Result;
Assert.AreEqual(entity.Id, fetchedEntity.TheId);
Assert.AreEqual(entity.Value * 2, fetchedEntity.TheValue);
}
}
}
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 DataContext_Transaction_Commits()
{
lock (TestDataContext.Lock)
{
using (var dataProvider = DataProvider.CreateTestProvider())
{
dataProvider.ExecuteNonQuery(Sqlite3QueryExpression.Raw($@"
CREATE TABLE [Entities]
(
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Value] INTEGER
)" ));
TestDataContext.ConfigureEntity = false;
var context = DataContext.Create <TestDataContext>(dataProvider);
var testEntityInstance = new EntityType
{
Value = 1
};
using (var transaction = context.CreateTransaction())
{
context.Entities.Insert(testEntityInstance)
.Execute();
var retrievedInstances = context.Entities.Select()
.ToList();
Assert.AreEqual(1, retrievedInstances.Count);
transaction.Commit();
}
var outerRetrievedInstances = context.Entities.Select()
.ToList();
Assert.AreEqual(1, outerRetrievedInstances.Count);
}
}
}