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}'."); } }