The information in this post is out of date.

Visit msdn.com/data/ef for the latest information on current and past releases of EF.

For Spatial Support in Code First see http://msdn.com/data/hh859721

For Spatial Support in the EF Designer see http://msdn.com/data/jj250903

 

Spatial data is one of the new features in Entity Framework June 2011 CTP. Spatial data allows users to represent locations on a map as well as points, geometric shapes, and other data which relies on a coordinate system. There are two main types of spatial data: Geography and Geometry. Geography data takes the ellipsoid nature of the earth into account while Geometry bases all measurements and calculations on Euclidean data. Please see this blog post if you would like to understand more about the Spatial design in EF.

In this walkthrough we will see how to use EF to interact with spatial data in a SQL Server database. We will create a console application in Visual Studio using the Code First and Database First Approach. Our application will use a LINQ query to find all landmarks within a certain distance of a person’s location.

Pre-requisites

  • Visual Studio 2010 Express and SQL Server 2008 R2 Express or Higher. Click here to download the Express edition of VS and SQL Server Express
  • Microsoft Entity Framework June 2011 CTP. Click here to download.
  • Microsoft Entity Framework Tools June 2011 CTP. Click here to download.
  • Microsoft SQL Server 2008 Feature Pack. This pack contains the SQL Spatial Types assembly used by Entity Framework. Click here to download.

  • You can also get the types by installing SQL Server Management Studio Express or higher. You can download Express here.
  • Your Visual Studio 2010 setup might include these types already, in which case you won’t need to install additional components.
  • SeattleLandmarks database. You can find it at the bottom of this post.

Setting up the Project

  • Launch Visual Studio and create a new C# Console application named SeattleLandmarks.
  • Make sure you are targeting the Entity Framework June 2011 CTP. Please see the EF June 2011 CTP Intro Post to learn how to change the target framework.

Code First Approach

We will first see how to create our application using Code First. You could alternatively use a Database First approach, which you will see below.

Creating Objects and Context

First we must create the classes we will use to represent People and Landmarks, as well as our context. For the sake of this walkthrough, we will create these in Program.cs. Normally you would create these in separate files. The classes will look as follows:

1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

11.

12.

13.

14.

15.

16.

17.

18.

19.

20.

public class Person

{

    public int PersonID { get; set; }

    public string Name { get; set; }

    public DbGeography Location { get; set; }

}

 

public class Landmark

{

    public int LandmarkID { get; set; }

    public string LandmarkName { get; set; }

    public DbGeography Location { get; set; }

    public string Address { get; set; }

}

 

public class SeattleLandmarksEntities : DbContext

{

    public DbSet<Person> People { get; set; }

    public DbSet<Landmark> Landmarks { get; set; }       

}


Database Initializer

We will use a database initializer to add seed data to our Landmarks and People tables. To do so, create a new class file called SeattleLandmarksSeed.cs. The contents of the file are the following:

1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

11.

12.

13.

14.

15.

16.

17.

18.

19.

20.

21.

22.

23.

24.

25.

26.

27.

28.

29.

30.

31.

32.

33.

34.

35.

36.

37.

38.

39.

40.

41.

42.

43.

44.

45.

46.

47.

48.

49.

50.

51.

52.

53.

54.

55.

56.

57.

58.

59.

60.

61.

62.

63.

64.

65.

66.

67.

68.

69.

70.

71.

72.

73.

74.

75.

76.

77.

78.

79.

80.

81.

82.

83.

84.

85.

86.

87.

88.

89.

90.

91.

92.

93.

94.

95.

96.

97.

98.

99.

100.

101.

102.

103.

104.

105.

106.

107.

108.

109.

110.

111.

112.

113.

114.

115.

116.

117.

118.

119.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.Entity;

using System.Data.Spatial;

 

namespace SeattleLandmarks

{

    class SeattleLandmarksSeed : DropCreateDatabaseAlways<SeattleLandmarksEntities>

    {

        protected override void Seed(SeattleLandmarksEntities context)

        {

 

            var person1 = new Person { PersonID = 1,

                                       Location = DbGeography.Parse("POINT(-122.336106 47.605049)"),

                                       Name = "John Doe" };

            var landmark1 = new Landmark { LandmarkID = 1,

                                    Address = "1005 E. Roy Street",

                                    Location = DbGeography.Parse("POINT(-122.31946 47.625112)"),

                                    LandmarkName = "Anhalt Apartment Building"

            };

            var landmark2 = new Landmark { LandmarkID = 2,

                                    Address = "",

                                    Location = DbGeography.Parse("POINT(-122.296623 47.640405)"),

                                    LandmarkName = "Arboretum Aqueduct"

            };

            var landmark3 = new Landmark { LandmarkID = 3,

                                    Address = "815 2nd Avenue",

                                    Location = DbGeography.Parse("POINT(-122.334571 47.604009)"),

                                    LandmarkName = "Bank of California Building"

            };

            var landmark4 = new Landmark { LandmarkID = 4,

                                    Address = "409 Pike Street",

                                    Location = DbGeography.Parse("POINT(-122.336124 47.610267)"),

                                    LandmarkName = "Ben Bridge Jewelers Street Clock"

            };

            var landmark5 = new Landmark { LandmarkID = 5,

                                    Address = "300 Pine Street",

                                    Location = DbGeography.Parse("POINT(-122.338711 47.610753)"),

                                    LandmarkName = "Bon Marché"

            };

            var landmark6 = new Landmark { LandmarkID = 6,

                                    Address = "N.E. corner of 5th Avenue & Pike Street",

                                    Location = DbGeography.Parse("POINT(-122.335576 47.610676)"),

                                    LandmarkName = "Coliseum Theater Building"

            };

            var landmark7 = new Landmark { LandmarkID = 7,

                                    Address = "",

                                    Location = DbGeography.Parse("POINT(-122.349755 47.647494)"),

                                    LandmarkName = "Fremont Bridge"

            };

            var landmark8 = new Landmark { LandmarkID = 8,

                                    Address = "",

                                    Location = DbGeography.Parse("POINT(-122.335197 47.646711)"),

                                    LandmarkName = "Gas Works Park"

            };

            var landmark9 = new Landmark { LandmarkID = 9,

                                    Address = "",

                                    Location = DbGeography.Parse("POINT(-122.304482 47.647295)"),

                                    LandmarkName = "Montlake Bridge"

            };

            var landmark10 = new Landmark { LandmarkID = 10,

                                     Address = "1932 2nd Avenue",

                                     Location = DbGeography.Parse("POINT(-122.341529 47.611693)"),

                                     LandmarkName = "Moore Theatre"

            };

            var landmark11 = new Landmark { LandmarkID = 11,

                                     Address = "200 2nd Avenue N./Seattle Center",

                                     Location = DbGeography.Parse("POINT(-122.352842 47.6186)"),

                                     LandmarkName = "Pacific Science Center"

            };

            var landmark12 = new Landmark { LandmarkID = 12,

                                     Address = "5900 Lake Washington Boulevard S.",

                                     Location = DbGeography.Parse("POINT(-122.255949 47.549068)"),

                                     LandmarkName = "Seward Park"

            };

            var landmark13 = new Landmark { LandmarkID = 13,

                                     Address = "219 4th Avenue N.",

                                     Location = DbGeography.Parse("POINT(-122.349074 47.619589)"),

                                     LandmarkName = "Space Needle"

            };

            var landmark14 = new Landmark { LandmarkID = 14,

                                     Address = "414 Olive Way",

                                     Location = DbGeography.Parse("POINT(-122.3381 47.612467)"),

                                     LandmarkName = "Times Square Building"

            };

            var landmark15 = new Landmark { LandmarkID = 15,

                                     Address = "5009 Roosevelt Way N.E.",

                                     Location = DbGeography.Parse("POINT(-122.317575 47.665229)"),

                                     LandmarkName = "University Library"

            };

            var landmark16 = new Landmark { LandmarkID = 16,

                                     Address = "1400 E. Galer Street",

                                     Location = DbGeography.Parse("POINT(-122.31249 47.632342)"),

                                     LandmarkName = "Volunteer Park"

            };

 

            context.People.Add(person1);

            context.Landmarks.Add(landmark1);

            context.Landmarks.Add(landmark2);

            context.Landmarks.Add(landmark3);

            context.Landmarks.Add(landmark4);

            context.Landmarks.Add(landmark5);

            context.Landmarks.Add(landmark6);

            context.Landmarks.Add(landmark7);

            context.Landmarks.Add(landmark8);

            context.Landmarks.Add(landmark9);

            context.Landmarks.Add(landmark10);

            context.Landmarks.Add(landmark11);

            context.Landmarks.Add(landmark12);

            context.Landmarks.Add(landmark13);

            context.Landmarks.Add(landmark14);

            context.Landmarks.Add(landmark15);

            context.Landmarks.Add(landmark16);

            context.SaveChanges();

        }

    }

}



Adding Entities containing Spatial Data
In the code above, we added a number of landmarks containing spatial data. Note that to initialize a spatial property we use the DbGeography.Parse method which takes WellKnownText. In this case, we passed a point with a longitude and latitude.

Invoking the Initializer
Now, to make sure the Initializer gets invoked, we must add the following lines to App.config inside <configuration>:

1.

2.

3.

4.

5.

<appSettings>

  <add key="DatabaseInitializerForType SeattleLandmarks.SeattleLandmarksEntities,

            SeattleLandmarks"

       value="SeattleLandmarks.SeattleLandmarksSeed, SeattleLandmarks" />

</appSettings>

Writing the App (Code First)

  • Open Program.cs and enter the following code:

1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

11.

12.

13.

14.

15.

16.

17.

18.

19.

20.

21.

22.

23.

24.

25.

26.

27.

28.

29.

30.

31.

32.

33.

34.

35.

36.

37.

38.

39.

40.

41.

42.

43.

44.

45.

46.

47.

48.

49.

50.

51.

52.

53.

54.

55.

56.

57.

58.

59.

60.

61.

62.

63.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.Spatial;

using System.Data.Entity;

 

namespace ConsoleApplication4

{

    class Program

    {

        static void Main(string[] args)

        {

            using (var context = new SeattleLandmarksEntities())

            {

 

                var person = context.People.Find(1);

                var distanceInMiles = 0.5;

                var distanceInMeters = distanceInMiles * 1609.344;

 

                var landmarks =

                    from l in context.Landmarks

                    where l.Location.Distance(person.Location) < distanceInMeters

                    select new

                    {

                        Name = l.LandmarkName,

                        Address = l.Address

                    };

 

                Console.WriteLine("\nLandmarks within " + distanceInMiles + " mile(s) of " +

                                 person.Name + "'s location:");

                foreach (var loc in landmarks)

                {

                    Console.WriteLine("\t" + loc.Name + " (" + loc.Address + ")");

                }

 

                Console.WriteLine("Done! Press ENTER to exit.");

                Console.ReadLine();

            }

        }

    }

 

    public class Person

    {

        public int PersonID { get; set; }

        public string Name { get; set; }

        public DbGeography Location { get; set; }

    }

 

    public class Landmark

    {

        public int LandmarkID { get; set; }

        public string LandmarkName { get; set; }

        public DbGeography Location { get; set; }

        public string Address { get; set; }

    }

 

    public class SeattleLandmarksEntities : DbContext

    {

        public DbSet<Person> People { get; set; }

        public DbSet<Landmark> Landmarks { get; set; }       

    }

}



The first thing we do is create a context. Then we create distance and person variables. In line 14, we use the Find method in DbSet to retrieve the first person. We use both of these in the LINQ query in line 19. In this query we select all the locations within a half mile of the given person’s location. To compute the distance, we use the Distance method in System.Data.Spatial.DbGeography. You can explore the full list of methods by browsing through the System.Data.Spatial namespace in the Class View window.

The output is the following:

Landmarks within 0.5 mile(s) of John Doe's location:

        Bank of California Building (815 2nd Avenue)

        Ben Bridge Jewelers Street Clock (409 Pike Street)

        Bon Marché (300 Pine Street)

        Coliseum Theater Building (N.E. corner of 5th Avenue & Pike Street)

Done! Press ENTER to exit.


Database First Approach

Here are the steps to follow if you would like to use Database First:

Attaching the Seattle Landmarks Database

  • Launch SQL Server Management Studio and connect to your instance of SQL Express.
  • Right click on the Databases node and select Attach…
  • Under Databases to attach, click Add… and then browse to the location of SeattleLandmarks.mdf. Click Add and then click OK.

Creating a Model

  • Add a new ADO.NET Entity Data Model to your project by right clicking on the project and navigating to Add > New Item.
  • In the Add New Item window, click Data, and then click ADO.NET Entity Data Model. Name your model ‘SeattleLandmarksModel’ and click Add.
  • The Entity Data Model Wizard will open. Click on Generate from database and then click Next>.
  • Select New Connection… on the Wizard. Under Server name, enter the name of your SQL Server instance (if you installed SQL Express, the server name should be .\SqlExpress)
  • Under Connect to a database, select SeattleLandmarks from the first dropdown menu.
  • The Connection Properties window should look as follows. Click OK to proceed

  • On the Entity Data Model Wizard, click Next, then click on the Tables checkbox to include People and Landmarks on your model. Click Finish.

The resulting Model is very simple. It includes two entities named Landmark and Person. Each of these contains a Spatial Property called Location:

Writing the App (Database First)

Our program above would work against Database First with a few minor changes: On line 15. Rather than using the Find() method, we will use the Take() method in ObjectContext to retrieve the first person; Lastly, the entities and context we need are automatically generated by the VS tools when using Database First. Therefore, we don’t need the People, Landmarks, and SeattleLandmarksEntities classes defined from line 43 onwards.

How It Works

EF treats Geometry and Geography as primitive types. This allows us to use them in the same way we would use integers and strings as properties in an entity. The SSDL for our entities is the following:

1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

11.

12.

13.

14.

15.

16.

17.

18.

19.

20.

21.

22.

23.

24.

25.

26.

27.

28.

29.

30.

<Schema Namespace="SeattleLandmarksModel.Store" Alias="Self"

        Provider="System.Data.SqlClient" ProviderManifestToken="2008"

        xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator"

        xmlns="http://schemas.microsoft.com/ado/2009/11/edm/ssdl">

  <EntityContainer Name="SeattleLandmarksModelStoreContainer">

    <EntitySet Name="Landmarks" EntityType="SeattleLandmarksModel.Store.Landmarks"

              store:Type="Tables" Schema="dbo" />

    <EntitySet Name="People" EntityType="SeattleLandmarksModel.Store.People"

              store:Type="Tables" Schema="dbo" />

  </EntityContainer>

  <EntityType Name="Landmarks">

    <Key>

      <PropertyRef Name="LandmarkID" />

    </Key>

    <Property Name="LandmarkID" Type="int" Nullable="false"

              StoreGeneratedPattern="Identity" />

    <Property Name="LandmarkName" Type="nvarchar" MaxLength="50" />

    <Property Name="Location" Type="geography" />

    <Property Name="Address" Type="nvarchar" MaxLength="100" />

  </EntityType>

  <EntityType Name="People">

    <Key>

      <PropertyRef Name="PersonID" />

    </Key>

    <Property Name="PersonID" Type="int" Nullable="false"

              StoreGeneratedPattern="Identity" />

    <Property Name="Name" Type="nvarchar" MaxLength="50" />

    <Property Name="Location" Type="geography" />

  </EntityType>

</Schema>

Notice that the type for Location in both entities is geography. You can see this in the VS designer by right clicking on Location and selecting Properties.

 

Conclusion

In this walkthrough we have seen how to create an application which leverages spatial data using the Code First and Database First approaches. Using spatial functions, we calculated the distance between two locations. You can find out more about spatial types in EF here. Lastly, we appreciate your feedback, so please feel free to leave your questions and comments below.

 

Pedro Ardila
Program Manager – Entity Framework