分表查询SplitTable报错,spBeginTime参数绑定无效 返回

SqlSugar
27 139
该叫什么 billkun 发布于1周前
悬赏:5 飞吻

 [SplitTable(SplitType.Day)]//按年分表 (自带分表支持 年、季、月、周、日)

    [SugarTable(tableName: "POSITION_{year}{month}{day}", tableDescription: "GISTIP")]//3个变量必须要有,这么设计为了兼容开始按年,后面改成按月、按日

    public class Position

    {

         #region 属性定义

        /// <summary>

        /// 雪花ID

        /// </summary>

        [SugarColumn(ColumnName = "ID", ColumnDataType = "BIGINT", IsPrimaryKey = true)] //设置主键

        public long Id { get; set; }

        /// <summary>

        /// 设备唯一码

        /// </summary>

        [SugarColumn(ColumnName = "EQUID", ColumnDataType = "VARCHAR(36)", IsNullable = false, DefaultValue = "")]

        public string EquId { get; set; }

        /// <summary>

        /// 设备版本

        /// </summary>

        [SugarColumn(ColumnName = "EQUVERSION", ColumnDataType = "VARCHAR(36)", IsNullable = true, DefaultValue = "")]

        public string EquVersion { get; set; }

        /// <summary>

        /// 绑定目标ID

        /// </summary>

        [SugarColumn(ColumnName = "TARGETID", ColumnDataType = "BIGINT", IsNullable = false, DefaultValue = "0")]

        public long? TargetId { get; set; }

        /// <summary>

        /// 绑定目标类型

        /// </summary>

        [SugarColumn(ColumnName = "TARGETTYPE", ColumnDataType = "TINYINT", IsNullable = false, DefaultValue = "0")]

        public UInt16? TargetType { get; set; }

        /// <summary>

        /// 操作者ID

        /// </summary>

        [SugarColumn(ColumnName = "OPERATEID", ColumnDataType = "BIGINT", IsNullable = false, DefaultValue = "0")]

        public long? OperateId { get; set; }

        /// <summary>

        /// 分表字段 在插入的时候会根据这个字段插入哪个表,在更新删除的时候用这个字段找出相关表

        /// 创建时间

        /// </summary>

        [SplitField]

        [SugarColumn(ColumnName = "CREATEDATE", ColumnDataType = "DATETIME", IsNullable = false)]

        public DateTime CreateDate { get; set; }

        /// <summary>

        /// 经度

        /// </summary>

        [SugarColumn(ColumnName = "LONGITUDE", ColumnDataType = "NUMBER(18,8)", IsNullable = false, DefaultValue = "0")]

        public decimal? Longitude { get; set; }

        /// <summary>

        /// 纬度

        /// </summary>

        [SugarColumn(ColumnName = "LATITUDE", ColumnDataType = "NUMBER(18,8)", IsNullable = false, DefaultValue = "0")]

        public decimal? Latitude { get; set; }

        /// <summary>

        /// 高程

        /// </summary>

        [SugarColumn(ColumnName = "ELEVATION", ColumnDataType = "NUMBER(8,2)", IsNullable = false, DefaultValue = "0")]

        public decimal? Elevation { get; set; }

        /// <summary>

        /// 方向

        /// </summary>

        [SugarColumn(ColumnName = "DIRECTION", ColumnDataType = "NUMBER(6,2)", IsNullable = false, DefaultValue = "0")]

        public decimal? Direction { get; set; }

        /// <summary>

        /// 速度

        /// </summary>

        [SugarColumn(ColumnName = "SPEED", ColumnDataType = "NUMBER(3)", IsNullable = false, DefaultValue = "0")]

        public UInt32? Speed { get; set; }

        /// <summary>

        /// 速度

        /// </summary>

        [SugarColumn(ColumnName = "SPEEDLIMIT", ColumnDataType = "NUMBER(3)", IsNullable = true, DefaultValue = "0")]

        public UInt32? SpeedLimit { get; set; }

        /// <summary>

        /// 速度

        /// </summary>

        [SugarColumn(ColumnName = "SPEEDLIMITTYPE", ColumnDataType = "TINYINT", IsNullable = true, DefaultValue = "0")]

        public UInt16? SpeedLimitType { get; set; }

        /// <summary>

        /// 标签 实时0 历史1 2补录 

        /// </summary>

        [SugarColumn(ColumnName = "TAG", ColumnDataType = "TINYINT", IsNullable = false, DefaultValue = "")]

        public UInt16? Tag { get; set; }

        /// <summary>

        /// 是否定位

        /// </summary>

        [SugarColumn(ColumnName = "ISANCHOR", ColumnDataType = "BIT", IsNullable = false, DefaultValue = "1")]

        public bool? IsAnchor { get; set; }

        /// <summary>

        /// acc是否开

        /// </summary>

        [SugarColumn(ColumnName = "ISACC", ColumnDataType = "BIT", IsNullable = false, DefaultValue = "1")]

        public bool? IsAcc { get; set; }

        /// <summary>

        /// 是否途径区域

        /// </summary>

        [SugarColumn(ColumnName = "ISPOI", ColumnDataType = "BIT", IsNullable = false, DefaultValue = "0")]

        public bool? IsPOI { get; set; }

        /// <summary>

        /// 是否超速

        /// </summary>

        [SugarColumn(ColumnName = "ISOVERSPEED", ColumnDataType = "BIT", IsNullable = false, DefaultValue = "0")]

        public bool? IsOverSpeed { get; set; }

        /// <summary>

        /// 是否超时 疲劳

        /// </summary>

        [SugarColumn(ColumnName = "ISOVERTIME", ColumnDataType = "BIT", IsNullable = false, DefaultValue = "0")]

        public bool? IsOverTime { get; set; }

        /// <summary>

        /// 是否禁行

        /// </summary>

        [SugarColumn(ColumnName = "ISPROHIBITION", ColumnDataType = "BIT", IsNullable = false, DefaultValue = "0")]

        public bool? IsProhibition { get; set; }

        /// <summary>

        /// 是否Dsm报警

        /// </summary>

        [SugarColumn(ColumnName = "ISDSMALARM", ColumnDataType = "BIT", IsNullable = false, DefaultValue = "0")]

        public bool? IsDsmAlarm { get; set; }

        /// <summary>

        /// 是否Dsm报警

        /// </summary>

        [SugarColumn(ColumnName = "ISADASALARM", ColumnDataType = "BIT", IsNullable = false, DefaultValue = "0")]

        public bool? IsAdasAlarm { get; set; }

        /// <summary>

        /// 途径区域ID

        /// </summary>

        [SugarColumn(ColumnName = "AREAID", ColumnDataType = "BIGINT", IsNullable = false, DefaultValue = "0")]

        public long? AreaId { get; set; }

        /// <summary>

        /// 状态

        /// </summary>

        [SugarColumn(ColumnName = "STATUS", ColumnDataType = "VARCHAR(128)", IsNullable = true, DefaultValue = "")]

        public string Status { get; set; }

        /// <summary>

        /// 地址

        /// </summary>

        [SugarColumn(ColumnName = "ADDRESS", ColumnDataType = "VARCHAR(128)", IsNullable = true, DefaultValue = "")]

        public string Address { get; set; }

        /// <summary>

        /// 0 预留 1 伪删除(误报)

        /// </summary>

        [SugarColumn(ColumnName = "ISDELETED", ColumnDataType = "BIT", IsNullable = false, DefaultValue = "0")]

        public bool? IsDeleted { get; set; }


        #endregion

}

}


--查询语句 测试

 var list = await db.Queryable<Position>().SplitTable(DateTime.MinValue, DateTime.Now).ToListAsync();

            return list;


sqlsugar 版本 5.1.1


热忱回答27

  • DateTime.MinValue 这个值换一下看看 换成 convert.todatetime("1900-01-01")

    0 回复
  • @fate sta:错误信息一样

    0 回复
  • @fate sta

    SELECT * FROM (SELECT *,ROW_NUMBER() OVER( ORDER BY sysdate ) AS RowIndex  FROM  (SELECT "ID","EQUID","EQUVERSION","TARGETID","TARGETTYPE","OPERATEID","CREATEDATE","LONGITUDE","LATITUDE","ELEVATION","DIRECTION","SPEED","SPEEDLIMIT","SPEEDLIMITTYPE","TAG","ISANCHOR","ISACC","ISPOI","ISOVERSPEED","ISOVERTIME","ISPROHIBITION","ISDSMALARM","ISADASALARM","AREAID","STATUS","ADDRESS","ISDELETED" FROM "POSITION_20220805"  WHERE  "CREATEDATE">=:spBeginTime AND "CREATEDATE"<= :spEndTime  UNION ALL 

    SELECT "ID","EQUID","EQUVERSION","TARGETID","TARGETTYPE","OPERATEID","CREATEDATE","LONGITUDE","LATITUDE","ELEVATION","DIRECTION","SPEED","SPEEDLIMIT","SPEEDLIMITTYPE","TAG","ISANCHOR","ISACC","ISPOI","ISOVERSPEED","ISOVERTIME","ISPROHIBITION","ISDSMALARM","ISADASALARM","AREAID","STATUS","ADDRESS","ISDELETED" FROM "POSITION_20220804"  WHERE  "CREATEDATE">=:spBeginTime AND "CREATEDATE"<= :spEndTime ) unionTable  ) T WHERE RowIndex BETWEEN 1 AND 2


    -- 错误信息 :Parameter 'SPBEGINTIME' not found in the collection.


    0 回复
  •  你这个是什么库 我codefirst表都建不起来

    0 回复
  • @fate sta:达梦数据库,表是可以建立的 精准查一天的也是正常的

    0 回复
  • @fate sta:最近一次我提交过 希望支持达梦数据库BulkCopy支持

    0 回复
  • @billkun:达梦最近兼容一下,可能没处理好 

    0 回复
  • @fate sta:你建立不起來 估计我设置了 [SugarTable(tableName: "POSITION_{year}{month}{day}", tableDescription: "GISTIP")]//  我这边是多库操作

    0 回复
  • @fate sta:麻烦 推进下速度

    0 回复
  • @fate sta:目前项目上对于达梦数据库应用 急需: 1 BulkCopy支持 和 2 分库分表下(按时间分表) 跨时间段的访问; 达梦数据库的远程连接我已发过你邮箱;你可以远程使用达梦的环境; 麻烦推进下BUG修改的速度和版本的迭代

    0 回复
  • @billkun:Ok 争取周一前完成

    0 回复
  • 分表已修复, 至于bulkcopy 也添加了,不过一直报缺少fastdll,我看其他ORM也这样。你可以向达梦官方咨询一下

    0 回复
  • 0 回复
  • @fate sta:收到 我这边抓紧在项目测试

    0 回复
  • @fate sta:版本已更新 

     startDate = Convert.ToDateTime("2022-08-04");

     endDate = Convert.ToDateTime("2022-08-05");

    var list = await db.Queryable<Position>().SplitTable(beginDate, DateTime.Now).ToListAsync(); 

    依然报错

    spBeginTime参数绑定无效

    ; 我能确定我数据库已经有4号和5号的分表

    0 回复
  • @billkun5.1.2-preview02 截图发一下更新版本是不是预览版本

    0 回复
  • 一会发布个版本可以重现

    0 回复
  • 5.1.2-preview03


    0 回复
  • 发布了

    0 回复
  • @fate sta

    5.1.2-preview03

    可以正常查询

    0 回复
  • @billkun:那你就看一下bulkcopy 原生也会报错一个DLL错误,如果是正版达梦 可以咨询他们官方

    0 回复
  • 提示会缺少DLL

    0 回复
  • @fate sta.NET | 达梦技术文档 (dameng.com) 这个是官方的 

    0 回复
  • @fate sta:不确定是不是这个dmfldr_dll.dll 引用方式;还要继续填坑

    0 回复
  • @billkun:你可以试一试,只要不是这DLL缺失了那差不多就对了,默认sqlsugar不引用 dmflr的

    0 回复
  • @fate sta:测试有效,两个问题可以关闭了;请问5.1.2正式版会在哪个时间点发布,我这边也同步发布我的项目正式版

    0 回复
  • @billkun:这个要有些时间,你先用预览版本

    0 回复