public void where_allows_abstract_condition_if_not_requiresConcreteCondition()
{
Expression <Func <Artist, Artist, bool> > filter = (x, y) => x.Id == y.Id;
_subject = new WhereBuilder(filter, false, 0);
_subject.ToString().Should().Be($"(\"Artists\".\"Id\" = \"Artists\".\"Id\")");
}
public void where_throws_without_concrete_condition_if_requiresConcreteCondition()
{
Expression <Func <Artist, Artist, bool> > filter = (x, y) => x.Id == y.Id;
_subject = new WhereBuilder(filter, true, 0);
Assert.Throws <InvalidOperationException>(() => _subject.ToString());
}
public void SqlServerSelectQuery_MethodExpression_BinaryExpression()
{
// Arrange
var command = new System.Data.SqlClient.SqlCommand();
ColumnMapCollection columns = MapRepository.Instance.GetColumns(typeof(Person));
MappingHelper mappingHelper = new MappingHelper(command);
Person person = new Person();
person.ID = 1;
person.Name = "Jordan";
person.Age = 33;
person.IsHappy = true;
person.BirthDate = new DateTime(1977, 1, 22);
List <Person> list = new List <Person>();
var where = new WhereBuilder <Person>(command, p => p.Name.Contains("John") && p.Age > 5, false);
IQuery query = new SelectQuery(columns, "dbo.People", where.ToString(), "", false);
// Act
string queryText = query.Generate();
// Assert
Assert.IsNotNull(queryText);
Assert.AreEqual(command.Parameters["@P0"].Value, "John");
Assert.AreEqual(command.Parameters["@P1"].Value, 5);
Assert.IsTrue(queryText.Contains("[Name] LIKE '%' + @P0 + '%'"));
Assert.IsTrue(queryText.Contains("[Age] > @P1"));
}
public void where_equal_const()
{
_subject = Where(x => x.Id == 10);
_subject.ToString().Should().Be($"(\"Artists\".\"Id\" = @Clause1_P1)");
_subject.Parameters.Get <int>("Clause1_P1").Should().Be(10);
}
public void enum_in_array()
{
var allowed = new ArtistStatusType[] { ArtistStatusType.Continuing, ArtistStatusType.Ended };
_subject = WhereMetadata(x => allowed.Contains(x.Status));
_subject.ToString().Should().Be($"(\"ArtistMetadata\".\"Status\" IN @Clause1_P1)");
}
public void where_string_is_null_value()
{
string imdb = null;
_subject = Where(x => x.CleanName == imdb);
_subject.ToString().Should().Be($"(\"Artists\".\"CleanName\" IS NULL)");
}
public void enum_in_array()
{
var allowed = new MovieStatusType[] { MovieStatusType.Announced, MovieStatusType.InCinemas };
_subject = Where(x => allowed.Contains(x.Status));
_subject.ToString().Should().Be($"(\"Movies\".\"Status\" IN @Clause1_P1)");
}
/// <summary>
/// 获取Where条件SQL
/// </summary>
public string GetWhereSql()
{
if (string.IsNullOrWhiteSpace(WhereBuilder.ToString().Trim()))
{
WhereBuilder.Append(" 1=1");
}
return(WhereBuilder.ToString().Trim());
}
public void where_string_is_null_value()
{
string cleanTitle = null;
_subject = Where(x => x.CleanTitle == cleanTitle);
_subject.ToString().Should().Be($"(\"Movies\".\"CleanTitle\" IS NULL)");
}
public void where_equal_lazy_property()
{
_subject = Where(x => x.QualityProfile.Value.Id == 1);
_subject.Parameters.ParameterNames.Should().HaveCount(1);
_subject.ToString().Should().Be($"(\"QualityProfiles\".\"Id\" = @Clause1_P1)");
_subject.Parameters.Get <int>("Clause1_P1").Should().Be(1);
}
public void where_column_ends_with_string()
{
var test = "small";
_subject = Where(x => x.CleanName.EndsWith(test));
_subject.ToString().Should().Be($"(\"Artists\".\"CleanName\" LIKE '%' || @Clause1_P1)");
_subject.Parameters.Get <string>("Clause1_P1").Should().Be(test);
}
public void where_string_contains_column()
{
var test = "small";
_subject = Where(x => test.Contains(x.CleanName));
_subject.ToString().Should().Be($"(@Clause1_P1 LIKE '%' || \"Artists\".\"CleanName\" || '%')");
_subject.Parameters.Get <string>("Clause1_P1").Should().Be(test);
}
public void where_column_starts_with_string()
{
var test = "small";
_subject = Where(x => x.CleanTitle.StartsWith(test));
_subject.ToString().Should().Be($"(\"Movies\".\"CleanTitle\" LIKE @Clause1_P1 || '%')");
_subject.Parameters.Get <string>("Clause1_P1").Should().Be(test);
}
public void where_column_contains_string()
{
var test = "small";
_subject = Where(x => x.CleanName.Contains(test));
_subject.ToString().Should().Be($"(\"Authors\".\"CleanName\" LIKE '%' || @Clause1_P1 || '%')");
_subject.Parameters.Get <string>("Clause1_P1").Should().Be(test);
}
public void where_equal_variable()
{
var id = 10;
_subject = Where(x => x.Id == id);
_subject.ToString().Should().Be($"(\"Artists\".\"Id\" = @Clause1_P1)");
_subject.Parameters.Get <int>("Clause1_P1").Should().Be(id);
}
public void where_equal_null_property()
{
var movie = new Movie {
CleanTitle = null
};
_subject = Where(x => x.CleanTitle == movie.CleanTitle);
_subject.ToString().Should().Be($"(\"Movies\".\"CleanTitle\" IS NULL)");
}
public void where_in_list_2()
{
var list = new List <int> {
1, 2, 3
};
_subject = Where(x => x.CleanName == "test" && list.Contains(x.Id));
_subject.ToString().Should().Be($"((\"Artists\".\"CleanName\" = @Clause1_P1) AND (\"Artists\".\"Id\" IN (1, 2, 3)))");
}
public void where_in_list()
{
var list = new List <int> {
1, 2, 3
};
_subject = Where(x => list.Contains(x.Id));
_subject.ToString().Should().Be($"(\"Artists\".\"Id\" IN (1, 2, 3))");
}
public void where_equal_null_property()
{
var artist = new Artist {
CleanName = null
};
_subject = Where(x => x.CleanName == artist.CleanName);
_subject.ToString().Should().Be($"(\"Artists\".\"CleanName\" IS NULL)");
}
public void enum_in_list()
{
var allowed = new List <AuthorStatusType> {
AuthorStatusType.Continuing, AuthorStatusType.Ended
};
_subject = WhereMetadata(x => allowed.Contains(x.Status));
_subject.ToString().Should().Be($"(\"AuthorMetadata\".\"Status\" IN @Clause1_P1)");
}
public void where_in_string_list()
{
var list = new List <string> {
"first", "second", "third"
};
_subject = Where(x => list.Contains(x.CleanName));
_subject.ToString().Should().Be($"(\"Artists\".\"CleanName\" IN @Clause1_P1)");
}
public void where_equal_null_property()
{
var author = new Author {
CleanName = null
};
_subject = Where(x => x.CleanName == author.CleanName);
_subject.ToString().Should().Be($"(\"Authors\".\"CleanName\" IS NULL)");
}
public void where_in_list()
{
var list = new List <int> {
1, 2, 3
};
_subject = Where(x => list.Contains(x.Id));
_subject.ToString().Should().Be($"(\"Movies\".\"Id\" IN (1, 2, 3))");
_subject.Parameters.ParameterNames.Should().BeEmpty();
}
public void where_equal_property()
{
var movie = new Movie {
Id = 10
};
_subject = Where(x => x.Id == movie.Id);
_subject.Parameters.ParameterNames.Should().HaveCount(1);
_subject.ToString().Should().Be($"(\"Movies\".\"Id\" = @Clause1_P1)");
_subject.Parameters.Get <int>("Clause1_P1").Should().Be(movie.Id);
}
public void where_equal_property()
{
var artist = new Artist {
Id = 10
};
_subject = Where(x => x.Id == artist.Id);
_subject.Parameters.ParameterNames.Should().HaveCount(1);
_subject.ToString().Should().Be($"(\"Artists\".\"Id\" = @Clause1_P1)");
_subject.Parameters.Get <int>("Clause1_P1").Should().Be(artist.Id);
}
public void SqlServerDeleteQuery_ShouldGenQuery()
{
// Arrange
var command = new System.Data.SqlClient.SqlCommand();
var where = new WhereBuilder <Person>(command, p => p.ID == 5, false);
IQuery query = new DeleteQuery("dbo.People", where.ToString());
// Act
string queryText = query.Generate();
// Assert
Assert.IsNotNull(queryText);
Assert.IsTrue(queryText.Contains("DELETE FROM dbo.People"));
Assert.IsTrue(queryText.Contains("WHERE ([ID] = @P0)"));
Assert.AreEqual(command.Parameters["@P0"].Value, 5);
}
public void SqlServerUpdateQuery_ShouldGenQuery()
{
// Arrange
var command = new System.Data.SqlClient.SqlCommand();
var db = MockRepository.GenerateStub<IDataMapper>();
db.Expect(d => d.Command).Return(command);
ColumnMapCollection columns = MapRepository.Instance.GetColumns(typeof(Person));
MappingHelper mappingHelper = new MappingHelper(db);
Person person = new Person();
person.ID = 1;
person.Name = "Jordan";
person.Age = 33;
person.IsHappy = true;
person.BirthDate = new DateTime(1977, 1, 22);
mappingHelper.CreateParameters<Person>(person, columns, true);
int idValue = 7;
TableCollection tables = new TableCollection { new Table(typeof(Person)) };
Expression<Func<Person, bool>> filter = p => p.ID == person.ID || p.ID == idValue || p.Name == person.Name && p.Name == "Bob";
var where = new WhereBuilder<Person>(command, new SqlServerDialect(), filter, tables, false, true);
IQuery query = new UpdateQuery(new SqlServerDialect(), columns, command, "dbo.People", where.ToString());
// Act
string queryText = query.Generate();
// Assert
Assert.IsNotNull(queryText);
Assert.IsTrue(queryText.Contains("UPDATE [dbo].[People]"));
Assert.IsTrue(queryText.Contains("[Name]"));
Assert.IsTrue(queryText.Contains("[Age]"));
Assert.IsTrue(queryText.Contains("[IsHappy]"));
Assert.IsTrue(queryText.Contains("[BirthDate]"));
Assert.IsTrue(queryText.Contains("[ID] = @P4"));
Assert.IsTrue(queryText.Contains("[ID] = @P5"));
Assert.IsTrue(queryText.Contains("[Name] = @P6"));
Assert.IsTrue(queryText.Contains("[Name] = @P7"));
Assert.AreEqual(command.Parameters["@P4"].Value, 1);
Assert.AreEqual(command.Parameters["@P5"].Value, 7);
Assert.AreEqual(command.Parameters["@P6"].Value, "Jordan");
Assert.AreEqual(command.Parameters["@P7"].Value, "Bob");
}
public void SqlServerSelectQuery_ShouldGenQuery()
{
// Arrange
var command = new System.Data.SqlClient.SqlCommand();
var db = MockRepository.GenerateStub <IDataMapper>();
db.Expect(d => d.Command).Return(command);
ColumnMapCollection columns = MapRepository.Instance.GetColumns(typeof(Person));
MappingHelper mappingHelper = new MappingHelper(db);
var orderBy = MockRepository.GenerateStub <ISortQueryBuilder>();
orderBy.Expect(o => o.BuildQuery()).Return("");
Person person = new Person();
person.ID = 1;
person.Name = "Jordan";
person.Age = 33;
person.IsHappy = true;
person.BirthDate = new DateTime(1977, 1, 22);
List <Person> list = new List <Person>();
TableCollection tables = new TableCollection {
new Table(typeof(Person))
};
Expression <Func <Person, bool> > filter = p => p.Name == "John" && p.Age > 15 || p.Age < 5 && p.Age > 1;
var where = new WhereBuilder <Person>(command, new SqlServerDialect(), filter, tables, false, true);
IQuery query = new SelectQuery(new SqlServerDialect(), tables, where.ToString(), orderBy, false);
// Act
string queryText = query.Generate();
// Assert
Assert.IsNotNull(queryText);
Assert.AreEqual(command.Parameters["@P0"].Value, "John");
Assert.AreEqual(command.Parameters["@P1"].Value, 15);
Assert.AreEqual(command.Parameters["@P2"].Value, 5);
Assert.AreEqual(command.Parameters["@P3"].Value, 1);
Assert.IsTrue(queryText.Contains("([t0].[Name] = @P0) AND ([t0].[Age] > @P1))"));
Assert.IsTrue(queryText.Contains("([t0].[Age] < @P2) AND ([t0].[Age] > @P3))"));
}
public void SqlServerUpdateQuery_ShouldGenQuery()
{
// Arrange
var command = new System.Data.SqlClient.SqlCommand();
ColumnMapCollection columns = MapRepository.Instance.GetColumns(typeof(Person));
MappingHelper mappingHelper = new MappingHelper(command);
Person person = new Person();
person.ID = 1;
person.Name = "Jordan";
person.Age = 33;
person.IsHappy = true;
person.BirthDate = new DateTime(1977, 1, 22);
mappingHelper.CreateParameters <Person>(person, columns, false, true);
int idValue = 7;
var where = new WhereBuilder <Person>(command, p => p.ID == person.ID || p.ID == idValue || p.Name == person.Name && p.Name == "Bob", false);
IQuery query = new UpdateQuery(columns, command, "dbo.People", where.ToString());
// Act
string queryText = query.Generate();
// Assert
Assert.IsNotNull(queryText);
Assert.IsTrue(queryText.Contains("UPDATE dbo.People"));
Assert.IsTrue(queryText.Contains("[Name]"));
Assert.IsTrue(queryText.Contains("[Age]"));
Assert.IsTrue(queryText.Contains("[IsHappy]"));
Assert.IsTrue(queryText.Contains("[BirthDate]"));
Assert.IsTrue(queryText.Contains("[ID] = @P5"));
Assert.IsTrue(queryText.Contains("[ID] = @P6"));
Assert.IsTrue(queryText.Contains("[Name] = @P7"));
Assert.IsTrue(queryText.Contains("[Name] = @P8"));
Assert.AreEqual(command.Parameters["@P5"].Value, 1);
Assert.AreEqual(command.Parameters["@P6"].Value, 7);
Assert.AreEqual(command.Parameters["@P7"].Value, "Jordan");
Assert.AreEqual(command.Parameters["@P8"].Value, "Bob");
}
public void SqlServerDeleteQuery_ShouldGenQuery()
{
// Arrange
var command = new System.Data.SqlClient.SqlCommand();
TableCollection tables = new TableCollection {
new Table(typeof(Person))
};
Expression <Func <Person, bool> > filter = p => p.ID == 5;
var where = new WhereBuilder <Person>(command, new SqlServerDialect(), filter, tables, false, false);
IQuery query = new DeleteQuery(new Dialect(), tables[0], where.ToString());
// Act
string queryText = query.Generate();
// Assert
Assert.IsNotNull(queryText);
Assert.IsTrue(queryText.Contains("DELETE FROM [PersonTable]"));
Assert.IsTrue(queryText.Contains("WHERE ([ID] = @P0)"));
Assert.AreEqual(command.Parameters["@P0"].Value, 5);
}
public void SqlServerUpdateQuery_ShouldGenQuery()
{
// Arrange
var command = new System.Data.SqlClient.SqlCommand();
ColumnMapCollection columns = MapRepository.Instance.GetColumns(typeof(Person));
MappingHelper mappingHelper = new MappingHelper(command);
Person person = new Person();
person.ID = 1;
person.Name = "Jordan";
person.Age = 33;
person.IsHappy = true;
person.BirthDate = new DateTime(1977, 1, 22);
mappingHelper.CreateParameters<Person>(person, columns, false, true);
int idValue = 7;
var where = new WhereBuilder<Person>(command, p => p.ID == person.ID || p.ID == idValue || p.Name == person.Name && p.Name == "Bob", false);
IQuery query = new UpdateQuery(columns, command, "dbo.People", where.ToString());
// Act
string queryText = query.Generate();
// Assert
Assert.IsNotNull(queryText);
Assert.IsTrue(queryText.Contains("UPDATE dbo.People"));
Assert.IsTrue(queryText.Contains("[Name]"));
Assert.IsTrue(queryText.Contains("[Age]"));
Assert.IsTrue(queryText.Contains("[IsHappy]"));
Assert.IsTrue(queryText.Contains("[BirthDate]"));
Assert.IsTrue(queryText.Contains("[ID] = @P5"));
Assert.IsTrue(queryText.Contains("[ID] = @P6"));
Assert.IsTrue(queryText.Contains("[Name] = @P7"));
Assert.IsTrue(queryText.Contains("[Name] = @P8"));
Assert.AreEqual(command.Parameters["@P5"].Value, 1);
Assert.AreEqual(command.Parameters["@P6"].Value, 7);
Assert.AreEqual(command.Parameters["@P7"].Value, "Jordan");
Assert.AreEqual(command.Parameters["@P8"].Value, "Bob");
}
public int Delete <T>(string tableName, Expression <Func <T, bool> > filter)
{
// Remember sql mode
var previousSqlMode = this.SqlMode;
SqlMode = SqlModes.Text;
var mappingHelper = new MappingHelper(this);
if (tableName == null)
{
tableName = MapRepository.Instance.GetTableName(typeof(T));
}
var dialect = QGen.QueryFactory.CreateDialect(this);
TableCollection tables = new TableCollection();
tables.Add(new Table(typeof(T)));
var where = new WhereBuilder <T>(Command, dialect, filter, tables, false, false);
IQuery query = QueryFactory.CreateDeleteQuery(dialect, tables[0], where.ToString());
Command.CommandText = query.Generate();
int rowsAffected = 0;
try
{
OpenConnection();
rowsAffected = Command.ExecuteNonQuery();
}
finally
{
CloseConnection();
}
// Return to previous sql mode
SqlMode = previousSqlMode;
return(rowsAffected);
}
public void SqlServerDeleteQuery_ShouldGenQuery()
{
// Arrange
var command = new System.Data.SqlClient.SqlCommand();
var where = new WhereBuilder<Person>(command, p => p.ID == 5, false);
IQuery query = new DeleteQuery("dbo.People", where.ToString());
// Act
string queryText = query.Generate();
// Assert
Assert.IsNotNull(queryText);
Assert.IsTrue(queryText.Contains("DELETE FROM dbo.People"));
Assert.IsTrue(queryText.Contains("WHERE ([ID] = @P0)"));
Assert.AreEqual(command.Parameters["@P0"].Value, 5);
}
public void SqlServerSelectQuery_MethodExpression_BinaryExpression()
{
// Arrange
var command = new System.Data.SqlClient.SqlCommand();
ColumnMapCollection columns = MapRepository.Instance.GetColumns(typeof(Person));
MappingHelper mappingHelper = new MappingHelper(command);
Person person = new Person();
person.ID = 1;
person.Name = "Jordan";
person.Age = 33;
person.IsHappy = true;
person.BirthDate = new DateTime(1977, 1, 22);
List<Person> list = new List<Person>();
var where = new WhereBuilder<Person>(command, p => p.Name.Contains("John") && p.Age > 5, false);
IQuery query = new SelectQuery(columns, "dbo.People", where.ToString(), "", false);
// Act
string queryText = query.Generate();
// Assert
Assert.IsNotNull(queryText);
Assert.AreEqual(command.Parameters["@P0"].Value, "John");
Assert.AreEqual(command.Parameters["@P1"].Value, 5);
Assert.IsTrue(queryText.Contains("[Name] LIKE '%' + @P0 + '%'"));
Assert.IsTrue(queryText.Contains("[Age] > @P1"));
}
public void SqlServerDeleteQuery_ShouldGenQuery()
{
// Arrange
var command = new System.Data.SqlClient.SqlCommand();
TableCollection tables = new TableCollection { new Table(typeof(Person)) };
Expression<Func<Person, bool>> filter = p => p.ID == 5;
var where = new WhereBuilder<Person>(command, new SqlServerDialect(), filter, tables, false, false);
IQuery query = new DeleteQuery(new Dialect(), tables[0], where.ToString());
// Act
string queryText = query.Generate();
// Assert
Assert.IsNotNull(queryText);
Assert.IsTrue(queryText.Contains("DELETE FROM [PersonTable]"));
Assert.IsTrue(queryText.Contains("WHERE ([ID] = @P0)"));
Assert.AreEqual(command.Parameters["@P0"].Value, 5);
}
public void SqlServerSelectQuery_MethodExpression_BinaryExpression()
{
// Arrange
var command = new System.Data.SqlClient.SqlCommand();
var db = MockRepository.GenerateStub<IDataMapper>();
db.Expect(d => d.Command).Return(command);
ColumnMapCollection columns = MapRepository.Instance.GetColumns(typeof(Person));
MappingHelper mappingHelper = new MappingHelper(db);
var orderBy = MockRepository.GenerateStub<ISortQueryBuilder>();
orderBy.Expect(o => o.BuildQuery()).Return("");
Person person = new Person();
person.ID = 1;
person.Name = "Jordan";
person.Age = 33;
person.IsHappy = true;
person.BirthDate = new DateTime(1977, 1, 22);
List<Person> list = new List<Person>();
TableCollection tables = new TableCollection { new Table(typeof(Person)) };
Expression<Func<Person, bool>> filter = p => p.Name.Contains("John") && p.Age > 5;
var where = new WhereBuilder<Person>(command, new SqlServerDialect(), filter, tables, false, true);
IQuery query = new SelectQuery(new SqlServerDialect(), tables, where.ToString(), orderBy, false);
// Act
string queryText = query.Generate();
// Assert
Assert.IsNotNull(queryText);
Assert.AreEqual(command.Parameters["@P0"].Value, "John");
Assert.AreEqual(command.Parameters["@P1"].Value, 5);
Assert.IsTrue(queryText.Contains("[Name] LIKE '%' + @P0 + '%'"));
Assert.IsTrue(queryText.Contains("[Age] > @P1"));
}