In this article we shall learn how to save more than 2 decimal places into the database using Entity Framework. In general, Entity framework only saves 2 decimal places for decimal data types in the database.
Introduction & Background
We had to save Latitude and Longitude of a location into the database and we chose to use
Decimal data type into the SQL server database table. However we found that it saves only two decimal places (precision) into the database so we were stuck. Googled for few hours and that didn't help.
Few suggest to create DecimalPrecision custom attribute and so on but that didn't work. So here I have come to a solution that worked fine for me and hope it will for you also.
Code to save decimal places (precision)
To demonstrate how to save, edit and list decimal places, let's first create a sample database table.
Create a database table
Let's first create a database table as shown below
Notice that the Lat (to save Latitude) and Lon (to save Longitude) data type is float.
Create ASP.NET MVC model
Below is the corresponding model for the above database structure.
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Web;
namespace MVCTraining5.Models
{
public class Location
{
[Key]
[DatabaseGenerated(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity)]
public int AutoId { get; set; }
public string Loc { get; set; }
public double Lat { get; set; }
public double Lon { get; set; }
}
}Notice that instead of keeping the data type as decimal, I have kept double as float corresponds to double in the C# with Entity Framework.
Create a controller
Follow
these steps to create a controller. Our controller looks like this. Note that your controller action may look a bit different as mine is
asynchronous controller action methods and this has nothing to do with saving decimal precision into the database.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Threading.Tasks;
using System.Net;
using System.Web;
using System.Web.Mvc;
using MVCTraining5.Models;
namespace MVCTraining5.Controllers
{
public class LocationsController : Controller
{
private TrainingMVCContext db = new TrainingMVCContext();
// GET: Locations
public async Task<ActionResult> Index()
{
return View(await db.Locations.ToListAsync());
}
// GET: Locations/Details/5
public async Task<ActionResult> Details(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Location location = await db.Locations.FindAsync(id);
if (location == null)
{
return HttpNotFound();
}
return View(location);
}
// GET: Locations/Create
public ActionResult Create()
{
return View();
}
// POST: Locations/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 async Task<ActionResult> Create([Bind(Include = "AutoId,Loc,Lat,Lon")] Location location)
{
if (ModelState.IsValid)
{
db.Locations.Add(location);
await db.SaveChangesAsync();
return RedirectToAction("Index");
}
return View(location);
}
// GET: Locations/Edit/5
public async Task<ActionResult> Edit(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Location location = await db.Locations.FindAsync(id);
if (location == null)
{
return HttpNotFound();
}
return View(location);
}
// POST: Locations/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 async Task<ActionResult> Edit([Bind(Include = "AutoId,Loc,Lat,Lon")] Location location)
{
if (ModelState.IsValid)
{
db.Entry(location).State = EntityState.Modified;
await db.SaveChangesAsync();
return RedirectToAction("Index");
}
return View(location);
}
// GET: Locations/Delete/5
public async Task<ActionResult> Delete(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Location location = await db.Locations.FindAsync(id);
if (location == null)
{
return HttpNotFound();
}
return View(location);
}
// POST: Locations/Delete/5
[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public async Task<ActionResult> DeleteConfirmed(int id)
{
Location location = await db.Locations.FindAsync(id);
db.Locations.Remove(location);
await db.SaveChangesAsync();
return RedirectToAction("Index");
}
protected override void Dispose(bool disposing)
{
if (disposing)
{
db.Dispose();
}
base.Dispose(disposing);
}
}
}
So on the whole above controller is a typical controller that access the database from the database.
Create ASP.NET MVC View
Views are created when you use scaffolding to create Controller, however you need a little modification to overcome below error message.
If you do not alter the view like explained in the Create/Edit/Index view below, you should be getting this error.
Exception: System.InvalidOperationException: The model item passed into the dictionary is of type 'System.Double', but this dictionary requires a model item of type 'System.String'.
Create view
This view will need a little modification for the Lat and Lon field, Look at the bold text below. Instead of trusting to Html.EditFor method, I have create a manual text box and kept the name same as the property name.
So change the view as suggested below.
@model MVCTraining5.Models.Location
@{
ViewBag.Title = "Create";
}
<h2>Create</h2>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div class="form-horizontal">
<h4>Location</h4>
<hr />
@Html.ValidationSummary(true, "", new { @class = "text-danger" })
<div class="form-group">
@Html.LabelFor(model => model.Loc, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.Loc, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Loc, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Lat, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
<input type="text" name="Lat" required class="form-control" />
@Html.ValidationMessageFor(model => model.Lat, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Lon, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
<input type="text" name="Lon" required class="form-control" />
@Html.ValidationMessageFor(model => model.Lon, "", new { @class = "text-danger" })
</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>
}
<div>
@Html.ActionLink("Back to List", "Index")
</div>Edit View
Similar to Create view, change the Edit view as well. I have manually set the Lat and Lon text box value to the Model property values.
@model MVCTraining5.Models.Location
@{
ViewBag.Title = "Edit";
}
<h2>Edit</h2>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div class="form-horizontal">
<h4>Location</h4>
<hr />
@Html.ValidationSummary(true, "", new { @class = "text-danger" })
@Html.HiddenFor(model => model.AutoId)
<div class="form-group">
@Html.LabelFor(model => model.Loc, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.Loc, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Loc, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Lat, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
<input type="text" name="Lat" class="form-control" value="@Model.Lat" />
@Html.ValidationMessageFor(model => model.Lat, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Lon, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
<input type="text" name="Lon" class="form-control" value="@Model.Lon" />
@Html.ValidationMessageFor(model => model.Lon, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
<div class="col-md-offset-2 col-md-10">
<input type="submit" value="Save" class="btn btn-default" />
</div>
</div>
</div>
}
<div>
@Html.ActionLink("Back to List", "Index")
</div>
Index view
List view also need a modification and that is instead of using Html.DisplayFor, use model item property directly. Look at the bold text below,.
@model IEnumerable<MVCTraining5.Models.Location>
@{
ViewBag.Title = "Index";
}
<h2>Index</h2>
<p>
@Html.ActionLink("Create New", "Create")
</p>
<table class="table">
<tr>
<th>
@Html.DisplayNameFor(model => model.Loc)
</th>
<th>
@Html.DisplayNameFor(model => model.Lat)
</th>
<th>
@Html.DisplayNameFor(model => model.Lon)
</th>
<th></th>
</tr>
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.Loc)
</td>
<td>
@item.Lat
</td>
<td>
@item.Lon
</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id=item.AutoId }) |
@Html.ActionLink("Details", "Details", new { id=item.AutoId }) |
@Html.ActionLink("Delete", "Delete", new { id=item.AutoId })
</td>
</tr>
}
</table>
Similarly, change the view of Details and Delete view as well. Use item.Lat and item.Lon instead of Html.DisplayFor(model => model.Lat) and Html.DisplayForm(model => model.Lon).
Once you have done all above, your database table should have all the decimal places (precision) saved like below.
Thanks for your patience for reading this article. Hope this was informative. Do let me know your comments or feedback.