Code - Financial Independence

Access Data Using the Entity Framework

Following the last tutorial on Linq to SQL, you can do pretty much the same exercise with Linq to Entities and C#. The concept is the same, we’re trying to reduce the amount of SQL code we’re writing to focus on the C# side and make our application more dynamic to changes in the data structure. You have two choices, you can go either database first or you can go code first. In code first, you design the classes and then you create the SQL database from them, in database first it’s the other way around. That is, classes are created from the database structure. I tend to prefer going database first because that’s how I am used to design my applications. In this example we’ll go database first.

Let’s get started!

1. Database Setup

For the scenario, we want to keep track of all the objects that have been sold for a set of stores. We will have a Stores table and SoldItems table joint in a 1 to many relationship. The first step will be to create the database.

Make sure SQL server and SQL Management Studio are installed on your computer as we’ll need them for this tutorial.

Below is the code for the database we’ll be using. I’ve added a few rows that you can use to create your own operations. Simply create a new script in Management Studio by going into File -> New -> Script with current connection. Copy and paste the code below and click on execute. Right-click on the Databases folder and hit Refresh.

CREATE DATABASE [Corporation]
GO
USE [Corporation]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SoldItems](
	[SoldItemID] [int] IDENTITY(1,1) NOT NULL,
	[StoreID] [int] NOT NULL,
	[Number] [int] NOT NULL,
	[Description] [varchar](150) NOT NULL,
	[Price] [money] NOT NULL,
	[Quantity] [int] NOT NULL,
 CONSTRAINT [PK_SoldItems] PRIMARY KEY CLUSTERED 
(
	[SoldItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Stores](
	[StoreID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[Street] [varchar](50) NOT NULL,
	[StreetNumber] [varchar](10) NOT NULL,
	[City] [varchar](50) NOT NULL,
	[Country] [varchar](50) NOT NULL,
	[IsActive] [bit] NOT NULL,
 CONSTRAINT [PK_Stores] PRIMARY KEY CLUSTERED 
(
	[StoreID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[SoldItems] ON 

INSERT [dbo].[SoldItems] ([SoldItemID], [StoreID], [Number], [Description], [Price], [Quantity]) VALUES (1, 1, 985649082, N'Shoes', 60.0000, 1)
INSERT [dbo].[SoldItems] ([SoldItemID], [StoreID], [Number], [Description], [Price], [Quantity]) VALUES (2, 2, 439849329, N'T-Shirt with logo', 70.0000, 2)
INSERT [dbo].[SoldItems] ([SoldItemID], [StoreID], [Number], [Description], [Price], [Quantity]) VALUES (3, 1, 328494300, N'Black Jeans', 100.0000, 1)
INSERT [dbo].[SoldItems] ([SoldItemID], [StoreID], [Number], [Description], [Price], [Quantity]) VALUES (4, 1, 432874373, N'Blue Jeans', 60.0000, 3)
SET IDENTITY_INSERT [dbo].[SoldItems] OFF
SET IDENTITY_INSERT [dbo].[Stores] ON 

INSERT [dbo].[Stores] ([StoreID], [Name], [Street], [StreetNumber], [City], [Country], [IsActive]) VALUES (1, N'StoreOne', N'Test', N'12', N'Pie', N'United States', 1)
INSERT [dbo].[Stores] ([StoreID], [Name], [Street], [StreetNumber], [City], [Country], [IsActive]) VALUES (2, N'Monument', N'Gray', N'454', N'Super', N'Canada', 1)
SET IDENTITY_INSERT [dbo].[Stores] OFF
ALTER TABLE [dbo].[SoldItems]  WITH CHECK ADD  CONSTRAINT [FK_SoldItems_Stores] FOREIGN KEY([StoreID])
REFERENCES [dbo].[Stores] ([StoreID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SoldItems] CHECK CONSTRAINT [FK_SoldItems_Stores]
GO
USE [master]
GO
ALTER DATABASE [Corporation] SET  READ_WRITE 
GO

Note: notice the On delete cascade which will delete all the attached SoldItems as a Store is removed.

Here is the final result from the schema in Management Studio.

Linq To Entities

2. Create a New Project in Visual Studio

Create a new project in Visual Studio, a console or Windows Forms application should do the trick!

Create New Project

3. Add a new ADO.Net Entity Context

The ADO.Net entity context will give you a nice friendly interface to add tables and stored procedures.

I suggest to create two new folders inside of the project to organize the data access layer: DataAccess -> DataObjects as shown below. Then right-click on the DataObjects folder and Add, New Item…

Data Access folders.

Now let’s add the Entity Data Context. Select ADO.NET Entity Data Model and give it a name. Mine will be named Corporation as it’s going to be renamed CorporationEntities.

Create Entity Data Context.

After you’ve named the Context, click on Add and you’ll be directed to another window like the following. Select EF Designer from database.

Database first Entity Context.

Next, Visual Studio wants to know the connection you wish to use. Click on New Connection then select SQL Server Connection.

Select SQL Serveur Connection.

Click on Next and we’ll input some credentials to access the database. In the first section, you can see a text box with the label Server name. Often what you want to enter in there is NAMEOFMACHINE\NAMEOFSQLINSTANCE. In my case, my PC is named Vier and the name of my instance is SQLEXPRESS. If you haven’t renamed your instance while installing SQL Server, chances are that it’s still SQLEXPRESS. You can stick with Windows Authentication for now. Then select the appropriate database name in the dropdown like shown below.

You can test the entered settings by clicking on the bottom left Test Connection button.

Create a new connection to the database.

Select the latest Entity Framework 6.x.

Entity Framework selection.

Choose the components you need to add to the Entity data model, if you follow along with me we’ll only need to check tables since we don’t have views or stored procedures in our architecture yet.

Components selection.

There you go! Once you click finish it will generate something like the following. It’s curious, we have the sysdiagram entity that’s been added. Let’s delete it by clicking the entity and then the delete key on our keyboard.

Created data context.

4. Modify Our Data Model

The model is static and will need to be updated if you make changes to the database. To do this operation, right-click on the background of the Entity model diagram and on Update Model from Database.

Update Entity Model.

You’ll be prompted with this window in which there are three tabs. One to Add elements, the other one to Refresh and the last one to delete. For now we don’t have anything new to add so you can select the Refresh tab and select Tables entirely if you want to update all the tables in the Entity model.

Add Update Delete Entities.

5. Create Data Access Classes

I’ve created two data classes which I call Data Links. I use them to accomplish basic operations on specific tables. Each table has its own data link class. It’s by no means a requirement to use this strategy but I’ve found that it separates the concerns properly between the UI, the business logic layer and the data access layer.

Below you can see both classes I created, you can do the same by right-click on the DataAccess folder then Add -> New Item… -> class.

Create Data Links

StoresDataLink.cs

Notice the namespace to LinqToEntities.DataAccess.DataObjects which matches my project name and the underlying folder structure. The following methods are basic create update delete retrieve methods to access the data, you can create more complex methods to access the data as you need them. Also, it shows the power of Linq for accessing databases.

using System;
using System.Collections.Generic;
using System.Linq;

using LinqToEntities.DataAccess.DataObjects;

namespace LinqToEntities.DataAccess
{
    public class StoresDataLink
    {
        private CorporationEntities _CorporationEntities;

        /// <summary>
        /// Parameterless constructor for a stand alone implementation of the class.
        /// </summary>
        public StoresDataLink()
        {
            _CorporationEntities = new CorporationEntities();
        }

        /// <summary>
        /// Constructor with parameters for use with transactions.
        /// </summary>
        /// 
        /// <param name="corporationEntities">The pre-initialized CorporationEntities object.</param>
        public StoresDataLink(CorporationEntities corporationEntities)
        {
            _CorporationEntities = corporationEntities;
        }

        /// <summary>
        /// Retrieves a Store from the database.
        /// </summary>
        /// 
        /// <param name="storeID">The ID of the Store to retrieve.</param>
        /// 
        /// <returns>A Store object.</returns>
        public Store Retrieve(int storeID)
        {
            if (!Exists(storeID))
                throw new ArgumentException("The storeID provided does not exist.");

            Store foundStore = _CorporationEntities.Stores.Single(store => store.StoreID == storeID);

            return foundStore;
        }

        /// <summary>
        /// Retrieves all Stores from the database.
        /// </summary>
        /// 
        /// <returns>An IEnumerable of Stores.</returns>
        public IEnumerable<Store> RetrieveAll()
        {
            IEnumerable<Store> stores = _CorporationEntities.Stores;

            return stores;
        }

        /// <summary>
        /// Adds a pre-defined Store to the database.
        /// </summary>
        /// 
        /// <param name="store">The Store to add.</param>
        /// <returns>The added Store.</returns>
        public Store Add(Store store)
        {
            Store addedStore = _CorporationEntities.Stores.Add(store);
            _CorporationEntities.SaveChanges();

            return addedStore;
        }

        /// <summary>
        /// Updates a Store in the database.
        /// </summary>
        /// 
        /// <param name="store">The updated Store to modify in the database.</param>
        public void Update(Store store)
        {
            if (!Exists(store.StoreID))
                throw new ArgumentException("The storeID provided does not exist.");

            Store storeToUpdate = _CorporationEntities.Stores.Single(selectedstore => selectedstore.StoreID == store.StoreID);

            storeToUpdate = store;
            _CorporationEntities.SaveChanges();
        }

        /// <summary>
        /// Deletes a Store from the database. Do not use to delete multiple items in a foreach loop or it will lock up.
        /// </summary>
        /// 
        /// <param name="storeID">The ID of the Store to delete.</param>
        public void Delete(int storeID)
        {
            if (!Exists(storeID))
                throw new ArgumentException("The storeID provided does not exist.");

            Store storeToDelete = _CorporationEntities.Stores.Single(selectedstore => selectedstore.StoreID == storeID);

            _CorporationEntities.Stores.Remove(storeToDelete);
            _CorporationEntities.SaveChanges();
        }

        /// <summary>
        /// Deletes multiple Stores from the database at once.
        /// </summary>
        /// 
        /// <param name="stores">The Stores to delete.</param>
        public void DeleteAll(IEnumerable<Store> stores)
        {
            _CorporationEntities.Stores.RemoveRange(stores);
            _CorporationEntities.SaveChanges();
        }

        /// <summary>
        /// Checks if a Store exists in the database.
        /// </summary>
        /// 
        /// <param name="soldItemID">The ID of the Store to check.</param>
        /// 
        /// <returns>True if it exists, False otherwise.</returns>
        public bool Exists(int storeID)
        {
            if (_CorporationEntities.Stores.Any(store => store.StoreID == storeID))
                return true;
            return false;
        }

        /// <summary>
        /// Counts the number of Stores in the database.
        /// </summary>
        /// 
        /// <returns>The number of Stores.</returns>
        public int CountAll()
        {
            return _CorporationEntities.Stores.Count();
        }
    }
}

SoldItemsDataLink.cs

If you want to access specific SoldItems without going throught the Store objects, you can use the following class. For the examples below I will only show the StoresDataLink in action.

using System;
using System.Collections.Generic;
using System.Linq;

using LinqToEntities.DataAccess.DataObjects;

namespace LinqToEntities.DataAccess
{
    public class SoldItemsDataLink
    {
        private CorporationEntities _CorporationEntities;

        /// <summary>
        /// Parameterless constructor for a stand alone implementation of the class.
        /// </summary>
        public SoldItemsDataLink()
        {
            _CorporationEntities = new CorporationEntities();
        }

        /// <summary>
        /// Constructor with parameters for use with transactions.
        /// </summary>
        /// 
        /// <param name="corporationEntities">The pre-initialized CorporationEntities object.</param>
        public SoldItemsDataLink(CorporationEntities corporationEntities)
        {
            _CorporationEntities = corporationEntities;
        }
        
        /// <summary>
        /// Retrieves a SoldItem from the database.
        /// </summary>
        /// 
        /// <param name="soldItemID">The ID of the SoldItem to retrieve.</param>
        /// 
        /// <returns>A SoldItem object.</returns>
        public SoldItem Retrieve(int soldItemID)
        {
            if (!Exists(soldItemID))
                throw new ArgumentException("The SoldItemID provided does not exist.");

            SoldItem foundItem = _CorporationEntities.SoldItems.Single(solditem => solditem.SoldItemID == soldItemID);

            return foundItem;
        }

        /// <summary>
        /// Retrieves all SoldItems from the database.
        /// </summary>
        /// 
        /// <returns>An IEnumerable of SoldItems.</returns>
        public IEnumerable<SoldItem> RetrieveAll()
        {
            IEnumerable<SoldItem> soldItems = _CorporationEntities.SoldItems;

            return soldItems;
        }

        /// <summary>
        /// Adds a pre-defined SoldItem to the database.
        /// </summary>
        /// 
        /// <param name="soldItem">The SoldItem to add.</param>
        public void Add(SoldItem soldItem)
        {
            _CorporationEntities.SoldItems.Add(soldItem);
            _CorporationEntities.SaveChanges();
        }

        /// <summary>
        /// Updates a SoldItem in the database.
        /// </summary>
        /// 
        /// <param name="soldItem">The updated SoldItem to modify in the database.</param>
        public void Update(SoldItem soldItem)
        {
            if (!Exists(soldItem.SoldItemID))
                throw new ArgumentException("The SoldItemID provided does not exist.");

            SoldItem soldItemToUpdate = _CorporationEntities.SoldItems.Single(solditem => solditem.SoldItemID == soldItem.SoldItemID);

            soldItemToUpdate = soldItem;
            _CorporationEntities.SaveChanges();
        }

        /// <summary>
        /// Deletes a SoldItem from the database.
        /// </summary>
        /// 
        /// <param name="soldItemID">The ID of the SoldItem to delete.</param>
        public void Delete(int soldItemID)
        {
            if (!Exists(soldItemID))
                throw new ArgumentException("The SoldItemID provided does not exist.");

            SoldItem soldItemToDelete = _CorporationEntities.SoldItems.Single(solditem => solditem.SoldItemID == soldItemID);

            _CorporationEntities.SoldItems.Remove(soldItemToDelete);
            _CorporationEntities.SaveChanges();
        }

        /// <summary>
        /// Checks if a SoldItem exists in the database.
        /// </summary>
        /// 
        /// <param name="soldItemID">The ID of the SoldItem to check.</param>
        /// 
        /// <returns>True if it exists, False otherwise.</returns>
        public bool Exists(int soldItemID)
        {
            if (_CorporationEntities.SoldItems.Any(solditem => solditem.SoldItemID == soldItemID))
                return true;
            return false;
        }
    }
}

6. Access the Data

We’ll do a few basic operations to show how it’s possible to access the data using the Entity Framework. The point of using the data links to interface with the Entity Framework is to gracefully separate the concerns as I stated above. That is, on the interface side we’ll be using only the data links and the data objects from the context to transfer data in or out of the database.

Here is how to represent the pattern graphically.

Data Links Pattern

I admit that using data links causes a little overhead but will enable further filtering and more consistency over the access to the database layer. What I mean is that if you’re working with multiple people on the same project and want to accomplish the same database access, you might use different methods to do so as with the DataLinks, one method to accomplish a specific task will be shared in the whole project. In short, you can access the data directly using the Entity Framework or use the DataLinks depending on your intended purpose. Both examples are shown in the code below.

Program.cs

using System;
using System.Collections.Generic;
using System.Linq;
using LinqToEntities.DataAccess;
using LinqToEntities.DataAccess.DataObjects;

namespace LinqToEntities
{
    class Program
    {
        static void Main(string[] args)
        {
            //If you want to see the raw database access using the Entity Framework, please skip below to: Accessing the same data using straight Entity Framework

            //-----------Completing data operations using DataLinks------------//
            CorporationEntities corporationEntities = new CorporationEntities();

            StoresDataLink storesDataLink = new StoresDataLink(corporationEntities);
            SoldItemsDataLink soldItemsDataLink = new SoldItemsDataLink(corporationEntities);

            //Cleanup database
            Console.WriteLine("Stores cleanup...");
            IEnumerable<Store> storesToCleanup = storesDataLink.RetrieveAll();
            storesDataLink.DeleteAll(storesToCleanup);

            Console.WriteLine("Number of stores in the database after cleanup... {0}", storesDataLink.CountAll());

            //One way to initialize a new Store
            Store storeOneToAdd = new Store();
            storeOneToAdd.Name = "Test Store";
            storeOneToAdd.Street = "front st.";
            storeOneToAdd.StreetNumber = "78-1";
            storeOneToAdd.City = "Toronto";
            storeOneToAdd.Country = "Canada";
            storeOneToAdd.IsActive = true;

            //The ID will be created automatically as it's an auto incremented identity
            //We could also retrieve the added store item to have a look at the generated ID
            storesDataLink.Add(storeOneToAdd);

            //In the second one we'll put items directly before adding it, the items will be added at the same time!
            //Another way to initialize a new Store
            Store storeTwoToAdd = new Store()
            {
                Name = "Store With Items",
                Street = "Back st.",
                StreetNumber = "77-2",
                City = "Toronto",
                Country = "Canada",
                IsActive = true
            };

            //We are adding a new item on the fly, don't need to specify any ID as they are auto generated and will be taken into account
            //by the Entity Framework in the Insert.
            storeTwoToAdd.SoldItems.Add(new SoldItem()
            {
                Number = 97843278,
                Description = "Blackberry Priv",
                Price = 499.99m,
                Quantity = 60
            });

            //I'll retrieve the added Store
            Store addedStore = storesDataLink.Add(storeTwoToAdd);
            Console.WriteLine("--Added Store--");
            OutputStoreInConsole(addedStore);

            //Update a store 
            Store storeToUpdate = storesDataLink.Retrieve(addedStore.StoreID);
            storeToUpdate.Name = "Super Store";

            storesDataLink.Update(storeToUpdate);

            //We'll retrieve the store once again to make sure it's been updated, you don't need to do that
            //normally because you will get an exception in case it didn't work.
            Store updatedStore = storesDataLink.Retrieve(addedStore.StoreID);
            OutputStoreInConsole(updatedStore);

            bool storeExistsPriorToDeletion = storesDataLink.Exists(updatedStore.StoreID);
            Console.WriteLine("Store exists prior to deletion : {0}", storeExistsPriorToDeletion);

            //Showcase of the delete method
            storesDataLink.Delete(updatedStore.StoreID);
            bool storeExists = storesDataLink.Exists(updatedStore.StoreID);
            Console.WriteLine("Deletion result store still exists in the database : {0}", storeExists);



            //-------Completing the same data operations using straight Entity Framework--------//

            //Cleanup Database
            Console.WriteLine("Stores cleanup...");
            IEnumerable<Store> storesToCleanupEF = corporationEntities.Stores;

            corporationEntities.Stores.RemoveRange(storesToCleanupEF);
            corporationEntities.SaveChanges();

            Console.WriteLine("Number of stores in the database after cleanup... {0}", corporationEntities.Stores.Count());

            //Add
            //We will add a store in two different ways
            Store storeOneToAddEF = new Store();
            storeOneToAddEF.Name = "Test Store EF";
            storeOneToAddEF.Street = "front st.";
            storeOneToAddEF.StreetNumber = "78-1";
            storeOneToAddEF.City = "Toronto";
            storeOneToAddEF.Country = "Canada";
            storeOneToAddEF.IsActive = true;

            corporationEntities.Stores.Add(storeOneToAddEF);
            //SaveChanges commits the changes to the database, you can do multiple changes before calling SaveChanges() if you would like
            corporationEntities.SaveChanges();

            Store storeTwoToAddEF = new Store()
            {
                Name = "Store With Items EF",
                Street = "Back st.",
                StreetNumber = "77-2",
                City = "Toronto",
                Country = "Canada",
                IsActive = true
            };

            storeTwoToAddEF.SoldItems.Add(new SoldItem()
            {
                Number = 97843278,
                Description = "Blackberry Priv EF",
                Price = 499.99m,
                Quantity = 60
            });

            Store addedStoreEF = corporationEntities.Stores.Add(storeTwoToAddEF);
            corporationEntities.SaveChanges();

            Console.WriteLine("--Added Store EF--");
            OutputStoreInConsole(addedStoreEF);

            //Update
            //We will update the store we've added previously
            Store storeToUpdateEF = corporationEntities.Stores.Single(store => store.StoreID == addedStoreEF.StoreID);
            storeToUpdateEF.Name = "Updated Store Name EF";

            //I don't have to call any special method to update because storeToUpdateEF is a pointer to the record in memory,
            //once modified, the record will be set to "dirty" and will be updated in the database on SaveChanges() method call with
            //any changes made to the object.
            corporationEntities.SaveChanges();
            OutputStoreInConsole(storeToUpdateEF);

            //Select and delete
            //We will delete the update store
            //Simple check to see if the store exists prior to deletion (this does not ensure that it won't throw an exception if
            //the store is deleted in the meantime.
            if (corporationEntities.Stores.Any(store => store.StoreID == storeToUpdateEF.StoreID))
            {
                //Retrieve a store for deletion (you can also retrieve a single store using another lambda expression)
                Store storeToDelete = corporationEntities.Stores.Single(store => store.StoreID == storeToUpdateEF.StoreID);

                corporationEntities.Stores.Remove(storeToDelete);
                corporationEntities.SaveChanges();
            }

            //Let's do a check to prove it doesn't exist anymore
            bool storeExistsEF = corporationEntities.Stores.Any(store => store.StoreID == storeToUpdateEF.StoreID);
            Console.WriteLine("Deletion result store still exists in the database : {0}", storeExistsEF);

            Console.ReadKey();
        }

        private static void OutputStoreInConsole(Store storeToOutput)
        {
            Console.WriteLine("Store Object : StoreID {0}, Name {1}, Street {2}, StreetNumber {3}, City {4}, Country {5}, IsActive {6}",
                               storeToOutput.StoreID,
                               storeToOutput.Name,
                               storeToOutput.Street,
                               storeToOutput.StreetNumber,
                               storeToOutput.City,
                               storeToOutput.Country,
                               storeToOutput.IsActive);
        }
    }
}

I have put a few Console.WriteLine() statements to show the output in the console but I strongly advise to add breakpoints and go step by step in the execution to really understand what’s going on. Comments have also been added to clarify some technical aspects of the operations completed.

Entity Framework vs Linq to SQL

In my opinion, the Entity Framework and Linq to SQL are comparable technologies, even if the Entity Framework seems to be more mainstream and maintained, I find they both make my life a lot simpler when I need to access databases from my applications. I hope they will both serve you as well as they do for me in my daily coding!

Suggested Reading

I suggest reading this book if you really want in dept information about C# and .Net in general. It’s a book I own and I refer to often.

Found an error in the code? Please send me a message so I can fix it as soon as possible!

Youtube

Part 1 – Setup

Part 2 – CRUD Operations

Part 3 – DataLinks

Previous article Use RRSP and TFSA to leverage Financial Independence

Related posts

0 Comments

No Comments Yet!

You can be first to comment this post!

Leave a Comment

*