Category: <span>Entity Framework</span>

Like many, our database has rather a lot of naughty nullable columns that really should be non-nullable, so we end up with null data issues all over the place. One way to avoid this is to modify the property in the .edmx file to add a default value. However, this is slow and painful, and easy to forget.

As there is no use case we can foresee where you would want a null string property, we have taken the step of adding a constructor to the entity (generated in the T4 template) that initialises every string property to an empty string. This means that whenever you create a new entity, any string properties will be initialised, avoiding any null values.

However, this leaves you with a problem if you want to set a (non-empty) default value for a string.

Many of the extension methods that we use have a signature that takes an Expression<Func<TSource, bool>> rather than just a Func<TSource, bool>? Ever wondered why? Do you even know what the difference is? No, nor do most people, but it’s worth getting to know, as it can be useful.

I recently came across a situation where I needed to delete all the data from a table and reinsert it (don’t ask why, it was quite sensible actually). I wanted to avoid anyone reading from the table while it was in an intermediate state, ie with part of the data in and part not. I did a quick search on using transactions in EF, and came across an article (not linked for reasons that should become apparent) that strongly recommended not using them at all. This all seemed sensible advice, until I read the comments. A lot of people criticised the article as giving very bad advice. Amongst the links was one to an MSDN page that explained how to use transactions with EF6 onwards. This was much cleaner than the advice in the previous article, and being from Microsoft, one hopes it is more authoritative. It’s worth reading the whole…

It seems we aren’t the only ones to find filtering and sorting child entity collections unnecessarily painful. Someone has been kind enough to write a Nuget package (part of a suite of them by the looks of things) that simplifies your code significantly.

This package removes the need for some of the tortuous hoops we had to negotiate before.

A fairly standard set up for me is to have a solution in which I have a Data project that contains an EF6 .edmx file, generated from an existing database. I then split the entities into a separate Entities project, and have a Repositories project that references them both.

The problem was, I wanted to write unit tests against the code, but couldn’t work out how to do it. This post shows what didn’t work, and then what did.

What should have been a relatively easy job turned into a very complex one. How I did battle with SQL Server and (mostly) won. By the way, in case you’re wondering about the title (and the subheading below if you’ve looked further on), then you obviously haven’t read The Hunting Of The Snark by Lewis Carroll. If so, I strongly recommend you do, partly because it’s a wonderful piece of sophisticated nonsense, and mainly because it’s far more entertaining than this article! It was subtitled “An Agony In Eight Fits” with each section being described as a Fit. It seemed appropriate for what happened here… Fit The First – Background I had a request to allow the user to enter some keywords, and we would show all support tickets that used the keywords in any of a number of fields. As SQL Server provides a full-text search facility, this seemed ideal for…

Whilst the benefits of using an OR/M such as Entity Framework are obvious, so are some of the disadvantages. The most obvious one (to me at least, and as I’m writing this article, I can drone on about what bothers me, OK?) is the performance impact. This comes in two flavours, the first-time hit and the not-first-time-hit (OK, so not a great name, feel free to suggest something better). The first-time hit is caused when you do your first query after starting the application. Entity Framework starts up a conversation with the database, queries the structure of all of the tables, and builds the model that you will use to query the database. Other than doing a quiet query when the application loads, while distracting the user with a dancing ferret or something, I don’t think there is a lot you can do about this. The other type of performance…

I previously blogged about a seemingly innocent LINQ problem that had me baffled for ages, which was how you sort or filter an entity’s child collection in Linq. For example, if you want to pull a collection of customers, and include all of their orders from this year, but none from earlier, then there doesn’t seem to be a simple way to do this in Linq. You need to do the query in two stages, the first which builds an anonymous type, and the second which links the to parts of it together. See that post for more details.

I also blogged about the problem of Linq not including child entities when doing joins, which requires you to cast the query to an ObjectQuery<> so you can use the Include() method on it.

The problem comes when you want to combine the two methods, meaning that your query needs to be constructed in two stages to ensure that the sorting or filtering of the child collection is done correctly, but you also need to cast the final result to an ObjectQuery<> before you send it out over WCF. The problem arises because you need to enumerate the query before doing the Include(), as that is the only way to ensure that the sorting is done, but calling AsEnumerable() gives you an IEnumerable<> (reasonably enough), which can’t be cast to an ObjectQuery! So what’s a fellow to do? Good question, and one that had me going for ages.

I am currently working on an application where I want to have a search feature that allows people to search for businesses within a certain distance of their home (or anywhere else they care to choose). I have some old UK postcode data knocking around, and was going to use that. For those not familiar with them, UK postcodes are made up of two parts, a major (also known as outward) part, and a minor (or inward) part. The major part is one or two letters followed by one or two digits, and the minor part is a digit, followed by two letters. Examples of valid postcode formats are M25 0LE, NW11 3ER and L2 3WE (no idea if these are genuine postcodes though). Coupled with the postcodes are northings and eastings. These odd-sounding beasties are simply the number of metres north and east from a designated origin, which is…