5

I'm generating Entity Framework database first (EF Designer from database). I've got two tables in Microsoft SQL Server:

CREATE TABLE dbo.Person
(
    Pk_Person_Id INT IDENTITY PRIMARY KEY,
    Name VARCHAR(255),
    EmailId VARCHAR(255),
);

CREATE TABLE dbo.PassportDetails
(
    Pk_Passport_Id INT PRIMARY KEY,
    Passport_Number VARCHAR(255),
    Fk_Person_Id INT UNIQUE 
       FOREIGN KEY REFERENCES dbo.Person(Pk_Person_Id)
);

INSERT INTO dbo.Person 
VALUES ('Niraj','v.a@emails.com'),
       ('Vishwanath','v.v@emails.com'),
       ('Chetan','c.v@emails.com');
GO

INSERT INTO dbo.PassportDetails 
VALUES (101, 'C3031R33', 1), (102, 'VRDK5695', 2), (103, 'A4DEK33D', 3);
GO

SELECT * FROM dbo.Person
SELECT * FROM dbo.PassportDetails;

In SQL Server the relations are shown as one-to-one because Fk_Person_Id is set as isUnique = true:

Fk_Person_Id INT UNIQUE 
     FOREIGN KEY REFERENCES dbo.Person(Pk_Person_Id));

Microsoft SQL diagram

Now in Visual Studio, I add a new ADO.NET Entity Data Model -> EF Designer from database -> select these two tables and leave all checkbox options by default.

Then after generation is over I see this in Visual Studio diagram .edmx:

VS diagram

And the relationship has changed for one-to-many - why? Is this wrong? I don't want a person to have a collection of passports - it's not the logic I'm trying to describe.

And the EF code:

public partial class Person
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Person()
    {
        this.PassportDetails = new HashSet<PassportDetail>();
    }

    public int Pk_Person_Id { get; set; }
    public string Name { get; set; }
    public string EmailId { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<PassportDetail> PassportDetails { get; set; }
}

public partial class PassportDetail
{
    public int Pk_Passport_Id { get; set; }
    public string Passport_Number { get; set; }
    public Nullable<int> Fk_Person_Id { get; set; }

    public virtual Person Person { get; set; }
}

Context

public partial class LightCRMEntities : DbContext
{
    public LightCRMEntities() : base("name=LightCRMEntities")
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        throw new UnintentionalCodeFirstException();
    }

    public virtual DbSet<PassportDetail> PassportDetails { get; set; }
    public virtual DbSet<Person> People { get; set; }
}
Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
Dmitriy Klyushin
  • 449
  • 1
  • 4
  • 22
  • 2
    Yeah, that's because EF6 only supports [shared primary key](https://weblogs.asp.net/manavi/associations-in-ef-4-1-code-first-part-3-shared-primary-key-associations) in 1:1. – Gert Arnold Jul 21 '18 at 11:11

1 Answers1

0

Well, in database model editor (.edmx file editor), by clicking on a relationship (arrow between entities daigrams), you can configure its properties in such way, to make it 1-to-1 (it won't change your database!), but then you can generate the code you need to create such database. In result, I got the following:

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'PassportDetails'
CREATE TABLE [dbo].[PassportDetails] (
    [Pk_Passport_Id] int  NOT NULL,
    [Passport_Number] varchar(255)  NULL
);
GO

-- Creating table 'Person'
CREATE TABLE [dbo].[Person] (
    [Pk_Person_Id] int IDENTITY(1,1) NOT NULL,
    [Name] varchar(255)  NULL,
    [EmailId] varchar(255)  NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Pk_Passport_Id] in table 'PassportDetails'
ALTER TABLE [dbo].[PassportDetails]
ADD CONSTRAINT [PK_PassportDetails]
    PRIMARY KEY CLUSTERED ([Pk_Passport_Id] ASC);
GO

-- Creating primary key on [Pk_Person_Id] in table 'Person'
ALTER TABLE [dbo].[Person]
ADD CONSTRAINT [PK_Person]
    PRIMARY KEY CLUSTERED ([Pk_Person_Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Pk_Passport_Id] in table 'PassportDetails'
ALTER TABLE [dbo].[PassportDetails]
ADD CONSTRAINT [FK__PassportD__Pk_Pa__5BE2A6F2]
    FOREIGN KEY ([Pk_Passport_Id])
    REFERENCES [dbo].[Person]
        ([Pk_Person_Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
  • 1
    I've changed properties to 1-1 but I'm getting an `error: Severity Code Description Project File Line Suppression State Error Running transformation: Multiplicity is not valid in Role 'PassportDetails' in relationship 'FK__PassportD__Fk_Pe__0B5CAFEA'. Because the Dependent Role properties are not the key properties, the upper bound of the multiplicity of the Dependent Role must be *.` – Dmitriy Klyushin Jul 21 '18 at 14:06
  • 1
    @DmitriyKlyushin Well, I posted the code to generate tables, drop your tables and recreate it with my code, then update your model and it should be fine :) – Michał Turczyn Jul 21 '18 at 14:16
  • thanks! yes it helped. In VisualStudio diagram now I see relation 1-0..1. So I make a conclusion that to create such a relation I need in Microsoft SQL Server make a relation between primary keys instead of a relation between a foreign key and a primary key. Right? – Dmitriy Klyushin Jul 21 '18 at 15:16