Hi, I am Jason Hunter, the Dev Lead on SQL Server Data Services (SSDS). On March 5th the SSDS team launched our new data service at MIX in Vegas. We are all very excited about the service's potential to change the way we develop software. There has been a bunch of media attention on SSDS, see links at our team blog.
This service is still so new and we haven't had anywhere near enough experience using it to understand all of the ways that it can be leveraged. However, to get people thinking outside of the old boxes, Jeff Currier and I gave a talk at MIX on some patterns and practices for using the new service. Over the next couple of weeks, he and I plan to post most of the material we covered in that talk in slightly greater detail. Jeff kicked off that effort this morning with his post on using XLinq to directly bind data from SSDS to UI. In this post I'd like to talk a little about another topic we touched on: using Heterogeneous Containers and Flexible Entities to achieve Master/Detail data sets.
We are frequently asked how the Authority/Container/Entity (ACE) model maps to traditional Relational concepts. This is actually a mapping with a lot of nuance. I usually think of ACE like this:
The nuance comes about because Containers can be heterogeneous – so the Entities in a Container can be of many different types. Think of this as a set of tables (e.g. Customer, Order, OrderDetail, etc.) combined together that all share the same leading key used to partition the data for horizontal scale (e.g. the ContainerId). All of these types of data are grouped together for query and consistency in the same Container. The resulting Container will have <Customer>, <Order>, and <OrderDetail> Entities that can be accessed in a single query (without a JOIN). Since the number of Containers is unbounded and dynamically load-balanced across many physical nodes in the data center, an Authority can scale out to a nearly limitless number of Containers, even though the size of any specific Container may be bounded.
A heterogeneous container can yield a single data type by filtering down the types in the result set using the Kind metadata property as in this C# snippet:
private void OnCustomerChanged(object sender, EventArgs e)
{
string containerUri = "https://mix08-demo.data.sitka.microsoft.com/v1/" +
((Container)m_customer.SelectedItem).Id;
m_orders.BeginUpdate();
m_orders.Items.Clear();
// Query explicitly for all of the Order objects in the given container. These
// containers are heterogeneous and contain many different types at the same time.
// We provide an explicit filter to e.Kind to get only the Order objects.
string query = @"from e in entities
where e.Kind == ""Order""
select e";
foreach (Order o in SSDS.Query<Order>(containerUri, query))
{
m_orders.Items.Add(o);
}
m_orders.SelectedIndex = 0;
m_orders.EndUpdate();
}
In this example, we query for only the Order objects from the container using a simple REST library (represented by the service class SSDS in the code above - I'll have more to say on how to write such a library - with sample code - in a future post!). The resulting strongly-typed objects are then bound to a ComboBox control (m_orders) in the UI. The result set on the wire looks something like:
<s:EntitySet
xmlns:s="http://schemas.microsoft.com/sitka/2008/03/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:x="http://www.w3.org/2001/XMLSchema">
<Order>
<s:Id>2001-11-02T01:01:01Z-O044501</s:Id>
<s:Version>5</s:Version>
<HighPriority xsi:type="x:boolean">true</HighPriority>
<OrderId xsi:type="x:string">O044501</OrderId>
<OrderDate xsi:type="x:dateTime">2001-11-02T01:01:01</OrderDate>
<Total xsi:type="x:decimal">26128.8674</Total>
</Order>
<Order>
<s:Id>2002-02-02T01:01:01Z-O045283</s:Id>
<s:Version>5</s:Version>
<HighPriority xsi:type="x:boolean">true</HighPriority>
<OrderId xsi:type="x:string">O045283</OrderId>
<OrderDate xsi:type="x:dateTime">2002-02-02T01:01:01</OrderDate>
<Total xsi:type="x:decimal">37643.1378</Total>
</Order>
</s:EntitySet>
The REST library used here leverages the built-in .NET serialization technology XmlSerializer to material instances of the Order class directly from this payload. (I'll have more to say on how XmlSerializer can be used with SSDS in a future post!) This makes the manipulation of data in SSDS simple and familiar.
What does this all have to do with Master/Detail? Well, you aren't restricted by the service to querying for a single type of data in a Container like you are with Relational tables. You can leverage the heterogeneous nature of a Container to perform simple common-value JOINs of data within a Container without actually needing to JOIN between two tables. A JOINless JOIN! Consider this C# snippet:
private void OnOrderChanged(object sender, EventArgs e)
{
// Get the selected Customer and Order from the data bound combo boxes.
// These are strongly typed objects materialized by XmlSerializer
// automatically.
string containerUri = "https://mix08-demo.data.sitka.microsoft.com/v1/" +
((Container)m_customer.SelectedItem).Id;
Order selectedOrder = (Order)m_orders.SelectedItem;
// Query for all Order and OrderDetail objects. (There are other types of
// objects in this container also. Filter by the OrderId. OrderId is a
// property that appears in both the Order object and the OrderDetail
// object even though the two types have completely different structure.
// We can filter across both types with the same expression.
//
// The GROUP BY in the LINQ expression will let us group the Order and
// OrderDetail objects into two separate streams for data binding, but
// all in a single round-trip to the server.
string query = String.Format(
@"from e in entities
where (e.Kind == ""Order"" ||
e.Kind == ""OrderDetail"") &&
e[""OrderId""] == ""{0}""
select e",
selectedOrder.OrderId);
var queryResults =
from Entity i in SSDS.Query<Entity>(containerUri, query)
group i by i.GetType();
BindingSource orderSource = new BindingSource();
BindingSource detailSource = new BindingSource();
// The query above is part of a LINQ expression. The query is delay
// executed and won't actually hit the server until the first data
// value is needed. We get both object type streams back in the same
// query, group them, and then bind the two stream separately to
// different data grids.
foreach (var kindGroup in queryResults)
{
if (kindGroup.Key == typeof(Order))
orderSource.DataSource = kindGroup;
else
detailSource.DataSource = kindGroup;
}
m_order.DataSource = orderSource;
m_detail.DataSource = detailSource;
}
In this example we query for both Order and OrderDetail objects while performing a simple common-value JOIN across the OrderId property of both types. Order.OrderId is a unique identifier for Order objects and hence this query will return only a single Order object. OrderDetail.OrderId is the identifier of the "master" Order object this is detail for. Hence, this method binds a very common data application pattern of Master (Order) and Detail (OrderDetail) to UI. The application UI might look like:
The REST payload on the wire looks something like:
<s:EntitySet
xmlns:s="http://schemas.microsoft.com/sitka/2008/03/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:x="http://www.w3.org/2001/XMLSchema">
<Order>
<s:Id>2001-11-02T01:01:01Z-O044501</s:Id>
<s:Version>5</s:Version>
<HighPriority xsi:type="x:boolean">true</HighPriority>
<OrderId xsi:type="x:string">O044501</OrderId>
<OrderDate xsi:type="x:dateTime">2001-11-02T01:01:01</OrderDate>
<Total xsi:type="x:decimal">26128.8674</Total>
</Order>
<OrderDetail>
<s:Id>D003013</s:Id>
<s:Version>1</s:Version>
<OrderId xsi:type="x:string">O044501</OrderId>
<LineTotal xsi:type="x:decimal">419.458900</LineTotal>
<Quantity xsi:type="x:decimal">1</Quantity>
<UnitPrice xsi:type="x:decimal">419.4589</UnitPrice>
<ProductId xsi:type="x:decimal">761</ProductId>
</OrderDetail>
<OrderDetail>
<s:Id>D003014</s:Id>
<s:Version>1</s:Version>
<OrderId xsi:type="x:string">O044501</OrderId>
<LineTotal xsi:type="x:decimal">1258.376700</LineTotal>
<Quantity xsi:type="x:decimal">3</Quantity>
<UnitPrice xsi:type="x:decimal">419.4589</UnitPrice>
<ProductId xsi:type="x:decimal">768</ProductId>
</OrderDetail>
</s:EntitySet>
Lastly, this example leverages the power of client-side Language Integrated Query (LINQ) to formulate, execute, and process the interaction with the service. The LINQ expression:
var queryResults =
from Entity i in SSDS.Query<Entity>(containerUri, query)
group i by i.GetType();
helps separate the two integrated object streams (an Order enumeration, and an OrderDetail enumeration) by performing a client-side GROUP BY. The last bit of data binding:
foreach (var kindGroup in queryResults)
{
if (kindGroup.Key == typeof(Order))
orderSource.DataSource = kindGroup;
else
detailSource.DataSource = kindGroup;
}
binds each of these two streams to the two separate grid controls in the UI (shown above). Data binding does the rest of the work.
NEXT UP: Using XML serialization to bridge the gap between SSDS and objects!
- Jason.
