public void Count_StringViewIsValid()
{
Assert.Equal("COUNT(pe.Name) AS TestAlias", SqlField <Person> .Count(p => p.Name, "TestAlias").ToString());
var alias = new SqlAlias <Passport>("t");
Assert.Equal("COUNT(t.Number) AS Passp", SqlField <Passport> .Count(alias, p => p.Number, "Passp").ToString());
}
public void TotalTest()
{
var countFld = SqlField <Person> .Count(p => p.LastName);
var select = new SqlSelect <Person>()
.AddFields(p => p.LastName, p => p.Name)
.AddFields <Passport>(p => p.Number)
.AddFields(countFld)
.InnerJoin <Person, Passport>((person, passport) => person.Id == passport.PersonId)
.Where(SqlFilter <Passport> .From(p => p.Number).IsNotNull().And(p => p.Number).NotEqualTo("3812-808316"))
.GroupBy(p => p.LastName)
.Having(SqlFilter <Person> .From <int>(countFld).GreaterThan(2))
.OrderBy(p => p.LastName);
var expected =
@"SELECT
pe.LastName, pe.Name, pa.Number, COUNT(pe.LastName)
FROM
Person pe
INNER JOIN
Passport pa ON pe.Id = pa.PersonId
WHERE
pa.Number IS NOT NULL AND pa.Number <> '3812-808316'
GROUP BY
pe.LastName
HAVING
COUNT(pe.LastName) > 2
ORDER BY
pe.LastName";
Assert.Equal(expected, select.RawSql);
}
public void IncorrectAliasThrowsException()
{
var select = new SqlSelect <Person>()
.InnerJoin <Person, Passport>((person, passport) => person.Id == passport.PersonId)
.AddFields(SqlField <Person> .Count(p => p.Id, "pa"));
Assert.Throws <IncorrectAliasException>(() => { var cmd = select.ParametricSql; });
}
public void GroupBy()
{
var select = new SqlSelect <Person>()
.AddFields(SqlField <Person> .Count(p => p.Id))
.GroupBy(p => p.LastName);
var expected =
@"SELECT
COUNT(pe.Id)
FROM
Person pe
GROUP BY
pe.LastName";
Assert.Equal(expected, select.ParametricSql);
}
public void Having()
{
var select = new SqlSelect <Person>()
.AddFields(SqlField <Person> .Count(p => p.Id))
.GroupBy(p => p.LastName)
.Having(SqlFilter <Person> .From <int>(SqlField <Person> .Count(p => p.Id)).GreaterThan(2));
var expected =
@"SELECT
COUNT(pe.Id)
FROM
Person pe
GROUP BY
pe.LastName
HAVING
COUNT(pe.Id) > 2";
Assert.Equal(expected, select.RawSql);
}
public void GreaterThanOrEqual()
{
Assert.Equal("pe.Id >= 5", SqlFilter <Person> .From(m => m.Id).GreaterThanOrEqual(5).RawSql);
Assert.Equal("pe.Name >= pe.LastName", SqlFilter <Person> .From(m => m.Name).GreaterThanOrEqual(m => m.LastName).RawSql);
Assert.Equal("pe.Id >= pa.PersonId", SqlFilter <Person> .From(m => m.Id).GreaterThanOrEqual <Passport>(m => m.PersonId).RawSql);
var peAlias = new SqlAlias <Person>("per");
var paAlias = new SqlAlias <Passport>("pas");
Assert.Equal("pe.Name >= per.LastName", SqlFilter <Person> .From(m => m.Name).GreaterThanOrEqual(m => m.LastName, peAlias).RawSql);
Assert.Equal("pe.Id >= pas.PersonId", SqlFilter <Person> .From(m => m.Id).GreaterThanOrEqual <Passport>(m => m.PersonId, paAlias).RawSql);
Assert.Equal("pe.Id >= COUNT(pa.Id)", SqlFilter <Person> .From(p => p.Id).GreaterThanOrEqual(SqlField <Passport> .Count(p => p.Id)).RawSql);
var alias = new SqlAlias <Passport>("pasp");
Assert.Equal("pe.Id >= COUNT(pasp.Id)", SqlFilter <Person> .From(p => p.Id).GreaterThanOrEqual(SqlField <Passport> .Count(alias, p => p.Id)).ToString());
}
public void NotEqualTo()
{
Assert.Equal("pe.Id <> 5", SqlFilter <Person> .From(m => m.Id).NotEqualTo(5).RawSql);
Assert.Equal("pe.Name <> pe.LastName", SqlFilter <Person> .From(m => m.Name).NotEqualTo(m => m.LastName).RawSql);
Assert.Equal("pe.Id <> pa.PersonId", SqlFilter <Person> .From(m => m.Id).NotEqualTo <Passport>(m => m.PersonId).RawSql);
var peAlias = new SqlAlias <Person>("per");
var paAlias = new SqlAlias <Passport>("pas");
Assert.Equal("pe.Name <> per.LastName", SqlFilter <Person> .From(m => m.Name).NotEqualTo(m => m.LastName, peAlias).RawSql);
Assert.Equal("pe.Id <> pas.PersonId", SqlFilter <Person> .From(m => m.Id).NotEqualTo <Passport>(m => m.PersonId, paAlias).RawSql);
Assert.Equal("pe.Id <> COUNT(pa.Id)", SqlFilter <Person> .From(p => p.Id).NotEqualTo(SqlField <Passport> .Count(p => p.Id)).RawSql);
var alias = new SqlAlias <Passport>("pasp");
Assert.Equal("pe.Id <> COUNT(pasp.Id)", SqlFilter <Person> .From(p => p.Id).NotEqualTo(SqlField <Passport> .Count(alias, p => p.Id)).ToString());
}