C# MVC Repository Pattern

[nabizain]

Hey guys, I really need your guidance and assistance. I have a method which gets a list of items from database:

My database tables:

public class Item
{
[Key]
public int ItemId { get; set; }
public string Title { get; set; }
public decimal Price { get; set; }
public string ItemArtUrl { get; set; }

public virtual int CategoryId { get; set; }

public virtual Category Categories { get; set; }

}

public class Category
{
public int CategoryId { get; set; }
public string Name { get; set; }
public string Description { get; set; }

public virtual ICollection<Item> Items { get; set; }

}

My viewmodels:

public class ItemViewModel
{
[Key]
public int ItemId { get; set; }
public string Title { get; set; }
public decimal Price { get; set; }
public string ItemArtUrl { get; set; }

public int CategoryId { get; set; }
public IEnumerable<SelectListItem> MyCategories { get; set; }
}



public class CategoryBusiness : ICategoryBusiness
{
public List<CategoryViewModel> GetAllCategories()
{
var categoryrepo = new CategoryRepository();

var query = (from x in categoryrepo.GetAll()
select new CategoryViewModel()
{
CategoryId = x.CategoryId,
Name = x.Name,
Description = x.Description
}).ToList();

return query;
}

}

In my controller [HttpGet] Create method, I create a drop down list which will be rendered in my view:

[HttpGet]
public ActionResult Create()
{
var categoryBusiness = new CategoryBusiness();
ViewBag.items = new SelectList(categoryBusiness.GetAllCategories(), "CategoryId", "Name");
return View();
}

[HttpPost]
public ActionResult Create(ItemViewModel item)
{
var itemBusiness = new ItemBusiness();

itemBusiness.Insert(item);

return View();
}

My Html code is as follows:

@model DemocraticLiberalCongress.Model.ItemViewModel

@{
ViewBag.Title = "Create";
Layout = "~/Views/Shared/_Layout.cshtml";
}

<h2>Create</h2>

@using (Html.BeginForm())
{
@Html.AntiForgeryToken()

<div class="form-group">
@Html.LabelFor(model => model.CategoryId, new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.DropDownList("items", null, htmlAttributes: new { @class = "form-control" })
@Html.ValidationMessageFor(model => model.CategoryId)
</div>
</div>

<div class="form-group">
<div class="col-md-offset-2 col-md-10">
<input type="submit" value="Create" class="btn btn-default" />
</div>
</div>
</div>
}

When I debug the program it renders the dropdown. However when I commit to the database. I get the following error:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.Items_dbo.Categories_CategoryId". The conflict occurred in database "DLCDataContext", table "dbo.Categories", column 'CategoryId'.
The statement has been terminated.


Thanks

 

[Haterade]

Check that you don't have conflicting foreign key constraints set up for the fields in SQL, such as a circular cascade. If a change is correctly being cascaded on one field, the other relevant field should probably be set to take no action.

 

[^^vampire^^]

Check in SSMS what the constraint is for "FK_dbo.Items_dbo.Categories_CategoryId"

I imagine that the CategoryId is not being passed through with the model on your post method.

Check that CategoryId is not null or 0 on object "item" in your Post method.
Also you should probably be doing this type of data validation before do anything with the object itself.

On that note you are also trying to insert an instance of ItemViewModel into the DB rather than an instance of Item.
You are receiving an an instance of ItemViewModel, which should then be mapped to an instance of Item

I would also recommend:
* looking into using AutoFac to manage your repos rather than creating them each time
* automapper to manage db entities and viewmodels and their mappings
* A unit of work pattern to manage db changes in one context


for now:

[HttpPost]
public ActionResult Create(ItemViewModel item)
{
if (!ModelState.IsValid)
{
//error as modelstate is invalid
}

if ((item.CategoryId == null) || (item.CategoryId == 0))
{
//error as categoryid is invalid
}

/*some sort of check to make sure the categoryid exists and is valid */
var categoryrepo = new CategoryRepository();
if (!categoryrepo.categories.Any(category => category.Id == item.CategoryId))
{
// error as category does not exist in db
}

//map ItemViewModel to Item

var newItem = new Item();
newItem.Title = item.Title;
// ......etc

var itemBusiness = new ItemBusiness();

itemBusiness.Insert(newItem);

return View();
}




Some info on repository mistakes people make:
https://programmingwithmosh.com/entity-framework/common-mistakes-with-the-repository-pattern/

 

[nabizain]

Check in SSMS what the constraint is for "FK_dbo.Items_dbo.Categories_CategoryId"

I imagine that the CategoryId is not being passed through with the model on your post method.

Check that CategoryId is not null or 0 on object "item" in your Post method.
Also you should probably be doing this type of data validation before do anything with the object itself.

On that note you are also trying to insert an instance of ItemViewModel into the DB rather than an instance of Item.
You are receiving an an instance of ItemViewModel, which should then be mapped to an instance of Item

I would also recommend:
* looking into using AutoFac to manage your repos rather than creating them each time
* automapper to manage db entities and viewmodels and their mappings
* A unit of work pattern to manage db changes in one context


for now:

[HttpPost]
public ActionResult Create(ItemViewModel item)
{
if (!ModelState.IsValid)
{
//error as modelstate is invalid
}

if ((item.CategoryId == null) || (item.CategoryId == 0))
{
//error as categoryid is invalid
}

/*some sort of check to make sure the categoryid exists and is valid */
var categoryrepo = new CategoryRepository();
if (!categoryrepo.categories.Any(category => category.Id == item.CategoryId))
{
// error as category does not exist in db
}

//map ItemViewModel to Item

var newItem = new Item();
newItem.Title = item.Title;
// ......etc

var itemBusiness = new ItemBusiness();

itemBusiness.Insert(newItem);

return View();
}




Some info on repository mistakes people make:
https://programmingwithmosh.com/entity-framework/common-mistakes-with-the-repository-pattern/

Hi Vampire

I have entered a few breakpoints and it seems that when I am posting back the CategoryId is 0

 

[Kosmik]

Hi Vampire

I have entered a few breakpoints and it seems that when I am posting back the CategoryId is 0

We had a similar issue a while back if I recall. When doing a multi insert you need to tell the query to return a context key otherwise it returns the primary key of the last item you inserted rather than the main records primary key. That was using dapper though.

 

[nabizain]

We had a similar issue a while back if I recall. When doing a multi insert you need to tell the query to return a context key otherwise it returns the primary key of the last item you inserted rather than the main records primary key. That was using dapper though.

Howsit mate how would I go about doing that

Thanks in advance

 

[^^vampire^^]

You currently set the Label for the categoryid control and validation thereof but no name/id for the select list at all.
The CategoryId is being set as 0 as it cannot find the CategoryId control.
You need to explicitly name this if it is not something rendered from the model. Add the id and name values after the class.

@Html.DropDownList("items", null, htmlAttributes: new { @class = "form-control", @id = "CategoryId", @name = "CategoryId" })

If you had to inspect the source before adding the above, I imagine the control would have no name or id set at all, and therefore there is no way for the razor engine and MVC to make a logical link between what is selected in the select list and the CategoryId property on your model.