Optimistic concurrency using a SQL DateTime in Entity Framework 4.0

This article explains how to implement optimistic concurrency checking using a SQL Server DateTime or DateTime2 column. It's a follow-up of my previous article on using a TimeStamp column for that same purpose. In most -if not all- concurrency checking cases it actually makes more sense to use a DateTime column instead of a TimeStamp. The DateTime data types occupy the same storage (8 bytes) as a TimeStamp, or even less: DateTime2 with 'low' precision takes only 6 bytes. On top of that: their content makes sense to the end user. Unfortunately the DateTime data types are 'a little bit' less evident to use for concurrency checking: you need to declare a trigger (or a stored procedure) on the table, and you need to hack the entity model. A sample table Sample time! Let's start with creating a table to hold some data. Table definition Here's how the table looks like (the solution at the end of the article contains a full T-SQL script). The LastModified column will be used for optimistic concurrency checking: CREATE TABLE [dbo].[Hero](     [Id] [int] IDENTITY(1,1) NOT NULL,     [Name] [nvarchar](50) NOT NULL,     [Brand] [nvarchar](50) NULL,     [LastModified] [datetime] NULL,  CONSTRAINT [PK_Hero] PRIMARY KEY CLUSTERED (     [Id] ASC )) Trigger definition Unlike an Identity or a TimeStamp value, a DateTime value is not automatically generated and/or updated. So we have to give the database a little help, e.g. by creating a trigger for insert and update on that table: CREATE TRIGGER [dbo].[trg_iu_Hero] ON [dbo].[Hero] AFTER INSERT, UPDATE AS BEGIN    SET NOCOUNT ON;      UPDATE [dbo].[Hero]       SET LastModified = GETDATE()     WHERE Id IN (SELECT Id FROM inserted) END Alternatively, you could insert through a stored procedure.  A sample application I already prepared for you a small sample application. Here's how the main window looks like: Cute, isn't it ? The problem In the entity model, you have to make sure that the LastModified column has the correct settings (Fixed and Computed): Run the application with just the generated code. You will observe that when you update a record, the entity's LastModified property will NOT be updated. SQL Server Profiler will reveal that only an update statement is issued. The new value of LastModified is assigned by the trigger but NOT fetched: The solution In order to let the Entity Framework fetch the new value of the DateTime column -or whatever column that is modified by a trigger-, you need to hack the model's XML and manually add the following attribute in the SSDL: Somewhere in Redmond there will certainly be an architect who will provide an excuse for this behavior. To us developers, this sure smells like a bug. Anyway, if you re-run the application with the modified SSDL, the new DateTime value will appear after insert or update. SQL Server profiler reveals the extra select statement: Source Code Here's the source code, the whole source code, and nothing but the source code: U2UConsult.EF40.DateTimeConcurrency.Sample.zip (616,27 kb) Enjoy! Thank you   This article is dedicated to my 3-year old daughter Merel. Last week she briefly turned into a real angel, but then decided to come back. I want to thank from the bottom of my heart everybody who helped saving her life: her mama, her mammie, the MUG, and the emergency, reanimation, intensive care, and pediatric departments of the uza hospital.  

The Missing Linq to SQL Spatial

This article provides hints and hacks on how to use the SQL Server spatial data types -Geography and Geometry- in Linq to SQL. The data provider has a problem with serializing and deserializing the SQL UDT's. If you ever tried to use these data types in a Linq to SQL (or Entity Framework) project then you certainly encountered the following error: “One or more selected items contain a data type that is not supported by the designer”. Setting up a test environment I created a small database called 'Spatial' with a schema called 'Europe', and a table called 'Countries'. Here's the structure of the table: CREATE TABLE [Europe].[Countries](     [CountryID] [int] IDENTITY(1,1) NOT NULL,     [CountryName] [nvarchar](50) NOT NULL,     [Shape] [geography] NOT NULL,  CONSTRAINT [PK_Countries] PRIMARY KEY CLUSTERED (     [CountryID] ASC ) The source code that accompanies this article contains the scripts to create the assets and populate the table. Here's an indication of the table contents: I created a Visual Studio 2008 Console application, and added a reference to Microsoft.SqlServer.Types. Then I added Linq to SQL classes (Europe.dbml). When I dropped the Europe.Counties table into the designer, the result was the expected error message: Hint 1: Use views that cast the data type I decided to create a view in the schema: vw_Countries. It exposes the same signature as the table, but with one difference: the shape column is returned as Varbinary(MAX) instead of Geography: CREATE VIEW [Europe].[vw_Countries] AS SELECT CountryID       ,CountryName       ,CONVERT(VARBINARY(MAX), Shape) AS Shape FROM Europe.Countries   The Geography UDT is physically stored as Varbinary(MAX) in SQL Server, so the table and the view are basically the same. There's one important difference though: Visual Studio is not allergic to the view. You can easily drag and drop it into the data model designer: The Linq data provider delivers the varbinary data as System.Data.Linq.Binary, but is -strangly enough- unable to convert this back to SqlGeography. It took me a while to figure out the conversion myself, but here's a working version: Binary belgiumb = (from c in db.vw_Countries                    where c.CountryID == 4                    select c.Shape).FirstOrDefault(); SqlGeography geo = new SqlGeography(); geo.Read(new System.IO.BinaryReader(new System.IO.MemoryStream(belgiumb.ToArray())));   Hint 2: Package your conversion code We're going to get stuck with this type of conversions for a while, so it makes sense to wrap the calculations e.g. in extension methods. Here's an example: /// <summary> /// Converts a Linq Binary to a SQL Server Geograpy. /// </summary> /// <remarks>Throws an Exception if the Binary contains invalid data.</remarks> public static SqlGeography AsGeography(this Binary binary) {     if (binary == null)     {         return SqlGeography.Null;     }       SqlGeography result = new SqlGeography();       result.Read(         new System.IO.BinaryReader(             new System.IO.MemoryStream(                 binary.ToArray())));       return result; }   So now I can call it like this: SqlGeography b = (from c in db.vw_Countries                   where c.CountryID == 4                   select c.Shape).FirstOrDefault().AsGeography(); Console.WriteLine(" The area of Belgium is {0} m²", b.STArea().Value);   Hack 1: Change source mappings I wanted to execute my queries directly against the table. So I copy/pasted the view in the dbml, and modified the source mapping: Now I can call the queries like this: belgium =     SqlGeography.Parse((from c in db.Countries                         where c.CountryID == 4                         select c.Shape.ToString()).FirstOrDefault());   Hack 2: Change data type mappings Since I wanted IntelliSense, I copy/pasted the Country table again, and changed the .NET type of the Shape property to SqlGeography: The good news is: I now have IntelliSense at design time: The bad news is: I will have exceptions all over the place. If you return a spatial data type in the select, then a query is successfully constructed and sent to SQL Server, but the return values cannot be deserialized. Here's such a query: belgium = (from c in db.Countries2            where c.CountryID == 4            select c.Shape).FirstOrDefault();   It results in a runtime exception: If you call a method -apart from ToString()- on a spatial data type in the where-clause, then you'll bump into a NotSupportedException already at compile time. Here's such a query: var query = (from c in db.Countries2              where (c.Shape.STArea() > 10).Value              select c.CountryName).FirstOrDefault();   And its result at compile time: Intermezzo I tried to use Varbinary as data type for the _Shape backing variable, and SqlGeography as data type for the Shape property, and call conversions in the getter and setter. It didn't work: the getters and setters seem to be bypassed by the Linq provider. Conclusion: hack 2 was not a good idea. We'll stop using the Countries2 entity... Hint 3: Use scalar functions Since STIntersects cannot be used, I created a scalar function that takes two Well-Known Texts, and returns whether or not the shapes intersect: CREATE FUNCTION [Europe].[Intersects] (     @Shape1 NVarchar(MAX),     @Shape2 NVarchar(MAX) ) RETURNS integer AS BEGIN       DECLARE @Geo1 Geography = geography::Parse(@Shape1)     DECLARE @Geo2 Geography = geography::Parse(@Shape2)       RETURN @Geo1.STIntersects(@Geo2)   END   Again, Visual Studio's designer has no problem with this, so you can drag and drop the function into the DataContext: So you can use it in your queries: var query = from c in db.Countries             where db.Intersects(c.Shape.ToString(), belgium.ToString()) == 1             select c.CountryName;   The response time seems to be good enough against such a small table. But I know that it is never a good idea to call functions in the where-clause of a query. It will force a table scan and an evaluation of the function for each row in the table. If you can live with the performance, then I suggest you just stick to this type of functions: they're highly reusable since they don't contain any hard-code table names. Hint 4: Use table valued functions If your geographical tables get bigger, it makes sense to define table valued functions or stored procedures to do the heavy lifting. The following example doesn't mask the usage of STIntersects in the where-clause, and may let SQL Server decide to use a spatial index (or you may even use a hint): CREATE FUNCTION [Europe].[Intersectors] (        @Shape NVarchar(MAX) ) RETURNS TABLE AS RETURN (     SELECT CountryID, CountryName     FROM Europe.Countries     WHERE Shape.STIntersects(geography::Parse(@Shape)) = 1 )   And yes: I do realize that Intersectors probable doesn't appear in a regular dictionary... Here's how a call to it looks like in Linq - we still have IntelliSense: var query = from c in db.Intersectors(belgium.ToString())              select c.CountryName;   I couldn't keep myself from comparing the performance of the two functions. When I checked the actual query plans,my jaw hit the ground , ouch! Completely against my expectation, the cost of the table value function was higher than the cost of the scalar function. And not just a little bit, but 5 times higher: Anyway, you should never blindly base your conclusions on just the query plans, so I started the SQL Profiler to inspect reality. After thorough testing I was relieved . I observed that the tabled valued function easily outruns the scalar version: it consumes between 3 and 8 times less CPU and returns the results 5 to 10 times faster. This is actually an impressive difference for such a small table. Here's the result from a test session (scripts are included in the source code): A negative aspect of this table valued function is its reusability: the function only works against the Europe.Countries table. If you need to query more tables with spatial data, then you need to add complexity to it, or start copy/pasting. Conclusions The current Linq to Sql provider doesn't support the SQL Server spatial data types properly, so you have to be creative if you want or need to use these. It's not a waste of time to invest some effort in implementing the work arounds I suggested, and in optimizing and performance tuning these. Your solution will last for a while, since there seems to be no Microsoft solution on the horizon. The problems are not solved in SQL 2008 R2, Visual Studio 2010, or .NET 4.0. Source code Here's the source code for the test program. It also contains the necessary SQL scripts for creating and populating the entities, as well as for testing and performance tuning: U2UConsult.DockOfTheBay.LinqToSpatialSample.zip (153,76 kb) Here's how its output should look like: Credits I would like to thank my colleague Kris Vandermotten for his input during the research.

Tuning SQL Server Lookups to a Linked Server

In SQL Server, if you join a local table with a table on a linked server (e.g. a remote Oracle instance) you should be prepared for horrible performance. In a lot of scenarios it makes a lot more sense to tell the remote server exactly what you need, store that data in a temporary table, and join locally with it. A couples of weeks ago I used this technique to bring the response time of some queries from SQL Server to a linked Oracle instance down from 500 seconds to less than one second. Let's say we want to enumerate the countries that use the Euro as currency, like this (in AdventureWorks2008):     SELECT c.CurrencyCode, r.Name       FROM Sales.CountryRegionCurrency  c INNER JOIN Person.CountryRegion r         ON c.CountryRegionCode = r.CountryRegionCode      WHERE c.CurrencyCode = 'EUR'   Here's the result:   Let's suppose that the names of the countries are in a separate database (e.g. an Oracle on an AS/400 box), and there's no replication in place. From SQL Server, we can get access to that source by defining a linked server. For demonstration and practical purposes -I don't have a portable AS/400 with Oracle on it- I'll create a linked server to the local SQL instance: execute sp_addlinkedserver '.'   The distributed query will now look like this:     SELECT c.CurrencyCode, r.Name       FROM Sales.CountryRegionCurrency  c INNER JOIN [.].AdventureWorks2008.Person.CountryRegion r         ON c.CountryRegionCode = r.CountryRegionCode      WHERE c.CurrencyCode = 'EUR'   For this particular query in this particular configuration, the response time is actually still nice (it's already five times slower, but you don't really notice that). In real-life queries -and with a real remote Oracle- you'll notice a dramatic decrease in performance. For this demo configuration, you can use Sql Profiler to reveal the query that was sent to the linked server. Instead of performing a selective look-up, SQL Server selected ALL of the rows, and forced even a SORT on it:     SELECT "Tbl1003"."CountryRegionCode" "Col1011","Tbl1003"."Name" "Col1012"       FROM "AdventureWorks2008"."Person"."CountryRegion" "Tbl1003"   ORDER BY "Col1011" ASC   Here's a small part of the result for the query:   You can imagine what will happen if your lookup target is not a small local table but a large complex view. This is bad for the remote machine, the local machine and the network between the two. All of this happens because SQL Server will try to optimize its own workload, and considers the linked server as a black box (which -in the case of an AS/400- it actually ìs ). What we should send to the linked server is a request for a -limited- number of key-value pairs, such as SELECT id, name FROM blablabla WHERE id in ('id1', 'id2', ...). We should send this query via the OPENQUERY function, so we can use the native SQL syntax of the remote DMBS. A classic way to create a short comma-separated list in T-SQL is with a variable and the COALESCE function. If the key is not numeric, then you need to wrap each value in quotes. OPENQUERY uses OLEDB under the hood, and this doesn't like double quotes. So you have to wrap each value in two single quotes that you have to wrap in single quotes during the concatenation. Oops, you're lost ? Just look at the code: DECLARE @Countries VARCHAR(MAX)   -- Create comma-separated list of lookup values ;WITH Countries AS ( SELECT CountryRegionCode   FROM Sales.CountryRegionCurrency  WHERE CurrencyCode = 'EUR' ) SELECT @Countries = COALESCE(@Countries + ',', '') + '''''' + CountryRegionCode + ''''''   FROM Countries After these calls, the @Countries variable holds a comma-separated list of country codes:   Unfortunately OPENQUERY does'nt take parameters, so we need to construct the whole query dynamically, and call it via EXECUTE. To store the result, we need to create a temporary table, because unfortunately table variables disappear from the scope with EXECUTE: CREATE TABLE #Countries (CountryRegionCode nvarchar(3), Name nvarchar(50))   DECLARE @Query VARCHAR(MAX)   SET @Query = 'INSERT #Countries ' +              'SELECT * FROM OPENQUERY ([.], ' +              '''SELECT CountryRegionCode, Name ' +                'FROM AdventureWorks2008.Person.CountryRegion ' +                'WHERE CountryRegionCode IN (' + @Countries + ')'')'   EXECUTE (@Query)   This is the value of the @Query variable:    After these calls, the #Countries table contains the remote data (at least the fraction we're interested in):   So we now can join locally:     SELECT c.CurrencyCode, r.Name       FROM Sales.CountryRegionCurrency  c INNER JOIN #Countries r         ON c.CountryRegionCode = r.CountryRegionCode      WHERE c.CurrencyCode = 'EUR'   And while the complexity of the code dramatically increased, the response time went down equally dramatically ... For the sake of completeness, here's the whole demo script: /***************/ /* Preparation */ /***************/   /* Add linked server to local instance */ execute sp_addlinkedserver '.'   USE AdventureWorks2008 GO   /********/ /* Test */ /********/    -- Local query     SELECT c.CurrencyCode, r.Name       FROM Sales.CountryRegionCurrency  c INNER JOIN Person.CountryRegion r         ON c.CountryRegionCode = r.CountryRegionCode      WHERE c.CurrencyCode = 'EUR'    -- Query through linked server     SELECT c.CurrencyCode, r.Name       FROM Sales.CountryRegionCurrency  c INNER JOIN [.].AdventureWorks2008.Person.CountryRegion r         ON c.CountryRegionCode = r.CountryRegionCode      WHERE c.CurrencyCode = 'EUR'    -- The query sent to the linked server (from Sql Profiler)       SELECT "Tbl1003"."CountryRegionCode" "Col1011","Tbl1003"."Name" "Col1012"       FROM "AdventureWorks2008"."Person"."CountryRegion" "Tbl1003"   ORDER BY "Col1011" ASC   /**************/ /* Workaround */ /**************/   DECLARE @Countries VARCHAR(MAX)   -- Create comma-separated list of lookup values ;WITH Countries AS ( SELECT CountryRegionCode   FROM Sales.CountryRegionCurrency  WHERE CurrencyCode = 'EUR' ) SELECT @Countries = COALESCE(@Countries + ',', '') + '''''' + CountryRegionCode + ''''''   FROM Countries -- OLE DB Drivers don't like double quotes, so we have to hexuplicate ;-))   -- Uncomment next line for testing -- SELECT @Countries   -- Create temporary table to hold results from query to linked server CREATE TABLE #Countries (CountryRegionCode nvarchar(3), Name nvarchar(50))   DECLARE @Query VARCHAR(MAX)   -- Build query to linked server SET @Query = 'INSERT #Countries ' +              'SELECT * FROM OPENQUERY ([.], ' +              '''SELECT CountryRegionCode, Name ' +                'FROM AdventureWorks2008.Person.CountryRegion ' +                'WHERE CountryRegionCode IN (' + @Countries + ')'')'   -- Uncomment next line for testing -- SELECT @Query   -- Execute query to linked server EXECUTE (@Query)   -- Uncomment next line for testing -- SELECT * FROM #Countries   -- Execute query entirely locally     SELECT c.CurrencyCode, r.Name       FROM Sales.CountryRegionCurrency  c INNER JOIN #Countries r         ON c.CountryRegionCode = r.CountryRegionCode      WHERE c.CurrencyCode = 'EUR'   DROP TABLE #Countries   /************/ /* Teardown */ /************/   /* Remove linked server */ execute sp_dropserver '.'

Displaying spatial data in WPF: from SqlGeometry to PathGeometry

This article explains how to visualize spatial data (e.g. from SQL Server 2008) in Windows Presentation Foundation without using 3rd party components or proprietary formats. We'll build a little form that allows us to enter test data in the Well-Known Text format (WKT) - manually or via copy/paste, e.g. from SQL Management Studio. A Draw button will convert our input to WPF graphics, and display it. Here's how the application looks like: It's probably a waste of time to do the validation of the input text and the parsing of its structure ourselves, since the native spatial SQL Server .NET UDTs -SqlGeometry and SqlGeography- are specialized in that. These types are stored in the Microsoft.SqlServer.Types assembly, so we should make a reference to that one in our project. On the user interface side, the best candidate type to visualize spatial data in WPF is without any doubt the Geometry class, which represents a composite 2D-shape. To create a WPF version of spatial data, we read the WKT format and use it to initialize a SqlGeometry instance. Then we call some of the OGC-functions to break the SqlGeometry Object Model down into a PathGeometry Structure. For ease of use, it makes sense to wrap this functionality in extension methods for SqlGeometry. Here's the class: namespace U2UConsult.DockOfTheBay {     using System.Windows;     using System.Windows.Media;     using System.Windows.Shapes;     using Microsoft.SqlServer.Types;       /// <summary>     /// Extension Methods for SqlGeometry.     /// </summary>     public static class SqlGeometryExtensions     {         /// <summary>         /// Translates a SqlGeometry into a Systems.Windows.Media.Geometry.         /// </summary>         public static Geometry AsWpfGeometry(this SqlGeometry sqlGeometry)         {             PathGeometry result = new PathGeometry();               switch (sqlGeometry.STGeometryType().Value.ToLower())             {                 case "point":                       // Return a little 'X'                     // (well: 'little' depends on the coordinate system ...)                     PathFigure pointFigure = new PathFigure();                     pointFigure.StartPoint = new Point(                         sqlGeometry.STX.Value - .1,                         sqlGeometry.STY.Value - .1);                     LineSegment line = new LineSegment(                         new Point(                             sqlGeometry.STX.Value + .1,                             sqlGeometry.STY.Value + .1),                         true);                     pointFigure.Segments.Add(line);                     result.Figures.Add(pointFigure);                       pointFigure = new PathFigure();                     pointFigure.StartPoint = new Point(                         sqlGeometry.STX.Value - .1,                         sqlGeometry.STY.Value + .1);                     line = new LineSegment(                         new Point(                             sqlGeometry.STX.Value + .1,                             sqlGeometry.STY.Value - .1),                         true);                     pointFigure.Segments.Add(line);                     result.Figures.Add(pointFigure);                       return result;                   case "polygon":                       // A Spacial Polygon is a collection of Rings                     // A Ring is a Closed LineString                     // So, return a PathFigure for each Ring                       // Outer Ring                     result.Figures.Add(LineStringToWpfGeometry(sqlGeometry.STExteriorRing()));                       // Inner Rings                     for (int i = 1; i <= sqlGeometry.STNumInteriorRing(); i++)                     {                         result.Figures.Add(LineStringToWpfGeometry(sqlGeometry.STInteriorRingN(i)));                     }                       return result;                   case "linestring":                       // Return a PathFigure                     result.Figures.Add(LineStringToWpfGeometry(sqlGeometry));                     return result;                   case "multipoint":                 case "multilinestring":                 case "multipolygon":                 case "geometrycollection":                       // Return a Group of Geometries                     GeometryGroup geometryGroup = new GeometryGroup();                       for (int i = 1; i <= sqlGeometry.STNumGeometries().Value; i++)                     {                         geometryGroup.Children.Add(sqlGeometry.STGeometryN(i).AsWpfGeometry());                     }                       return geometryGroup;                   default:                       // Unrecognized Type                     // Return an empty Geometry                     return Geometry.Empty;             }         }           /// <summary>         /// Translates a SqlGeometry into a Systems.Windows.Shapes.Path.         /// </summary>         public static Path AsPath(this SqlGeometry sqlGeometry)         {             Path path = new Path();             path.Data = sqlGeometry.AsWpfGeometry();             return path;         }           /// <summary>         /// Translates a LineString or a single Polygon Ring to a PathFigure.         /// </summary>         private static PathFigure LineStringToWpfGeometry(SqlGeometry sqlGeometry)         {             PathFigure result = new PathFigure();               result.StartPoint = new Point(                 sqlGeometry.STPointN(1).STX.Value,                 sqlGeometry.STPointN(1).STY.Value);               for (int i = 1; i <= sqlGeometry.STNumPoints(); i++)             {                 LineSegment lineSegment = new LineSegment();                 lineSegment.Point = new Point(                     sqlGeometry.STPointN(i).STX.Value,                     sqlGeometry.STPointN(i).STY.Value);                 result.Segments.Add(lineSegment);             }               return result;         }     } } To use these extension methods, all we need to do is create a SqlGeometry instance with some data. Then we need to ensure it's valid against the OGC standards, so that the OGC compliant sql methods behave properly. Finally we call the conversion, like this: // Make OGC Compliant if (!sqlGeometry.STIsValid()) {     sqlGeometry = sqlGeometry.MakeValid(); }   // Transformation Samples Path path = sqlGeometry.AsPath(); Geometry geometry = sqlGeometry.AsWpfGeometry(); We end up with a Geometry that is expressed in the original spatial coordinates: latitude/longitude or X/Y against a specific SRID. So we need to translate and scale it, to project it to windows coordinates. Since we only visualize one shape, it suffices to let it stretch automatically in its container. By the way: don't forget to draw upside-down, because the origin of a Control is the upper left corner while the origin of a map is generally at the bottom left: // Flip Y-coordinate // Origin of a map is usually at the bottom left path.LayoutTransform = new ScaleTransform(1, -1);   // Automate Translation & Inflation path.Stretch = Stretch.Uniform;   Samples I tested the code against a high number of possible (mostly Belgian) shapes. Here are some examples: The river Zenne at the point where it leaves Brussels, heading to the north. An example of a LineString. The province of Antwerp. An example of a MultiPolygon. The province of Flemish Brabant. An example of a Polygon with an inner Ring. The arrondissement Halle-Vilvoorde. An example of a Polygon.   Here's the full code for the WPF Window. To bring you up-to-speed immediately, it starts with a reduced shape (24 Points) of Belgium: XAML: <Window    x:Class="U2UConsult.DockOfTheBay.SpatialSample"    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"    Title="SQL Spatial to WPF Sample"    Icon="/U2UConsult.DockOfTheBay;component/dotbay.png">     <Grid>         <Grid.RowDefinitions>             <RowDefinition Height="*" />         </Grid.RowDefinitions>         <Grid.ColumnDefinitions>             <ColumnDefinition Width="*" />             <ColumnDefinition Width="2*" />         </Grid.ColumnDefinitions>         <Button            x:Name="DrawButton"            Click="DrawButton_Click"            Content="Draw"            Grid.Row="0" Grid.Column="1"            Margin="15"            Height="32" Width="64"            HorizontalAlignment="Left"            VerticalAlignment="Bottom"            />         <!-- Reduced shape of Belgium -->         <TextBox            x:Name="GeometryTextBox"            Margin="5"            Grid.Row="0" Grid.Column="0"            TextWrapping="Wrap"            AcceptsReturn="True"            AcceptsTab="True"            Text="POLYGON ((                     5.4695768356323242 49.499450206756592,                     5.8744573593139648 49.576767921447754,                     5.7810144424438477 49.959678173065186,                     6.4083404541015625 50.333068847656250,                     6.1721935272216800 50.550515174865723,                     6.2783794403076172 50.616397857666016,                     5.6911020278930664 50.761138916015625,                     5.8341245651245117 51.168460845947266,                     5.2405147552490234 51.261853218078613,                     5.0372371673583984 51.485389232635500,                     4.4786109924316406 51.480998992919922,                     3.9020967483520508 51.198946952819824,                     3.1825008392333984 51.361250877380371,                     2.5581483840942383 51.093193054199219,                     2.6278944015502930 50.814075946807861,                     3.1747541427612305 50.752677917480469,                     3.2816371917724609 50.526985168457031,                     3.6048288345336914 50.489061832427979,                     3.7020025253295900 50.300303936004639,                     4.2115697860717773 50.269905090332031,                     4.1991643905639648 49.960120201110840,                     4.6723327636718750 49.985515117645264,                     4.8746204376220700 50.151000022888184,                     4.8553209304809570 49.794033050537109,                     5.4695768356323242 49.499450206756592))"            ToolTip="Place your Well-Known Text here ..."            />         <Border            x:Name="DrawingCanvas"            Padding="15"            Grid.Row="0" Grid.Column="1"            />     </Grid> </Window> C#: namespace U2UConsult.DockOfTheBay {     using System;     using System.Windows;     using System.Windows.Controls;     using System.Windows.Media;     using System.Windows.Shapes;     using Microsoft.SqlServer.Types; // Add Reference !!!       /// <summary>     /// Demonstrates displaying SQL Server Spatial data in WPF.     /// </summary>     public partial class SpatialSample : Window     {         public SpatialSample()         {             InitializeComponent();         }           private void DrawButton_Click(object sender, RoutedEventArgs e)         {             try             {                 // Read Well-Known Text                 SqlGeometry sqlGeometry = SqlGeometry.Parse(this.GeometryTextBox.Text);                   // Make OGC Compliant                 if (!sqlGeometry.STIsValid())                 {                     sqlGeometry = sqlGeometry.MakeValid();                 }                   // Transform to Path                 Path path = sqlGeometry.AsPath();                   // Basic Properties                 path.Stroke = Brushes.Black;                 path.StrokeThickness = 1;                   // Polygons only ...                 //path.Effect = new DropShadowEffect() { Direction = 225 };                 //path.Fill = Brushes.DarkGreen;                   // Flip Y-coordinate                 // Origin of a map is usually at the bottom left                 path.LayoutTransform = new ScaleTransform(1, -1);                   // Automate Translation & Inflation                 path.Stretch = Stretch.Uniform;                   this.DrawingCanvas.Child = path;             }             catch (Exception ex)             {                 // Input not valid                 this.DrawingCanvas.Child = new TextBlock()                 {                     TextWrapping = TextWrapping.Wrap,                     MaxHeight = 128,                     VerticalAlignment = VerticalAlignment.Top,                     Foreground = Brushes.Red,                     Text = ex.Message                 };             }         }     } }

SQL Spatial Tools: Map Projections

SQL Server Spatial Tools on CodePlex contains useful extra functions for the SqlGeometry and SqlGeography data types, as well as a new data type for affine transformations (to scale, translate, and rotate) and a handful of Map Projections. This article describes how to use these projections and visualize the result in Windows Presentation Foundation. All projections are instantiated from static method calls against the SqlProjection class, with one to five parameters. SQL Spatial Tools contains sample T-Sql scripts, but here's how it looks like in C# (for the inverse projection, don't forget to first assign a Spatial Reference System Identifier to the geometry): Projection SqlGeography shape3D = new SqlGeography(); shape3D = SqlGeography.Parse("some valid WKT"); // Or read from DB SqlProjection proj = SqlProjection.LambertConformalConic(0, 90, 12, 36); SqlGeometry shape2D = proj.Project(shape3D); Inverse Projection SqlGeometry shape2D = SqlGeometry.Parse("some valid WKT"); shape2D.STSrid = 4326; // WGS 84 SqlProjection proj = SqlProjection.AlbersEqualArea(0, 0, 15, 30); SqlGeography shape3D = proj.Unproject(shape2D);   That's all there is to!   I added this code to an improved version of my SqlGeometry Extension Methods for WPF Visualization. Here's how the resulting application looks like, displaying a reduced shape of Belgium, and the exact location of my office @ U2U Consult:    OK, I admit: projecting Belgium is not that spectacular. It will have the same shape in virtually every projection: it's a small surface on an average latitude. So let's apply some transformations on a more representative victim such as the Tropic of Cancer. This will generally be projected as a straight line, but if you stand on the North Pole -e.g. via a Gnomonic Projection- it looks like a circle:  And with a Transverse Mercator projection it should look like an Ellipse:   Unfortunately the SQL Spatial Tools code only implements the Spherical version of the Transverse Mercator projection, and not (yet ?) the Ellipsoidal version. Otherwise SQL Spatial Tools would have all the ingredients for a latitude/longitude WGS84 to UTM conversion. After all, you only need to project, then scale (to convert to meters), and finally translate (for false easting). This is a horrible calculation, but don't worry: Proj.NET on CodePlex should be able to handle this (this feels like a topic for a later article). Anyway, here's the full solution: U2UConsult.DockOfTheBay.SpatialProjectionsSample.zip (144,07 kb).