Linq to SqlXML


I’m hacking along on my Document DB emulator ontop of Sql Server XML columns.

I have some decent Linq support in place now.
The following query:

var query = from order in orders
            //must have status shipped
            where order.Status >= OrderStatus.Shipped      
            //must contain foo or bar products
            where order.OrderDetails.Any(d => d.ProductNo == "Foo" || d.ProductNo == "Bar")
            //must have an order total > 100
            where order.OrderDetails.Sum(d => d.ItemPrice * d.Quantity) > 100 
            select order;

will yield the following Sql + XQuery to the Sql Server:

select *
from documents
where CollectionName = 'Order'  and 
--must have an order total > 100
(documentdata.exist('/object/state[(
     fn:sum( 
          for $A in OrderDetails[1]/object/state 
                return ($A/ItemPrice[1] * $A/Quantity[1])) > xs:decimal(100))]') = 1) and 
--must contain foo or bar products
(documentdata.exist('/object/state[OrderDetails[1]/object/state[((ProductNo[1] = "Foo") or 
 (ProductNo[1] = "Bar"))]]') = 1) and 
--must have status shipped
(documentdata.exist('/object/state[(Status[1] >= xs:int(2))]') = 1)

Building a Document DB ontop of Sql Server


I’ve started to build a Document DB emulator ontop of Sql Server XML columns.
Sql Server XML columns can store schema free xml documents, pretty much like RavenDB or MongoDB stores schema free Json/Bson documents.

XML Columns can be indexed and queried using XPath queries.

So I decided to build an abstraction layer ontop of this in order to achieve similair ease of use.
I’ve built a serializer/deserializer that deals with my own XML structure for documents (state + metadata) and also an early Linq provider for querying.

Executing the following code:

var ctx = new DocumentContext("main");
var customers = ctx.GetCollection<Customer>().AsQueryable();

var query = from customer in customers
            where customer.Address.City == "abc" && customer.Name == "Acme Inc5"
            orderby customer.Name
            select customer;

var result = query.ToList();
foreach (var item in result)
{
    Console.WriteLine(item.Name);
    Console.WriteLine(item.Address.City);
}

Will yield the following SQL + XPath query:

select *

from documents

where CollectionName = 'Customer' and
   ((documentdata.exist('/object/state/Address/object/state/City/text()[. = "abc"]') = 1) and
    (documentdata.exist('/object/state/Name/text()[. = "Acme Inc5"]') = 1))

order by documentdata.value('((/object/state/Name)[1])','nvarchar(MAX)')

The result of the query will be returned to the client and then deserialized into the correct .NET type.