Posts Tagged 'Entity Framework'

New ASP.NET Training Course at Learning Tree

I was back in Learning Tree’s Reston offices last week, presenting the beta of my new ASP.NET course – Building ASP.NET Web Applications: Hands-On.  (The beta is part of our course development process where we try out the course in front of students for the first time.  Their feedback is an important part of refining exercises and slides to make sure that everything is clear, that all the exercises work as written and that we have the right balance of material).

I’ve been busy writing the course over the past few months, which is why this blog went very quiet for a while. The new course takes you all the way from explaining What is ASP.NET? through to building a multi-layer application using Code-First Entity Framework, the Web API and the HTML5 Geolocation API. (I put the course example online, so if you want to see what we build during the week, check out

What’s so exciting about the new course? (Apart from the fact that I wrote it, of course…)

Well… there’s Visual Studio 2012….

A lot of people aren’t keen on the new monochrome look and – horrors – capitalized MENU items – but there are some really nice new features like Page Inspector and the new improved Add Reference dialog. Beyond that, it remains a very powerful development environment that makes web development a pleasure. And it means, of course, that we can develop with .NET 4.5 – and that means access to a host of cool new features. There’s the Web API:

And bundling & minimizing – which both reduces the size of your .css and .js files for production and makes sure that all your small files are combined into a single  large file, which is a big help in reducing download times for the client:

And there’s also out-of-the-box support for HTML5…

The class covers all these and more, and takes attendees from creating a simple Web Form at the beginning of the class right through to building a layered application with a Code-First Entity Framework data access layer, a business layer calling IQueryables in the data access layer and a UI that uses everything from combining Model Binding with the ListView through to providing an alternative jQuery Mobile view of the entire web site. So if you’re new to ASP.NET Web Forms or just want to refresh your skillset, why not give it a try!

This is me in full flow at the front of the class…

And helping an attendee with one of the exercises…

Kevin Rattan

For other related information, check out this course from Learning Tree:

Building ASP.NET Web Applications: Hands-On

Exposing IQueryable/oData Endpoints With Web API

This is a follow on from my post on Web API and the Entity Framework. In that post, I showed a couple of approaches to dealing with JSON serialization problems in the Visual Studio 11 beta. Now I want to look at returning IQueryable from Web API methods.

IQueryable allows you to do what it says on the box: return an object that can be queried from the client. In other words, you can pass through arguments that tell the server what data to retrieve. This is a hot topic on the Web, with some people strongly against the idea and some strongly for it. The argument is between convenience (look how easy it is to get any data I want!) and security/architecture (look how easy it is for someone else to get any data they want).

I don’t have strong views either way. I share the concerns of those who worry about leaving client layers free to bombard the data layer with inappropriate (and potentially dangerous) queries, but I also like the convenience of being able to shape my queries from the client—especially given the Web API’s (partial) support of the oData specification. (For those unfamiliar with oData, it allows you to use querystring arguments to modify the query at the server, e.g., $top=10 will become .Take(10) in the EF query).

If I don’t use IQuerable, I will need to write lots of different methods to allow for different queries (e.g., in cocktails-r-us, I need to search for cocktails by beverage, non-liquid ingredient, name, id, etc.). Here is a simple example from my demo project, with two methods:  one returning an IEnumerable of BeverageTypes, the other a single BeverageType by id:

original version of code

If I want to get an individual BeverageType, I make a get request along these lines: http://%5Bmysite%5D/api/beveragetype/2. Here is the Firebug output from such a request:

output from non-oData request

If I switch to IQueryable as the return type, however, I can supply both queries from a single method (note the addition of ‘AsQuerable()’ at the end of the method return):

IQueryable version of the code

Now I can write my oData query as “http://%5BMySite%5D/api/beveragetype?$filter=id eq 2“, so I no longer need my separate specialized method.

screenshot of oData request and result

Let’s try and simplify the method. The oData specification allows us to expand associations using $expand, so let’s remove the .Include(“Beverages”) call from our method and pass that through on the querystring as follows: http://%5BMySite%5D/api/beveragetype?$filter=id eq 2&$expand=Beverages.

Here is the new code:

code without include

And here is the result… not quite what we were hoping for:

result of $expand - no include

It turns out that the Web API does not support $expand…. And I rather hope it never does. If Web API supported $expand, then my users would be able to create huge queries with far too many joins. In cocktails-r-us, I only want to return all the details of a cocktail (ingredients, comments, ratings, related cocktails, etc.) for one cocktail at a time. I don’t want users to be able to join all those tables in one massive query. So, that’s the upside. The downside is that I have to go back to using multiple methods, but even then I should only need two:  one to get a specific cocktail (or, in this case, BeverageType) by ID, the other to return lists of them by whatever criteria the client prefers.

final version of two methods

Since I can deal with security concerns by forcing my clients to authenticate (and only giving access to trusted developers in the first place), and that leaves me with only one concern: will my client developers write ill-advised queries? They might, for example return too many rows at once instead of paginating through the data as I would prefer. Fortunately, there is a solution: the [ResultLimit(n)] attribute. This restricts the number of items returned. So now our remote users will have to page through the data rather than return it all at once.

ResultLimit attribute

If we examine the output in Firefox/Firebug, you can see that only 5 rows are returned even though the query requested all the rows:

output with limit in place

ResultLimit is not a perfect solution. It only restricts the number of items returned from the method, not the number retrieved from the database. Here is what’s happening on the server:

Results on server

However, since the remote user won’t be able to get the data they want the easy way, they will be forced to write oData pagination queries like “http://%5Bsite%5D/api/beveragetype?$skip=5&$top=5&$orderby=Type desc” which would give them the following output on the client:

oData paginated query

I understand why people are nervous about exposing IQueryable, and I wouldn’t do it in a completely open way where anyone’s code can access my data without authentication, but I love its openness and flexibility and the way it works so easily with Web API.

Kevin Rattan

For related information, check out this course from Learning Tree: Building Web Applications with ASP.NET MVC.

Working With the Entity Framework and the Web API

In my last post, I talked about the new Web API controllers in MVC and showed how they work with simple data. In the real world, of course, I want them to work with my existing data, which uses Entity Framework. It turns out that this is far from straightforward (at least, in the beta).

Let’s start by trying to expose some standard EF data. When I began coding my personal website I used model-first rather than code-first development. (At that point, my focus was on getting to know jQuery mobile, and I was not concerned with best practices in MVC; I have refactored it since). So let’s go back to basics and begin with an .edmx version of the simple data I used in my last post.

beverageType in designer

The code to return this object using Entity Framework and Web API is as follows (with the result first placed into a  variable  so I can more easily examine the return in the debugger):

code to return IEnumerable of BeverageType

When we try and access this in Internet Explorer, we get the following error message:

“The type ‘BeverageType’ cannot be serialized to JSON because its IsReference setting is ‘True’. The JSON format does not support references because there is no standardized format for representing references. To enable serialization, disable the IsReference setting on the type or an appropriate parent class of the type.”

Interestingly, if we use Firefox, it actually works–but here the response is formatted as XML. Still, that suggests we’re almost there. Let’s make it more interesting. Let’s add an Association to our .edmx file to make it more realistic.

design view of association

And let’s add an Include to our Entity Framework code so that we return the Association along with our object:

code with include

Strangely, we get exactly the same results as before. Internet Explorer gives the same JSON error message. Firefox returns the same XML, without the association. Perhaps turning off LazyLoading will improve the Firefox return?

code removing lazy loading

Sadly, it makes no difference. We get exactly the same error, and since there is no convenient way to change the IsReference setting in an .edmx, perhaps it’s time to switch to Code First Generation and see if we can’t return JSON properly to Internet Explorer.

Here are my types:

code first types

And here is my DbContext:

code first dbcontext

Let’s run it again and see what happens now… And yes–this time, we’ve managed to break both Internet Explorer and Firefox! Both get the following error message:

“You must write an attribute ‘type’=’object’ after writing the attribute with local name ‘__type’. ”

Now, this error is actually useful. It tells us we need to turn off proxy generation, so let’s tweak our DbContext. And we may as well turn of lazy loading here while we’re at it:

turning off proxy creation

Now the Web API makes a valiant effort, sends through the beginning of a JSON version of the data to both IE and Firefox, and then gives up with an error message:

screen capture error

Here is the error message:

“System.Runtime.Serialization.SerializationException: Object graph for type ‘Beverage’ contains cycles and cannot be serialized if reference tracking is disabled.”

At this point, I imagine we’re all starting to get a little frustrated. I know I am. So, to cut a long story (and a lot of Web searching) short… it looks like the fundamental problem lies in the interaction between the Entity Framework and the DataContractSerializer used in the beta. This is supposed to be fixed by a change to the JSON.Net serializer when Web API goes live – but in the meantime, what to do?

There are two possible approaches (and I’ve placed the code for both online here):

  1. Substitute the JSON.Net serializer in place of the default. For this, you need first to follow the helpful instructions in this blog post: and then
    1. Add an instruction to the serializer to ignore self-referencing loops
    2. Add the new serializer in the Global.asax
  2. Use projection. The suggestions online seem to fall into two camps:
    1. Use projection with anonymous objects.
    2. Use projection with Data Transfer Objects.

But if I did want to use projection, why would I want to:

  1. return anonymous objects when I have perfectly good POCO objects, or
  2. create new DTOs when I already have perfectly good POCO objects?

So I wondered what would happen if I used projection with the same POCO Code First objects that the serializer had choked on when I returned them directly…

projection using code first poco objects

And here is the result in Firefox/Firebug:

output from projection

And in Internet Explorer/F12 Developer Tools

output in IE:

The Web API is going to be great, but it takes a little work if you’re using the beta. If you want to look at the code for either solution I outlined above, it’s available here. (The full project is 13MB, even as a zip, so I just put up the code: if you want it to run, you’ll need to copy the files into a VS11 project and download the various Nugets, as well as getting EF to generate the database for you).

In my next article, I’m going to take a look at the thorny issue of whether to return an IQueryable and—if you do choose to do so—how you can protect yourself against over-large queries.

Kevin Rattan

For related information, check out this course from Learning Tree:  Building Web Applications with ASP.NET MVC.

Integrating Your Own Code with Entity Framework: The Factory

In my last post, I showed how to use the code-first technology in Entity Framework 4.1 to create your business entity classes and integrate them into EF. I’ll complete that code-first process in this post and add a warning about a limitation in the process compared to Entity Framework’s original data-first process.

After defining your business entities, the next step in the code-first process is to create the factory that will transfer data (and updates) between the database and your business entity classes. You start creating your factory by defining a class that inherits from DbContext:

Public Class NorthwindModel
      Inherits System.Data.Entity.DbContext

End Class

Once you’ve created the class, you need to add a property that returns a DbSet class of the classes that represent a row in the table. This example returns the OrderDetail class in a property called OrderDetails and Product class in in a property called Products:

Public Property OrderDetails As System.Data.Entity.DbSet(OfOrderDetail)
Public Property Products As System.Data.Entity.DbSet(OfProduct)

Of course, this factory class won’t be able to do much if you haven’t told it where your data is kept. The simplest solution is to add a connection string to your config file’s ConnectionStrings section with the same name as your factory class (“NorthwindModel” in this example). If you want complete control you can pass a connection string your DbContext object’s constructor. The most flexible way to handle that is to create a constructor for your factory that accepts a connection string and passes the string on to the constructor of the DbContext your factory inherits from:

Public Sub New(ByRef ConnectionString As String)

End Sub

A simple LINQ query will tell you if you’ve successfully defined your classes and factory:

Dim dbc As New NorthwindModel("Data Source=.\SQLEXPRESS…")

Dim res = (From ordl In dbc.OrderDetails
Select ordl.Product).ToList
For Each prd In res
    Debug.Print(prd.ProductName & ":" & prd.OrderLines.Count.ToString)

Updates work as you would expect them to with EF. This code, for instance, retrieves the first Product object and changes its UnitPrice before saving the changes:

Dim res = (From prd In dbc.Products
Select prd).First

res.UnitPrice = 2

Since you’re now supporting updates, it makes sense to expand the OrderDetail class to include some business logic to ensure that only good data is entered. This code in the OrderDetail class’ UnitPrice property ensures that no one can enter negative numbers (or 0) for the price:

Private _UnitPrice As Int16
PublicProperty UnitPrice As Decimal
Return _UnitPrice

End Get
   Set(value As Decimal)
If value < 1 Then

Throw New Exception("Invalid value for Price. Must be greater than 0.")

      End If
     _UnitPrice = value
End Set
End Property


This design is a simple implementation of an object model for the Northwind database—effectively, I’ve implemented the Table Module pattern where there’s one table for each class and one class for each table (as discussed in Learning Tree’s design patterns and best practices for .NET course). There’s nothing wrong with that, and (for most applications) much of any application will use the Table Module pattern. However, using the code first development process described in this post (and the previous post) means that you can’t implement the Domain Object Model where a single table may, for instance, be represented by multiple classes. As the design patterns course demonstrates, those more functional models can be easily developed using EF’s data-first process.

As an example, there really are two kinds of products in the Northwind Products table: active products and inactive products (products that have been discontinued). The business logic for active and inactive products should be different—you shouldn’t be able to order an inactive product, for instance. An implementation of the Domain Object Model pattern would create a base Product class that was responsible for functionality shared by both active and inactive products. The next step in the pattern would be to create an ActiveProduct class that inherits from Product and contains all (and only) the code for active products. Finally, a second, InActiveProduct class that also inherits from Product would contain all (and only) the logic for Inactive products. While this is easy to implement in the data-first process, it isn’t possible in the code-first process: the DbContext object won’t allow the same class to appear twice in the DbSet properties.

But if what’s important to you is to integrate your code into Entity Framework (and you may already have the classes you’ll want to integrate and just need to add some data annotations to them), the code-first tools will let you do that quickly. And you’ll pick up all the functionality of EF and LINQ on the way.

Peter Vogel

Integrate Your Own Code with Entity Framework: The Business Entities

While Microsoft’s Partial Classes and Partial Methods let you extend the classes generated for you by Entity Framework, in the end you’re working with someone else’s code (albeit, code that is, effectively, written for you by the ADO.NET team). But with Entity Framework 4.1‘s code-first technology you can write your own classes and have EF use them. You’re still able to use LINQ to retrieve your data and EF to save your changes. To use EF’s code-first technology, after downloading EF 4.1 and creating your project in Visual Studio, just add references to the EntityFramework, System.ComponentModel.DataAnnotations and System.Data.Entit libraries.

For this example, I’ll use the Northwind database because it demonstrates some of the typical issues you’ll encounter in integrating your code with an existing database using EF’s code-first technology. I’ll start by creating a Products class to represent a row in the Products table and include a few properties that correspond to the columns in the table:

Public Class Products  Public Property ProductID As Integer Public Property ProductName As String  Public Property CategoryID As Integer End Class

EF will, by default, tie this class and its properties to the Products table with its columns by matching their respective names. However, I’ll now end up with a pluralized class name (Products) representing a single row in the table. I’d rather have a singular name for this class. I can change the name of my class and still tie it to the Products table by using the DataAnnotations Table attribute:

<System.ComponentModel.DataAnnotations.Table("Products")> Public Class Product

I also need to tell EF which of these columns is the table’s primary key field by adding the Key annotation to my ProductID property:

<System.ComponentModel.DataAnnotations.Key()> Public Property ProductID As Integer

My next step is to create the OrderDetails class to represent a row in the Order Details table. The Table attribute lets me not only singularize the name of the object but deal with the unfortunate space in the name of the Order Details table which I can’t match in my class name:

<System.ComponentModel.DataAnnotations.Table("Order Details")> Public Class OrderDetail  Public Property OrderId As Integer  Public Property ProductId As Integer  Public Property UnitPrice As Decimal  Public Property Quantity As Int16 End Class

The OrderDetail table has a composite key made up the OrderId and ProductId columns. Both of the matching properties need to be marked with the Key attribute. They also need to have the Column attribute added to specify the order that the two columns appear in the key:

<System.ComponentModel.DataAnnotations.Key()> <System.ComponentModel.DataAnnotations.Column(Order:=0)> Public Property OrderId As Integer <System.ComponentModel.DataAnnotations.Key()> <System.ComponentModel.DataAnnotations.Column(Order:=1)> Public Property ProductId As Integer

My final change is to tie these two classes together in a way that mimics the foreign key/primary key relationship between the two tables in the database. To the OrderDetail class, I add a property to the OrderDetails class that points to the product that the OrderDetail is tied. As required by code-first, mark it as Overridable. The name of the property doesn’t matter here but its return type must point to the class that represents the related table:

Public Overridable Property Product As Product 

In the Product class, I need a similar property that points to the multiple Order Details rows that a Product may appear on. That means that I need to declare the OrderDetails property as some object that implements the ICollection interface. Again, the property must be marked as Overridable and the datatype of the return value must be the related class:

Public Overridable Property OrderDetails As ICollection(Of OrderDetail) 

Other than adding the annotations, this is the same code you’d write if you were creating your own business entities. The next step is to create the factory class that will retrieve rows from the database and create these objects and put in some business code of my own.

Peter Vogel

More tips for ASP.NET MVC jQuery Mobile sites

I’ve had a very enjoyable week finalizing the alpha of my new ASP.NET MVC/jQuery Mobile site and putting it online at cocktails-r-us. I’m now testing and refining it (as well as dutifully drinking lots of cocktails so I can take photos and upload them to the site: all for the good of the application, you understand. Now, if I can just work out a way to claim them on expenses….). Along the way, I’ve come across a few problems (and solutions) that come from combining ASP.NET MVC with jQuery mobile and thought I’d share them with you.

One nice feature of jQuery mobile RC2 is that you can easily hide labels in a way that works with assistive technologies. This is achieved using a css class (“ui-hidden-accessible”). You can then use the HTML5 placeholder attribute to supply helpful text to your users. This allows you to make excellent use of the highly restricted space available in mobile devices.


html code

Becomes this:

output using placeholder

This is great…. Except for the fact that the ASP.NET MVC LabelFor() helper, unlike other helpers, has no overload allowing you to add HTML attributes – so there’s no out-of-the-box way to add the class attribute without having to go back to hard-coded HTML. And so you end up with this…

output showing label

The result is that you end up hard-coding the HTML, when what you really want to do is something like this:

html helper code

Fortunately, the solution lies in something I’ve already blogged about. In earlier posts, I detailed how the MVC3 ActionLink helper allows you to add the hyphenated data-* HTML5 attributes using an underscore, and how you can create your own helper to do so in MVC2. Well, it turned out I also needed that code in MVC3 after all. The UrlHelper class does not apply the substitution – so when I used the UrlHelper in an ImageLink helper, I had to use the ‘MVC2’ approach. That turned out to be fortunate, as I was able to reuse that code in a helper that extended LabelFor() to accept an anonymous object for HTML attributes – and at the same time future proof it against the need to add any data-* attributes in the future. (I won’t post the code, as it’s too similar to a previous blog post, but I’ve put it online here for anyone who wants it.)

So then I ran the code and… didn’t get what I was hoping for. The LabelFor() helper worked as I’d intended… but EditorFor() was ignoring the placeholder attribute, leaving me with this:

textbox with no label or placeholder

Not very helpful, is it? The code does not throw an error, but the attribute doesn’t get added and there’s nothing in the UI to guide the user. There are a couple of complex fixes for this. You can create a template for EditorFor(), or you can create your own custom attributes. I chose to go down the easy route, and switch from the EditorFor() helper provided by the Visual Studio Entity Framework scaffolding, and use TextBoxFor() instead – which did respect the attributes and gave me the result I was looking for.

Here’s the code:

code with textboxfor

And here’s the output.

working output

So I suppose the message is – ASP.NET MVC and jQuery Mobile can play nicely together, but it’s early days yet and expect to do a lot of fiddling around to get the exact behavior you’re looking for.

Kevin Rattan

For other related information, check out these courses from Learning Tree:

Building Web Applications with ASP.NET MVC

jQuery: A Comprehensive Hands-On Introduction

Pre-Compile LINQ to Entities Queries for Faster Processing

When you issue a LINQ statement against an Entity Framework model, LINQ and Entity Framework conspire together to generate an SQL statement that’s sent to the database engine. The problem is that, if you execute that the same LINQ statement twice, that conversion is performed twice. For a statement executed more than once, especially if it’s complicated, you’d prefer to do the conversion only once.

Starting with the .NET Framework 3.5 you can precompile your query by passing it to the Compile method of the CompiledQuery class. While this technique will save you time, you shouldn’t expect a blindingly fast improvement in your application’s response time—Entity Framework probably isn’t spending a lot of time converting your LINQ statements to SQL. This technique will, however, make possible some additional opportunities that I’ll discuss in later posts.

Here’s how to work this magic, starting with a LINQ statement that uses no parameters. This statement retrieves every Order in the Northwind database:

A simple LINQ query

Even in a statement this simple, you’ll need to pass your pre-compiled LINQ statement the ObjectContext object it uses (the northwndEntities object in this example). And, of course, after the statement executes, you’d like to get access to the Orders collection the statement returns.

You pre-compile your LINQ statement by passing it to the Compile method of the CompiledQuery object. The Compile method is a generic method so you have to tell it the data types of the objects it will be dealing with. In this case, that’s the inbound parameter (the ObjectContext) and the outbound collection returned by the LINQ statement. That collection will implement the IQueryable interface which is, itself, a generic type, so you’ll also need to specify the data type of the Entity Framework object being returned in the collection. So the initial part of calling the Compile method, which specifies the data types, looks like this:

Passing data types to the Compile method

After specifying the data types of the parameters the Compile method must deal with, you actually only pass the method one parameter: A lambda function that accepts the inbound ObjectContext and holds your LINQ statement:

Passing the LINQ statement to the Compile method

Putting it all together, this example compiles a LINQ statement that is passed a nortwndEntities object (the ObjectContext object) and returns a collection of northwndModel.Order objects. The lambda method that holds the LINQ statement accepts, as a parameter, the ObjectContext required by the LINQ query. The compiled LINQ statement is returned to be held in a variable (in this case, a variable called “qry”):

The full Compile method, setting a variable

You can now retrieve the query by calling the Invoke method on the variable, passing any required parameters. This example passes in the ObjectContext and catches the result:

Using the variable's invoke method, passing the ObjectContext

Of course that doesn’t addressing handling statements with multiple parameters (e.g. with a Where clause) but I’ll come back to that in my next post (along with how to declare that qry variable). After that, I’ll look at leveraging this feature to call LINQ queries on a background thread and wrap up by looking at what’s coming in .NET 4.5.

Peter Vogel

Learning Tree International

.NET & Visual Studio Courses

Learning Tree offers over 210 IT training and Management courses, including a full curriculum of .NET training courses.

Free White Papers

Questions on current IT or Management topics? Access our Complete Online Resource Library of over 65 White Papers, Articles and Podcasts

Enter your email address to subscribe to this blog and receive notifications of new posts by e-mail.

Join 29 other followers

Follow Learning Tree on Twitter


Do you need a customized .NET training solution delivered at your facility?

Last year Learning Tree held nearly 2,500 on-site training events worldwide. To find out more about hosting one at your location, click here for a free consultation.
Live, online training

%d bloggers like this: