Skip to main content

Repository Pattern with Cross Table Objects


In a recent post, I mentioned one of the advantages of using Entity Framework in your application is that you can build a generic repository class very easily. Of course, its not realistic to think that all access to the data will be a single table at a time. Most often it’s the case where you need to return data that spans multiple tables.

I’m  going to show you how I created a simple repository class that spans tables.

Creating the Summary/DTO Object

The first thing I like to start with is to create the simple POCO object that will be used to transport the data. This is essential to define first so that you do not get caught up in data structures, but instead define the data as the application is going to need it.

In the case with my database, I have a table called “Avail” that contains a ton of foreign keys to a contact table. I needed to display this data, but instead of a bunch of foreign keys, I needed to display the actual names of people. etc.

I ended up defining the object as follows:


Creating the Repository Class

After the DTO Object is created, its just a matter of creating a repository class that manages this object. In my implementation of the repository, I have an IRepository Interface that all of the repositories use to promote ease of understanding when working with them:


On top of this, I created a separate IFullAvailRepository so that I could customize this down the road if necessary with custom calls. For now, its empty.


I then created the repository class an implement the interface:


Implementing the interface

Now the hard (easy?) part. Using the power of Entity Framework and LINQ to Entities, implementing the interface for a custom object is actually quite easy. The most important part is now the objects are retrieved from the data context. By using an IQueryable object, we can abstract out filling the custom object into a simple property, and the rest of the methods just leverage that property.


Using the repository

Using this implementation, the DB isn’t actually hit until needed, so we can do queries to retrieve objects or sets of objects and only the items requested are in memory, instead of loading all of the items and doing the queries there. For example, this unit test retrieves 1 specific item out of the database, and its super fast.




I’m new to the repository pattern, but so far I really like the abstraction is provides to the data layer. I’m sure I’ll run into issues in the future, but so far, its working great!


Popular posts from this blog

Build/Deploy Windows service with TFS

Building and deploying a web service or website via TFS is pretty straight forward, but automatically deploying a windows service or console application takes a b it of setup. I’ve outlined the steps below to get your service deployed. Step 1: Set up Automated Build and MSDeploy on the Target server. If you are doing any sort of automated build/deploy I recommend using MSDeploy. You can follow steps 1-3 from a previous post here.Step 2: Edit the .csproj fileThe project file for the app you are trying to deploy needs to be edited so that TFS will create a directory for the output of the project. Otherwise the project will be built, but the assemblies will be placed in the code drop location with no organization to it.To edit the file, go to the open file dialog and locate the csproj file. Select it but don’t click “Open. Instead click on the dropdown arrow next to the Open button, select “Open with”, and choose the XML (Text) editor as shown:Once the file is open, add the following “Pr…

Get NodeAuthorization working in Kubernetes with acs-engine

Node Authorization in k8s I'm starting to get into the container world and I'm loving it. Recently we helped a client build out and deploy a micro-services application in Kubernetes. We created the cluster in Azure using the open source project acs-engine. After we got the cluster set up, our client asked for some updates to the cluster for security reasons. One of those updates was to enable Node Authorization.

What is Node Authorization? Node Authorization locks down each Node in the cluster to only be able to do actions on itself. If this is not turned on, its possible for a malicious pod to take actions on any other node, including reading secrets, deleting pods, etc. There is an excellent post by Antoine Cotten that explains this very well ( as well as RBAC, which is a different subject altogether).
How do I set it up? Based on the current documentation, it looks like setting up Node Authorization should be easy. Basically follow these steps Turn on TLS Boot StrappingEdit…