In this blog I will show you how to quickly store and retrieve data in your existing project using the Entity Framework. As example project I will use an existing EPiServer project.
Enabling Entity Framework in your project
Start with adding the Entity Framework references to your project. Right-click on References and choose Manage NuGet Packages. Search for Entity Framework package and install it.
NuGet Package Manager for installing Entity Framework 6
Alternatively use the Package Manager Console and type in:
PM> Install-Package EntityFramework
Creating a model
Now lets say we have a webshop and we want to store a webshop order in the database using the Entity Framework. For that lets create two models and put them in ~/Models/EntityFramework or any other directory you prefer.
|
1 2 3 4 5 6 7 8 9 10 |
public class Order { public int OrderId { get; set; } public string Name { get; set; } public string Email { get; set; } public DateTime OrderDateTime { get; set; } public double TotalValue { get; set; } public virtual List<Product> Products { get; set; } } |
And a model for the products you ordered.
|
1 2 3 4 5 6 7 8 9 10 |
public class Product { public int ProductId { get; set; } public int ProductPageId { get; set; } public int Amount { get; set; } public double CurrentPrice { get; set; } public int OrderId { get; set; } public virtual Order Order { get; set; } } |
Some cool attributes that you can use in your models are described here:
http://msdn.microsoft.com/en-us/data/jj591583.aspx
Now lets have a brief look at those models. Most of the properties are self explaining but there is one special property which is the virtual property. This is used for lazy loading. Meaning that if you load an order from the database you get all related products in the same query for free. And by free I mean without doing extra coding or requests.
Now we need one more thing. Because this is just a model and it still needs to be connected (mapped) to a database. For that we use a Database Context.
The main class that coordinates Entity Framework functionality for a given data model is the database context class. You create this class by deriving from the System.Data.Entity.DbContextclass. In your code you specify which entities are included in the data model. You can also customize certain Entity Framework behavior.
Lets create a simple database context for our order models.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
namespace EPiServer.Templates.Alloy.Models.EntityFramework { public class OrderContext : DbContext { public OrderContext(string connString) : base(connString) { } public DbSet<Order> Orders { get; set; } public DbSet<Product> Products { get; set; } } } |
As you can see it doesnt do much more then asking you which database to use by requiring a connectionstring in the constructor and telling it to store Order and Product models.
Storing data using the Entity Framework
Now lets pretend we just received a new order through the submission of a form on your webshop.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
public ActionResult Submit(WebshopOrderPage currentPage, string Naam, string Email, string Shoppingcart) { var orderdetails = JsonConvert.DeserializeAnonymousType<List<ShoppingCart>>(Shoppingcart, new List<ShoppingCart>()); var service = ServiceLocation.ServiceLocator.Current.GetInstance<IContentRepository>(); using (var db = new OrderContext("EPiServerDB")) { // Save order var order = new EPiServer.Templates.Alloy.Models.EntityFramework.Order(); order.Name = Naam; order.Email = Email; order.OrderDateTime = DateTime.Now; db.Orders.Add(order); db.SaveChanges(); // Save products related to order foreach (var p in orderdetails) { var productdetails = service.Get<WebshopProductPage>(new ContentReference(p.productID)); db.Products.Add(new Product { Amount = p.Amount, OrderId = order.OrderId, ProductPageId = p.productID, CurrentPrice = productdetails.Price }); order.TotalValue += productdetails.Price*p.Amount; } db.SaveChanges(); } var model = new PageViewModel<WebshopOrderPage>(currentPage); model.CurrentPage.orderComplete = true; return View("~/Views/WebshopOrderPage/Index.cshtml",model); } |
I understand this is a lot of code. But lets take a look at the Entity Framework specific code. The code belongs to an EPiServer project in which we already have a connectionstring EPiServerDB configured.
|
1 2 3 4 |
<connectionStrings> <clear /> <add name="EPiServerDB" connectionString="Data Source=localhost;Initial Catalog=dbTrainingEpiserverAlloy;Integrated Security=False;User ID=dbUserTrainingEpiserverAlloy;Password=somecoolpassword;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" /> </connectionStrings> |
We want the Entity Framework to use the same database. Ofcource by passing a different connectionstring you can also save your EF models to a different database.
|
1 2 3 4 |
using (var db = new OrderContext("EPiServerDB")) { .... } |
I think the rest of the code is pretty self explaining. You save the order and then save all individual products you bought. The string shoppingcart is a JSON serialized object containing which product you bought (every product has its own content page and therefor is related to a product page), how many times you want this specific product and what its price is.
Retrieving your orders from the database using Entity Framework
This is also very simple. All you need to do is something like this:
|
1 2 3 4 |
using (var db = new OrderContext("EPiServerDB")) { var orders = db.Orders.Where(x => x.OrderId == 5); } |
This will return you order with ID 5.
Quick tip when you accidently deleted your local database file (.mdf)
If you like to mess around like me you probably have tried once to delete the mdf file and see what happends. I expected deleting the file would just reset my database back to an empty one but instead the site kept complaining about ‘Cannot attach the file ‘.mdf’ as database in MVC‘. The reason is if you delete the DB file, it still stays registered with SqlLocalDB. You can fix it by deleting you localDB like this: (run from your console)
|
1 2 |
>> sqllocaldb.exe stop v11.0 >> sqllocaldb.exe delete v11.0 |
Now restart your app or run ‘update-database’ and the database file should be recreated.