SqlSugar应用

实体自动映射到dto

db.Queryable<AbpUser>().Select<AbpUsersDto>(); //实体自动映射到dto

手动指定部分列的映射规则

[SugarTable("AbpUsers")]
public class AbpUser
{
    public int Id { get; set; }
    public string UserName { get; set; } //用户名
    public string Surname { get; set; }  //昵称
    public string EmailAddress { get; set; }
}

public class AbpUsersDto
{
    public int UserNameLength { get; set; }
    public int EmailAddressLength { get; set; }

    public string Surname { get; set; }
}

db.Queryable<AbpUser>().Select(it => new AbpUsersDto()
{
	UserNameLength = it.UserName.Length, //手动指定映射规则
	EmailAddressLength = it.EmailAddress.Length 手动指定映射规则
},
true)//其余字段自动映射

//产生的脚本如下
SELECT LEN([UserName]) AS [UserNameLength] , LEN([EmailAddress]) AS [EmailAddressLength] ,[Surname] AS [Surname] FROM [AbpUsers] 

根据LeftJoin的别名自动映射

[SugarTable("InvOutBill")]
public class InvOutBill
{
    public string BillNo { get; set; }
    public int CreatedUserId { get; set; }
}

public class InvOutBillDto
{
    public string BillNo { get; set; }
    public string CreateUserName { get; set; } //创建者账号：根据LeftJoin的别名自动映射
}

db.Queryable<InvOutBill>()
.LeftJoin<AbpUsers>((i, Create)=>i.CreatedUserId == Create.Id)
.Select<InvOutBillDto>()

更新部分字段

//方法1
_repSysUser.AsUpdateable(obj).UpdateColumns(it => new { it.IsDelete, it.UpdateTime, it.UpdateUserId }).ExecuteCommand();

//方法2
_rep.Update(u => new xxx()
        {
            IsDelete = True,
        }, u => u.Id == id);

批量更新

    public bool UpdateSequences(SequencesInput input)
    {
        var dtList = new List<Dictionary<string, object>>();
        foreach (var item in input.Items)
        {
            var dt = new Dictionary<string, object>();
            dt.Add("id", item.Id);
            dt.Add("Sequence", item.Sequence);
            dtList.Add(dt);
        }

        var t666 = _rep.Context.Updateable(dtList).AS("QueTemplateQuestion").WhereColumns("Id").ExecuteCommand();
        return true;
    }

生成的脚本如下

UPDATE S
SET S.[Sequence] = T.[Sequence]
FROM [QueTemplateQuestion] S
     INNER JOIN (SELECT 541524708270149 AS [id], 1 AS [Sequence]
                 UNION ALL
                 SELECT 541525189718085 AS [id], 3 AS [Sequence]) T
        ON S.[Id] = T.[Id]