Entity Framework: reset migrations

The last week I have been deep diving into Entity Framework and Code-First. For an old-school database programmer who started the career in Oracle SQL plus, and continued to Microsoft SQL Server Management Studio the transmission has not been easy 🙂

The concept is similar, but the definition of the database is very close to the code. You define your table as classes (model) and set up the columns as properties and relationship in navigation properties. This is easy to understand, but the difficult part is to find all the tweaks to be able to set the proper multiplicity (1:1 and 1:m,) default values for columns, and last but not least audit triggers on columns like CreatedOn, CreatedBy, UpdatedOn and UpdatedBy. However, these topics are covered for my next few posts.

After experimenting the last week with Entity Framework Code-First, I suddenly had about 10-15 different migrations/Versions in my solution. That was not ideal and I wanted to find a way to reset the migration history and make the current version of my domain model as the initial migration for the project.

This can be achieved by the following commands in the “Package Manager Console” in Visual Studio 2012:

update-database -targetmigration:"0" -force -verbose
add-migration Initial
update-database

Now, I have a clean “Models” and “Migration” folder…Quite nice!

Versioning in Entity Framework Code-First

I have used Entity Framework for some projects over the past years, but never taken advantages of the Code-First functionality provided. However, with the stronger presence of Windows Azure and new services in “Microsoft Could”, I see how “Code-First Migrations” will help me versioning and deploying applications and database definitions.

Earlier this week I tried Code-First functionality together with ASP.NET Web API, but it became too complex to get things running, and I decided to take a step back and concentrate on Code-First Migrations.

After some googling and examples found on codeproject.com and MSDN blogs, I found a simple and great “getting started tutorial”. This is a basic tutorial for “Code-First Migrations” and shows most of the available functions, such as

  • Installing latest version of Entity Framework from NuGet Manager
  • Creating a data model in the model classes
  • Creating database from Visual Studio 2012
  • Adding changes through a new migration
  • Switching between different version/migrations
  • Adding custom code in the migration (class model)
  • Rolling back to original version
  • Generating script for full database
  • Make sure program has the latest version of the database

Full source-code.

Next up:

Combining Code-First and ASP.NET Web API

Found a great post by Jon Galloway, explaining how to create POCO/Model classes for the code-First Migration in Entity Framework.

Calculate distance between two GPS points (SQL Server)

In my last post, I found out how to check if a GPS point resides an area. In addition, I need to calculate the distance between two GPS points. Again, I am using samples from my neighborhood. The distance between train stations in Fredrikstad and Sarpsborg. Now I will only calculate the distance air wise, but this can be extended to use Bing data services to calculate how far this distance is in walking, driving and maybe by travelling by train.

blog_bing_distance

Code:

DECLARE @p3 GEOGRAPHY;
DECLARE @p4 GEOGRAPHY;
SET @p3 = geography::Parse('POINT(59.209111 10.950330)');
SET @p4 = geography::Parse('POINT(59.286441 11.116841)');
SELECT @p3.STDistance(@p4) as Meters1;

Happy coding….

Check if GPS points reside area (SQL Server)

I have been playing around with Bing Maps, GPS coordinates and SQL Server 2012 spatial data methods tonight. I am not sure if I will use these methods in my applications, but they are pretty nice and runs pretty fast on SQL Server 2012.

The picture below shows are geographical area in my neighborhood. The points 1, 2 and 3 defines a 2-dimentional area, and the two point 4 and 6 resides within this area, but point is outside. My objective is to create SQL statements to check if these points are inside or outside the area.

blog_bing_area

The easiest way of doing this is to use the new data type geography introduced in SQL Server 2008. The first thing we need to do is to define the area by using a POLYGON of the points 1-3. Secondly, we need to define the Points using the POINT object. The POLYGON and POINT object can be created by using the static method Parse. The last thing to do, is to check if the points reside the area. This is archieved by using the OGC Method STContains.

Code:

DECLARE @area GEOGRAPHY;
DECLARE @kalsnes GEOGRAPHY;
DECLARE @rostad GEOGRAPHY ;
DECLARE @ringstadaasen GEOGRAPHY ;
DECLARE @coords NVARCHAR(200);

SET @coords = '59.306414 10.945834, 59.316500 10.999811, ' + 
			'59.269515 11.024359, 59.306414 10.945834';

SET @area = geography::Parse('POLYGON ((' + @coords + '))');
SET @kalsnes = geography::Parse('POINT(59.308294 11.046463)');
SET @rostad = geography::Parse('POINT(59.299119 10.992154)');
SET @ringstadaasen = geography::Parse('POINT(59.304000 10.970417)');

SELECT @area.STContains(@kalsnes) AS kalsnes1;
SELECT @area.STContains(@rostad) AS rostad1;
SELECT @area.STContains(@ringstadaasen) AS ringstadaasen1;

Happy coding…

Free ebook: building hybrid Azure applications

Yesterday, I came accross a free 400 pages eBook in PDF format on Windows Azure Development. The book “Building Hybrid Applications in the Cloud on Windows Azure” seems to be the same as a video I saw on Channel9 last Autumn. I can’t find it anymore, but here is some links that might be in the same genre.

Build 2012: Building Hybrid Applications using the Windows Azure Service Bus

MSDN Live 2011:  MSDN Live Meeting Building hybrid applications with the Windows Azure Platform

Tomorrow, I will attent a Microsoft event with Ralph Squillace og Peter Dwersteg presenting Notifications Hubs, ServiceBus og Mobile Services, and developing Windows Store Applications. Looking forward to it.

 

Dynamcis CRM 2011: reinstalling server from RTM to Rollup 10 v2

Yesterday afternoon I had to start a full re-installation of a CRM 2011 server due to missing product key to upgrade from trail version to production server license.  The trail server had installed rollup 10 v2 with English database language and Norwegian language package. The installation media provided by the customer was the RTM version, and I need to install this and first install the Rollup 6 before applying the Rollup 10 v2. When this was completed, I could reimport the old organization database.

Dynamics CRM in the Field” team has a good post with compact information with the different rollup and service updated, build number, release dates and KB articles.

In addition, Pablo Peralta has good step-by-step post on how to install a CRM 2011 server On-premise.