private static void DynamicSql()
{
using (var context = new QueryingContext())
{
context.Database.Initialize(true);
context.Database.Log = Log;
var sql = new SqlSentenceBuilder();
dynamic parameters = new ExpandoObject();
sql.AddSelect("SUM(Units) AS Units, Name");
sql.AddFrom("OrderLines INNER JOIN Products ON OrderLines.ProductId = Products.Id");
sql.AddGroupBy("Name");
const bool filter = true;
if (filter)
{
sql.AddWhere("OrderId = @OrderId", "myFilter");
parameters.OrderId = 1;
//sql.RemoveWhere("myFilter");
//((IDictionary<string, object>)parameters).Remove("OrderId");
}
sql.AddOrderBy("Name");
sql.EnablePaginationWithCte(1, 10);
Console.WriteLine(sql.Build());
var data = context.Database.Connection.Query <ComplexSqlType>(sql.Build(), (ExpandoObject)parameters);
foreach (var item in data)
{
Console.WriteLine(item);
}
}
}
public void Pagination()
{
var builder = new SqlSentenceBuilder();
builder.AddSelect(@"Canal
,Tipologia
,NivelClasificacion1
,NivelClasificacion2
,SUM(MerchandisePlanningFilas.Facturacion) AS Facturacion");
builder.AddFrom("MerchandisePlanningFilas");
builder.AddWhere("VersionId = 4");
builder.AddOrderBy("MerchandisePlanningFilas.Tipologia ASC, NivelClasificacion1 ASC, [MerchandisePlanningFilas].NivelClasificacion2 ASC");
builder.AddGroupBy("Canal");
builder.AddGroupBy("Tipologia");
builder.AddGroupBy("NivelClasificacion1, NivelClasificacion2");
builder.EnablePaginationWithCte(1, 10);
Debug.Print(builder.BuildWithCount());
Debug.Print(builder.Build());
}
public void Pagination()
{
var builder = new SqlSentenceBuilder();
builder.AddSelect(@"Canal
,Tipologia
,NivelClasificacion1
,NivelClasificacion2
,SUM(MerchandisePlanningFilas.Facturacion) AS Facturacion" );
builder.AddFrom("MerchandisePlanningFilas");
builder.AddWhere("VersionId = 4");
builder.AddOrderBy("MerchandisePlanningFilas.Tipologia ASC, NivelClasificacion1 ASC, [MerchandisePlanningFilas].NivelClasificacion2 ASC");
builder.AddGroupBy("Canal");
builder.AddGroupBy("Tipologia");
builder.AddGroupBy("NivelClasificacion1, NivelClasificacion2");
builder.EnablePaginationWithCte(1, 10);
Debug.Print(builder.BuildWithCount());
Debug.Print(builder.Build());
}