0

The table sits on Azure SQL Database, and was created with

CREATE TABLE [dbo].[ADVUser] 
(
    [Id]         INT IDENTITY(1,1),
    [UserId]     UNIQUEIDENTIFIER NOT NULL,
    [CRMTItemId] VARCHAR (50)     NOT NULL,

    PRIMARY KEY CLUSTERED ([Id] ASC),
    FOREIGN KEY ([CRMTItemId]) REFERENCES [dbo].[CRMTItem] ([Id])
);

and I'm inserting with

using (var db = new Entities())
{
    try
    {
        db.ADVUsers.Add(new ADVUser()
        {
            CRMTItemId = "000001",
            UserId = new Guid("e1ef8e25-1624-46f0-9336-1674f1cffaeb")
        });

        db.SaveChanges();
    }
    catch (Exception e)
    {
        throw;
    }
}

Where CRMItem with id 000001 already exists.

I also tried 

using (var db = new ADVWKSPEntities())
{
    var item = db.CRMTItems.First();

    try
    {
        item.ADVUsers.Add(new ADVUser()
        {
            UserId = new Guid("e1ef8e25-1624-46f0-9336-1674f1cffaeb")
        });

        db.SaveChanges();
    }
    catch (Exception e)
    {
        throw;
    }
}

What is going on?

Update

I am using database first method, and this is the model that was generated for ADVUser:

//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated from a template.
//
//     Manual changes to this file may cause unexpected behavior in your application.
//     Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace ADVWKSP.Models
{
    using System;
    using System.Collections.Generic;

    public partial class ADVUser
    {
        public int Id { get; set; }
        public System.Guid UserId { get; set; }
        public string CRMTItemId { get; set; }

        public virtual CRMTItem CRMTItem { get; set; }
    }
}
Bassie
  • 9,529
  • 8
  • 68
  • 159
  • 2
    This table is very strange. Why would you want to add a guid UserId when you already have an int Id? Is the `Id` property decorated with `[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]`? – Zohar Peled Feb 26 '18 at 05:05
  • Is CRMTItemId a string? – mcalex Feb 26 '18 at 05:06
  • @ZoharPeled Because there might be multiple `ADVUser`s associated with different projects. This would be a many-many relationship, except for the fact that there is no users table (they are 0365 users), so I am implementing it as a 1-many relationship. Regardless of that, I am not explicitly inserting an Id value, so why would I get that error? – Bassie Feb 26 '18 at 05:07
  • what is the error message actually? – RizkiDPrast Feb 26 '18 at 05:08
  • @RizkiDPrast The error message is in the title of this question.. – Bassie Feb 26 '18 at 05:08
  • @ZoharPeled has the right idea with the decorated property. Possible duplicate of ["Cannot insert explicit value for identity column in table when IDENTITY\_INSERT is set to OFF" with composite key](https://stackoverflow.com/questions/23808967/cannot-insert-explicit-value-for-identity-column-in-table-when-identity-insert) – Brien Foss Feb 26 '18 at 05:08
  • Can you show the model class and anything set up for the entity in OnModelCreating? (Is this Code First or using an EDMX?) – lc. Feb 26 '18 at 05:09
  • have you tried this https://stackoverflow.com/questions/27541338/identity-insert-is-set-to-off-visual-studio ? – RizkiDPrast Feb 26 '18 at 05:11
  • @lc. I added the code for model. I am using db-first approach. – Bassie Feb 26 '18 at 05:21
  • 2
    You need to open edmx file and ensure that target column (Id) has store generated pattern set to Identity and not to something else. – Evk Feb 26 '18 at 05:24
  • @Evk That seems to have worked (it was set to `none`) - but wont that just be overwritten if I update the models from database? How can I set that to permanently stay set to `Identity`? – Bassie Feb 26 '18 at 05:27
  • It should have been set to identity originally. Why it was not - I have no idea, but anyway it should not get back to none if you refresh model from database (if it is set to identity in databse you are refreshing from of course). – Evk Feb 26 '18 at 05:31

0 Answers0