Håkon Bergem Håkon Bergem - 7 months ago 17
SQL Question

ForeignKey won't work | ASP .NET MVC 5 app

I'm trying to connect messages posted to the database with the corresponding userId which sent it.

But it wont work, and returns the error: Cannot insert the value NULL into column 'UserId', table 'aspnet-idelabs10-20160411101634.dbo.Ides'; column does not allow nulls. INSERT fails.
The statement has been terminated.


Which is pretty logic since it cant be null.

This is my code:

namespace idelabs10.Migrations
{
using System;
using System.Data.Entity.Migrations;

public partial class second : DbMigration
{
public override void Up()
{

CreateTable(
"dbo.Ides",
c => new
{
IdeId = c.Int(nullable: false, identity: true),
Emne = c.String(),
Forslag = c.String(),
UserId = c.String(nullable: false, maxLength: 128),
})
.PrimaryKey(t => t.IdeId)
.ForeignKey("dbo.AspNetUsers", t => t.UserId, cascadeDelete: true)
.Index(t => t.UserId);

CreateTable(
"dbo.AspNetRoles",
c => new
{
Id = c.String(nullable: false, maxLength: 128),
Name = c.String(nullable: false, maxLength: 256),
})
.PrimaryKey(t => t.Id)
.Index(t => t.Name, unique: true, name: "RoleNameIndex");

CreateTable(
"dbo.AspNetUserRoles",
c => new
{
UserId = c.String(nullable: false, maxLength: 128),
RoleId = c.String(nullable: false, maxLength: 128),
})
.PrimaryKey(t => new { t.UserId, t.RoleId })
.ForeignKey("dbo.AspNetRoles", t => t.RoleId, cascadeDelete: true)
.ForeignKey("dbo.AspNetUsers", t => t.UserId, cascadeDelete: true)
.Index(t => t.UserId)
.Index(t => t.RoleId);

CreateTable(
"dbo.AspNetUsers",
c => new
{
Id = c.String(nullable: false, maxLength: 128),
Email = c.String(maxLength: 256),
EmailConfirmed = c.Boolean(nullable: false),
PasswordHash = c.String(),
SecurityStamp = c.String(),
PhoneNumber = c.String(),
PhoneNumberConfirmed = c.Boolean(nullable: false),
TwoFactorEnabled = c.Boolean(nullable: false),
LockoutEndDateUtc = c.DateTime(),
LockoutEnabled = c.Boolean(nullable: false),
AccessFailedCount = c.Int(nullable: false),
UserName = c.String(nullable: false, maxLength: 256),
})
.PrimaryKey(t => t.Id)
.Index(t => t.UserName, unique: true, name: "UserNameIndex");

CreateTable(
"dbo.AspNetUserClaims",
c => new
{
Id = c.Int(nullable: false, identity: true),
UserId = c.String(nullable: false, maxLength: 128),
ClaimType = c.String(),
ClaimValue = c.String(),
})
.PrimaryKey(t => t.Id)
.ForeignKey("dbo.AspNetUsers", t => t.UserId, cascadeDelete: true)
.Index(t => t.UserId);

CreateTable(
"dbo.AspNetUserLogins",
c => new
{
LoginProvider = c.String(nullable: false, maxLength: 128),
ProviderKey = c.String(nullable: false, maxLength: 128),
UserId = c.String(nullable: false, maxLength: 128),
})
.PrimaryKey(t => new { t.LoginProvider, t.ProviderKey, t.UserId })
.ForeignKey("dbo.AspNetUsers", t => t.UserId, cascadeDelete: true)
.Index(t => t.UserId);



}


Table "Ides" is the one that wont function the way i want, cause it cant seem to request the userid from aspnetuser.

Any ideas?
Im pretty new to this btw :)

Code for the controller called Forslag2

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.Mvc;
using idelabs10.Models;

namespace idelabs10.Controllers
{
public class Forslag2Controller : Controller
{
private ApplicationDbContext db = new ApplicationDbContext();

// GET: Forslag2
public ActionResult Index()
{
return View(db.Ides.ToList());
}

// GET: Forslag2/Details/5
public ActionResult Details(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Ide ide = db.Ides.Find(id);
if (ide == null)
{
return HttpNotFound();
}
return View(ide);
}

// GET: Forslag2/Create
public ActionResult Create()
{
return View();
}

// POST: Forslag2/Create
// To protect from overposting attacks, please enable the specific properties you want to bind to, for
// more details see http://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Create([Bind(Include = "IdeId,Emne,Forslag,UserId")] Ide ide)
{
if (ModelState.IsValid)
{
db.Ides.Add(ide);
db.SaveChanges();
return RedirectToAction("Index");
}

return View(ide);
}

// GET: Forslag2/Edit/5
public ActionResult Edit(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Ide ide = db.Ides.Find(id);
if (ide == null)
{
return HttpNotFound();
}
return View(ide);
}

// POST: Forslag2/Edit/5
// To protect from overposting attacks, please enable the specific properties you want to bind to, for
// more details see http://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Edit([Bind(Include = "IdeId,Emne,Forslag,UserId")] Ide ide)
{
if (ModelState.IsValid)
{
db.Entry(ide).State = EntityState.Modified;
db.SaveChanges();
return RedirectToAction("Index");
}
return View(ide);
}

// GET: Forslag2/Delete/5
public ActionResult Delete(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Ide ide = db.Ides.Find(id);
if (ide == null)
{
return HttpNotFound();
}
return View(ide);
}

// POST: Forslag2/Delete/5
[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public ActionResult DeleteConfirmed(int id)
{
Ide ide = db.Ides.Find(id);
db.Ides.Remove(ide);
db.SaveChanges();
return RedirectToAction("Index");
}

protected override void Dispose(bool disposing)
{
if (disposing)
{
db.Dispose();
}
base.Dispose(disposing);
}
}
}

Answer

First of all, you need to activate Authorization for the Action, so that only logged-in Posts are allowed. If you are using a standard MVC app, then it's very simple, just add the "Authorize"-attribute to the method. Next, pick up the UserId from the environment and use it for the Insert, see example below (I removed UserId from incoming members to avoid confusion):

[HttpPost]
[ValidateAntiForgeryToken]
[Authorize]
public ActionResult Create([Bind(Include = "IdeId,Emne,Forslag")] Ide ide)
{
    if (ModelState.IsValid)
    {
        ide.UserId = User.Identity.GetUserId()
        db.Ides.Add(ide);
        db.SaveChanges();
        return RedirectToAction("Index");
    }

    return View(ide);
}