none
One to many relationship RRS feed

  • Question

  • I have two entities Task and Attempt, One task has many Attempt, however I got a Migration error "Sequence contains more than one element".

    Please correct me.

     public class OutboundContext : DbContext
        {
            public DbSet<Task> Tasks { get; set; }
            public DbSet<Attempt> Attempts { get; set; }
            public OutboundContext()
                : base("Outbound")
            {
                Database.SetInitializer<OutboundContext>(new CreateDatabaseIfNotExists<OutboundContext>());
            }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<Task>().HasMany(t => t.Attempts).WithRequired();
            }
    
     public class Task
        {
            [Key]
            public Guid TaskId { get; set; }
            [Required]
            public int DestinationNumber { get; set; }
            [Required]
            public int CountryCode { get; set; }
            // blah blah
            public virtual ICollection<Attempt> Attempts { get; set; }
        }
    
        public class Attempt
        {
            [Key]
            public Guid Id { get; set; }
            public string AttemptsMetaData { get; set; }
            public virtual Task Task { get; set; }
        }
    This is used by Code First Relationships Fluent API. If we use data annotations, what is the code? Please provide the two methods for my case. Thanks.

    Wednesday, April 16, 2014 5:38 PM

Answers

  • Hi,

    >> By your method, the table Task doesn't have the column "Attempt".

    The task table does not need to have the column “Attempt”, it has a collection for Attempt as:

    public virtual ICollection<Attempt> Attempts { get; set; } 

    Because task and attempt are many to one relationship. Below is the whole code:

    public class S17Context : DbContext
    
        {
    
            public DbSet<Task> Tasks { get; set; }
    
            public DbSet<Attempt> Attempts { get; set; }
    
            public S17Context()
    
                : base("S17Context")
    
            {
    
            }
    
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
    
            {
    
                modelBuilder.Entity<Task>().HasMany(t => t.Attempts).WithRequired(t => t.Task).HasForeignKey(t => t.TaskId);
    
            }
    
        }
    
    
        public class Task
    
        {
    
            [Key]
    
            public Guid TaskId { get; set; }
    
            [Required]
    
            public int DestinationNumber { get; set; }
    
            [Required]
    
            public int CountryCode { get; set; }
    
            public string URL { get; set; }
    
            public virtual ICollection<Attempt> Attempts { get; set; }
    
        }
    
    
        public class Attempt
    
        {
    
            [Key]
    
            public Guid Id { get; set; }
    
            public string AttemptsMetaData { get; set; }
    
            public Guid TaskId { get; set; }
    
            public virtual Task Task { get; set; }
    
        }
    

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by ardmore Wednesday, April 23, 2014 3:34 PM
    Wednesday, April 23, 2014 8:14 AM
    Moderator

All replies

  • Hello ardmore,

    What operation you are doing cause the error? I made a test with your model, I could create the database and then use the migrations to add a new column successfully, however.

    For the error “Sequence contains more than one element", one reason I know is that this will be caused by using SingleOrDefault method if there is more than one element in the sequence. Please check if you are under such scenario.

    >>This is used by Code First Relationships Fluent API. If we use data annotations, what is the code? Please provide the two methods for my case. Thanks.

    I think the below link should be helpful.

    Configuring/Mapping Properties and Types with the Fluent API:

    http://msdn.microsoft.com/en-us/data/jj591617.aspx

    Code First Data Annotations:

    http://msdn.microsoft.com/en-us/data/jj591583.aspx

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, April 17, 2014 2:51 AM
    Moderator
  • Have you checked the columns? Do they match the entity definition? I dropped the tables and re-created. The table Attempt has fields 'Task_TaskId' and 'Task_TaskId1'. 

    Also, if you created the tables, please add dummy date to test it. I found sometimes tables can be created but failed to insert the data. 

    If you create tables and insert data correctly, would you please present your code and snapshot of the table data?


    • Edited by ardmore Thursday, April 17, 2014 8:41 PM add more
    Thursday, April 17, 2014 1:16 PM
  • >> Have you checked the columns? Do they match the entity definition? I dropped the tables and re-created. The table Attempt has fields 'Task_TaskId' and 'Task_TaskId1'.

    Yes, I have the 'Task_TaskId1' flied, too. This is because we do not specify the foreign key. So have a try to use the fluent API as:

    modelBuilder.Entity<Task>().HasMany(t => t.Attempts).WithRequired(t=>t.Task).HasForeignKey(t => t.TaskId);

    And add a foreign key flied in subproperty as:

    public class Attempt
    
        {
    
            [Key]
    
            public Guid Id { get; set; }
    
            public string AttemptsMetaData { get; set; }
    
            public Guid TaskId { get; set; }
    
            public virtual Task Task { get; set; }
    
    }
    

    The generated tables are as:

    CREATE TABLE [dbo].[Tasks] (
    
        [TaskId]            UNIQUEIDENTIFIER NOT NULL,
    
        [DestinationNumber] INT              NOT NULL,
    
        [CountryCode]       INT              NOT NULL,
    
        [URL]               NVARCHAR (MAX)   NULL,
    
        CONSTRAINT [PK_dbo.Tasks] PRIMARY KEY CLUSTERED ([TaskId] ASC)
    
    );
    
    
    CREATE TABLE [dbo].[Attempts] (
    
        [Id]               UNIQUEIDENTIFIER NOT NULL,
    
        [AttemptsMetaData] NVARCHAR (MAX)   NULL,
    
        [TaskId]           UNIQUEIDENTIFIER NOT NULL,
    
        CONSTRAINT [PK_dbo.Attempts] PRIMARY KEY CLUSTERED ([Id] ASC),
    
        CONSTRAINT [FK_dbo.Attempts_dbo.Tasks_TaskId] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Tasks] ([TaskId]) ON DELETE CASCADE
    
    );
    
    
    
    GO
    
    CREATE NONCLUSTERED INDEX [IX_TaskId]
    
        ON [dbo].[Attempts]([TaskId] ASC);
    

    >>Also, if you created the tables, please add dummy date to test it. I found sometimes tables can be created but failed to insert the data.

    I use below code to do an insert operation and successfully:

    using (S17Context db = new S17Context())
    
                {
    
                    db.Database.CreateIfNotExists();
    
    
                    CF.S17.Task task = new Task() { TaskId = Guid.NewGuid(), URL = "1", CountryCode = 1, DestinationNumber = 1 };
    
    
                    db.Tasks.Add(task);
    
    
                    db.SaveChanges();
    
    
                    Attempt a = new Attempt() { Id = Guid.NewGuid(), TaskId = task.TaskId, AttemptsMetaData = "1" };
    
    
                    db.Attempts.Add(a);
    
    
                    db.SaveChanges();
    
                }
    

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, April 18, 2014 8:03 AM
    Moderator
  • Hi Bao, it is very helpful.

    I will try it. I could be wrong on inserting data code. Two more questions.

    1. Can we mix fluent API and data annotation together?
    2. Hoe did you get the sql script for generated tables? What command?
    Friday, April 18, 2014 12:07 PM
  • Yes, we can. Usually, we use the data annotation to define a property to be a key flied or not, a required flied or not, an identified flied or not, etc. For relationships, we use the fluent API to config it so that it will be more understanding.

    >> 2.Hoe did you get the sql script for generated tables? What command?

    Actually, the sql script is copied from the database and I do not use any command. But if you want to see the generated script for the whole database, you can use the code first migrations. Using the PMC to create it so that you can see the whole sql scripts:

    Enable-Migrations -ContextTypeName CF.S17.S17Context
    
    add-migration Init
    
    Update-Database –Verbose
    

    The '-Verbose' flag is used to view the SQL statements being applied to the target database.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, April 21, 2014 8:50 AM
    Moderator
  • I don't know what is "URL" in your code? And why it was assigned to "1"?

    My Task class doesn't have this item. I guess that you wanted to link two classes because of "Attempt". I am lack of this area knowledge and no MSDN document found.

    Would you please mind elaborating the detail?  

    Monday, April 21, 2014 3:22 PM
  • Hi,

    >> I don't know what is "URL" in your code? And why it was assigned to "1"?

    Okey. I should show the Taks entity class in my second post, it is my mistake:

    public class Task
    
        {
    
            [Key]
    
            public Guid TaskId { get; set; }
    
            [Required]
    
            public int DestinationNumber { get; set; }
    
            [Required]
    
            public int CountryCode { get; set; }
    
            public string URL { get; set; }// remove it if you want it.
    
            public virtual ICollection<Attempt> Attempts { get; set; }
    
    }
    

    The URL is an extra flied I used to test the migrations and because it is a string type, I gave it "1" simply. You can remove it if you want. Then you can see the  whole model code.

    >>I guess that you wanted to link two classes because of "Attempt".

    Yes, you are right since the mode is copied from yours which is one to many relationship.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, April 22, 2014 5:51 AM
    Moderator
  • Sorry to bother you again. By your method, the table Task doesn't have the column "Attempt". Can you show your snapshot?
    Tuesday, April 22, 2014 1:33 PM
  • Hi,

    >> By your method, the table Task doesn't have the column "Attempt".

    The task table does not need to have the column “Attempt”, it has a collection for Attempt as:

    public virtual ICollection<Attempt> Attempts { get; set; } 

    Because task and attempt are many to one relationship. Below is the whole code:

    public class S17Context : DbContext
    
        {
    
            public DbSet<Task> Tasks { get; set; }
    
            public DbSet<Attempt> Attempts { get; set; }
    
            public S17Context()
    
                : base("S17Context")
    
            {
    
            }
    
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
    
            {
    
                modelBuilder.Entity<Task>().HasMany(t => t.Attempts).WithRequired(t => t.Task).HasForeignKey(t => t.TaskId);
    
            }
    
        }
    
    
        public class Task
    
        {
    
            [Key]
    
            public Guid TaskId { get; set; }
    
            [Required]
    
            public int DestinationNumber { get; set; }
    
            [Required]
    
            public int CountryCode { get; set; }
    
            public string URL { get; set; }
    
            public virtual ICollection<Attempt> Attempts { get; set; }
    
        }
    
    
        public class Attempt
    
        {
    
            [Key]
    
            public Guid Id { get; set; }
    
            public string AttemptsMetaData { get; set; }
    
            public Guid TaskId { get; set; }
    
            public virtual Task Task { get; set; }
    
        }
    

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by ardmore Wednesday, April 23, 2014 3:34 PM
    Wednesday, April 23, 2014 8:14 AM
    Moderator