Can Entity Framework create a mapping from a foreign key to a GUID on a parent table?
I have two tables like this in my database (some columns have been removed to make it easier)
CREATE TABLE [dbo].[Question] (
[QuestionId] INT IDENTITY (1, 1) NOT NULL,
[ProblemId] INT NOT NULL,
[QuestionUId] UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
CONSTRAINT [PK_Question] PRIMARY KEY CLUSTERED ([QuestionId] ASC)
);
CREATE UNIQUE NONCLUSTERED INDEX [Question_QuestionUId_UIX]
ON [dbo].[Question]([QuestionUId] ASC);
CREATE TABLE [dbo].[AdminTestQuestion] (
[AdminTestQuestionId] INT IDENTITY (1, 1) NOT NULL,
[AdminTestId] INT NOT NULL,
[QuestionNumber] INT NOT NULL,
[QuestionUId] UNIQUEIDENTIFIER NOT NULL,
CONSTRAINT [PK_AdminTestQuestion] PRIMARY KEY CLUSTERED ([AdminTestQuestionId] ASC),
CONSTRAINT [FK_AdminTestQuestionQuestionUId] FOREIGN KEY ([QuestionUId]) REFERENCES [dbo].[Question] ([QuestionUId])
);
When I run the Entity Framework Reverse editor, it does not create a mapping from AdminTestQuestion> Question, and it does not create the AdminTestQuestions collection in the Question class. I was hoping to see something like this, but due to what I understand is hard to come by when one end of the foreign key is a GUID.
public partial class Question
{
public Question()
{
this.Answers = new List<AdminTestQuestion>();
}
public int QuestionId { get; set; }
public int ProblemId { get; set; }
public System.Guid QuestionUId { get; set; }
}
I also expected to display information like this in the AdminTestQuestion mapping file:
this.HasRequired(t => t.QuestionUId)
.With????(t => t.Questions)
.HasForeignKey(d => d.QuestionUId);
Can anyone advise me if I can create this type of display in a GUID with EF6 +?
source to share
There is no way to do this with EF6 as it is unaware of the Unique index on the QuestionUId column. The only way to do this is to make QuestionUId the primary key in the question table, and I can't see anything that you don't do this:
CREATE TABLE [dbo].[Question] (
[QuestionId] INT IDENTITY (1, 1) NOT NULL,
[ProblemId] INT NOT NULL,
[QuestionUId] UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
CONSTRAINT [PK_Question] PRIMARY KEY CLUSTERED ([QuestionUId] ASC)
);
CREATE TABLE [dbo].[AdminTestQuestion] (
[AdminTestQuestionId] INT IDENTITY (1, 1) NOT NULL,
[AdminTestId] INT NOT NULL,
[QuestionNumber] INT NOT NULL,
[QuestionUId] UNIQUEIDENTIFIER NOT NULL,
CONSTRAINT [PK_AdminTestQuestion] PRIMARY KEY CLUSTERED ([AdminTestQuestionId] ASC),
CONSTRAINT [FK_AdminTestQuestionQuestionUId] FOREIGN KEY ([QuestionUId]) REFERENCES [dbo].[Question] ([QuestionUId])
);
Which generates (using Code First from Database):
public partial class Question
{
public Question()
{
AdminTestQuestion = new HashSet<AdminTestQuestion>();
}
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int QuestionId { get; set; }
public int ProblemId { get; set; }
[Key]
public Guid QuestionUId { get; set; }
public virtual ICollection<AdminTestQuestion> AdminTestQuestion { get; set; }
}
public partial class AdminTestQuestion
{
public int AdminTestQuestionId { get; set; }
public int AdminTestId { get; set; }
public int QuestionNumber { get; set; }
public Guid QuestionUId { get; set; }
public virtual Question Question { get; set; }
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Question>()
.HasMany(e => e.AdminTestQuestion)
.WithRequired(e => e.Question)
.WillCascadeOnDelete(false);
}
source to share