private static QueryExpression CreateComparison(QueryExpression left, SqlComparisonOperator comparisonType, QueryExpression right)
{
switch (comparisonType)
{
case SqlComparisonOperator.AreEqual:
return(QueryExpression.AreEqual(left, right));
case SqlComparisonOperator.AreNotEqual:
return(QueryExpression.AreNotEqual(left, right));
case SqlComparisonOperator.GreaterThan:
return(QueryExpression.GreaterThan(left, right));
case SqlComparisonOperator.GreaterThanOrEqualTo:
return(QueryExpression.GreaterThanOrEqualTo(left, right));
case SqlComparisonOperator.LessThan:
return(QueryExpression.LessThan(left, right));
case SqlComparisonOperator.LessThanOrEqualTo:
return(QueryExpression.LessThanOrEqualTo(left, right));
case SqlComparisonOperator.Like:
return(QueryExpression.Like(left, right));
}
throw new InvalidOperationException();
}
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 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 Can_Write_Delete_With_Where()
{
var query = QueryExpression.Delete(
QueryExpression.Table("TestTable"),
QueryExpression.AreEqual(QueryExpression.Column("Id"), QueryExpression.Column("Id"))
);
var sql = TestHelpers.ConvertToSql(query);
Assert.AreEqual("DELETE FROM [TestTable] WHERE [Id] = [Id];", sql);
}
public void Can_Write_Update_With_Where()
{
var query = QueryExpression.Update(
QueryExpression.Table("TestTable"),
new[] { QueryExpression.Column("Value1") },
new[] { QueryExpression.Column("Value2") },
QueryExpression.AreEqual(QueryExpression.Column("Value2"), QueryExpression.Column("Value3"))
);
var sql = TestHelpers.ConvertToSql(query);
Assert.AreEqual(sql, $"UPDATE [TestTable] SET [Value1] = [Value2] WHERE [Value2] = [Value3];");
}
private LinqQueryExpression <TEntity> HasFlagCall <TEntity>(MethodCallExpression methodCallExpression,
ExpressionConversionVisitor <TEntity> expressionConverter)
where TEntity : class
{
var expression = expressionConverter.Visit(methodCallExpression.Arguments[1]) as LinqQueryExpression <TEntity>;
var value = expressionConverter.Visit(methodCallExpression.Arguments[2]) as LinqQueryExpression <TEntity>;
return(new LinqQueryExpression <TEntity>(
QueryExpression.AreEqual(
QueryExpression.And(expression.QueryExpression, value.QueryExpression),
value.QueryExpression
),
(expression.JoinBuilders ?? JoinBuilder <TEntity> .Empty).Concat(
value.JoinBuilders ?? JoinBuilder <TEntity> .Empty
)));
}
public static SqlSelectOperation <TEntity, TView> LastInsertId <TEntity, TView>(this SqlSelectOperation <TEntity, TView> sql)
where TEntity : class
where TView : class
{
var whereBuilder = sql as IWhereQueryBuilder <TEntity>;
var primaryKeyField = sql.DataSet.DataModel.StorageModel.Fields.FirstOrDefault(
q => q.IsPrimaryKey && q.IsServerGenerated
);
if (primaryKeyField == null)
{
throw new System.InvalidOperationException("To query last inserted ID a server generated primary key is required.");
}
var expression = QueryExpression.AreEqual(
QueryExpression.Column(primaryKeyField.ColumnName),
QueryExpression.LastInsertedId()
);
whereBuilder.AndWhere(new SqlValueExpression <TEntity, bool>(expression, JoinBuilder <TEntity> .Empty));
var entityBinding = sql.DataSet.Bindings.BindEntityToView <TView>();
var storageBinding = sql.DataSet.DataModel.GetOutStorageBinding(entityBinding);
var pkStorageBindingPair = storageBinding.FieldBindings.FirstOrDefault(q => ReferenceEquals(q.SourceField, primaryKeyField));
if (pkStorageBindingPair == null)
{
throw new System.InvalidOperationException("Primary key field isn't bound on the view type.");
}
var pkEntityBindingPair = entityBinding.FieldBindings.FirstOrDefault(q => ReferenceEquals(q.TargetField, pkStorageBindingPair.TargetField));
if (pkEntityBindingPair == null)
{
throw new System.InvalidOperationException("Primary key field isn't bound on the view type.");
}
entityBinding = new Mapping.Binding.DataModelBinding <Modelling.TypeModels.TypeModel <TEntity>, Modelling.TypeModels.PropertyField, Modelling.TypeModels.TypeModel <TView>, Modelling.TypeModels.PropertyField>(
entityBinding.SourceModel,
entityBinding.TargetModel,
new[] { pkEntityBindingPair }
);
sql.Bind(entityBinding);
return(sql);
}
public JoinQueryExpression Build()
{
var rightIdentifier = QueryExpression.AliasReference(((IAliasIdentifier)this).AliasIdentifier);
var leftIdentifier = QueryExpression.AliasReference(Left.AliasIdentifier);
var onCondition = default(QueryExpression);
foreach (var columnPair in ColumnPairs)
{
var newCondition = QueryExpression.AreEqual(
QueryExpression.Column(columnPair.LeftColumnName, leftIdentifier),
QueryExpression.Column(columnPair.RightColumnName, rightIdentifier)
);
onCondition = onCondition.AndAlso(newCondition);
}
return(QueryExpression.Join(
QueryExpression.As(
QueryExpression.AliasReference(Right.AliasIdentifier), rightIdentifier.IdentifierName, out var _
),
onCondition,
Direction
));
}
protected override Expression VisitBinary(BinaryExpression node)
{
var left = Visit(node.Left) as LinqQueryExpression <TEntity>;
if (left == null)
{
throw new InvalidOperationException("Couldn't convert expression to SQL query expression.");
}
var right = Visit(node.Right) as LinqQueryExpression <TEntity>;
if (right == null)
{
throw new InvalidOperationException("Couldn't convert expression to SQL query expression.");
}
var leftExpression = left.QueryExpression;
var rightExpression = right.QueryExpression;
switch (node.NodeType)
{
case ExpressionType.AddChecked:
case ExpressionType.Add:
return(new LinqQueryExpression <TEntity>(
QueryExpression.Add(leftExpression, rightExpression),
ConcatJoinBuilders(left.JoinBuilders, right.JoinBuilders)
));
case ExpressionType.SubtractChecked:
case ExpressionType.Subtract:
return(new LinqQueryExpression <TEntity>(
QueryExpression.Subtract(leftExpression, rightExpression),
ConcatJoinBuilders(left.JoinBuilders, right.JoinBuilders)
));
case ExpressionType.MultiplyChecked:
case ExpressionType.Multiply:
return(new LinqQueryExpression <TEntity>(
QueryExpression.Multiply(leftExpression, rightExpression),
ConcatJoinBuilders(left.JoinBuilders, right.JoinBuilders)
));
case ExpressionType.Divide:
return(new LinqQueryExpression <TEntity>(
QueryExpression.Divide(leftExpression, rightExpression),
ConcatJoinBuilders(left.JoinBuilders, right.JoinBuilders)
));
case ExpressionType.And:
return(new LinqQueryExpression <TEntity>(
QueryExpression.And(leftExpression, rightExpression),
ConcatJoinBuilders(left.JoinBuilders, right.JoinBuilders)
));
case ExpressionType.ExclusiveOr:
return(new LinqQueryExpression <TEntity>(
QueryExpression.ExclusiveOr(leftExpression, rightExpression),
ConcatJoinBuilders(left.JoinBuilders, right.JoinBuilders)
));
case ExpressionType.Or:
return(new LinqQueryExpression <TEntity>(
QueryExpression.Or(leftExpression, rightExpression),
ConcatJoinBuilders(left.JoinBuilders, right.JoinBuilders)
));
case ExpressionType.AndAlso:
return(new LinqQueryExpression <TEntity>(
QueryExpression.AndAlso(leftExpression, rightExpression),
ConcatJoinBuilders(left.JoinBuilders, right.JoinBuilders)
));
case ExpressionType.OrElse:
return(new LinqQueryExpression <TEntity>(
QueryExpression.OrElse(leftExpression, rightExpression),
ConcatJoinBuilders(left.JoinBuilders, right.JoinBuilders)
));
case ExpressionType.Equal:
return(new LinqQueryExpression <TEntity>(
QueryExpression.AreEqual(leftExpression, rightExpression),
ConcatJoinBuilders(left.JoinBuilders, right.JoinBuilders)
));
case ExpressionType.NotEqual:
return(new LinqQueryExpression <TEntity>(
QueryExpression.AreNotEqual(leftExpression, rightExpression),
ConcatJoinBuilders(left.JoinBuilders, right.JoinBuilders)
));
case ExpressionType.GreaterThan:
return(new LinqQueryExpression <TEntity>(
QueryExpression.GreaterThan(leftExpression, rightExpression),
ConcatJoinBuilders(left.JoinBuilders, right.JoinBuilders)
));
case ExpressionType.GreaterThanOrEqual:
return(new LinqQueryExpression <TEntity>(
QueryExpression.GreaterThanOrEqualTo(leftExpression, rightExpression),
ConcatJoinBuilders(left.JoinBuilders, right.JoinBuilders)
));
case ExpressionType.LessThan:
return(new LinqQueryExpression <TEntity>(
QueryExpression.LessThan(leftExpression, rightExpression),
ConcatJoinBuilders(left.JoinBuilders, right.JoinBuilders)
));
case ExpressionType.LessThanOrEqual:
return(new LinqQueryExpression <TEntity>(
QueryExpression.LessThanOrEqualTo(leftExpression, rightExpression),
ConcatJoinBuilders(left.JoinBuilders, right.JoinBuilders)
));
default:
throw new Exception($"Unsupported binary node type '{node.NodeType}'.");
}
}