Create a Flexible Query Application

Article
08/17/2015

Use Service References in Visual Studio to programmatically consume Marketplace datasets that the data publisher has specified as flexible query datasets.

Important

The code in a service reference should not be modified as modification could result in improper functioning and side effects. Additionally, modifications will be overwritten whenever the service reference is refreshed.

Prerequisites

Before you proceed make sure you have:

A valid Windows Live ID account. If you do not have a Live ID go to the Windows Live home page and sign up.
A valid Marketplace account. If you do not have a Marketplace go to the topic Create Your Marketplace Account and follow the instructions there.
A subscription to the Marketplace flexible query dataset you want to use in your application. If you have not subscribed to a flexible query dataset go to Subscribe to a Data Offer and follow the instructions there.

Sections in this Topic

Section	Description
Step 1: Create Your Project in Visual Studio	Steps to take in Visual Studio to start a new console project for the Marketplace.
Step 2: Add the Service Reference for your Data Service	Steps you need to take to add a Service Reference to consume Marketplace data.
Step 3: Consume Data with Managed Code	Code you write to consume a Marketplace flexible query dataset.
Completed Code	The complete console application in C# and VB.
Metadata	Example XML metadata for this flexible query dataset.

Step 1: Create Your Project in Visual Studio

Run Visual Studio as Administrator

Find Visual Studio in your Start Menu.
Right click Visual Studio.
In the dropdown menu click Run as administrator.
When asked if you want to allow this program (devenv.exe) to make changes to your computer click Yes.

Create a New Project

On the Visual Studio Start Page select New Project….
Select Visual C# and Console Application (Visual Basic and Console Application if you prefer Visual Basic) in the New Project dialog.
From the left drop-down list at the top of the New Project dialog box, select .NET Framework 4.

Visual Studio 2010 defaults to .NET Framework 4. If you use an earlier version of Visual Studio and do not have .NET Framework 4 installed go to the .NET Framework 4 download page to download and install it.
Give your project a meaningful name. For example, for this project you could name it USCrime2006and2007.
Click OK.
Add the following code to the top of Program.cs (Program.vb) which is needed to authenticate your access to the dataset.
```
using System.Net; // needed for authentication
```
```
Imports System.Net ' needed for authentication
```

Step 2: Add the Service Reference for your Data Service

After starting Visual Studio as an administrator and creating a new console application (Step 1) add the service reference for the data service you want to consume.

Get the Service Root URL

Go to the Marketplace home page.
Click the My Data tab.
Find the 2006 - 2008 Crime in the United States data service.
Click Use to the right of the dataset name. This takes you to the Details Page. for this dataset.
Scroll down the page to the tabs below Learn How to Use this Data in Visual Studio
Click the Details tab.
Find the Service root URL.

Figure 1 – Service root URL
Copy the Service root URL to your clipboard.

Add the Service Reference to Your Project

Return to your Visual Studio project.
In Solution Explorer right click References.
Select Add Service Reference… from the dropdown menu.
Paste the Service root URL from your clipboard in the Address dropdown.
Click Go.
Wait a few moments for the Services text box to populate with the service available to your project.
Enter a meaningful namespace name in the Namespace textbox.
For Example: DataGovCrimes.
Click OK.
The name of your service reference will appear under Service References in Solution Explorer.

Add the Service Reference to Your Code

Select Program.cs (Program.vb) in the Visual Studio Code Pane.
Add the following statement which makes your service reference accessible to your application.

Note

Use the program namespace plus the service reference namespace name you created in #6 above.
```
using USCrime2006and2007.DataGovCrimes; // using ApplicationName.ServiceReferenceName
```
```
Imports USCrime2006and2007.DataGovCrimes ' Imports ApplicationName.ServiceReferenceName
```

Step 3: Consume Data with Managed Code

Create a class to do the work

Create a public class, CrimeData, within your project namespace.
Create two private variables within your class.
- A Uri to the service.
- A service container that stores your service credentials in the service context.
  
  Find the Entity type
  1. Go to the “Details Page.”
  2. Click Get Started with this Dataset.
  3. Click the Overview tab.
    The entity set name and entity collection name are displayed.
```
class CrimeData
{
    private Uri serviceURI;
    private datagovCrimesContainer context;
}  // end class CrimeData
```
```
Class CrimeData
    Private serviceUri As Uri
    Private context As datagovCrimesContainer
End Class
```

Create a constructor for the CrimeData class.
The constructor initializes both private variables and initializes user credentials.
The SERVICE_ROOT_URL is the https link you copied to your clipboard above. (See Get the Service Root URL.)
The context is the service container which is used for user credentials.
The IgnoreMissingProperties property is set to true to make the client robust to properties being added to the type on the server. (See MSDN documentation for additional information.)
The USER_ID is your Live ID.
The SECURE_ACCOUNT_KEY is the Marketplace account key you’re using for this application. (See Manage Your Marketplace Account.)

class CrimeData
{
    private Uri serviceURI;
    private datagovCrimesContainer context;

    // constructor for the CrimeData class
    public CrimeData()  
    {
        serviceURI = new Uri(SERVICE_ROOT_URL);
        context = new datagovCrimesContainer(serviceURI);
        context.IgnoreMissingProperties = true;
        context.Credentials = new NetworkCredential(USER_ID,
                                                    SECURE_ACCOUNT_ID); // Your Marketplace account key
    }  // end constructor

}  // end class CrimeData

Class CrimeData
    Private serviceUri As Uri
    Private context As datagovCrimesContainer

    ' – constructor for the CrimeData class
    Public Sub New()
        serviceUri = New Uri(SERVICE_ROOT_URL)
        context = New datagovCrimesContainer(serviceUri)
        context.IgnoreMissingProperties = True
        context.Credentials = New NetworkCredential(USER_ID,
                                                    SECURE_ACCOUNT_KEY) ' Your Marketplace account key
    End Sub
End Class

Create a public method within your class that returns a generic list. For our program the generic list type is CityCrime.
This method queries the service and if successful returns the result set as a list. If the query fails for any reason the method returns a null (Nothing).

class CrimeData
{
    private Uri serviceURI;
    private datagovCrimesContainer context;

    // constructor for the CrimeData class
    public CrimeData()
    {
        serviceURI = new Uri(SERVICE_ROOT_URL);
        context = new datagovCrimesContainer(serviceURI);
        context.IgnoreMissingProperties = true;
        context.Credentials = new NetworkCredential(USER_ID,
                                                    SECURE_ACCOUNT_ID);
    }  // end constructor

    // -- public method that returns a list of crime data
    public IList<CityCrime> GetCrimeData()
    {
        IEnumerable<CityCrime> query = context.CityCrime.Where(crime => crime.City == "Newport").OrderBy(crime => crime.Year);

        try
        {
            return query.ToList();
        }
        catch (Exception ex)
        {
            Console.WriteLine("Error: {0}", ex.Message);
            return null;
        }  // end try

    }  // end function GetCrimeData

}  // end class CrimeData

Class CrimeData
    Private serviceUri As Uri
    Private context As datagovCrimesContainer

    ' – constructor for the CrimeData class
    Public Sub New()
        serviceUri = New Uri(SERVICE_ROOT_URL)
        context = New datagovCrimesContainer(serviceUri)
        context.IgnoreMissingProperties = True
        context.Credentials = New NetworkCredential(USER_ID,
                                                    SECURE_ACCOUNT_KEY) ' Your Marketplace account key
    End Sub

    ' – public function that returns a list of crime data
    Public Function GetCrimeData() As IList(Of CityCrime)
        Dim query As IEnumerable(Of CityCrime)
        ' – query the service
        query = From crimes In context.CityCrime
                     Select crimes

        Try
            Return query.ToList()
        Catch ex As Exception
            Console.WriteLine("Error: {0}", ex.Message)
            Return Nothing
        End Try
    End Function
End Class

The query can be any valid Marketplace LINQ query and may optionally include the where (Where) filter and/or orderby (Order By).
For more information on LINQ queries see LINQ Query Expressions (C# Programming Guide) (LINQ in Visual Basic).
For information on LINQ query limitations within the Marketplace see Supported OData Query Options.

The following LINQ queries are valid within the Marketplace.

query = from crimes in context.CityCrime
            where crimes.City == "Newport" 
            orderby crimes.Year Ascending
            select crimes;
//  this query could also be written as
//  IEnumerable<CityCrime> query = context.CityCrime.Where(crime => crime.City == "Newport").OrderBy(crime => crime.Year);

query = From Crimes In Context.CityCrime
    Where crimes.City = "Newport"
    Order By (crimes.Year) Ascending
    Select crimes
' – this query could also be written as
' – IEnumerable(Of CityCrime) query = context.CityCrime.Where(crime => crime.City == "Newport").OrderBy(crime => Crime.Year)

Write the code in Main() that utilizes your CrimeData class and GetCrimeData() method to consume the data and display it.

using System.Net;
using USCrime2006and2007.DataGovCrimes; // using ApplicationName.ServiceReferenceName

namespace USCrime2006and2007
{
   class Program
   {
      public static void Main(string[] args)
      {
          IList<CityCrime> crimeList;  // CityCrime is the entity type returned by the service
          CrimeData crimeData = new CrimeData();

          crimeList = crimeData.GetCrimeData();

          if (crimeList != null)
          {
              Console.WriteLine("{0,4} {1,-12} {2,-15} {3,10} {4,13}","Year",
                                                                      "City",
                                                                      "State",
                                                                      "Population",
                                                                      "Violent Crime");

              foreach (CityCrime c in crimeList)
              {
                  Console.WriteLine("{0,4} {1,-12} {2,-15} {3,10} {4,13}", c.Year, 
                                                                           c.City, 
                                                                           c.State, 
                                                                           c.Population,
                                                                           c.ViolentCrime);
              } // end foreach

          }  // end if

          Console.Write("Tap any key to exit. ");
          Console.ReadKey();

      }  // end Main

    }  // end class Program

}  // end namespace

Imports System.Net
Imports USCrime2006and2007.DataGovCrimes ' Imports ApplicationName.ServiceReferenceName

Module Module1

    Sub Main()
        Dim crimeList As IList(Of CityCrime) ' CityCrime is the entity type returned by the service
        Dim crimeData As CrimeData = New CrimeData()

        crimeList = CrimeData.GetCrimeData()

        If Not crimeList Is Nothing Then
            Console.WriteLine("{0,4} {1,-12} {2,-15} {3,10} {4,13}", "Year",
                                                                     "City",
                                                                     "State",
                                                                     "Population",
                                                                     "Violent Crime")

            Dim crime As CityCrime
            For Each crime In crimeList
                Console.WriteLine("{0,4} {1,-12} {2,-15} {3,10} {4,13}", crime.Year,
                                                                         crime.City,
                                                                         crime.State,
                                                                         crime.Population,
                                                                         crime.ViolentCrime)
            Next
        End If

        Console.Write("Tap any key to exit program. ")
        Console.ReadKey()

    End Sub

End Module

Completed Code

using System.Net;
using USCrime2006and2007.DataGovCrimes; // using ApplicationName.ServiceReferenceName

namespace USCrime2006and2007
{
   class Program
   {
      public static void Main(string[] args)
      {
          IList<CityCrime> crimeList;  // CityCrime is the entity type returned by the service
          CrimeData crimeData = new CrimeData();

          crimeList = crimeData.GetCrimeData();

          if (crimeList != null)
          {
              Console.WriteLine("{0,4} {1,-12} {2,-15} {3,10} {4,13}","Year",
                                                                      "City",
                                                                      "State",
                                                                      "Population",
                                                                      "Violent Crime");

              foreach (CityCrime c in crimeList)
              {
                  Console.WriteLine("{0,4} {1,-12} {2,-15} {3,10} {4,13}", c.Year, 
                                                                           c.City, 
                                                                           c.State, 
                                                                           c.Population,
                                                                           c.ViolentCrime);
              } // end foreach

          }  // end if

          Console.Write("Tap any key to exit. ");
          Console.ReadKey();

      }  // end Main

    }  // end class Program

    class CrimeData
    {
        private Uri serviceUri;
        private datagovCrimesContainer context;

        // constructor for the CrimeData class
        public CrimeData()
        {
            serviceUri = new Uri(SERVICE_ROOT_URL);
            context = new datagovCrimesContainer(serviceUri);
            context.IgnoreMissingProperties = true;
            context.Credentials = new NetworkCredential USER_ID,
                                                        SECURE_ACCOUNT_KEY); // Your Marketplace account key
        }  // end constructor
 
        // – public function that returns a list of crime data
        public IList<CityCrime> GetCrimeData()
        {
            IEnumerable<CityCrime> query = context.CityCrime.Where(crime => crime.City == "Newport").OrderBy(crime => crime.Year);
 
            try
            {
                return query.ToList();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: {0}", ex.Message);
                return null;
            }  // end try

        }  // end function

    }  // end class CrimeData

}  // end namespace

Imports System.Net
Imports USCrime2006and2007.DataGovCrimes ' Imports ApplicationName.ServiceReferenceName

Module Module1

    Sub Main()
        Dim crimeList As IList(Of CityCrime) ' CityCrime is the entity type returned by the service
        Dim crimeData As CrimeData = New CrimeData()

        crimeList = CrimeData.GetCrimeData()

        If Not crimeList Is Nothing Then
            Console.WriteLine("{0,4} {1,-12} {2,-15} {3,10} {4,13}", "Year",
                                                                     "City",
                                                                     "State",
                                                                     "Population",
                                                                     "Violent Crime")

            Dim crime As CityCrime
            For Each crime In crimeList
                Console.WriteLine("{0,4} {1,-12} {2,-15} {3,10} {4,13}", crime.Year,
                                                                         crime.City,
                                                                         crime.State,
                                                                         crime.Population,
                                                                         crime.ViolentCrime)
            Next
        End If

        Console.Write("Tap any key to exit program. ")
        Console.ReadKey()

    End Sub

End Module

Class CrimeData
    Private serviceUri As Uri
    Private context As datagovCrimesContainer

    ' – constructor for the CrimeData class
    Public Sub New()
        serviceUri = New Uri(SERVICE_ROOT_URL)
        context = New datagovCrimesContainer(serviceUri)
        context.IgnoreMissingProperties = True
        context.Credentials = New NetworkCredential(USER_ID,
                                                    SECURE_ACCOUNT_KEY) ' Your Marketplace account key
    End Sub

    ' – public function that returns a list of crime data
    Public Function GetCrimeData() As IList(Of CityCrime)
        Dim query As IEnumerable(Of CityCrime)
        ' – query the service
        query = From crimes In context.CityCrime
                     Select crimes

        Try
            Return query.ToList()
        Catch ex As Exception
            Console.WriteLine("Error: {0}", ex.Message)
            Return Nothing
        End Try
    End Function
End Class

Metadata

The dataset’s metadata informs intellisense as you write your code. If your IDE does not support intellisense or intellisense fails to work you can get the metadata from the service.

Get the dataset’s service root URL.
See the section Get the Service Root URL above for instructions on how to get the service root URL.
Add /$metadata to the end of your service root URL.
For example, if your service root URL is https://api.datamarket.azure.com/Data.ashx/fabrikam.com/inventory the metadata URL is https://api. datamarket.azure.com/Data.ashx/fabrikam.com/inventory/$metadata.
Navigate your browser to the metadata URL.

Parse the metadata.
The following metadata is for the 2006 - 2008 Crime in the United States data set (https://api.datamarket.azure.com/Data.ashx/data.gov/Crimes/$metadata).

<?xml version="1.0" encoding="utf-8" standalone="yes" ?> 
<edmx:Edmx Version="1.0" xmlns:edmx="https://schemas.microsoft.com/ado/2007/06/edmx" xmlns:dr="https://schemas.microsoft.com/dallas/2010/04">   
   <edmx:DataServices xmlns:m="https://schemas.microsoft.com/ado/2007/08/dataservices/metadata" m:DataServiceVersion="1.0">      
      <Schema Namespace="data.gov.Crimes"xmlns:d="https://schemas.microsoft.com/ado/2007/08/dataservices"
              xmlns:m="https://schemas.microsoft.com/ado/2007/08/dataservices/metadata"
              xmlns="https://schemas.microsoft.com/ado/2007/05/edm">
         <EntityType Name="CityCrime">
            <Key>
               <PropertyRef Name="ROWID" />
            </Key>
            <Property Name="ROWID" Type="Edm.Int32" Nullable="false" dr:Queryable="true" dr:Returned="true" />
            <Property Name="State" Type="Edm.String" Nullable="true" dr:Queryable="true" dr:Returned="true" />
            <Property Name="City" Type="Edm.String" Nullable="true" dr:Queryable="true" dr:Returned="true" />
            <Property Name="Year" Type="Edm.Int32" Nullable="false" dr:Queryable="true" dr:Returned="true" />
            <Property Name="Population" Type="Edm.Int32" Nullable="false" dr:Queryable="false" dr:Returned="true" />
            <Property Name="ViolentCrime" Type="Edm.Int32" Nullable="false" dr:Queryable="false" dr:Returned="true" />
            <Property Name="MurderAndNonEgligentManslaughter" Type="Edm.Int32" Nullable="false" dr:Queryable="false" dr:Returned="true" />
            <Property Name="ForcibleRape" Type="Edm.Int32" Nullable="false" dr:Queryable="false" dr:Returned="true" />
            <Property Name="Robbery" Type="Edm.Int32" Nullable="false" dr:Queryable="false" dr:Returned="true" />
            <Property Name="AggravatedAssault" Type="Edm.Int32" Nullable="false" dr:Queryable="false" dr:Returned="true" />
            <Property Name="PropertyCrime" Type="Edm.Int32" Nullable="false" dr:Queryable="false" dr:Returned="true" />
            <Property Name="Burglary" Type="Edm.Int32" Nullable="false" dr:Queryable="false" dr:Returned="true" />
            <Property Name="LarcenyTheft" Type="Edm.Int32" Nullable="false" dr:Queryable="false" dr:Returned="true" />
            <Property Name="MotorVehicleTheft" Type="Edm.Int32" Nullable="false" dr:Queryable="false" dr:Returned="true" />
            <Property Name="Arson" Type="Edm.Int32" Nullable="false" dr:Queryable="false" dr:Returned="true" />
         </EntityType>
         <EntityContainer Name="datagovCrimesContainer" m:IsDefaultEntityContainer="true">
            <EntitySet Name="CityCrime" EntityType="data.gov.Crimes.CityCrime" />
         </EntityContainer>
      </Schema>
   </edmx:DataServices>
</edmx:Edmx>

Code Example 1 – Metadata XML

You are able to get a lot of useful information from the schema.

The entity container name.
<EntityContainer Name="datagovCrimesContainer" m:IsDefaultEntityContainer="true">
This is the type of the private context identifier used to authenticate access to the service.
The entity set name and type.
<EntitySet Name="CityCrime" EntityType="data.gov.Crimes.CityCrime" />
This is the type of the IEnumerable<T> and IList<T>.
The entity and type.
<EntityType Name="CityCrime">
This is the type used in your query and foreach (For Each) loop variable.
The name of each field that is returned by a query.
Name=”State”
The data type of each field.
Type=”Edm.String”
Whether the field can be null or not.
Nullable=”false”
Whether or not a field may be used to filter query results.
dr:Queryable=”true”
dr:Queryable="false"
A queryable field may be used in a where (Where) clause of your query. Non-queryable fields cannot be used in a query where (Where) clause.
Whether or not this field is returned when you query the dataset.
dr:Returned=”true”