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);
                }
            }
        }
예제 #2
0
 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());
 }
예제 #3
0
        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());
        }