Aren’t you tired of samples and demos that deal with Customers, Orders, Products, and Categories? Admit it…you’d love to see something different, you’d welcome a fresh perspective, and I know you’ve been waiting patiently. My name is Samir Bajaj; I’m a developer in the Data Programmability team, and I’m here to tell you that the wait is over.

I’ve wanted to post on this blog for the longest time, and I’ve also known exactly what I’d be writing about, but for one reason or another I’ve not been able to find time to actually do it. I’m sure many of you have been in this situation—you’re so engrossed in working on cool and exciting technology, and with great people, that writing about it often takes a back seat. Well, I finally decided to make time for this endeavor, not only because I’ve been putting it off for too long, but because I think this is truly novel and enabling technology.

There is another reason—but first, a brief digression. A few months ago, I wrote a Drawing Editor application based on the Entity Framework. Having worked at a major CAD company before moving to Microsoft, I realized that a Graphics application built on the Entity Framework and backed by an industrial strength relational database like SQL Server would make a compelling demo of the functionality that the framework has to offer. Later, as I began to get familiar with LINQ to SQL, I figured that one way to showcase these two technologies would be to build an application that can switch between the two frameworks while working against the same back end.

A Rudimentary Spatial Database

By itself, an application like a Drawing Editor would not be terribly exciting. Sure, it would give you yet another example of how good the Entity Framework is at bridging the impedance mismatch, but we’ll go a step further, a step closer to a real-world application. I am not going to build a Drawing Editor directly atop the Entity Framework—I’ll give you a taste of a slightly more capable application that truly makes it a different kind of a sample.

If you haven’t already checked out the spatial capabilities of SQL Server 2008, let me just say that it’s one of my favorite new features. In a nutshell, a spatial database supports spatial ADTs like lines, circles and polygons, offers spatial operators like Area and Intersect, and provides spatial indexing for efficient access to such data.

clip_image002

The sample application that I’ve built comes with a rudimentary spatial layer and a basic, in-memory spatial index. I’ll explain how the application leverages that capability shortly.

LINQ to SQL and the Entity Framework

You can find a fair amount of information on the capabilities of LINQ to SQL versus those of the Entity Framework, and I’m not going to delve into that here. Let me just say that each technology has specific strengths that make it more appealing in some scenarios over the other. For example, LINQ to SQL would be your best bet in a RAD-like environment with SQL Server as the storage back-end. In Enterprise scenarios that demand more flexibility, the Entity Framework would be the technology of choice.

Having said that, I wrote the sample application uses the Strategy pattern to allow the user to switch between LINQ to SQL and the Entity Framework for the application data model. Our stack therefore looks like the following (see how the layers map to the conceptual diagram in the previous section).

clip_image004

Note that because I decided to showcase both technologies, my application uses a TPH model, which is supported by both LINQ to SQL as well as the Entity Framework.

Overview of the Application

The objective of this exercise is to demonstrate one of the wide varieties of ways in which LINQ to SQL as well as the Entity Framework can be applied. In that spirit, SketchPad is a nowhere near a full-featured drawing editor—it merely illustrates how the data platform technologies can be used to create and enable real-world applications.

SketchPad is a WinForms-based application that I’d roughly describe as a ‘Notepad for Shapes’. It allows you to retrieve (Read), draw (Create) lines and circles on the canvas and save (Update) them to the database. Select and Delete operations are also available.

Support for rectangles also exists; however, I use them to highlight the bounding boxes and the spatial index structure. One thing to note in particular is how the sample leverages partial classes, interfaces, and abstract base classes to add “business logic” (in this case, the spatial aspects) into the entities.

The following UML diagram captures the primary classes of the application at a high level.

clip_image006

By default, launching the application will create a LINQ to SQL DataContext to communicate with the store. However, the command-line switch “/ef” will make the application use the Entity Framework instead. The splash screen at launch, as well as the title of the editor window will indicate the runtime in use.

clip_image008

clip_image010

Search

Search is ubiquitous, and it has become the means of choice for access to any kind of information. One of the “value adds” of this sample is that it combines the scalability of a relational database with a second-level access structure on the geometric data. Just like searching for text in Notepad, you can search for shapes in SketchPad based on specific attributes.

clip_image012

The app leverages the index by first querying the database for the location (spatial coordinates) of the objects with the user-specified attributes. The location information is then used to look up the index and identify the shapes on the display. The painting logic subsequently highlights the relevant shapes.

clip_image014

While this may appear to be a nice-to-have, you need to imagine the magnitude of the data sets used by GIS and CAD applications, where efficient search is paramount. If you’re an electrical engineer looking for some component in the layout of a multi-million-transistor chip, you’d like to have the layout editor be able to search for the component on the basis of one or more attributes.

Be sure to check out the other goodies under the Options menu in the application—including index visualization, which animates the process of building the spatial index that the application uses.

Prerequisites: Building and Running the Sample

This sample is available with complete source code at http://code.msdn.com/SketchPad — I would encourage you to make changes and play with the application. The sample only scratches the surface of what the Entity Framework and LINQ to SQL have to offer. Go ahead and feel free to experiment with the sample in order to evolve it and learn more about the exciting new technologies from Microsoft.

Here’s what you’ll need:

The Visual Studio solution for the sample is comprised of four projects:

  1. Spatial: This assembly contains all the spatial logic.
  2. LinqToSql: This is the LINQ to SQL data layer implementation.
  3. EF: This is the Entity Framework data layer implementation.
  4. SketchPad: This is the client (“main”) application that displays the UI and invokes the appropriate entry points in the data layer.

Although I used the tools to generate the EDM metadata artifacts from the database for the EF model, I instead opted for manually attributing the classes for the LINQ to SQL model. The sample comes with a database that contains logos of some cars that I sketched using the application. The connection string for the database is stored in the application configuration file—you’ll have to edit it in accordance with your server installation.

When you launch the app, you’ll be presented with a blank canvas where you can draw lines and circles by choosing the appropriate menu option (from the Draw menu). To display the contents of the pre-seeded database, select the FileàDatabaseà Open menu item

clip_image016

When the database connection is opened, the connection string is displayed on the status bar:

clip_image018

Stored Procedure Invocation

The database with the sample shapes also has a stored procedure that computes the area of a circle, given its ID (primary key) in the table. You invoke that by selecting a circle on the display, and then using the right-click context menu to call the sproc.

clip_image020

The result will be displayed in the status bar of the main form.

Feedback

Needless to say, SketchPad is really the “Hello, World” version of a full-featured, industrial strength drawing editor. However, at its core, it contains elements that you will find in most, if not all, real-world graphics editing software—except with the distinction of the ability to harness the power of a scalable, enterprise-grade relational database like Microsoft SQL Server.

I’d be interested in finding out what kind of samples and or documentation you’d like to see, both for the Entity Framework as well as LINQ to SQL. If you have ideas, drop me a line at entities@live.com.