.Net Programming - Financial independence

How To Query Table Relationships with Linq/Entity Framework in C#

A great thing that you can do with Entity Framework 6 is to query a database with table relationships without even writing a single line of SQL code! The Entity Framework makes it very easy and intuitive to access data from one object to another without even using joins. If you want to see how to use joins with Linq though, please refer to this post.

In this tutorial we’ll have a look at two scenarios:

  1. how to access children objects from a parent;
  2. how to access the parent object from one of its children.

1. Setup Your Database

Let’s setup our database first, make sure you have the two following Software installed on your computer:

Note: for SQL Server you can get the express version if you want. Also, here is a video on how to install both Management Studio and SQL Server. You don’t need to setup the networking part, only the basic installation at the beginning.

Launch SQL Management Studio and click on File -> New -> Query With Current Connection.

Copy and paste the following SQL code and run it using the Execute button in the top toolbar. It will create a sample database for you containing two tables (Stores and SoldItems) that we will use throughout the example.

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 (15, 23, 97843278, N'BlackBerry Priv', 499.9900, 60)
INSERT [dbo].[SoldItems] ([SoldItemID], [StoreID], [Number], [Description], [Price], [Quantity]) VALUES (16, 23, 32999201, N'Motorola RAZR', 1200.0000, 10)
INSERT [dbo].[SoldItems] ([SoldItemID], [StoreID], [Number], [Description], [Price], [Quantity]) VALUES (18, 23, 93002010, N'BlackBerry Leap', 120.0000, 15)
SET IDENTITY_INSERT [dbo].[SoldItems] OFF
SET IDENTITY_INSERT [dbo].[Stores] ON 

INSERT [dbo].[Stores] ([StoreID], [Name], [Street], [StreetNumber], [City], [Country], [IsActive]) VALUES (22, N'Test Store EF', N'front st.', N'78-1', N'Toronto', N'Canada', 1)
INSERT [dbo].[Stores] ([StoreID], [Name], [Street], [StreetNumber], [City], [Country], [IsActive]) VALUES (23, N'Store With Items EF', N'Back st.', N'77-2', N'Toronto', 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

2. Setup a Project in Visual Studio

Launch Visual Studio Community or download and install it if you don’t already have it on your computer, then create a new project of type .Net Framework Console.

You can create a folder structure like shown below (DataAccess->DataObjects) to organize your code better.

Entity Framework Folder Structure.

Then right-click on your Project and Add -> New Item and create a new EF 6.x DbContext Generator.

Entity Framework 6.x Create DbContext.

Update The Entity Framework With Your Database

To make sure your newly created database and the Entity Framework in your project are in sync, you’ll need to update the DbContext you’ve just created. To do so, double-click on the .edmx file and you will see the following screen appear.

Entity Framework 6 DbContext Screen

Right-click anywhere in the blank to open a context menu, then click on Update Model from Database.

Entity Framework Update Model From Database.

Select the two tables in the database like in the image below. Those tables will be mapped to the Entity Framework’s DbContext so that you can access them directly in the code using the xEntities object. x being replaced by how you named your .tt file, in our case it was named Corporation so the generated object is CorporationEntities.

Entity Framework Select Tables to add.

Next, click finish and now we can clearly see the 1 to * relationship in the newly updated Entity Framework diagram. How convenient is that! Everything is just like in our database, we’re ready to access some data.

Newly added tables to the Entity Framework DbContext.

3. Access SoldItems From a Specific Store

The first way we’re going to access the data is by starting from the parent Store table and get the children of a specific store from the SoldItems table. As I said before, the Corporation database we’re using has a 1 to many relationship between Stores and SoldItems. In other words, a store can have multiple SoldItems but a SoldItem can only belong to one specific Store. That’s why we’ll retrieve multiple SoldItems.

Program.cs

Here is what my Program.cs looks like at this point, make sure you look at the comments as they explain the processing step by step.

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

using CorporationRelationships.DataAccess.DataObjects;

namespace CorporationRelationships
{
    class Program
    {
        static void Main(string[] args)
        {
            // Define a new Corporation Entities DataContext.
            CorporationEntities corporationEntities = new CorporationEntities();

            // Select, from the Stores table, a single record where the record has a StoreID of 23.
            Store selectedStore = corporationEntities.Stores.Single(store => store.StoreID == 23);

            // From that record we select all of the SoldItems which is a collection of objects. We also convert it to a List (.ToList()) to fit in our List<SoldItem>.
            List<SoldItem> soldItems = selectedStore.SoldItems.ToList();

            // Then we write all those SoldItems in the console one by one using a foreach loop.
            foreach (SoldItem soldItem in soldItems)
                Console.WriteLine($"SoldItem associated to Store 23 - SoldItemID: {soldItem.SoldItemID}, Description: {soldItem.Description}.");

            Console.ReadKey();
        }
    }
}

Note: make sure the namespaces (using) are correct when copy pasting my code. For example, if your project is not named CorporationRelationships, it might be in a different namespace. The same applies for the using CorporationRelationships.DataAccess.DataObjects.

Notice from the code above that I first create the CorporationEntities object with which we will access the “in-memory” tables. Also, the tables are accessible by the corporationEntities variable. You can then refer to any of them just by calling the property inside with the name of the table like: corporationEntities.Stores.TheMethodYouWantToCall(). While we’re at it, it would be possible to add stored procedures if the database had any and access them directly like: corporationEntities.myStoredProcedureName(firstParameter, secondParameter).

Another interesting thing I want to point out is the selectedStore.SoldItems.ToList();. In this line I retrieve the list of SoldItems from the store that we’ve selected without even using a Join or anything. In the background we’re actually creating a full SQL query with joins but Linq To Entities makes it seamless for us. I think it’s much more convenient because you can access linked objects in a more natural way than with SQL.

4. Access the related Store From a Specific SoldItem

Now we’ll go the other way around. We want to start from a SoldItem and retrieve the associated Store. Once again, table relationships used without even joins!

Program.cs

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

using CorporationRelationships.DataAccess.DataObjects;

namespace CorporationRelationships
{
    class Program
    {
        static void Main(string[] args)
        {
            // Define a new Corporation Entities DataContext.
            CorporationEntities corporationEntities = new CorporationEntities();

            // Select, from the Stores table, a single record where the record has a StoreID of 23.
            Store selectedStore = corporationEntities.Stores.Single(store => store.StoreID == 23);

            // From that record we select all of the SoldItems which is a collection of objects. We also convert it to a List to fit in our List<SoldItem>.
            List<SoldItem> soldItems = selectedStore.SoldItems.ToList();

            // Then we write all those SoldItems in the console one by one using a foreach loop.
            foreach (SoldItem soldItem in soldItems)
                Console.WriteLine($"SoldItem associated to Store 23 - SoldItemID: {soldItem.SoldItemID}, Description: {soldItem.Description}.");

            // Retrieve the associated store to the SoldItem with the ID 18. 
            Store associatedStore = (from solditem in corporationEntities.SoldItems
                                     where solditem.SoldItemID == 18
                                     select solditem.Store).Single();

            // Display the Store associated with SoldItem 18 in the Console.
            Console.WriteLine($"Store associated to SoldItem 18: {associatedStore.StoreID}, {associatedStore.Name}.");

            Console.ReadKey();
        }
    }
}

For this part I’ve added a Linq query to retrieve the associated Store from the SoldItem with the identifier 18. Notice that from the Linq query in parentheses, I am selecting the Store that is in the SoldItem with the SoldItemID 18. Then I will use Single to make sure I have only one element since C# won’t know if that query will retrieve multiple results. We know that information because SoldItemID is actually the key and is unique.

It’s important to see that the query returns an IQueryable which is a not yet executed query, then I select one record from this IQueryable using Single(). This will force the execution of the query on the database and store the result in memory (associatedStore variable in this case).

Can Our Lives Be Even Easier?!

The goal of this tutorial was to illustrate how intuitive Linq To Entities can be and how it generates SQL code in the background for you to retrieve information in a more seamless fashion. This helps reduce the amount of code you’ll write as well as keeping much of it in C# rather than juggling between the two programming languages and increase the overall complexity and debugging time.

That said, what we’ve seen is just a glimpse of the power that you can leverage using Linq To Entities. For example, using Joins and other keywords, you’ll be able to retrieve more complex sets of Data.

If you’re only interested in the C# Linq part, you can stop now. Otherwise, if you would like to know a bit more about the real SQL output being generated, follow on with what’s next.

Let’s Look at the Generated SQL Query

For the more advanced stuff, let’s have a look at the queries that have been generated by Linq To Entities, just for fun! To accomplish this we’ll need to revamp our code a little. In the previous example we’ve executed the query on the database as soon as we created it using Single(). In other words, the Entity Framework retrieved the data immediately. But now since we want to know what query would be executed, we’ll need to separate the creation from the action.

In the following, I create the query and store it in an IQueryable object. Then I retrieve the SQL query using the ToString() method.

// Create a query and store it in the associatedStoreQuery variable, ready for execution.
IQueryable<Store> associatedStoreQuery = (from solditem in corporationEntities.SoldItems
                                                     where solditem.SoldItemID == 18
                                                     select solditem.Store);

// Execute the query contained in the IQueryable object by acting on it (in other words the Entity Framework will need to get the data)
Store associatedStore = associatedStoreQuery.Single();

Console.WriteLine($"Store associated to SoldItem 18: {associatedStore.StoreID}, {associatedStore.Name}.");

// Let's have a look at the generated SQL
string sqlCommandText = associatedStoreQuery.ToString();
Console.WriteLine(sqlCommandText);

Finally, here is the full code with both database access and generated queries displaying in the console.

Program.cs

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

using CorporationRelationships.DataAccess.DataObjects;

namespace CorporationRelationships
{
    class Program
    {
        static void Main(string[] args)
        {
            // Define a new Corporation Entities DataContext.
            CorporationEntities corporationEntities = new CorporationEntities();

            // Create the query to be executed which will select a single record where the record has a StoreID of 23. Notice that what's contained in store.SoldItems is in fact an ICollection object.
            IQueryable<ICollection<SoldItem>> soldItemsSelectionQuery = from store in corporationEntities.Stores
                                                                        where store.StoreID == 23
                                                                        select store.SoldItems;

            // From that record we select all of the SoldItems. We also convert it to a List to fit in our List<SoldItem>.
            List<SoldItem> soldItems = soldItemsSelectionQuery.Single()
                                                              .ToList();

            // Then we write all those SoldItems in the console one by one using a foreach loop.
            foreach (SoldItem soldItem in soldItems)
                Console.WriteLine($"SoldItem associated to Store 23 - SoldItemID: {soldItem.SoldItemID}, Description: {soldItem.Description}.");

            // Create a query and store it in the associatedStoreQuery variable, ready for execution.
            IQueryable<Store> associatedStoreQuery = (from solditem in corporationEntities.SoldItems
                                                      where solditem.SoldItemID == 18
                                                      select solditem.Store);

            // Execute the query contained in the IQueryable object by acting on it (in other words the Entity Framework will need to get the data)
            Store associatedStore = associatedStoreQuery.Single();

            Console.WriteLine($"Store associated to SoldItem 18: {associatedStore.StoreID}, {associatedStore.Name}.");

            // Let's have a look at the generated SQL. We call the ToString() method on the first and second queries.
            Console.WriteLine("--First Query Text--");
            string sqlCommandTextFirstQuery = soldItemsSelectionQuery.ToString();
            Console.WriteLine(sqlCommandTextFirstQuery);

            Console.WriteLine("--Second Query Text--");
            string sqlCommandTextSecondQuery = associatedStoreQuery.ToString();
            Console.WriteLine(sqlCommandTextSecondQuery);

            Console.ReadKey();
        }
    }
}

Output

Linq To Entities With Query Displayed in Console.

As you can see, Linq To Entities uses Joins in the background without you having to do any work or write any code for it. If we want to be more specific about the type of join used, then we’ll have to use them in Linq as well, that’s for another post!

Happy coding everyone!

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

YouTube

Next article How to Change the Interface Language in Visual Studio
Previous article Invest With Exchange Traded Funds on Low Saving Rate

Related posts

0 Comments

No Comments Yet!

You can be first to comment this post!

Leave a Comment

Your data will be safe! Your e-mail address will not be published. Also other data will not be shared with third person. Required fields marked as *