C# sqlSugar 无实体操作

private SqlSugarClient GetDB()
{
    SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
    {
        ConnectionString = "Host=127.0.0.1;Database=test;Username=postgres;Password=123456;SearchPath=public",
        DbType = DbType.PostgreSQL,
        IsAutoCloseConnection = true,
        ConfigureExternalServices = new ConfigureExternalServices()
        {
            EntityNameService = (type, entity) =>
            {
                //禁用字段删除
                entity.IsDisabledDelete = true;
            }
        }
    },
    db =>
    {

        db.Aop.OnLogExecuting = (sql, pars) =>
        {
            //获取原生SQL推荐 5.1.4.63  性能OK
            Console.WriteLine(UtilMethods.GetNativeSql(sql, pars));

            //获取无参数化SQL 对性能有影响，特别大的SQL参数多的，调试使用
            Console.WriteLine(UtilMethods.GetSqlString(DbType.SqlServer, sql, pars));
        };
        //db.Aop.OnLogExecuted = (sql, pars) =>
        //{
        //    Console.WriteLine(UtilMethods.GetNativeSql(sql, pars));
        //};
    });

    return db;
}
[HttpPost]
public void Delete()
{
    SqlSugarClient db = GetDB();

    var deleteable = db.Deleteable<object>().AS("table1");
    deleteable.Where("id=1");
    deleteable.Where("name=@name", new SugarParameter("@name", "test"));
    deleteable.Where(new List<IConditionalModel>
    {
        new ConditionalModel {
             FieldName = "name",
             FieldValue = "value",
             ConditionalType= ConditionalType.Equal
        }
    });

    if (true)
    {
        deleteable.Where("name2='test'");
    }
    deleteable.ExecuteCommand();
}


[HttpPost]
public void UpdateDynamic()
{
    SqlSugarClient db = GetDB();

    var updateable = db.Updateable<object>().AS("table1");
    updateable.SetColumns("name", "");
    updateable.Where("id=1");
    updateable.Where(new List<IConditionalModel>
    {
        new ConditionalModel {
             FieldName = "name",
             FieldValue = "value",
             ConditionalType= ConditionalType.Equal
        }
    });

    if (true)
    {
        updateable.Where("name2='test'");
    }
    updateable.ExecuteCommand();
}

/// <summary>
/// 批量更新
/// </summary>
[HttpPost]
public void UpdateBatch()
{
    SqlSugarClient db = GetDB();

    var dt1 = new Dictionary<string, object>
    {
        { "name", "x1" },
        { "name2", "test1" },
        { "id",1 },
    };

    var dt2 = new Dictionary<string, object>
    {
        { "name", "x2" },
        { "name2", "test2" },
        { "id", 2 },
    };
    var dtList = new List<Dictionary<string, object>>
    {
        dt1,dt2
    };

    var updateable = db.Updateable(dtList).AS("table1");
    updateable.WhereColumns("name");
    updateable.WhereColumns("name","");

    updateable.ExecuteCommand();
}

/// <summary>
/// 根据条件更新数据
/// </summary>
[HttpPost]
public void UpdateCondition()
{
    SqlSugarClient db = GetDB();

    db.Updateable<object>().AS("table1")
     .SetColumns(
        it => 
        SqlFunc.MappingColumn<string>("name"),
        //it => SqlFunc.MappingColumn<string>("price+1")
        "test"
     )
    .Where(new List<IConditionalModel>
    {
        new ConditionalModel {
             FieldName = "name",
             FieldValue = "x1",
             ConditionalType= ConditionalType.Equal
        }
    }).ExecuteCommand();

}

/// <summary>
/// 动态建表、索引
/// </summary>
[HttpPost]
public void Test()
{
    SqlSugarClient db = GetDB();


    string tableName = "table1";
    var typeBilder = db.DynamicBuilder().CreateClass(tableName, new SugarTable() { });

    //可以循环添加列
    var fieldType = typeof(string);
    typeBilder.CreateProperty("id", typeof(int), new SugarColumn() { IsPrimaryKey = true, IsIdentity = true });
    typeBilder.CreateProperty("name", fieldType, new SugarColumn() { IsPrimaryKey = true, DefaultValue = "test" });
    typeBilder.CreateProperty("name2", typeof(string), new SugarColumn() { ColumnDescription = "desc" });
    typeBilder.CreateProperty("name3", typeof(string), new SugarColumn() { ColumnDescription = "desc" });
    //SugarColumn 说明看标题4


    var indexAttr = new SugarIndexAttribute("xx", "Name", OrderByType.Asc);

    //创建类
    var type = typeBilder.BuilderType();//想缓存有typeBilder.WithCache

    ////给Property添加、获取特性
    //var propertyInfo = type.GetProperty("Name");
    //TypeDescriptor.AddAttributes(propertyInfo, indexAttr);

    db.UseTran(() =>
    {
        //创建表
        db.CodeFirst.InitTables(type); //建表属性API看迁移
        //throw new Exception("xx");
        var isUnique = true;
        var indexName = "indexName";
        if (!db.DbMaintenance.IsAnyIndex(indexName))
        {
            db.DbMaintenance.CreateIndex(tableName, new string[] { "name" }, indexName, isUnique);
        }

    }, ex => throw ex);


}


/// <summary>
/// 动态查询
/// </summary>
[HttpPost]
public List<dynamic> Select()
{
    //List<dynamic> t=db.Ado.SqlQuery<dynamic>(sql); 

    SqlSugarClient db = GetDB();

    //普通条件
    //动态条件查询

    //原生SQL用匿名对象 sql 查询
    //List<dynamic> t = db.Ado.SqlQuery<dynamic>("select * from table1");

    var queryable = db.Queryable<dynamic>().AS("table1", "t");

    queryable.AddJoinInfo("table2", "a", "a.id=t.id", JoinType.Left);

    //queryable.Where(new List<IConditionalModel>
    //{
    //    new ConditionalModel {
    //         FieldName = "name",
    //         FieldValue = "x1",
    //         ConditionalType= ConditionalType.Equal
    //    }
    //});
    queryable.Where(new List<IConditionalModel>
    {
        new ConditionalModel {
             FieldName = "t.name",
             FieldValue = "x1",
             ConditionalType= ConditionalType.Like
        }
    });

    var testListName = new List<string> { "x1", "x2" };
    queryable.Where(new List<IConditionalModel>
    {
        new ConditionalModel {
             FieldName = "t.name2",
             FieldValue = string.Join(',',testListName),
             ConditionalType= ConditionalType.In
        }
    });

    queryable.Where(new List<IConditionalModel>
    {
        new ConditionalModel {
             FieldName = "t.name3",
             FieldValue = string.Join(',',testListName),
             ConditionalType= ConditionalType.NotIn
        }
    });


    //动态排序
    queryable.OrderBy("t.id asc");
    queryable.OrderBy("t.name desc");

    //多行数据
    var result = queryable.Clone().ToList();

    //单行数据
    var firstData = queryable.Clone().First();

    //分页
    var pageData = queryable.Clone().ToPageList(1, 10);

    var distinctData = queryable.Clone().Distinct().ToList();
    //queryable.ForEachDataReader((data) =>
    //{

    //});


    //返回数据

    return result;
}