public void AndFilter()
{
var where = new SqlWhere(new And(
new Eq(new Field("Title"), new Literal("Some Title")),
new Lt(new Field("Posted"), new Literal(DateTimeOffset.Now))
));
Assert.AreEqual(@"(exists (select * from FieldIndexes where FieldIndexes.RecordId = Records.Id and FieldIndexes.[Name] = 'Title' and FieldIndexes.[Text] = @p0)) and (exists (select * from FieldIndexes where FieldIndexes.RecordId = Records.Id and FieldIndexes.[Name] = 'Posted' and FieldIndexes.[Moment] < @p1))", where.Clause);
Assert.AreEqual(2, where.Parameters.Count);
}
public void InFilter()
{
var where = new SqlWhere(new And(
new Eq(new Field("Title"), new Literal("Some Title")),
new In(new Field("Numbers"), new Literal(1), new Literal(2), new Literal(3), new Literal(4))
));
Assert.AreEqual(@"(exists (select * from FieldIndexes where FieldIndexes.RecordId = Records.Id and FieldIndexes.[Name] = 'Title' and FieldIndexes.[Text] = @p0)) and (exists (select * from FieldIndexes where FieldIndexes.RecordId = Records.Id and FieldIndexes.[Name] = 'Numbers' and FieldIndexes.[Number] in (@p1, @p2, @p3, @p4)))", where.Clause);
Assert.AreEqual(5, where.Parameters.Count);
}
private DbRawSqlQuery <Record> GetRecords()
{
var parameters = new List <object>();
StringBuilder query = new StringBuilder();
// SELECT, FROM and JOINs
query.AppendLine(
@"SELECT Records.Id, Records.ClassName, Records.Storage, Records.Name
FROM Records
LEFT JOIN Classes ON Classes.Name = Records.ClassName
LEFT JOIN Interfaces ON Interfaces.ClassName = Classes.Name"
);
if (sort != null)
{
int i = 0;
foreach (var item in sort)
{
query.AppendLine();
query.AppendFormat("LEFT JOIN (select * from FieldIndexes i where i.Name = '{0}') Sort{1} ON Sort{1}.RecordId = Records.Id",
item.IndexName, i);
i++;
}
}
// WHERE
query.AppendLine();
query.Append("WHERE 1=1");
if (typeof(TModel).IsInterface)
{
query.AppendLine();
query.AppendFormat(@"AND Interfaces.Name = '{0}'", typeof(TModel).FullName);
}
else
{
query.AppendLine();
query.AppendFormat(@"AND Records.ClassName = '{0}'", typeof(TModel).FullName);
}
if (filter != null)
{
var sqlWhere = new SqlWhere(filter);
query.AppendLine();
query.Append("AND (" + sqlWhere.Clause + ")");
parameters.AddRange(sqlWhere.Parameters.Select((o, i) => new SqlParameter("@p" + i, o.Value)));
}
// GROUP BY
query.AppendLine();
query.Append("GROUP BY Records.Id, Records.ClassName, Records.Storage, Records.Name");
// ORDER BY
if (sort != null)
{
query.AppendLine();
query.Append("ORDER BY ");
query.Append(string.Join(", ", sort.Select((o, i) =>
(o.Ascending ? "max" : "min") + "(Sort" + i + ".Text)" + (o.Ascending ? " asc" : " desc") + ", " +
(o.Ascending ? "max" : "min") + "(Sort" + i + ".Moment)" + (o.Ascending ? " asc" : " desc") + ", " +
(o.Ascending ? "max" : "min") + "(Sort" + i + ".Number)" + (o.Ascending ? " asc" : " desc") + ", " +
(o.Ascending ? "max" : "min") + "(Sort" + i + ".Float)" + (o.Ascending ? " asc" : " desc")
)));
}
else
{
query.AppendLine();
query.Append("ORDER BY Records.Name");
}
// PAGING
if (pageSize > 0)
{
query.AppendLine();
query.Append(
@"OFFSET (@currentPage - 1) ROWS
FETCH NEXT @pageSize ROWS ONLY"
);
parameters.Add(new SqlParameter("currentPage", currentPage));
parameters.Add(new SqlParameter("pageSize", pageSize));
}
var records = Db.Database.SqlQuery <Record>(query.ToString(), parameters.ToArray());
return(records);
}