AzureMobileClient Database首次手动创建数据表失败？

Question

我尝试在 XamarinForm 中使用数据库优先模型使用 AzureMobileClient。目前我不使用离线同步。因此，我使用此脚本在 AZURE SQL DB 中创建表:

CREATE TABLE [dbo].[TodoItems] (
-- This must be a string suitable for a GUID
[Id]            NVARCHAR (128)     NOT NULL,

-- These are the system properties
[Version]       ROWVERSION         NOT NULL,
[CreatedAt]     DATETIMEOFFSET (7) NOT NULL,
[UpdatedAt]     DATETIMEOFFSET (7) NULL,
[Deleted]       BIT                NOT NULL,

-- These are the properties of our DTO not included in EntityFramework
[Text]          NVARCHAR (MAX)     NULL,
[Complete]      BIT                NOT NULL,
);

CREATE CLUSTERED INDEX [IX_CreatedAt]
ON [dbo].TodoItems([CreatedAt] ASC);

ALTER TABLE [dbo].[TodoItems]
ADD CONSTRAINT [PK_dbo.TodoItems] PRIMARY KEY NONCLUSTERED ([Id] ASC);

CREATE TRIGGER [TR_dbo_TodoItems_InsertUpdateDelete] ON [dbo].[TodoItems]
AFTER INSERT, UPDATE, DELETE AS
BEGIN
    UPDATE [dbo].[TodoItems]
        SET [dbo].[TodoItems].[UpdatedAt] = CONVERT(DATETIMEOFFSET, 
              SYSUTCDATETIME())
        FROM INSERTED WHERE inserted.[Id] = [dbo].[TodoItems].[Id]
END;

基于 Azure 提供的示例 TodoItem。我可以执行 GetAllItems 而不会出现任何错误(表现在为空)。但是，当我尝试插入项目时，我在 Azure 后端收到此错误:

{[Message, The operation failed with the following error: 'Cannot insert the value NULL into column 'CreatedAt', table 'TechCenterCentaur.dbo.TodoItems'; column does not allow nulls. INSERT fails.The statement has been terminated.'.]}

通常 Azure 应该自动处理这个问题？

我只是在我的 XF 代码中这样做:

TodoItem cl = new TodoItem();
cl.Name = "Test";

await _todoTable.InsertAsync(cl);

对后端的调用很好，TodoItem 仅包含 Test，所有其他字段均为空。后端发生异常:

public async Task<IHttpActionResult> PostTodoItem(TodoItem item)
{
  try
  {
     TodoItem current = await InsertAsync(item);  //crash here
     return CreatedAtRoute("Tables", new { id = current.Id }, current);
  }
  catch (System.Exception e)
  {

     throw;
  }
}

有什么建议吗？

Answer 1

好的，我找到了解决方案。问题出在我的 SQL 表中。我缺少 ID 和 CreatedDate 的新 GUID 的 2 个 ALTER TABLE。

这是我的新脚本:

USE [TechCenterCentaur]
GO

/****** Object:  Table [dbo].[TodoItems]    Script Date: 2017-11-08 11:09:14 
  ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TodoItems](
  [Id] [nvarchar](128) NOT NULL,
  [Text] [nvarchar](max) NULL,
  [Complete] [bit] NOT NULL,
  [Version] [timestamp] NOT NULL,
  [CreatedAt] [datetimeoffset](7) NOT NULL,
  [UpdatedAt] [datetimeoffset](7) NULL,
  [Deleted] [bit] NOT NULL,
CONSTRAINT [PK_dbo.TodoItems] PRIMARY KEY NONCLUSTERED 
(
  [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

ALTER TABLE [dbo].[TodoItems] ADD  DEFAULT (newid()) FOR [Id]
GO

ALTER TABLE [dbo].[TodoItems] ADD  DEFAULT (sysutcdatetime()) FOR 
       [CreatedAt]
GO

CREATE TRIGGER [TR_dbo_TodoItems_InsertUpdateDelete] ON [dbo].[TodoItems]
AFTER INSERT, UPDATE, DELETE AS
BEGIN
    UPDATE [dbo].[TodoItems]
        SET [dbo].[TodoItems].[UpdatedAt] = CONVERT(DATETIMEOFFSET, 
           SYSUTCDATETIME())
        FROM INSERTED WHERE inserted.[Id] = [dbo].[TodoItems].[Id]
END;
GO