Saturday, August 30, 2008

ADO.NET Data Services (Project "Astoria") Query String Options and Expression Syntax

I'm stealing this directly from the ADO.NET Data Services documentation here:

http://msdn.microsoft.com/en-us/data/bb931106.aspx

However, it's all in a Word document (??) right now, so I thought I'd repost to something that might be a bit easier to find.

Structure of Web Data Services URLs

The basic format for URLs is:

http://host/vdir/<service>/<EntitySet>[(<Key>)[/<NavigationProperty>[(<Key>)/...]]]

Note: in the syntax above [ ] imply optional components

The 4 main elements of the URL syntax are:

1. The data service URL. The data service URL is the first part of the URL that points to the data service .svc/.rse file. For example, http://host/myapp/northwind.svc. The examples below assume that the URLs start with that prefix for brevity.

2. The entity-set name (optional). If you include an entity-set name, then all the entities in that entity-set are returned. For example, /Customers would return all of the customers in the Northwind data service. The system allows for an optional filter predicate contained in parenthesis to subset the response to a single entity. For single-key entities, you can simply indicate the key value, and the resulting URL will point to that entity specifically. For example, if there is a customer entity with a key ‘ALFKI’, its URL would be /Customers(‘ALFKI’). Additional expression-based filtering on a set is enabled by using query string parameters, which are described later in this document

3. A navigation property (optional). A navigation property can be placed after the entity-set name (separated by a “/”), indicating that you want to traverse the relationship being pointed to. For example, /Customers(‘ALFKI’)/Orders would return the sales orders of the customer with the primary key ‘ALFKI’. As noted above, a filter can also be applied to the navigation property using query string operators (described later in this document) to return only a subset of the related entities. For example, /Customers(‘ALFKI’)/Orders?$filter=OrderDate gt '1998-1-1' returns all the orders posted after Jan 1st, 1998, for the customer with a key ‘ALFKI’. Since the result of traversing a relationship through a navigation property is another set of entities, you can continue to add navigation properties to the URL to navigate through the relationship graph specified in the data service schema. For example, /Customers(‘ALFKI’)/Orders(1)/Employees returns the employees that created sales order 1 for the customer with a key of ‘ALFKI’.

Query string options

While the URL format allows for filtering and traversing through the graph of entities in the store, it does not have constructs to control the output. For that, a number of optional query string parameters are supported by the Astoria data services. Table 2 below lists all of the query options along with their description and some usage examples.

expand

The ‘expand’ option allows you to embed one or more sets of related entities in the results. For example, if you want to display a customer and its sales orders, you could execute two requests, one for /Customers(‘ALFKI’) and one for /Customers(‘ALFKI’)/Orders. The ‘expand’ option returns the related entities in-line with the response of the parent URL request.

You may specify multiple navigation properties to expand by separating them with commas, and you may traverse more than one relationship by using a dot to jump to the next navigation property.

--a customer with related sales orders

/Customers[ALFKI]?$expand=Orders

--a customer with related sales orders and employee information related to those orders

/Customers[ALFKI]?$expand=Orders.Employees

--Orders with related employees information and related shipper information

/Orders[10248]?$expand=Employees,Shippers

orderby

Sort the results by the criteria given in this value. Multiple properties can be indicated by separating them with a comma. The sort order can be controlled by using the “asc” (default) and “desc” modifiers.

/Customers?$orderby=City

/Customers?$orderby=City desc

/Customers?$orderby=City desc,CompanyName

skip

Skip the number of rows given in this parameter when returning results. This is useful in combination with “top” to implement paging (e.g. if using 10-entity pages, saying $skip=30&top=$10 would return the fourth page). Note that skip only makes sense on sorted sets; if an orderby option is included, ‘skip’ will skip entities in the order given by that option. If no orderby option is given, ‘skip’ will sort the entities by primary key and then perform the skip operation.

--return all customers except the first 10

/Customers?$skip=10

--return the 4th page, in 10-row pages

/Customers?$skip=30&$top=10

top

Restrict the maximum number of entities to be returned. This option is useful both by itself and in combination with skip, where it can be used to implement paging as discussed in the description of ‘skip’.

/Customers?$top=5

--top 5 sales orders with the highest TotalDue

/Orders?$orderby=TotalDue&$top=5

filter

Restrict the entities returned from a query by applying the expression specified in this operator to the entity set identified by the last segment of the URI path

/Customers?$filter=City eq ‘London’

-- all customers in London

/Customers?$filter='Wayne, John' eq insert(ContactName,

length(lastname), ',')

-- Match all Customers with the value of the property  ‘fullname’ equal to ‘Wayne, John’

Table 1. Query string options

Expression Syntax

The simple expression language that is used in filter operators supports references to columns and literals. The literal values can be strings enclosed in single quotes , numbers and boolean values (true or false). If a date/time value needs to be specified, it can be written as a string (in quotes) and the system will attempt to convert it to a date/time if the other operand is a date/time type.

The operators in the expression language use abbreviations of the names instead of symbols to reduce the amount of escaping necessary in the URL. The abbreviations are listed in the table below.

Logical Operators

eq

Equal

/Customers?filter=City eq 'London'

ne

Not equal

/Customers?filter=City ne 'London'

gt

Greater than

/Product?$filter=UnitPrice gt 20

gteq

Greater than or equal

/Orders?$filter=Freight gteq 800

lt

Less than

/Orders?$filter=Freight lt 1

lteq

Less than or equal

/Product?$filter=UnitPrice lteq 20

and

Logical and

/Product?filter=UnitPrice lteq 20 and UnitPrice gt 10

or

Logical or

/Product?filter=UnitPrice lteq 20 or UnitPrice gt 10

not

Logical negation

/Orders?$ ?$filter=not endswith(ShipPostalCode,'100')

Arithmetic Operators

add

Addition

/Product?filter=UnitPrice add 5 gt 10

sub

Subtraction

/Product?filter=UnitPrice sub 5 gt 10

mul

Multiplication

/Orders?$filter=Freight mul 800 gt 2000

div

Division

/Orders?$filter=Freight div 10 eq 4

mod

Modulo

/Orders?$filter=Freight mod 10 eq 0

Grouping Operators

( )

Precedence grouping

/Product?filter=(UnitPrice sub 5) gt 10

Table 2. Operators for filter expressions

In addition to the operators described above, a set of functions are also defined for use with the filter query string operator. The following tables list the available functions. This CTP does not support Aggregate functions (sum, min, max, avg, etc) as they would change the meaning of the ‘/’ operator to allow traversal through sets. For example, /Customers?$filter=average(Orders/Amount) gt 50.00 is not supported. Additionally, ISNULL or COALESCE operators are not defined. There is a null literal which can be used for comparison following CLR semantics.

String Functions

bool contains(string p0, string p1)

bool endswith(string p0, string p1)

bool startswith(string p0, string p1)

int length(string p0)

int indexof(string arg)

string insert(string p0, int pos, string p1)

string remove(string p0, int pos)

string remove(string p0, int pos, int length)

string replace(string p0, string find, string replace)

string substring(string p0, int pos)

string substring(string p0, int pos, int length)

string tolower(string p0)

string toupper(string p0)

string trim(string p0)

string concat(string p0, string p1)

Date Functions

int day(DateTime p0)

int hour(DateTime p0)

int minute(DateTime p0)

int month(DateTime p0)

int second(DateTime p0)

int year(DateTime p0)

Math Functions

double round(double p0)

decimal round(decimal p0)

double floor(double p0)

decimal floor(decimal p0)

double ceiling(double p0)

decimal ceiling(decimal p0)

Type Functions

bool IsOf(type p0)

bool IsOf(expression p0, type p1)

<p0> Cast(type p0)

<p1> Cast(expression p0, type p1)

Examples

  • /Orders?$filter=ID eq 1From
    • From all the Orders in the data store, return only the Orders with the ‘ID’ property equal to 201
  • /Customers?$filter='Wayne, John' eq insert(fullname, length(lastname), ',')
    • Match all Customers with the value of the property ‘fullname’ equal to ‘Wayne, John’
  • /Customer$filter=isof(‘SpecialCustomer’)
    • Match all customers that are of type SpecialCustomer. Entity sets support inheritance and thus customer entities in the entity set may be of different types within a type hierarchy

Wednesday, August 20, 2008

Chicago .NET Users Group Presentation on SQL Cache Dependency Patterns

Thanks to all that came out.  I appreciate you taking a listen.

I've uploaded the Sql Cache Dependency Patterns presentation notes and demos for you all to enjoy.  Also, below is the copy from the handout that was available at the meeting (also in the zip file for download).

Summary of SQL Server 2005 Cache Dependency Patterns

Declarative SqlDependency

Overview

Declaratively add database dependencies into your ASP.NET web applications using an attribute-based approach. Dependencies can be added at the page level for all data sources, or at the individual SqlDataSource level.

Appropriate Uses

If there is heavy use of ASP.NET declarative databinding using simple T-SQL queries.

Pros

Extremely simple to use, piggy backs on the flexible OutputCache scheme.

Cons

Only available to web applications. Severely restricts query composition and database options.

System.Web.Cache.SqlCacheDependency.

Overview

Implementation of CacheDependency that integrates database dependency into the existing System.Web.Cache architecture.

Appropriate Uses

If there is heavy use of the System.Web.Cache architecture, and T-SQL queries are simple.

Pros

Very simple to use, and leverages the System.Web.Cache CacheDependency architecture.

Cons

Only available to web applications. Severely restricts query composition and database options.

System.Data.SqlClient.SqlDependency

Overview

Provides an event-based approach to the problem by notifying the application of when the set of data has changed.

Appropriate Uses

When cache storage is home grown, or complicated to invalidate.

Pros

Available to both WinForms and ASP.NET. Allows flexibility in the clearing of cache, which provides a wider range of cache storage options.

Cons

Severely restricts query composition and database options.

System.Data.SqlClient.SqlNotificationRequest

Overview

This is the lowest level of SqlNotifications provided by the .NET Framework. Monitors query changes and executes the delivery of messages to the Service Broker Queues .

Appropriate Uses

More control is required around how often the application is notified of changes.

Pros

Provides ultimate control over how the notification process is handled.

Cons

Queries and database options are still highly restrictive. No control of the message sent to the notification listener. Lots of plumbing to write.

Roll Your Own with SQL Server Service Broker

Overview

A custom approach, leveraging the SQL Server Service Broker to handle data changes and notification delivery.

Appropriate Uses

When a highly granular, row based cache dependency scheme is in place.

Pros

Allows you to receive detailed information about the exact record that caused the notification. Ultimate flexibility around the entire cache invalidation strategy. Does not restrict the types of commands that you need to execute.

Cons

Can be complicated to implement depending on how complex the cache invalidation rules are. Lots of plumbing to write.

Gotchas

· Not all versions of SQL Server have the SQL Server Service Broker. See http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx.

· You must enable the Service Broker per database. See the \SQL\CreateNewBroker.sql from the sample application

· You must create an encryption key per database. See the \SQL\ CreateNewBroker.sql from the sample application

· The DB principal of the database is incorrect because it has been restored from another server. See http://support.microsoft.com/kb/913423

· You must call System.Data.SqlClient.SqlDependency.Start() if using SqlDependency or SqlCacheDependency.

· Queries must abide by the following rules if you aren’t rolling your own: http://msdn.microsoft.com/en-us/library/ms181122.aspx

· LINQ to SQL must generate valid queries.

· Wiring the SqlDependency, SqlCacheDependency and SqlNotificationRequest must happen prior to executing the command.

Tuesday, August 19, 2008

Using Twitter to Thrill Customers

So check this out. 

I'm prepping for this presentation and I'm planning on boring my audience with yet another AdventureWorks sample DB demo.  In case you haven't noticed, they've recently moved all Microsoft SQL Server sample DBs to CodePlex.

Anywho, I was trying to download the sample DB from CodePlex and it was taking forever.  So I used this spare time to write on Twitter about it.  Nothing horrible, just sort of a prayer to the bandwidth gods, or latency angels, or server uptime wizards, or whatever.

Then this morning I see in my inbox, this message from a fella at Microsoft:

"Hello Jim,

I noticed in your Twitter post that you were having trouble downloading AdventureWorks (http://twitter.com/jfiorato/statuses/891763277).  Were you able to complete your download?  If not, would you like any assistance from us?

Thanks,

Jonathan"

I was totally blown away.  It's seriously fantastic service.  What do they usually say? 

Only about 1 in 100 or 1000 customers actually complains to the company that's selling the product?  Can you imagine the service you can provide to people who are posting complaints to the ultimate anonymous gripe platform, Twitter?

This one dude, using a Summize search alert for CodePlex, is kicking ass.

Thanks again Jonathan.

Sunday, August 17, 2008

The Physical and Logical Topology of Microsoft "Velocity"

I thought it would be good to put up the physical and logical models of Velocity so you can all get a grasp of what it looks like.

Physical Topology

Below is the physical topology of a Velocity caching tier.  As you can see, each cache cluster contains one or more cache servers (hosts) with an instance of Velocity running on it as a Windows service.  The individual hosts are each cognizant of a folder share the contains information that links them all together in a cluster (Microsoft understands this is a single point of failure, and intends to solve this by RTM).  This is one level at which the synchronization of caches occurs.

Physical Topology

Obviously, you can just keep adding more servers to the cluster in order to scale.  Each server in the cluster is configured with a service port (for cache communication), a cluster port (for administration), and a memory governor that controls how much memory is allocated on the server for the cluster.  This configuration happens at install, as seen below:

Installation Configuration

The Cache Administration Tool noted in the diagram above is a command line tool that provides simple control and diagnostic functions, allowing administrators to start and stop clusters, hosts, and get real time statistics about cache inventory.

Each application has a reference to one or more host, through the web.config or app.config.  Velocity will then determine the host to retrieve the cache from.  Below is a diagram of the configuration topology:

Configuration Topology

Logical Topology

Below is the logical model of Velocity.  You can see that there are three different ways in which you can store caches across clusters.  You can use the default cache, a named cache, or a region. 

Logical Model

If you're using a single cluster for a single application, using the default cache will suffice.  However, if you're using the same cache cluster for multiple applications, then a named cache should be used in order to logically partition your applications (think one named cache per database).  At the named or default cache level, you can configure cache policies individually such as fail-over, expiration, and eviction.

Regions are caches that can be created as a subset of a named cache.  Region caches are guaranteed to exist on a single host server.  Though not entirely clear to me, I've gathered that the Velocity internals use this as a unit for replication within named caches (behind the scenes), and they've made it available in the case that you'd like to skip the overhead of routing, and go directly to a cache location for your data.

That's it for  the topology.  Hope it gave you a good understanding of how Velocity is organized and how it's solving the problem of distributed caching.

Saturday, August 16, 2008

Distributed Caching with Microsoft "Velocity" - An Introduction

Something that I think a lot of software developers have dealt with when building high availability web applications is how to manage distributed cache.  It's one thing to be able to get your data/objects into cache, but it's another thing to come up with a great cache synchronization architecture that's reliable and scalable.  To break down the common synchronization problem simply: In a load balanced environment, how do changes made on one server, force cache purging on other servers in the farm? 

This problem has resulted in the creation of distributed caching frameworks, and the emergence of a caching tier.  There's been a couple of frameworks that have been available for some time now.  Some are free (memcached) and some that aren't (NCache).  Now Microsoft is entering the game with a distributed caching framework code named "Velocity".

Velocity solves this problem by providing the infrastructure required to keep caches synchronized across application boundaries.  It essentially fuses memory across application and network boundaries, providing a unified view of a cache from a distributed application.

The key features of Velocity are:

  • It can cache any CLR object that has been declared as Serializable (either through the SerializableAttribute, or by implementing the ISerializable interface).
  • Through configuration, caches can embedded into your application, or accessed over the network.
  • Provides another option for session storage by allowing you to configure sessions state to be persisted to the Velocity cache.
  • Highly flexible, allowing you to have "regions" of data within the same application to use different caching strategies.
  • Highly modular in structure, allowing you to hook in transactions, or even replace the network layer.

The Velocity team is part of the Data Platform group in Microsoft, and in fact Velocity shares the same clustering technology with the team that is building SQL Server Data Services (SSDS), which is a cloud computing initiative from the SQL Server team.  The result of this collaboration and sharing is going to be a huge ability to scale this technology to thousands of nodes within a cache. 

Additionally, they are working with the MSN.com and Live.com teams to see if those sites could benefit from Velocity.  To think, you have the same scaling facility that two of the highest trafficked sites have.  For free!

This is an extremely useful technology coming out of Microsoft, and long overdue, and I'm a fan of the way they are putting it together.  I'll be posting more about the different configuration options and appropriate uses of

Friday, August 15, 2008

Speaking at Chicago .NET Users Group - August 20th

Update: Subject changed.  I'll be speaking on SQL Server Cache Dependency Patterns

I'll be speaking on Distributed Caching with Microsoft Velocity at the August 20th CNUG meeting. Meeting is at the Downers Grove Microsoft office.

Tip For Those Who Sell Housewares

A little tip for those of you that sell home goods and Furnishings.

Find a nice area that has lovely weekly rentals (if you're in the Chicago area, think Saugatuck, MI, Door County, WI, Galena, IL).

Fill these weekly rentals with your stuff for free. Make sure you've got your name and how to buy all over. All these places have concierge books. Get a "Furnished By" or "Pots and Pans provided by", if your name isn't all over your product.

Proof? Got back from Saugatuck Michigan a month ago, and came home today to a brand new set of Rachel Ray pans. Same ones that were in the rental.

Tuesday, August 05, 2008

Speaking at Codeapalooza - September 6th

Beware the shameless plug to follow:

I'm going to be speaking at Codeapalooza on September 6th on ADO.NET Data Services (Project Astoria). There's lots of other great sessions lined up that you might be interested in as well.

http://www.codeapalooza.com