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