FreeSql
FreeSql copied to clipboard
dotnetcore
当Sqlite使用LeftJoin进行关联时，若关联的子表日期时间字段存储为integer，关联会出现转换错误
假设有3张表：
CREATE TABLE "code_template" (
"id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
"guid" text(64),
"template_name" text(255),
"template_description" text(255),
"template_type" integer,
"template_content" text,
"template_save_directory" text,
"template_save_file_name_rule" integer DEFAULT 2,
"template_save_file_extension" text(255),
"sequence_number" integer DEFAULT 0,
"remark" text(255),
"created_by" text(100) NOT NULL,
"created_by_name" text(100),
"created_time" integer NOT NULL DEFAULT (datetime('now', 'localtime')),
"updated_by" text(100) NOT NULL,
"updated_by_name" text(100),
"updated_time" integer NOT NULL DEFAULT (datetime('now', 'localtime')),
"revision" text(128),
"is_deleted" integer NOT NULL DEFAULT 0
);
CREATE TABLE "code_generate_plan" (
"id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
"plan_name" text(255),
"plan_description" text(500),
"sequence_number" integer DEFAULT 0,
"remark" text(255),
"created_by" text(100) NOT NULL,
"created_by_name" text(100),
"created_time" integer NOT NULL DEFAULT (datetime('now', 'localtime')),
"updated_by" text(100) NOT NULL,
"updated_by_name" text(100),
"updated_time" integer NOT NULL DEFAULT (datetime('now', 'localtime')),
"revision" text(128),
"is_deleted" integer NOT NULL DEFAULT 0
);
CREATE TABLE "code_generate_plan_template" (
"id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
"fk_code_generate_plan_id" integer,
"fk_code_template_id" integer,
"log_level" integer DEFAULT 1,
"file_rewrite" integer DEFAULT 1,
"sequence_number" integer DEFAULT 0,
"is_enabled" integer DEFAULT 1,
"remark" text(255),
"created_by" text(100) NOT NULL,
"created_by_name" text(100),
"created_time" integer NOT NULL DEFAULT (datetime('now', 'localtime')),
"updated_by" text(100) NOT NULL,
"updated_by_name" text(100),
"updated_time" integer NOT NULL DEFAULT (datetime('now', 'localtime')),
"revision" text(128),
"is_deleted" integer NOT NULL DEFAULT 0
);
code_generate_plan_template.fk_code_generate_plan_id = code_generate_plan.id; code_generate_plan_template.fk_code_template_id = code_template.id;
实体结构如下：
[JsonObject(MemberSerialization.OptIn), Table(Name = "code_generate_plan")]
public class CodeGeneratePlanModel
{
[JsonProperty("id")]
[Column(Name = "id", IsIdentity = true, IsPrimary = true)]
[Display(Name = "自增主键")]
public virtual int? Id { get; set; }
[JsonProperty("plan_name")]
[Column(Name = "plan_name", DbType = "Text(255)")]
[Display(Name = "方案名")]
public virtual string? PlanName { get; set; }
[JsonProperty("plan_description")]
[Column(Name = "plan_description", DbType = "Text(500)")]
[Display(Name = "方案说明")]
public virtual string? PlanDescription { get; set; }
[JsonProperty("sequence_number")]
[Column(Name = "sequence_number", DbType = "Integer")]
[Display(Name = "排序号")]
public virtual int? SequenceNumber { get; set; }
[JsonProperty("remark")]
[Column(Name = "remark", DbType = "Text(255)")]
[Display(Name = "备注")]
public virtual string? Remark { get; set; }
[JsonProperty("created_by")]
[Column(Name = "created_by", DbType = "Text")]
[Display(Name = "创建人")]
public virtual string? CreatedBy { get; set; }
[JsonProperty("created_by_name")]
[Column(Name = "created_by_name", DbType = "Text")]
[Display(Name = "创建人名称")]
public virtual string? CreatedByName { get; set; }
[JsonProperty("created_time")]
[Column(Name = "created_time")]
[Display(Name = "创建时间")]
public virtual DateTime? CreatedTime { get; set; }
[JsonProperty("updated_by")]
[Column(Name = "updated_by", DbType = "Text")]
[Display(Name = "更新人")]
public virtual string? UpdatedBy { get; set; }
[JsonProperty("updated_by_name")]
[Column(Name = "updated_by_name", DbType = "Text")]
[Display(Name = "更新人名称")]
public virtual string? UpdatedByName { get; set; }
[JsonProperty("updated_time")]
[Column(Name = "updated_time")]
[Display(Name = "更新时间")]
public virtual DateTime? UpdatedTime { get; set; }
[JsonProperty("revision")]
[Column(Name = "revision", DbType = "Text")]
[Display(Name = "乐观锁")]
public virtual string? Revision { get; set; }
[JsonProperty("is_deleted")]
[Column(Name = "is_deleted", DbType = "Integer")]
[Display(Name = "删除标识")]
public virtual int IsDeleted { get; set; }
}
[JsonObject(MemberSerialization.OptIn), Table(Name = "code_template")]
public class CodeTemplateModel
{
[JsonProperty, Column(Name = "id",IsIdentity = true, IsPrimary = true)]
public int? Id { get; set; }
[JsonProperty, Column(Name = "updated_time")]
public DateTime? UpdatedTime { get; set; }
[JsonProperty, Column(Name = "updated_by_name", DbType = "TEXT(100)")]
public string? UpdatedByName { get; set; }
[JsonProperty, Column(Name = "updated_by", DbType = "TEXT(100)")]
public string? UpdatedBy { get; set; }
[JsonProperty, Column(Name = "created_time")]
public DateTime? CreatedTime { get; set; }
[JsonProperty, Column(Name = "created_by_name", DbType = "TEXT(100)")]
public string? CreatedByName { get; set; }
[JsonProperty, Column(Name = "created_by", DbType = "TEXT(100)")]
public string? CreatedBy { get; set; }
[JsonProperty, Column(Name = "revision", DbType = "TEXT(128)")]
public string? Revision { get; set; }
[JsonProperty, Column(Name = "remark", DbType = "TEXT(255)")]
public string? Remark { get; set; }
[JsonProperty, Column(Name = "template_save_directory", DbType = "TEXT")]
public string? TemplateSaveDirectory { get; set; }
[JsonProperty, Column(Name = "template_content", DbType = "TEXT")]
public string? TemplateContent { get; set; }
[JsonProperty, Column(Name = "template_type")]
public int? TemplateType { get; set; }
[JsonProperty, Column(Name = "template_description", DbType = "TEXT(255)")]
public string? TemplateDescription { get; set; }
[JsonProperty, Column(Name = "template_name", DbType = "TEXT(255)")]
public string? TemplateName { get; set; }
[JsonProperty, Column(Name = "guid", DbType = "TEXT(64)")]
public string? Guid { get; set; }
[JsonProperty, Column(Name = "sequence_number")]
public int? SequenceNumber { get; set; }
[JsonProperty, Column(Name = "is_deleted")]
public int IsDeleted { get; set; }
}
[JsonObject(MemberSerialization.OptIn), Table(Name = "code_generate_plan_template")]
public class CodeGeneratePlanTemplateModel
{
[JsonProperty("id")]
[Column(Name = "id", IsIdentity = true, IsPrimary = true)]
[Display(Name = "自增主键")]
public virtual int? Id { get; set; }
[JsonProperty("fk_code_generate_plan_id")]
[Column(Name = "fk_code_generate_plan_id", DbType = "Integer")]
[Display(Name = "code_generate_plan.id")]
public virtual int? FkCodeGeneratePlanId { get; set; }
[JsonProperty("fk_code_template_id")]
[Column(Name = "fk_code_template_id", DbType = "Integer")]
[Display(Name = "code_template.id")]
public virtual int? FkCodeTemplateId { get; set; }
[JsonProperty("log_level")]
[Column(Name = "log_level", DbType = "Integer")]
[Display(Name = "日志级别")]
public virtual int? LogLevel { get; set; }
[JsonProperty("file_rewrite")]
[Column(Name = "file_rewrite", DbType = "Integer")]
[Display(Name = "文件覆盖")]
public virtual int? FileRewrite { get; set; }
[JsonProperty("sequence_number")]
[Column(Name = "sequence_number", DbType = "Integer")]
[Display(Name = "排序号")]
public virtual int? SequenceNumber { get; set; }
[JsonProperty("is_enabled")]
[Column(Name = "is_enabled", DbType = "Integer")]
[Display(Name = "是否启用")]
public virtual int? IsEnabled { get; set; }
[JsonProperty("remark")]
[Column(Name = "remark", DbType = "Text(255)")]
[Display(Name = "备注")]
public virtual string? Remark { get; set; }
[JsonProperty("created_by")]
[Column(Name = "created_by", DbType = "Text")]
[Display(Name = "创建人")]
public virtual long? CreatedBy { get; set; }
[JsonProperty("created_by_name")]
[Column(Name = "created_by_name", DbType = "Text")]
[Display(Name = "创建人名称")]
public virtual string? CreatedByName { get; set; }
[JsonProperty("created_time")]
[Column(Name = "created_time")]
[Display(Name = "创建时间")]
public virtual DateTime? CreatedTime { get; set; }
[JsonProperty("updated_by")]
[Column(Name = "updated_by", DbType = "Text")]
[Display(Name = "更新人")]
public virtual string? UpdatedBy { get; set; }
[JsonProperty("updated_by_name")]
[Column(Name = "updated_by_name", DbType = "Text")]
[Display(Name = "更新人名称")]
public virtual string? UpdatedByName { get; set; }
[JsonProperty("updated_time")]
[Column(Name = "updated_time")]
[Display(Name = "更新时间")]
public virtual DateTime? UpdatedTime { get; set; }
[JsonProperty("revision")]
[Column(Name = "revision", DbType = "Text")]
[Display(Name = "乐观锁")]
public virtual string? Revision { get; set; }
[JsonProperty("is_deleted")]
[Column(Name = "is_deleted", DbType = "Integer")]
[Display(Name = "删除标识")]
public virtual int? IsDeleted { get; set; }
[JsonProperty("code_template")]
[Display(Name = "代码模板")]
public CodeTemplateModel? CodeTemplate
{
get;
set;
}
[JsonProperty("code_generate_plan")]
[Display(Name = "代码生成方案")]
public CodeGeneratePlanModel? CodeGeneratePlan
{
get;
set;
}
}
当这三个表单独查询时，没有任何问题；
var res2 = db.Select<CodeGeneratePlanModel>().ToList();
var res3 = db.Select<CodeTemplateModel>().ToList();
当我尝试进行关联查询时：
var res = db.Select<CodeGeneratePlanTemplateModel>()
.LeftJoin(p=> p.CodeGeneratePlan.Id == p.FkCodeGeneratePlanId )
.LeftJoin(p => p.CodeTemplate.Id == p.FkCodeTemplateId)
.WhereCascade(p=>p.IsDeleted == 0)
.OrderBy(p => p.SequenceNumber)
.OrderByDescending(p => p.Id)
.ToList();
会出现以下错误： Invalid cast from 'Int64' to 'DateTime'.
排查后发现： sqlite中将created_time、updated_time设置为integer类型时： 在单表查询中，不会出现值类型转换异常； 在进行关联查询时，会出现值类型转换异常；
将created_time、updated_time设置为datetime或text类型，关联查询及单表查询均正常；
上述情况在sqlite单表查询中是否为怪异行为？
异常堆栈信息：
在 FreeSql.Internal.CommonProvider.AdoProvider.LoggerException(IObjectPool`1 pool, PrepareCommandResult pc, Exception ex, DateTime dt, StringBuilder logtxt, Boolean isThrowException)
在 FreeSql.Internal.CommonProvider.AdoProvider.ExecuteReaderMultiple(Int32 multipleResult, DbConnection connection, DbTransaction transaction, Action`2 fetchHandler, Action`2 schemaHandler, CommandType cmdType, String cmdText, Int32 cmdTimeout, DbParameter[] cmdParms)
在 FreeSql.Internal.CommonProvider.AdoProvider.ExecuteReader(DbConnection connection, DbTransaction transaction, Action`1 fetchHandler, CommandType cmdType, String cmdText, Int32 cmdTimeout, DbParameter[] cmdParms)
在 FreeSql.Internal.CommonProvider.Select0Provider`2.ToListAfPrivate(String sql, GetAllFieldExpressionTreeInfo af, ReadAnonymousTypeOtherInfo[] otherData)
在 FreeSql.Internal.CommonProvider.Select0Provider`2.ToListPrivate(GetAllFieldExpressionTreeInfo af, ReadAnonymousTypeOtherInfo[] otherData)
在 FreeSql.Internal.CommonProvider.Select0Provider`2.ToList(Boolean includeNestedMembers)
在 FreeSql.Internal.CommonProvider.Select1Provider`1.ToList(Boolean includeNestedMembers)
在 Module.Developer.Tool.Db.DeveloperToolDbDal.GetAllCodeGeneratePlanTemplateByFkCodeGeneratePlanId(IFreeSql`1 db, Nullable`1 fkCodeGeneratePlanId) 在 E:\Codes\Private\FastDotNetApp\Client.WPF\Modules\Module.Developer.Tool\Db\DeveloperToolDbDal.cs 中: 第 39 行
在 Module.Developer.Tool.ViewModels.CodeGeneratePlanViewModel.<ExecuteQueryChild>b__26_1(LoadingDialog loadingDialoag) 在 E:\Codes\Private\FastDotNetApp\Client.WPF\Modules\Module.Developer.Tool\ViewModels\Dialogs\CodeGeneratePlanViewModel.cs 中: 第 197 行
在 Core.WPF.Ui.Extensions.UiDialogService.<>c__DisplayClass0_1.<LoadingTextAsync>b__0() 在 E:\Codes\Private\FastDotNetApp\Client.WPF\Cores\Core.WPF.Ui\Extensions\UiDialogService.cs 中: 第 88 行
因为没有自己作long -> DateTime映射
单表使用了 DateTime.TryParse 不会查询报错，但值超值
如果我在不更改数据库字段类型的情况下，进行关联查询，能通过手动转换值类型来避免报错吗？ 如果可以，应当如何实现呢？