Programmatically seeding the database with the Azure App Service console throws the following error message:

SqlException (0x80131904): Cannot insert the value NULL into column 'Id'

However, programmatically seeding the database locally is error free. What could be the issue?

Failed Solutions:

• Set the Id column as Nullable directly in database
• Annotate the entity class with the[Keyless] attribute
• Annotate the entity class's Id property with the [Key] attribute

Table Schema

 ------------- --------------- ------------- 
| Table Name: Cities                        |
 ------------- --------------- ------------- 
| Column Name |   Data Type   | Allow Nulls | 
 ------------- --------------- ------------- 
| Id (PK)     | int           |     NO      |
| CityCode    | int           |     NO      |
| Name        | nvarchar(MAX) |     YES     |
| State       | nvarchar(MAX) |     YES     |
| Country     | nvarchar(MAX) |     YES     | 
 ------------- --------------- ------------- 

Entity Class:

public class City
{
  public int Id { get; set; }
  public int CityCode { get; set; }
  public string Name { get; set; }
  public string State { get; set; }
  public string Country { get; set; }
}

Seeding Code:

public class DbInitializer : IDbInitializer
{
    private readonly IServiceScopeFactory _scopeFactory;
    private readonly IWebHostEnvironment _hostEnvironment;

    public DbInitializer(IServiceScopeFactory scopeFactory, IWebHostEnvironment environment)
    {
        _scopeFactory = scopeFactory;
        _hostEnvironment = environment;
    }

    public void Initialize()
    {
        using (var serviceScope = _scopeFactory.CreateScope())
        {
            using (var context = serviceScope.ServiceProvider.GetService<ApplicationDbContext>())
            {
                context.Database.EnsureCreatedAsync();
            }
        }
    }

    public async Task SeedData()
    {
        using (var serviceScope = _scopeFactory.CreateScope())
        {
            using (var context = serviceScope.ServiceProvider.GetService<ApplicationDbContext>())
            {
                if (!context.Cities.Any())
                {
                    string path = Path.Combine(_hostEnvironment.WebRootPath, "citylist.json");
                    using (StreamReader reader = new StreamReader(path))
                    {
                        string jsonData = reader.ReadToEnd();
                        List<City> cityList = JsonConvert.DeserializeObject<List<City>>(jsonData);
                        await context.Cities.AddRangeAsync(cityList);
                        await context.SaveChangesAsync();
                    }
                }
            }
        }
    }
}

Azure Publish Pubxml File:

<Project>
  <PropertyGroup>
    <WebPublishMethod>MSDeploy</WebPublishMethod>
    <ResourceId>/subscriptions/e0fa5197-c6/resourceGroups/rg-demoapps/providers/Microsoft.Web/sites/weatherappraj</ResourceId>
    <ResourceGroup>rg-demoapps</ResourceGroup>
    <PublishProvider>AzureWebSite</PublishProvider>
    <LastUsedBuildConfiguration>Release</LastUsedBuildConfiguration>
    <LastUsedPlatform>Any CPU</LastUsedPlatform>
    <SiteUrlToLaunchAfterPublish></SiteUrlToLaunchAfterPublish>
    <LaunchSiteAfterPublish>true</LaunchSiteAfterPublish>
    <ExcludeApp_Data>false</ExcludeApp_Data>
    <ProjectGuid>0f865719-3270</ProjectGuid>
    <MSDeployServiceURL></MSDeployServiceURL>
    <DeployIisAppPath>weatherappraj</DeployIisAppPath>
    <RemoteSitePhysicalPath />
    <SkipExtraFilesOnServer>true</SkipExtraFilesOnServer>
    <MSDeployPublishMethod>WMSVC</MSDeployPublishMethod>
    <EnableMSDeployBackup>true</EnableMSDeployBackup>
    <EnableMsDeployAppOffline>true</EnableMsDeployAppOffline>
    <UserName></UserName>
    <_SavePWD>true</_SavePWD>
    <_DestinationType>AzureWebSite</_DestinationType>
    <TargetFramework>netcoreapp3.1</TargetFramework>
    <SelfContained>false</SelfContained>
    <InstallAspNetCoreSiteExtension>false</InstallAspNetCoreSiteExtension>
  </PropertyGroup>
</Project>

CodePudding user response：

Can you check if the source data is having a value as NULL instead of an actual NULL

Similar kind of case happened with me where the data was having NULL as string value and not the usual NULL, integer columns won't allow such strings

Just to rule out this possibility, this check can be done if you can extract the source data in an excel and check for any such string value

CodePudding user response：

The source of the problem was that the Id column was not set as the identity column, causing the database to attempt a Null insertion and throwing the exception. But, it's working now.