文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >从EF Core 2迁移到EF Core 3-日期时间毫秒精度

问从EF Core 2迁移到EF Core 3-日期时间毫秒精度
EN

Stack Overflow用户
提问于 2020-01-07 16:55:41
回答 2查看 807关注 0票数 0

我有一些将一些数据插入数据库的迁移，这是其中之一：

代码语言：javascript
复制
 migrationBuilder.InsertData(
            table: "TSegment",
            columns: new[] { "SegmentId", "CreatedOn", "ParentId", "SegmentType", "UpdatedOn" },
            values: new object[,]
            {
                { 1, new DateTime(2019, 1, 22, 12, 24, 23, 48, DateTimeKind.Utc).AddTicks(4948), null, "YouAndYourFamily", new DateTime(2019, 1, 22, 12, 24, 23, 48, DateTimeKind.Utc).AddTicks(5782) },
                { 2, new DateTime(2019, 1, 22, 12, 24, 23, 48, DateTimeKind.Utc).AddTicks(6649), 1, "AboutYou", new DateTime(2019, 1, 22, 12, 24, 23, 48, DateTimeKind.Utc).AddTicks(6658) },
                { 3, new DateTime(2019, 1, 22, 12, 24, 23, 48, DateTimeKind.Utc).AddTicks(6670), 1, "YourFamily", new DateTime(2019, 1, 22, 12, 24, 23, 48, DateTimeKind.Utc).AddTicks(6676) },
                { 4, new DateTime(2019, 1, 22, 12, 24, 23, 48, DateTimeKind.Utc).AddTicks(6679), 1, "Employment", new DateTime(2019, 1, 22, 12, 24, 23, 48, DateTimeKind.Utc).AddTicks(6685) },
                { 5, new DateTime(2019, 1, 22, 12, 24, 23, 48, DateTimeKind.Utc).AddTicks(6688), null, "HomeAndContact", new DateTime(2019, 1, 22, 12, 24, 23, 48, DateTimeKind.Utc).AddTicks(6694) },
                { 6, new DateTime(2019, 1, 22, 12, 24, 23, 48, DateTimeKind.Utc).AddTicks(6700), 5, "YourHome", new DateTime(2019, 1, 22, 12, 24, 23, 48, DateTimeKind.Utc).AddTicks(6703) },
                { 7, new DateTime(2019, 1, 22, 12, 24, 23, 48, DateTimeKind.Utc).AddTicks(6709), 5, "ContactDetails", new DateTime(2019, 1, 22, 12, 24, 23, 48, DateTimeKind.Utc).AddTicks(6712) }
            });

在将EF Core升级到版本3之后，上面的代码将生成此插入：

代码语言：javascript
复制
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'SegmentId', N'CreatedOn', N'ParentId', N'SegmentType', N'UpdatedOn') AND [object_id] = OBJECT_ID(N'[TSegment]'))
SET IDENTITY_INSERT [TSegment] ON;
INSERT INTO [TSegment] ([SegmentId], [CreatedOn], [ParentId], [SegmentType], [UpdatedOn])
VALUES (1, '2019-01-22T12:24:23.0484948Z', NULL, N'YouAndYourFamily', '2019-01-22T12:24:23.0485782Z'),
(2, '2019-01-22T12:24:23.0486649Z', 1, N'AboutYou', '2019-01-22T12:24:23.0486658Z'),
(3, '2019-01-22T12:24:23.0486670Z', 1, N'YourFamily', '2019-01-22T12:24:23.0486676Z'),
(4, '2019-01-22T12:24:23.0486679Z', 1, N'Employment', '2019-01-22T12:24:23.0486685Z'),
(5, '2019-01-22T12:24:23.0486688Z', NULL, N'HomeAndContact', '2019-01-22T12:24:23.0486694Z'),
(6, '2019-01-22T12:24:23.0486700Z', 5, N'YourHome', '2019-01-22T12:24:23.0486703Z'),
(7, '2019-01-22T12:24:23.0486709Z', 5, N'ContactDetails', '2019-01-22T12:24:23.0486712Z');
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'SegmentId', N'CreatedOn', N'ParentId', N'SegmentType', N'UpdatedOn') AND [object_id] = OBJECT_ID(N'[TSegment]'))
    SET IDENTITY_INSERT [TSegment] OFF;

正如我们所看到的，日期毫秒有6个小数位，但是如果我将EF Core降级到版本2，相同的迁移将生成以下插入：

代码语言：javascript
复制
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'SegmentId', N'CreatedOn', N'ParentId', N'SegmentType', N'UpdatedOn') AND [object_id] = OBJECT_ID(N'[TSegment]'))
SET IDENTITY_INSERT [TSegment] ON;
INSERT INTO [TSegment] ([SegmentId], [CreatedOn], [ParentId], [SegmentType], [UpdatedOn])
VALUES (1, '2019-01-22T12:24:23.048', NULL, N'YouAndYourFamily', '2019-01-22T12:24:23.048'),
(2, '2019-01-22T12:24:23.048', 1, N'AboutYou', '2019-01-22T12:24:23.048'),
(3, '2019-01-22T12:24:23.048', 1, N'YourFamily', '2019-01-22T12:24:23.048'),
(4, '2019-01-22T12:24:23.048', 1, N'Employment', '2019-01-22T12:24:23.048'),
(5, '2019-01-22T12:24:23.048', NULL, N'HomeAndContact', '2019-01-22T12:24:23.048'),
(6, '2019-01-22T12:24:23.048', 5, N'YourHome', '2019-01-22T12:24:23.048'),
(7, '2019-01-22T12:24:23.048', 5, N'ContactDetails', '2019-01-22T12:24:23.048');
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'SegmentId', N'CreatedOn', N'ParentId', N'SegmentType', N'UpdatedOn') AND [object_id] = OBJECT_ID(N'[TSegment]'))
    SET IDENTITY_INSERT [TSegment] OFF;

由于SQL Server的精度是小数点后3位，而且我不能将所有的datetime列都改为Datetime2，有谁知道解决这个问题的方法吗？

sql-server
ef-core-3.1
entity-framework-core-3.1
EN

回答 2

Stack Overflow用户

发布于 2020-01-07 17:00:37

您可以通过将其包装在sqlDataTime结构中来解决问题，如下所示：

代码语言：javascript
复制
new SqlDateTime (DateTime value)

如果是poco对象：

您可以尝试使用数据无名氏指定列类型，如下所示

代码语言：javascript
复制
[Column(TypeName = "datetime")]
DateTime CreatedOn {get;set;}

请参阅docs

票数 1
EN

Stack Overflow用户

发布于 2020-01-07 22:42:34

好吧，问题并不是毫秒。我从迁移中删除了AddTicks和毫秒：

代码语言：javascript
复制
  migrationBuilder.InsertData(
            table: "TSegment",
            columns: new[] { "SegmentId", "CreatedOn", "ParentId", "SegmentType", "UpdatedOn" },
            values: new object[,]
            {
                { 1, new DateTime(2019, 1, 22, 12, 24, 23, DateTimeKind.Utc), null, "YouAndYourFamily", new DateTime(2019, 1, 22, 12, 24, 23,DateTimeKind.Utc) },
                { 2, new DateTime(2019, 1, 22, 12, 24, 23, DateTimeKind.Utc), 1, "AboutYou", new DateTime(2019, 1, 22, 12, 24, 23,DateTimeKind.Utc)},
                { 3, new DateTime(2019, 1, 22, 12, 24, 23, DateTimeKind.Utc), 1, "YourFamily", new DateTime(2019, 1, 22, 12, 24, 23,DateTimeKind.Utc) },
                { 4, new DateTime(2019, 1, 22, 12, 24, 23, DateTimeKind.Utc), 1, "Employment", new DateTime(2019, 1, 22, 12, 24, 23,DateTimeKind.Utc) },
                { 5, new DateTime(2019, 1, 22, 12, 24, 23, DateTimeKind.Utc), null, "HomeAndContact", new DateTime(2019, 1, 22, 12, 24, 23,DateTimeKind.Utc) },
                { 6, new DateTime(2019, 1, 22, 12, 24, 23, DateTimeKind.Utc), 5, "YourHome", new DateTime(2019, 1, 22, 12, 24, 23,DateTimeKind.Utc) },
                { 7, new DateTime(2019, 1, 22, 12, 24, 23, DateTimeKind.Utc), 5, "ContactDetails", new DateTime(2019, 1, 22, 12, 24, 23,DateTimeKind.Utc) }
            });

它现在生成以下脚本：

代码语言：javascript
复制
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'SegmentId', N'CreatedOn', N'ParentId', N'SegmentType', N'UpdatedOn') AND [object_id] = OBJECT_ID(N'[TSegment]'))
SET IDENTITY_INSERT [TSegment] ON;
INSERT INTO [TSegment] ([SegmentId], [CreatedOn], [ParentId], [SegmentType], [UpdatedOn])
VALUES (1, '2019-01-22T12:24:23.0000000Z', NULL, N'YouAndYourFamily', '2019-01-22T12:24:23.0000000Z'),
(2, '2019-01-22T12:24:23.0000000Z', 1, N'AboutYou', '2019-01-22T12:24:23.0000000Z'),
(3, '2019-01-22T12:24:23.0000000Z', 1, N'YourFamily', '2019-01-22T12:24:23.0000000Z'),
(4, '2019-01-22T12:24:23.0000000Z', 1, N'Employment', '2019-01-22T12:24:23.0000000Z'),
(5, '2019-01-22T12:24:23.0000000Z', NULL, N'HomeAndContact', '2019-01-22T12:24:23.0000000Z'),
(6, '2019-01-22T12:24:23.0000000Z', 5, N'YourHome', '2019-01-22T12:24:23.0000000Z'),
(7, '2019-01-22T12:24:23.0000000Z', 5, N'ContactDetails', '2019-01-22T12:24:23.0000000Z');
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'SegmentId', N'CreatedOn', N'ParentId', N'SegmentType', N'UpdatedOn') AND [object_id] = OBJECT_ID(N'[TSegment]'))
SET IDENTITY_INSERT [TSegment] OFF;

它仍然会在毫秒内生成6位小数，并抛出以下异常：

Microsoft.Data.SqlClient.SqlException (0x80131904)：从字符串转换日期和/或时间时转换失败。at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception，Boolean breakConnection，1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj，Boolean callerHasConnectionLock，Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior，SqlCommand cmdHandler，SqlDataReader dataStream，BulkCopySimpleResultSet bulkCopyHandler，TdsParserStateObject stateObj，Boolean& dataReady) at，Boolean，timeout，Boolean asyncWrite) at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName) at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject) at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands，IRelationalConnection asyncWrite) at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade parameterValues) at IntelliFlo.Platform.EntityFramework.Database.Tasks.DatabaseMigrationTask.Run() at IntelliFlo.Platform.EntityFramework.Database.DatabaseInitializer.ExecuteTask(IDatabaseTask targetMigration)

我是不是遗漏了什么？

票数 -1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/59625028

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档