NetCore代码笔记

1.  搜索条件

2.  获取最近一条

3. netcore查询mysql最大、条数等数据

4. 获取实体详情

5. 使用事务、回滚

6. sql搜索中添加In函数

7. linux中运行netcore项目

8. Linq中动态搜索功能

1、搜索条件

搜索条件映射成Sql语句有2种写法，第一种是把输入条件参数转成Expression表达式树，适合简单的Sql查询，第二种是拼接原生Sql，适合复杂的Sql查询。

1 ) 第一种转成Expression表达式树写法

      private Expression<Func<PositionEntity, bool>> ListFilter(PositionListParam param)
      {
          var expression = LinqExtensions.True<PositionEntity>();
          if (param != null)
          {
              if (!string.IsNullOrEmpty(param.PositionName))
              {
                  expression = expression.And(t => t.PositionName.Contains(param.PositionName));
              }
              if (!string.IsNullOrEmpty(param.PositionIds))
              {
                  long[] positionIdArr = CommonHelper.SplitToArray<long>(param.PositionIds, ',');
                  expression = expression.And(t => positionIdArr.Contains(t.Id.Value));
              }
          }
          return expression;
      }

 

调用方法如下：

   public async Task<List<PositionEntity>> GetPageList(PositionListParam param, Pagination pagination)
      {
          var expression = ListFilter(param);
          var list = await this.BaseRepository().FindList(expression, pagination);
          return list.ToList();
      }

 

2 ) 第二种拼接原生Sql写法

 private List<DbParameter> ListFilter(LogApiListParam param, StringBuilder strSql)
      {
          strSql.Append(@"SELECT  a.id as Id,
                                  a.base_modify_time as BaseModifyTime,
                                  a.base_modifier_id as BaseModifierId,
                                  a.log_status as LogStatus,
                          FROM    sys_log_api a
                                  LEFT JOIN sys_user b ON a.base_modifier_id = b.id
                                  LEFT JOIN sys_department c ON b.department_id = c.id
                          WHERE   1 = 1");
          var parameter = new List<DbParameter>();
          if (param != null)
          {
              if (!string.IsNullOrEmpty(param.UserName))
              {
                  strSql.Append(" AND b.user_name like @UserName");
                  parameter.Add(DbParameterExtension.CreateDbParameter("@UserName", '%' + param.UserName + '%'));
              }          
          }
          return parameter;
      }

 

调用方法如下：

   public async Task<List<LogApiEntity>> GetPageList(LogApiListParam param, Pagination pagination)
      {
          var strSql = new StringBuilder();
          List<DbParameter> filter = ListFilter(param, strSql);
          var list = await this.BaseRepository().FindList<LogApiEntity>(strSql.ToString(), filter.ToArray(), pagination);
          return list.ToList();
      }

 

 

2、获取最近一条

//根据某属性获取   public async Task<AreaEntity> GetEntityByAreaCode(string areaCode)
        {
            //todo根据……获取
            return await this.BaseRepository().FindEntity<AreaEntity>(p => p.AreaCode == areaCode);
        }

/// <summary>
        /// 根据手机号码获取最新的短信记录
        /// </summary>
        /// <param name="Mobile"></param>
        /// <returns></returns>
        public async Task<List<VerifiyCodeEntity>> GetLastVerifity(string  Mobile,string code)
        {
            var resu = "";
            var parm = new VerifiyCodeParam();
            parm.Mobile = Mobile;
            parm.Code = code;
            var sql = new StringBuilder();
            var op = ListFilter(parm, sql, " BaseCreateTime desc",1);
            var list = await this.BaseRepository().FindList<VerifiyCodeEntity>(sql.ToString(), op.ToArray());
            return list.ToList();
        }
    private List<DbParameter> ListFilter(VerifiyCodeParam param, StringBuilder strSql,string orderBy="",int limit=0)
        {
            strSql.Append(@"SELECT  BaseCreateTime,Mobile,Code,TypeId from sysverifiycode
                            WHERE   1 = 1");
            var parameter = new List<DbParameter>();
            if (param != null)
            {
                if (!string.IsNullOrEmpty(param.Code))
                {
                    strSql.Append(" AND Code = @Code");
                    parameter.Add(DbParameterExtension.CreateDbParameter("@Code", param.Code));
                }
                if (param.State > 0)
                {
                    strSql.Append(" AND State = @State");
                    parameter.Add(DbParameterExtension.CreateDbParameter("@State", param.State));
                }
                if (!string.IsNullOrEmpty(param.Mobile))
                {
                    strSql.Append(" AND Mobile = @Mobile");
                    parameter.Add(DbParameterExtension.CreateDbParameter("@Mobile",  param.Mobile ));
                }
                if (!orderBy.IsEmpty())
                {
                    strSql.Append("  order by  " + orderBy);
                }
                if (!limit.IsEmpty())
                {
                    strSql.Append("  limit "+limit);
                }
            }
            return parameter;
        }

 

3、获取条数、最大值等

     public async Task<int> GetMaxSort()
        {
            object result = await this.BaseRepository().FindObject("SELECT MAX(NewsSort) FROM SysNews");
            int sort = result.ParseToInt();
            sort++;
            return sort;
        }

 4、获取实体详情

 public async Task<EnterpriseFilingEntity> GetEntityDetail(long id)
        {
            var strSql = new StringBuilder();
            strSql.Append(@"select sys.DictValue as EnterpriseNatureName,sysTypeOfProductionUnit.DictValue as TypeOfProductionUnitName,  sysAreaUnit.DictValue as AreaUnitName, town.Name as TownHealthSupervisionName,e.* from enterprisefiling e 
                                        left JOIN sysdatadictdetail sys on sys.Id=e.EnterpriseNature 
                                        left JOIN sysdatadictdetail sysAreaUnit on sysAreaUnit.Id=e.AreaUnit 
                                        left JOIN sysdatadictdetail sysTypeOfProductionUnit on sysTypeOfProductionUnit.Id=e.TypeOfProductionUnitId 
                                        left JOIN townhealthsupervision town on town.Id=e.TownHealthSupervisionId");
            strSql.AppendFormat(@"  where e.Id={0}",id);
            return await BaseRepository().FindModel<EnterpriseFilingEntity>(strSql.ToString());
        }

 

5、使用事务、回滚

    var db = await this.BaseRepository().BeginTrans();
            try
            {
                long[] idArr = TextHelper.SplitToArray<long>(ids, ',');
                await db.Delete<DepartmentEntity>(idArr);
                await db.CommitTrans();
            }
            catch
            {
                await db.RollbackTrans();
                throw;
            }

 

6、sql搜索中添加In函数

  if (param.Ids!=null)
                {
                    strSql.Append(" AND s.Id in (@ids)");
                    parameter.Add(DbParameterExtension.CreateDbParameter("@ids",string.Join(',',param.Ids)));
                }

7.linux中运行netcore项目

dotnet YiSha.Admin.WebApi.dll

 8.Linq中动态搜索功能

using LinqKit;
var where = PredicateBuilder.New<AreaEntity>();
            if (param != null)
            {
                if (param.ParentAreaId.Trim() != string.Empty)
                {
                    where = where.And(es => es.ParentAreaId.Value == param.ParentAreaId.ToLong());
                }
                if (param.ParentAreaId.Trim() != string.Empty)
                {
                    where = where.And(es => es.ParentAreaId.Value == param.ParentAreaId.ToLong());
                }
                list.Where(where).ToList();