With ADO.Net Data Service you can add parameters in the Querystring. The following parameters are recognized:
-
expand
-
orderby
-
skip
-
top
-
filter
So if your request for all products was AdventureWorks.svc/Product you can add parameters like this AdventureWorks.svc/Product?$[paramatername]=expression
For the examples I use the Product table of the Adventureworks database.
Filter
Filter allows you to filter the selected result based on a expression. The expression has two types of operators: logical, and arithmetic.
The logical operators are:
- eq (==)
- ne (!=)
- gt (<)
- gteq (<=)
- lt (>)
- lteq (>=)
- and (&&)
- or (||)
- not (!)
The arithmetic operators are:
- add (Addition)
- sub (Subtraction)
- mul (Multiplication)
- div (Division)
- mod (Modulus)
Example
Let's say I want to have the product with the value 534 in the ProductId column.
[code:c#]
AdventureWorks.svc/Product?$filter=ProductID eq 534
[/code]
Result
[code:xml]
<entry>
<id>http://localhost:55832/AdventureWorks.svc/Product(534)</id>
<title type="text"></title>
<updated>2009-03-11T08:22:23Z</updated>
<author>
<name />
</author>
<link rel="edit" title="Product" href="Product(534)" />
<link rel="related/BillOfMaterials" title="BillOfMaterials" href="Product(534)/BillOfMaterials" />
<link rel="related/BillOfMaterials1" title="BillOfMaterials1" href="Product(534)/BillOfMaterials1" />
<link rel="related/ProductModel" title="ProductModel" href="Product(534)/ProductModel" />
<link rel="related/ProductSubcategory" title="ProductSubcategory" href="Product(534)/ProductSubcategory" />
<link rel="related/UnitMeasure" title="UnitMeasure" href="Product(534)/UnitMeasure" />
<link rel="related/UnitMeasure1" title="UnitMeasure1" href="Product(534)/UnitMeasure1" />
<link rel="related/ProductCostHistory" title="ProductCostHistory" href="Product(534)/ProductCostHistory" />
<link rel="related/ProductDocument" title="ProductDocument" href="Product(534)/ProductDocument" />
<link rel="related/ProductInventory" title="ProductInventory" href="Product(534)/ProductInventory" />
<link rel="related/ProductListPriceHistory" title="ProductListPriceHistory" href="Product(534)/ProductListPriceHistory" />
<link rel="related/ProductProductPhoto" title="ProductProductPhoto" href="Product(534)/ProductProductPhoto" />
<link rel="related/ProductReview" title="ProductReview" href="Product(534)/ProductReview" />
<link rel="related/ProductVendor" title="ProductVendor" href="Product(534)/ProductVendor" />
<link rel="related/PurchaseOrderDetail" title="PurchaseOrderDetail" href="Product(534)/PurchaseOrderDetail" />
<link rel="related/ShoppingCartItem" title="ShoppingCartItem" href="Product(534)/ShoppingCartItem" />
<link rel="related/SpecialOfferProduct" title="SpecialOfferProduct" href="Product(534)/SpecialOfferProduct" />
<link rel="related/TransactionHistory" title="TransactionHistory" href="Product(534)/TransactionHistory" />
<link rel="related/WorkOrder" title="WorkOrder" href="Product(534)/WorkOrder" />
<category term="AdventureWorksModel.Product" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
<content type="application/xml">
<m:properties>
<d:ProductID m:type="Edm.Int32">534</d:ProductID>
<d:Name>Top Tube</d:Name>
<d:ProductNumber>TO-2301</d:ProductNumber>
<d:MakeFlag m:type="Edm.Boolean">true</d:MakeFlag>
<d:FinishedGoodsFlag m:type="Edm.Boolean">false</d:FinishedGoodsFlag>
<d:Color m:null="true" />
<d:SafetyStockLevel m:type="Edm.Int16">500</d:SafetyStockLevel>
<d:ReorderPoint m:type="Edm.Int16">375</d:ReorderPoint>
<d:StandardCost m:type="Edm.Decimal">0.0000</d:StandardCost>
<d:ListPrice m:type="Edm.Decimal">0.0000</d:ListPrice>
<d:Size m:null="true" />
<d:Weight m:type="Edm.Decimal" m:null="true" />
<d:DaysToManufacture m:type="Edm.Int32">1</d:DaysToManufacture>
<d:ProductLine m:null="true" />
<d:Class m:null="true" />
<d:Style m:null="true" />
<d:SellStartDate m:type="Edm.DateTime">1998-06-01T00:00:00</d:SellStartDate>
<d:SellEndDate m:type="Edm.DateTime" m:null="true" />
<d:DiscontinuedDate m:type="Edm.DateTime" m:null="true" />
<d:rowguid m:type="Edm.Guid">4c0bad8e-066b-46b8-bfe9-da61539606e8</d:rowguid>
<d:ModifiedDate m:type="Edm.DateTime">2004-03-11T10:01:36.827</d:ModifiedDate>
</m:properties>
</content>
</entry>
[/code]
Expand
Example
Let's say we want to use the ProductReviews of the product in our application. To do this we need to load the ProductReviews values on request, we can achieve this with the expand paramater.
To get a clear view of what the expand parameter does I included the filter funtion on ProductId 534 so we can compare the results.
AdventureWorks.svc/Product?$expand=ProductReview&$filter=ProductID eq 534
Result
[code:xml]
<entry>
<id>http://localhost:55832/AdventureWorks.svc/Product(534)</id>
<title type="text"></title>
<updated>2009-03-11T08:32:28Z</updated>
<author>
<name />
</author>
<link rel="edit" title="Product" href="Product(534)" />
<link rel="related/BillOfMaterials" title="BillOfMaterials" href="Product(534)/BillOfMaterials" />
<link rel="related/BillOfMaterials1" title="BillOfMaterials1" href="Product(534)/BillOfMaterials1" />
<link rel="related/ProductModel" title="ProductModel" href="Product(534)/ProductModel" />
<link rel="related/ProductSubcategory" title="ProductSubcategory" href="Product(534)/ProductSubcategory" />
<link rel="related/UnitMeasure" title="UnitMeasure" href="Product(534)/UnitMeasure" />
<link rel="related/UnitMeasure1" title="UnitMeasure1" href="Product(534)/UnitMeasure1" />
<link rel="related/ProductCostHistory" title="ProductCostHistory" href="Product(534)/ProductCostHistory" />
<link rel="related/ProductDocument" title="ProductDocument" href="Product(534)/ProductDocument" />
<link rel="related/ProductInventory" title="ProductInventory" href="Product(534)/ProductInventory" />
<link rel="related/ProductListPriceHistory" title="ProductListPriceHistory" href="Product(534)/ProductListPriceHistory" />
<link rel="related/ProductProductPhoto" title="ProductProductPhoto" href="Product(534)/ProductProductPhoto" />
<link rel="related/ProductReview" title="ProductReview" href="Product(534)/ProductReview">
<m:inline>
<feed>
<title type="text">ProductReview</title>
<id>http://localhost:55832/AdventureWorks.svc/Product(534)/ProductReview</id>
<updated>2009-03-11T08:32:28Z</updated>
<link rel="self" title="ProductReview" href="Product(534)/ProductReview" />
</feed>
</m:inline>
</link>
<link rel="related/ProductVendor" title="ProductVendor" href="Product(534)/ProductVendor" />
<link rel="related/PurchaseOrderDetail" title="PurchaseOrderDetail" href="Product(534)/PurchaseOrderDetail" />
<link rel="related/ShoppingCartItem" title="ShoppingCartItem" href="Product(534)/ShoppingCartItem" />
<link rel="related/SpecialOfferProduct" title="SpecialOfferProduct" href="Product(534)/SpecialOfferProduct" />
<link rel="related/TransactionHistory" title="TransactionHistory" href="Product(534)/TransactionHistory" />
<link rel="related/WorkOrder" title="WorkOrder" href="Product(534)/WorkOrder" />
<category term="AdventureWorksModel.Product" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
<content type="application/xml">
<m:properties>
<d:ProductID m:type="Edm.Int32">534</d:ProductID>
<d:Name>Top Tube</d:Name>
<d:ProductNumber>TO-2301</d:ProductNumber>
<d:MakeFlag m:type="Edm.Boolean">true</d:MakeFlag>
<d:FinishedGoodsFlag m:type="Edm.Boolean">false</d:FinishedGoodsFlag>
<d:Color m:null="true" />
<d:SafetyStockLevel m:type="Edm.Int16">500</d:SafetyStockLevel>
<d:ReorderPoint m:type="Edm.Int16">375</d:ReorderPoint>
<d:StandardCost m:type="Edm.Decimal">0.0000</d:StandardCost>
<d:ListPrice m:type="Edm.Decimal">0.0000</d:ListPrice>
<d:Size m:null="true" />
<d:Weight m:type="Edm.Decimal" m:null="true" />
<d:DaysToManufacture m:type="Edm.Int32">1</d:DaysToManufacture>
<d:ProductLine m:null="true" />
<d:Class m:null="true" />
<d:Style m:null="true" />
<d:SellStartDate m:type="Edm.DateTime">1998-06-01T00:00:00</d:SellStartDate>
<d:SellEndDate m:type="Edm.DateTime" m:null="true" />
<d:DiscontinuedDate m:type="Edm.DateTime" m:null="true" />
<d:rowguid m:type="Edm.Guid">4c0bad8e-066b-46b8-bfe9-da61539606e8</d:rowguid>
<d:ModifiedDate m:type="Edm.DateTime">2004-03-11T10:01:36.827</d:ModifiedDate>
</m:properties>
</content>
</entry>
Skip
Example all productreviews
AdventureWorks.svc/ProductReview
Result
Example skip productreviews
AdventureWorks.svc/ProductReview?$skip=2
Result
Only the last two ProductReview objects get returned.
Top
Example
[code:c#]
AdventureWorks.svc/ProductReview?$top=3
Result
This will return the first three product reviews.
Orderby
Example
[code:c#]
AdventureWorks.svc/Product?$orderby=Name
Result
This will sort the results by the column 'Name'.
You can also define build a more complex order clause by combining columns in a comma-delimited list, example:
[code:c#]
AdventureWorks.svc/Product?$orderby=Name desc,ModifyDate,ListPrice
Result
Only the product with ProductID values equeals 534 will be returned.
Combining attributes
You can combine the attributes. You can do this by creating a URL and seperating the attibutes with a '&', example;
AdventureWorks.svc/Product?$[paramatername]=expression&[paramatername]=expression
Example
[code:c#]
AdventureWorks.svc/Product?$expand=ProductReview&$filter=ProductID eq 534