Sql Server 2012 FileTable & Nhibernate

In the project I'm currently working on we're taking advantage of Sql Server's 2012 FileTable feature. Windows API compatibility allows our users to use windows explorer to batch "upload" literally hundreds of documents at a time and allows for easy/familiar file management. We also need a way to link to files from what we are calling DocumentExtensions. The general use case is a user uploads files/folders to the application using the virtual network share that sql server creates. Then they use a web application to link selected files/folders to DocumentExtensions.

Call me old school if you like but we still rely heavily on Nhibernate as our ORM. I just haven't found a compelling reason to switch to Entity Framework. So here's the problem, we want to use Nhibernate to link DocumentExtensions to files/folders that have already been uploaded.

Here's an integration test to help explain what is needed:

	[TestFixture]
	public class When_a_link_is_added : DocumentExtensionTests
	{
	[Test]
	public void then_it_should_be_persisted()
	{
	// Arrange
	var applicationDocumentsFileTable = new ApplicationDocumentsFileTableBuilder()
	.WithFileTableManger(new FileTableManagerBuilder().WithSession(base.Session).Build())
	.Build();
	var pathLocatorResolver = new PathLocatorResolverBuilder()
	.WithSession(base.Session)
	.Build();
	var sut = new DocumentExtensionBuilder().Build();
	var pathToFile = Path.Combine(applicationDocumentsFileTable.GetDirectory().FullName, "temp.txt");
	using (File.CreateText(pathToFile)) { }
	// Act
	var link = sut.AddLink(pathLocatorResolver, pathToFile);
	base.SaveFlushClear(sut);
	// Assert
	var documentExtensionLink = base.Session.Get<DocumentExtensionLink>(link.Id);
	Utils.AssertArePropertiesEqual(link, documentExtensionLink, link.GetPropertyName(x => x.PathToPhysicalDocument));
	Assert.AreEqual(pathToFile, documentExtensionLink.PathToPhysicalDocument);
	}
	}

view raw DocumentExtensionTests.cs hosted with ❤ by GitHub

First off, this is an integration test that interacts with a real database. Also, I'm using the Builder patter to create real services instead of mocking things out. This test is not the fastest but it gives me a great deal of confidence that what I've implemented actually works all the way from the top to the bottom. I find this very comforting especially when working on techniques that are new to me.

So the ApplicationDocumentsFileTable is an abstraction that allows me to easily gain access to the DirectoryInfo object at the root of the FileTable. I find myself creating a bunch of little abstractions on top of the FileTable features. I'm using this to create a temp file at the root of the FileTable.

Next is the PathLocatorResolver is yet another one of these FileTable abstractions I'll get into in a bit.

Once I create the temp file, I create a DocumentExtension entity that I then want to have linked to the temp file. After the link is created I Save, Flush, and clear the Nhibernate session. To prove that everything was persisted correctly I grab the link by it's id and check to make sure the properties equal the properties from the link that was created above. I'm excluding the PathToPhysicalDocument property because as you'll see, this is a property based off an Formula.

Alright, on to the implementation:

	public abstract class DocumentExtension : Entity<DocumentExtension>
	{
	private readonly ApplicationDetail applicationDetail;
	private readonly Iesi.Collections.Generic.ISet<DocumentExtensionLink> documentLinks;
	protected DocumentExtension()
	{
	this.documentLinks = new HashedSet<DocumentExtensionLink>();
	}
	public DocumentExtension(ApplicationDetail applicationDetail) : this()
	{
	if (applicationDetail == null) throw new ArgumentNullException("applicationDetail");
	this.applicationDetail = applicationDetail;
	}
	public virtual ApplicationDetail ApplicationDetail { get { return this.applicationDetail; } }
	public virtual IEnumerable<DocumentExtensionLink> DocumentLinks
	{
	get { return documentLinks; }
	}
	public abstract string GetName();
	public virtual DocumentExtensionLink AddLink(IPathLocatorResolver pathLocatorResolver, string pathToPhysicalDocument)
	{
	var link = new DocumentExtensionLink(this, pathLocatorResolver.GetFromPath(pathToPhysicalDocument));
	this.documentLinks.Add(link);
	return link;
	}
	}

view raw DocumentExtension.cs hosted with ❤ by GitHub

	<?xml version="1.0" encoding="utf-8" ?>
	<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="core" namespace="core.Entities">
	<class name="DocumentExtension">
	<id name="Id" access="field.camelcase">
	<generator class="guid.comb" />
	</id>
	<discriminator column="Discriminator" />
	<many-to-one name="ApplicationDetail" column="ApplicationDetailId" cascade="save-update" access="field.camelcase" />
	<set name="DocumentLinks" table="DocumentExtensionLink" inverse="true" cascade="all-delete-orphan" access="field.camelcase">
	<key column="DocumentExtensionId" />
	<one-to-many class="DocumentExtensionLink" />
	</set>
	<subclass name="core.Entities.DocumentExtensions.PlanFormsDocumentExtension" discriminator-value="PlanFormsDocumentExtension">
	<many-to-one name="Plan" column="PlanId" cascade="save-update" access="field.camelcase" />
	</subclass>
	</class>
	</hibernate-mapping>

view raw DocumentExtension.hbm.xml hosted with ❤ by GitHub

There is a lot going on here that I'm not going to explain now. The interesting thing here is the IPathLocatorResolver. If you remember I built one up in the test and explicitly passed it into the AddLink method. Well I stick to the thought that it is bad to inject services into domain entities but when you must use the Double Dispatch pattern. This service is responsible for taking a path to a document and resolving it to a FileTable path locator hierarchyid.

	public class PathLocatorResolver : IPathLocatorResolver
	{
	private readonly ISession session;
	public PathLocatorResolver(ISession session)
	{
	if (session == null) throw new ArgumentNullException("session");
	this.session = session;
	}
	public string GetFromPath(string pathToPhysicalDocument)
	{
	return this.session.CreateSQLQuery("Select GetPathLocator('{0}').ToString()".ToFormat(pathToPhysicalDocument))
	.UniqueResult<string>();
	}
	}

view raw PathLocatorResolver.cs hosted with ❤ by GitHub

This just leaves the DocumentExtensionLink entity:

	public class DocumentExtensionLink : Entity<DocumentExtensionLink>
	{
	private readonly DocumentExtension documentExtension;
	private readonly string applicationDocumentPathLocator;
	private string pathToPhysicalDocument;
	protected DocumentExtensionLink() {}
	public DocumentExtensionLink(DocumentExtension documentExtension, string applicationDocumentPathLocator)
	{
	if (documentExtension == null) throw new ArgumentNullException("documentExtension");
	this.documentExtension = documentExtension;
	this.applicationDocumentPathLocator = applicationDocumentPathLocator;
	}
	public virtual string PathToPhysicalDocument
	{
	get { return pathToPhysicalDocument; }
	protected set { pathToPhysicalDocument = value; }
	}
	public virtual DocumentExtension DocumentExtension
	{
	get { return documentExtension; }
	}
	public virtual string ApplicationDocumentPathLocator
	{
	get { return applicationDocumentPathLocator; }
	}
	}

view raw DocumentExtensionLink.cs hosted with ❤ by GitHub

	<?xml version="1.0" encoding="utf-8" ?>
	<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="core" namespace="core.Entities">
	<class name="DocumentExtensionLink">
	<id name="Id" access="field.camelcase">
	<generator class="guid.comb" />
	</id>
	<property name="PathToPhysicalDocument" access="field.camelcase" formula="(select file_stream.GetFileNamespacePath(1, 2) from ApplicationDocument ad where ad.path_locator = ApplicationDocumentPathLocator)" />
	<property name="ApplicationDocumentPathLocator" access="field.camelcase" />
	<many-to-one name="DocumentExtension" column="DocumentExtensionId" cascade="save-update" access="field.camelcase" />
	</class>
	</hibernate-mapping>

view raw DocumentExtensionLink.hbm.xml hosted with ❤ by GitHub