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