EntityCommandExecutionException - Trying to link 2 models in 1 database

Question

Pretty much I have 2 models - A announcement where some can post an announcement and a seen model which determins if someone has seen the announcement. her eis the models:

Announce:

public class Announcement
{
    public int AnnouncementId { get; set; }
    public string AnnouncementContent { get; set; }
    public virtual ApplicationUser User { get; set; }
}

and Seen:

public class Seen
{
    public int SeenId { get; set; }

    public virtual Announcement Announcement { get; set; }
    public virtual ApplicationUser User { get; set; }
}

in my AnnouncementController.Index I have this code which pretty much supposed to be, if you view this page, mark off every announcement as seen bbut am getting errors at the "new seen" part:

public ActionResult Index()
{
    string currentUserId = User.Identity.GetUserId();
    var currentUser = db.Users.FirstOrDefault(x => x.Id == currentUserId);


    if(db.Announcements != null)
    {
        foreach (Announcement anoun in db.Announcements)
        {
            new Seen
            {
                User = db.Users.Add(currentUser),
                Announcement = db.Announcements.FirstOrDefault(x => x.AnnouncementId == anoun.AnnouncementId),
            };
        }
    }
    return View(db.Announcements.ToList());
}

There is already an open DataReader associated with this Command which must be closed first.

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
    {
        public ApplicationDbContext()
            : base("DefaultConnection", throwIfV1Schema: false)
        {
        }

        public DbSet<Announcement> Announcements { get; set; }
        public DbSet<Comment> Comments { get; set; }
        public DbSet<Seen> Seens { get; set; }

        public static ApplicationDbContext Create()
        {
            return new ApplicationDbContext();
        }
    }

When you have an error and you ask a question about that error you need to include that error. In this case its called an [Exception](https://msdn.microsoft.com/en-us/library/system.exception(v=vs.110).aspx) (*this is how errors manifest themselves in .net*). Include the Message, the Type, the StackTrace, and repeat this recursivly across InnerExceptions all the way down. Use the edit link on your question to include that detail, do not include it as a comment. Please also read [How do I ask a Good Question](http://stackoverflow.com/help/how-to-ask). — Igor, Dec 16 '16 at 19:14

Igor · Answer 1 · 2016-12-16T19:38:02.363

The error is because you are actively iterating over a collection streaming from the database and then trying to re-issue another select to the database inside that loop. It can be fixed by forcing the materialization of the collection before you enter the loop using ToList() (other options are also available like AsEnumerable or ToArray).

foreach (Announcement anoun in db.Announcements.ToList())
{
    new Seen
    {
        User = db.Users.Add(currentUser),
        Announcement = db.Announcements.FirstOrDefault(x => x.AnnouncementId == anoun.AnnouncementId),
    };
}

That being said I am not sure why you are doing it this way. Why not attach the instance anoun directly as you are only using a single (or same in the code shown) DbContext instance (variable named db).

foreach (Announcement anoun in db.Announcements)
{
    new Seen
    {
        User = currentUser, // also this seemed wrong before, just assign the reference directly
        Announcement = anoun
    };
}

Or make it even more simple:

var newSeens = db.Announcements.Select(x => new Seen(){User = currentUser, Announcement = x}).ToList();
db.Seens.AddRange(newSeens);
db.SaveChanges();

This assumes that the user has not seen any announcement. If the user has seen some then you need to filter db.Announcements on existing Seen records for that user.

Thanks for this! The bottom one doesnt work but that not what I was looking for so thank you for that anyway. The new problem is I dont get any errors with the second method but no records get added to the seens database. — PigeonMilk Stories, Dec 16 '16 at 19:36
@PigeonMilkStories - Its because you are not adding them to the `DbContext` and also not calling `db.SaveChanges();` after. But glad to help. — Igor, Dec 16 '16 at 19:37
@PigeonMilkStories - also please consider accepting an answer (see [How to accept SO answers](http://meta.stackexchange.com/a/5235)) — Igor, Dec 16 '16 at 19:43

score 0 · Answer 2 · answered Dec 16 '16 at 19:32

0

Event if you did not have that error, you still have other issues. See below:

public ActionResult Index() {
string currentUserId = User.Identity.GetUserId();
var currentUser = db.Users.FirstOrDefault( x => x.Id == currentUserId );

List<Seen> seens = new List<Seen>();
if( db.Announcements != null ) {
   foreach( Announcement anoun in db.Announcements ) {
      seens.Add(
      new Seen
      {
         User = currentUser, // You have this already so why go to the database again?
         Announcement = anoun, // Same with this.
      });

   }
}

// Save seens to the database

return View( db.Announcements.ToList() );

answered Dec 16 '16 at 19:32

CodingYoshi

25,467
4
62
64

Hi, thank you for the reply. I added: db.Seens.AddRange(seens); db.SaveChanges(); but this still doesnt update the database, any ideas as i am stumped. – PigeonMilk Stories Dec 16 '16 at 19:52
Do you get an error? – CodingYoshi Dec 16 '16 at 19:58
none, isit something to do with me seen model as the seen database contains: SeenId, Announcement_AnnouncementId and User_Id – PigeonMilk Stories Dec 16 '16 at 20:01
That depends on what SeenId is. I cannot see your dbcontext code so hard to say. But yes most likely it is because of that. – CodingYoshi Dec 16 '16 at 20:05
I just editted it with my dbContext – PigeonMilk Stories Dec 16 '16 at 20:57

EntityCommandExecutionException - Trying to link 2 models in 1 database

2 Answers2