Base Query
http://ogdi.cloudapp.net/v1/dc/CrimeIncidents/
Filter Expression
Query Builder
Text Query
4
0
| POSTED ONJul 30, 2010 03:33 PM | TYPEGeneral Comment (no reply required) |
|
| POSTED ONDec 06, 2010 02:58 PM | TYPEGeneral Comment (no reply required) |
|
| POSTED ONJun 13, 2011 07:18 PM | TYPEGeneral Comment (no reply required) |
|
. The basic format
of a filter expression is property comparison expression,
where property is the name of a property, comparison is a comparison
operator, and expression is the expression with which the property's
value should be compared.
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Collections.Generic" %>
<%@ Import Namespace="System.Linq" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
// This sample requires the .NET Framework 3.5 SP1.
// To get this the sample to work, perform the
// following steps:
//
// 1. Create a new Web Site in Visual Studio 2008 SP1
// 2. Add a new Web Form (name it what you like)
// 3. Make sure the "Place code in separate file" is unchecked in the
// Add New Item dialog
// 4. Replace the entire contents of the .aspx file with the entire
// text of this sample
// 5. Right click on the project node in Solution Explorer
// 6. Select Add Service Reference
// 7. Enter service endpoint URL into the Address field
// For this example, we are using the dc container and the url is http://ogdi.cloudapp.net/v1/dc
//
// The Ogdi.DataServiceContexts sample makes it
// very easy for .NET developers to query
// for data using LINQ. This sample is uses code
// generation and will enable you to automatically
// generate updated classes as new data is published
// into OGDI.
protected void Page_Load(object sender, EventArgs e)
{
var context = new Ogdi.dcDataService(new Uri("http://ogdi.cloudapp.net/v1/dc"));
//var context = new Ogdi.DataServiceContexts.dc.dcDataServiceContext();
// NOTE: You can replace BankLocations with any EntitySet available
// See http://ogdisdk.cloudapp.net/DataCatalog.aspx
// for a full list.
var query = from locations in context.BankLocations
select locations;
// The query above translates into the following URL:
// http://ogdi.cloudapp.net/v1/dc/BankLocations()
// Execute query
List<Ogdi.BankLocationsItem> results = query.ToList();
// Bind the reults to the GridView.
GridView1.DataSource = results;
GridView1.DataBind();
// Execute the same query, but return KML
var wc = new System.Net.WebClient();
var queryUrl = query.ToString() + "?format=kml";
TextBox1.Text = wc.DownloadString(new Uri(queryUrl));
// Currently, the OGDI service only supports the $filter
// and $top WCF Data Services querystring paramaters.
// Therefore, the following queries are valid:
query = (from locations in context.BankLocations
select locations).Take(5);
// The query above translates into the following URL:
// http://ogdi.cloudapp.net/v1/dc/BankLocations()?$top=5
query = from locations in context.BankLocations
where locations.name == "Adams Bank"
select locations;
// The query above translates into the following URL:
// http://ogdi.cloudapp.net/v1/dc/BankLocations()?$filter=name eq 'Adams Bank'
query = (from locations in context.BankLocations
where locations.name == "Adams Bank"
select locations).Take(5);
// The query above translates into the following URL:
// http://ogdi.cloudapp.net/v1/dc/BankLocations()?$filter=name eq 'Adams Bank'&$top=5
// Although $filter is supported, not all $filter options
// are available. For example, substringof() is not supported.
query = from locations in context.BankLocations
where locations.name.Contains("Adam")
select locations;
// The query above translates into the following URL:
// http://ogdi.cloudapp.net/v1/dc/BankLocations()?$filter=substringof('Adam',name)
// Any LINQ queries that translate into usage of unsupported
// WCF Data Services querystring parameters will
// result in a DataServiceQueryException.
// As another example, the query below will result in the usage of $skip.
// Therefore a DataServiceQueryException will be thrown.
query = (from locations in context.BankLocations
where locations.name == "Adams Bank"
select locations).Skip(5).Take(5);
// The query above translates into the following URL:
// {http://ogdi.cloudapp.net/v1/dc/BankLocations()?$filter=name eq 'Adams Bank'&$skip=5&$top=5
// NOTE: To implement paging, see "C#/.NET Paging" sample
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
<asp:TextBox ID="TextBox1" runat="server" TextMode="MultiLine" Width="100%" Height="200px"></asp:TextBox>
</div>
</form>
</body>
</html>
NOTE: This is a working sample. You can copy / paste this code into an html file. However, you must download the dependent jquery-1.4.2.min.js file for successful execution. You can download jquery-1.4.2.min.js from here.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<title></title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<script type="text/javascript" src="http://ecn.dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.2&mkt=en-us"></script>
<script src="http://ajax.microsoft.com/ajax/jquery/jquery-1.4.2.min.js" type="text/javascript"></script>
<script type="text/javascript">
var map;
var kmlLayer;
var mapLoaded = false;
function LoadMap()
{
map = new VEMap('myMap');
map.onLoadMap = map_Loaded;
map.LoadMap();
}
function map_Loaded()
{
mapLoaded = true;
}
function LoadKml()
{
map.Clear();
kmlLayer = new VEShapeLayer();
// using &format=kml returns a KML document instead of AtomPub
var queryUrl =
"http://ogdi.cloudapp.net/v1/dc/CrimeIncidents?$filter=&format=kml";
var veLayerSpec = new VEShapeSourceSpecification(
VEDataType.ImportXML,
queryUrl,
kmlLayer);
map.ImportShapeLayerData(veLayerSpec, Kml_Loaded, true);
}
function Kml_Loaded(veShapeLayer)
{
var shapeCount = veShapeLayer.GetShapeCount();
for (var i = 0; i < shapeCount; i++)
{
var shape = veShapeLayer.GetShapeByIndex(i);
if (shape.GetType() == VEShapeType.Polygon)
{
shape.HideIcon();
}
}
var message = "KML Loaded. Hover over the pushpins. " +
"You will see entity.entityid as the Description of the Bing Maps Infobox."
alert(message);
}
function LoadAdditionalData()
{
// We don't want KML here. We want additional "raw data" that we can match up
// with the data in the KML using entity.entityid. To do this we ask for JSON
// using &format=json plus we provide a callback function name
// using &callback=yourCallbackFunctionName (this is known as JSONP).
var queryUrl =
"http://ogdi.cloudapp.net/v1/dc/CrimeIncidents?$filter=&format=json&callback=?";
$.getJSON(queryUrl, null, AdditionalData_Loaded);
}
function AdditionalData_Loaded(result)
{
result = result.d;
for (var i = 0, length = kmlLayer.GetShapeCount(); i < length; i++)
{
var shape = kmlLayer.GetShapeByIndex(i);
if (shape.GetType() == VEShapeType.Pushpin)
{
// All KML queries contain entity.entityid as the value
// for the description element of the Placemark.
// You can use this to match up the results from the
// JSONP query by entity
var entity = GetEntityById(shape.GetDescription(), result);
if (entity != null)
{
// Here we can get at any of the returned object
// properties and formulate whatever UI for the
// Bing Maps InfoBox we want. Since this sample
// is designer to work with all possible queries, we
// just use entity.entityid since every entity has
// an entityid.
var description = "This entityid comes from the object returned from the JSONP query: "
+ entity.entityid;
shape.SetDescription(description);
}
}
}
var message = "Additional Data Loaded. Hover over the pushpins. " +
"You will see that the Description of the Bing Maps " +
"Infobox has now been customized based on the additional data returned."
alert(message);
}
function GetEntityById(id, array)
{
for (var i = 0, length = array.length; i < length; i++)
{
if (array[i].entityid == id)
{
return array[i];
}
}
return null;
}
</script>
</head>
<body onload="LoadMap();">
<form id="form1" runat="server">
<div>
<div id='myMap' style="position: relative; width: 900px; height: 500px;">
</div>
<p>Click the button below to query the service for KML and use Bing Maps to load the KML on the map. The
default rendering of the KML puts the entity Id into the Bing Maps shape (VEShape) description. You
can use this Id to query the service for more data.</p>
<input id="btnLoadKml" type="button" value="Load Kml" onclick="LoadKml();" />
<br />
<p>Click the button below to query the service for the entities related to the original KML query. The
returned entities will be used to update the Bing Maps shape (VEShape) description from entity.Id
to data from the returned entity.</p>
<input id="btnLoadAdditionalData" type="button" value="Load Additional Data" onclick="LoadAdditionalData();" />
<br />
</div>
</form>
</body>
</html>
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Collections.Generic" %>
<%@ Import Namespace="System.Linq" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
// This sample requires the .NET Framework 3.5 SP1 & Microsoft Chart Controls for Microsoft .NET Framework 3.5
// To get this the sample to work, perform the
// following steps:
//
// 1. Create a new Web Site in Visual Studio 2008 SP1.
// 2. Add a new Web Form (name it what you like).
// 3. Make sure the "Place code in separate file" is unchecked in the
// Add New Item dialog
// 4. Replace the entire contents of the .aspx file with the entire
// text of this sample
// 5. Right click on the project node in Solution Explorer
// 6. Select Add Service Reference
// 7. Enter service endpoint URL into the Address field
// For this example, we are using the dc container and the url is http://ogdi.cloudapp.net/v1/dc
// 8. Right click on the project node in Solution Explorer
// 9. Select Add Reference..
// 10.In .Net tab select component name "System.Web.DataVisualization"
// 11.Click OK button
// 12.Open Web.config and add following tags in that file at respective locations mentioned below
// a. In <controls> tag add following tag
// <add
// tagPrefix="asp"
// namespace="System.Web.UI.DataVisualization.Charting"
// assembly="System.Web.DataVisualization,
// Version=3.5.0.0,
// Culture=neutral,
// PublicKeyToken=31bf3856ad364e35"/>
// b. In <httpHandlers> tag add following tag
// <add
// path="ChartImg.axd"
// verb="GET,HEAD" type="System.Web.UI.DataVisualization.Charting.ChartHttpHandler,
// System.Web.DataVisualization,
// Version=3.5.0.0,
// Culture=neutral,
// PublicKeyToken=31bf3856ad364e35"
// validate="false"/>
//
// The generated proxy makes it very easy for .NET developers to query for data using LINQ.
protected void Page_Load(object sender, EventArgs e)
{
var context = new Ogdi.dcDataService(new Uri("http://ogdi.cloudapp.net/v1/dc"));
// NOTE: You can replace CrimeIncidents with any EntitySet available
// See http://ogdisdk.cloudapp.net/DataCatalog.aspx
// for a full list.
var query = from crimeIncident in context.CrimeIncidents
select crimeIncident;
// The query above translates into the following URL:
// http://ogdi.cloudapp.net/v1/dc/CrimeIncidents()
// Execute query
List<Ogdi.CrimeIncidentsItem> results = query.ToList();
// Set entityset name
var entitySetName = "CrimeIncidents";
// Set Horizontal Axis Column Name
var horizontalColName = "offense";
// Create an object of type Dictionary<string, double> to store keyvalue pairs
Dictionary<string, double> keyValueList = new Dictionary<string, double>();
// Fetch the required data by firing LINQ query on DataTable
var lstData = from d in results
orderby d.offense
group d by d.offense into grp
select new
{
xValue = grp.Key.ToString(),
yValue = grp.Count()
};
keyValueList = lstData.ToDictionary(t => t.xValue.ToString(), t => Convert.ToDouble(t.yValue));
// Number of elements on X-axis
int calculatedWidth = Convert.ToInt32(keyValueList.Keys.Count) * 15;
// If calculated width is greater than 860 then ...
if (calculatedWidth > 860)
{
// Set Bar Chart width to Calculated width
OgdiBarChart.Width = calculatedWidth;
}
else
{
// Set Bar Chart width to 860
OgdiBarChart.Width = 860;
}
// Set Bar Chart height to 500
OgdiBarChart.Height = 500;
// Specify how the image of the chart will be rendered.
// BinaryStreaming --> Chart is streamed directly to the client.
// ImageMap --> Chart is rendered as an image map.
// ImageTag --> Chart is rendered using an HTML image tag.
OgdiBarChart.RenderType = RenderType.ImageTag;
// Set the palette for the Chart control.
// Berry --> utilizes blues and purples.
// Bright --> utilizes bright colors.
// BrightPastel --> utilizes bright pastel colors.
// Chocolate --> utilizes shades of brown.
// EarthTones --> utilizes earth tone colors such as green and brown.
// Excel --> utilizes Excel-style colors.
// Fire --> utilizes red, orange and yellow colors.
// Grayscale --> utilizes grayscale colors, that is, shades of black and white.
// Light --> utilizes light colors.
// None --> No palette is used.
// Pastel --> utilizes pastel colors.
// SeaGreen --> utilizes colors that range from green to blue.
// SemiTransparent --> utilizes semi-transparent colors.
OgdiBarChart.Palette = ChartColorPalette.BrightPastel;
// Add a Title object to the end of the title collection.
OgdiBarChart.Titles.Add(
new Title(entitySetName, Docking.Top, new System.Drawing.Font(
"Calibri",
14,
System.Drawing.FontStyle.Bold),
System.Drawing.Color.FromArgb(26, 59, 105)));
// Create a ChartArea class object which represents a chart area on the chart image.
ChartArea chartArea = new ChartArea("ColumnChartArea");
// Create a Series class object which stores data points and series attributes.
Series series = new Series("Series 1");
// Set an Axis object that represents the primary X-axis.
chartArea.AxisX.Title = horizontalColName;
chartArea.AxisX.TitleFont = new System.Drawing.Font("Calibri", 12, System.Drawing.FontStyle.Bold);
chartArea.AxisX.IsLabelAutoFit = true;
chartArea.AxisX.IntervalType = DateTimeIntervalType.Auto;
chartArea.AxisX.LabelStyle.Font = new System.Drawing.Font("Calibri", 9);
chartArea.AxisX.Interval = 1;
// Set an Axis object that represents the primary Y-axis.
chartArea.AxisY.Title = "No. of occurrences of \"" + horizontalColName + "\"";
chartArea.AxisY.TitleFont = new System.Drawing.Font("Calibri", 12, System.Drawing.FontStyle.Bold);
chartArea.AxisY.IsLabelAutoFit = true;
chartArea.AxisY.LabelStyle.Font = new System.Drawing.Font("Calibri", 9);
// Add a ChartArea object to the ChartAreas collection.
OgdiBarChart.ChartAreas.Add(chartArea);
// Set the chart type of a series.
series.ChartType = SeriesChartType.Column;
// Set the value type plotted along the X-axis to String
series.XValueType = ChartValueType.String;
// Set the value type plotted along the Y-axis to Double
series.YValueType = ChartValueType.Double;
// Set the font of the data point.
series.Font = new System.Drawing.Font("Calibri", 8, System.Drawing.FontStyle.Bold);
// Set a flag that indicates whether to show the value of the data point on the label.
series.IsValueShownAsLabel = true;
// Set the tooltip
series.ToolTip = "'#VALX' with value '#VALY'";
// Add a Series object to the Series collection.
OgdiBarChart.Series.Add(series);
// If there is data for representing chart in Viewdata then ...
if (keyValueList != null)
{
// For each XY value pair do ...
foreach (KeyValuePair<string, double> XyPair in keyValueList)
{
// Add a DataPoint object to the end of the collection,
// with the specified X-value and Y-value.
this.OgdiBarChart.Series[0].Points.AddXY(
// If key is empty then put xValue = <<BLANK>>...
XyPair.Key.Trim().Equals(string.Empty) ? "<<BLANK>>" :
// else if Key length is greater than 20 then xValue becomes
// first 18 characters appeneded with two dots
// else if Key length is less than or equal to 20 then
// xValue will be considered as entire value of key
XyPair.Key.Length > 20 ? XyPair.Key.Substring(0, 18) + ".." : XyPair.Key
,
// yValue is assigned to value in Xypair
XyPair.Value);
}
}
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Chart ID="OgdiBarChart" runat="server">
</asp:Chart>
</div>
</form>
</body>
</html>
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Collections.Generic" %>
<%@ Import Namespace="System.Linq" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
// This sample requires the .NET Framework 3.5 SP1 & Microsoft Chart Controls for Microsoft .NET Framework 3.5
// To get this the sample to work, perform the
// following steps:
//
// 1. Create a new Web Site in Visual Studio 2008 SP1.
// 2. Add a new Web Form (name it what you like).
// 3. Make sure the "Place code in separate file" is unchecked in the
// Add New Item dialog
// 4. Replace the entire contents of the .aspx file with the entire
// text of this sample
// 5. Right click on the project node in Solution Explorer
// 6. Select Add Service Reference
// 7. Enter service endpoint URL into the Address field
// For this example, we are using the dc container and the url is http://ogdi.cloudapp.net/v1/dc
// 8. Right click on the project node in Solution Explorer
// 9. Select Add Reference..
// 10.In .Net tab select component name "System.Web.DataVisualization"
// 11.Click OK button
// 12.Open Web.config and add following tags in that file at respective locations mentioned below
// a. In <controls> tag add following tag
// <add
// tagPrefix="asp"
// namespace="System.Web.UI.DataVisualization.Charting"
// assembly="System.Web.DataVisualization,
// Version=3.5.0.0, Culture=neutral,
// PublicKeyToken=31bf3856ad364e35"/>
// b. In <httpHandlers> tag add following tag
// <add
// path="ChartImg.axd"
// verb="GET,HEAD"
// type="System.Web.UI.DataVisualization.Charting.ChartHttpHandler,
// System.Web.DataVisualization,
// Version=3.5.0.0,
// Culture=neutral,
// PublicKeyToken=31bf3856ad364e35"
// validate="false"/>
//
// The generated proxy makes it very easy for .NET developers to query for data using LINQ.
protected void Page_Load(object sender, EventArgs e)
{
var context = new Ogdi.dcDataService(new Uri("http://ogdi.cloudapp.net/v1/dc"));
// NOTE: You can replace CrimeIncidents with any EntitySet available
// See http://ogdisdk.cloudapp.net/DataCatalog.aspx
// for a full list.
var query = from crimeIncident in context.CrimeIncidents
select crimeIncident;
// The query above translates into the following URL:
// http://ogdi.cloudapp.net/v1/dc/CrimeIncidents()
// Execute query
List<Ogdi.CrimeIncidentsItem> results = query.ToList();
// Set entityset name
var entitySetName = "CrimeIncidents";
// Create an object of type Dictionary<string, double> to store keyvalue pairs
Dictionary<string, double> keyValueList = new Dictionary<string, double>();
// Fetch the required data by firing LINQ query on DataTable
var lstData = from d in results
orderby d.offense
group d by d.offense into grp
select new
{
xValue = grp.Key.ToString(),
yValue = grp.Count()
};
keyValueList = lstData.ToDictionary(t => t.xValue.ToString(), t => Convert.ToDouble(t.yValue));
// Set chart height
OgdiPieChart.Height = 630;
// Set chart height
OgdiPieChart.Width = 800;
// Specify how the image of the chart will be rendered.
// BinaryStreaming --> Chart is streamed directly to the client.
// ImageMap --> Chart is rendered as an image map.
// ImageTag --> Chart is rendered using an HTML image tag.
OgdiPieChart.RenderType = RenderType.ImageTag;
// Set the palette for the Chart control.
// Berry --> utilizes blues and purples.
// Bright --> utilizes bright colors.
// BrightPastel --> utilizes bright pastel colors.
// Chocolate --> utilizes shades of brown.
// EarthTones --> utilizes earth tone colors such as green and brown.
// Excel --> utilizes Excel-style colors.
// Fire --> utilizes red, orange and yellow colors.
// Grayscale --> utilizes grayscale colors, that is, shades of black and white.
// Light --> utilizes light colors.
// None --> No palette is used.
// Pastel --> utilizes pastel colors.
// SeaGreen --> utilizes colors that range from green to blue.
// SemiTransparent --> utilizes semi-transparent colors.
OgdiPieChart.Palette = ChartColorPalette.BrightPastel;
// Add a Title object to the end of the title collection.
OgdiPieChart.Titles.Add(
new Title(
entitySetName,
Docking.Top,
new System.Drawing.Font(
"Calibri",
14,
System.Drawing.FontStyle.Bold
),
System.Drawing.Color.FromArgb(26, 59, 105))
);
// Create a ChartArea class object which represents a chart area on the chart image.
ChartArea chartArea = new ChartArea("ColumnChartArea");
// Create a Series class object which stores data points and series attributes.
Series series = new Series("Series 1");
// Add a ChartArea object to the ChartAreas collection.
OgdiPieChart.ChartAreas.Add(chartArea);
// Set the chart type of a series.
series.ChartType = SeriesChartType.Pie;
// Set the value type plotted along the X-axis to String
series.XValueType = ChartValueType.String;
// Set the value type plotted along the Y-axis to Double
series.YValueType = ChartValueType.Double;
// Set the tooltip
series.ToolTip = "'#VALX' : '#VALY' [#PERCENT{P2}]";
// Set the LegendText
series.LegendText = "'#VALX' : '#VALY' [#PERCENT{P2}]";
// Disable PieLabelStyle
series["PieLabelStyle"] = "Disabled";
// Add a Series object to the Series collection.
OgdiPieChart.Series.Add(series);
// If there is data for representing chart in Viewdata then ...
if (keyValueList != null)
{
// For each XY value pair do ...
foreach (KeyValuePair<string, double> XyPair in keyValueList)
{
// Add a DataPoint object to the end of the collection,
// with the specified X-value and Y-value.
this.OgdiPieChart.Series[0].Points.AddXY(
// If key is empty then put xValue = <<BLANK>>...
XyPair.Key.Trim().Equals(string.Empty) ? "<<BLANK>>" :
// else if Key length is greater than 20 then xValue becomes
// first 18 characters appeneded with two dots
// else if Key length is less than or equal to 20 then
// xValue will be considered as entire value of key
XyPair.Key.Length > 20 ? XyPair.Key.Substring(0, 18) + ".." : XyPair.Key
,
// yValue is assigned to value in Xypair
XyPair.Value);
}
}
// Set chart properties
OgdiPieChart.BorderSkin.SkinStyle = BorderSkinStyle.Emboss;
OgdiPieChart.BorderColor = System.Drawing.Color.FromArgb(26, 59, 105);
OgdiPieChart.BorderlineDashStyle = ChartDashStyle.Solid;
OgdiPieChart.BorderWidth = 2;
// Add new legend to collection of Legends
OgdiPieChart.Legends.Add("Legend1");
// Set legend properties
OgdiPieChart.Legends[0].Docking = Docking.Bottom;
OgdiPieChart.Legends[0].Alignment = System.Drawing.StringAlignment.Center;
OgdiPieChart.Legends[0].LegendStyle = LegendStyle.Table;
OgdiPieChart.Legends[0].IsTextAutoFit = true;
OgdiPieChart.Legends[0].TableStyle = LegendTableStyle.Auto;
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Chart ID="OgdiPieChart" runat="server">
</asp:Chart>
</div>
</form>
</body>
</html>
