Wednesday, July 22, 2009

LINQ to SQL or NHibernate

So I’ve been trying to figure out which direction I wanted my team to go in as far as O/RMs go and I think I’ve decided. I’ve heard the only good things about Entity Framework are the portions taken from the LINQ to SQL team. Therefore, I spent some time playing with LINQ to SQL and definitely think it’s a great tool for quick and dirty projects. However, after spending a few days and actually using NHibernate in a major project…I love it. So far, we haven’t come across anything that it can’t do and after hearing some stories indirectly from Jeffrey Palermo I’m pretty sure we won’t. So on to the example.

The application I was needing to build was a simple information request form and I used LINQ to SQL first and wanted to plug in a NHibernate version later. Kind of like the Rob Conery & Jimmy Bogard challenge on the MVC Storefront.

Here’s my Core project: (Really small I know...needed something to get the point across)

image

I won’t go into all the details on the implementation, but the main thing I want to show is the difference in mapping and implementation of the repository. If you don’t know how to configure NHibernate, I recommend the NHibernate in Action book. As for LINQ to SQL, create a DBML and drag some tables over…DONE.

First I’ll show a few methods of IRequestRepository implemented with NHibernate and then the LINQ to SQL version.

public Core.Model.InformationRequest GetById(Guid id)
{
using (ISession session = NHibernateHelper.OpenSession())
return session.Get<Core.Model.InformationRequest>(id);
}
public IList<Core.Model.InformationRequest> GetAll()
{
using (ISession session = NHibernateHelper.OpenSession())
{
var records = session
.CreateCriteria(typeof(Core.Model.InformationRequest))
.List<Core.Model.InformationRequest>();
return records;
}
}

public IList<Core.Model.InformationRequest> GetAllCompleted()
{
using (ISession session = NHibernateHelper.OpenSession())
{
return session
.CreateQuery("from InformationRequest WHERE IsCompleted='true'")
.List<Core.Model.InformationRequest>();
}
}
public void Save(Core.Model.InformationRequest newRequest)
{
using (ISession session = NHibernateHelper.OpenSession())
using (ITransaction transaction = session.BeginTransaction())
{
session.Save(newRequest);
transaction.Commit();
}
}
Obviously I’m still learning NHibernate because we haven’t encapsulated anything or really figured out the best way to refactor it.

Here is the implementation of the same methods using LINQ to SQL.

image

public Core.Model.InformationRequest GetById(Guid id)
{
return dc.GetById(id);
}

public IList<Core.Model.InformationRequest> GetAll()
{
return dc.GetAll()
.ToList();
}

public IList<Core.Model.InformationRequest> GetAllCompleted()
{
return dc.GetAll()
.WithCompletionStatus(true)
.ToList();
}
public void Save(Core.Model.InformationRequest request)
{
using (var db = new OPIDataContext(_connstring))
{
var newrequest = RequestMap.GetDbRequestFrom(request);
db.InformationRequests.InsertOnSubmit(newrequest);

db.SubmitChanges();
}
}
With LINQ to SQL, I used a few filters and some mapping, but I’ll show those once I’ve shown the mapping file for NHibernate.
<?xml version="1.0" encoding="utf-8" ?>
<
hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
namespace="TPIAR.Core.Model"
assembly="TPIAR.Core">

<
class name="InformationRequest" table="[opi].[InformationRequest]">
<
id name="Id" column="RequestId">
<
generator class="guid.comb" />
</
id>
<
component class="Sample.Core.Model.Common.PersonName,Sample" name="Name">
<
property name="FirstName"/>
<
property name="LastName"/>
</
component>
<
property name="CompanyName" />
<
component class="Sample.Core.Model.Common.Address,Sample" name="Address">
<
property column="Address" name="StreetLine1"/>
<
property name="City"/>
<
property name="State"/>
<
property name="PostalCode"/>
</
component>
<
property name="TelephoneNumber"/>
<
property name="FaxNumber"/>
<
property name="EmailAddress"/>
<
property name="RequestSummary"/>
<
property name="RequestDate"/>
<
property name="CompletionDate"/>
<
property name="IsCompleted"/>
</
class>
</
hibernate-mapping>
With NHibernate, that’s it! Really easy once you know the syntax. Also, as a general rule, I like to write things from scratch when I’m learning so I fully understand what’s going on. So I won’t be using CodeSmith or some other similar tool.

Now for LINQ to SQL:
internal static InformationRequest GetById(this OPIDataContext dataContext, Guid Id)
{
var r = Enumerable.SingleOrDefault(dataContext.InformationRequests, x => x.RequestId == Id);

return new InformationRequest
{
Id = r.RequestId,
Name = new PersonName(r.FirstName, r.LastName),
CompanyName = r.CompanyName,
Address = new Address{City = r.City, PostalCode = r.PostalCode, State = r.State, StreetLine1 = r.Address},
TelephoneNumber = r.TelephoneNumber,
FaxNumber = r.FaxNumber,
EmailAddress = r.EmailAddress,
RequestSummary = r.RequestSummary,
RequestDate = r.RequestDate,
CompletionDate = r.CompletionDate,
IsCompleted = r.IsCompleted
};
}

internal static IQueryable<InformationRequest> GetAll(this OPIDataContext dataContext)
{
var requests = from r in dataContext.InformationRequests
select new InformationRequest
{
Id = r.RequestId,
Name = new PersonName(r.FirstName, r.LastName),
CompanyName = r.CompanyName,
Address =
new Address
{
City = r.City,
PostalCode = r.PostalCode,
State = r.State,
StreetLine1 = r.Address
},
TelephoneNumber = r.TelephoneNumber,
FaxNumber = r.FaxNumber,
EmailAddress = r.EmailAddress,
RequestSummary = r.RequestSummary,
RequestDate = r.RequestDate,
CompletionDate = r.CompletionDate,
IsCompleted = r.IsCompleted
};

return requests;
}

internal static DataAccess.InformationRequest GetDbRequestFrom(InformationRequest request)
{
var ir = new DataAccess.InformationRequest
{
RequestId = (request.Id == new Guid("99999999-9999-9999-9999-999999999999") ? Guid.NewGuid() : request.Id),
Address = request.Address.StreetLine1,
City = request.Address.City,
CompanyName = request.CompanyName,
CompletionDate = request.CompletionDate,
EmailAddress = request.EmailAddress,
FaxNumber = request.FaxNumber,
FirstName = request.Name.FirstName,
IsCompleted = request.IsCompleted,
LastName = request.Name.LastName,
PostalCode = request.Address.PostalCode,
RequestDate = request.RequestDate,
RequestSummary = request.RequestSummary,
State = request.Address.State,
TelephoneNumber = request.TelephoneNumber
};

return ir;
}
Here is the filter I used:
internal static IQueryable<InformationRequest> WithCompletionStatus(this IQueryable<InformationRequest> qry, bool status)
{
return qry
.Where(x => x.IsCompleted == status);
}
So there you go…LINQ to SQL & a NHibernate implementation that use exactly the same Core & UI. I also have a couple integration tests, which look like this:
[Test]
public void should_get_all_completed_using_linqtosql()
{
IRequestRepository rep = new SqlRequestRepository("connstring");
IList<InformationRequest> list = rep.GetAllCompleted();

Assert.AreEqual(2, list.Count);
}

[Test]
public void should_get_all_completed_using_nhibernate()
{
IRequestRepository rep = new NhRequestRepository();
IList<InformationRequest> list = rep.GetAllCompleted();

Assert.AreEqual(2, list.Count);
}
So I guess that’s about it. Please post comments or questions. I love feedback! Thanks!

kick it on DotNetKicks.com

blog comments powered by Disqus
Related Posts Plugin for WordPress, Blogger...