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