LINQ-to-SQL

Well, I've started building my first application using LINQ over the past 2 weekends (and quite a few evenings), and am reusing / converting old code I had from NHibernate-1.2.1.4000 GA to LINQ.

Thus far, my experience with LINQ is that there are some huge wins over NHibernate. However, there are definitely some drawbacks as well. Here are the pros and cons I have encountered while moving from NHibernate to LINQ.

Pros:

1. Tight integration between your Data Model / Class Diagram (which is presented in Visual Studio .NET 2008 in the form of a DBML file that you can edit via drag-and-drop) and your model classes.

In Visual Studio .NET 2008, to create a new set of LINQ to SQL classes, you select "Add New Item", and select "LINQ to SQL Classes" from the Visual Studio Installed Templates.

This will create a DBML file, and corresponding model classes. It will also create your DataContext, which is analogous to a Hibernate Session.

All you have to do to create your model classes is open up the Server Explorer, make a connection to your database (the connection string is then stored in a Settings.setting file, which is used by the Application during runtime to connect the DataContext to the database, and begin dragging and dropping tables from the Server Explorer into your DBML file's workspace.

2. Strongly-typed Queries. Actually, this is a bit of a double-edged sword. On one hand, it's nice to have your query syntax integrated as part of the language and to have query syntax errors flagged at compile-time instead of runtime.

On the other hand, it makes it more difficult to do queries that are built during runtime.

With NHibernate HQL (and ADO.NET SqlCommand), the query is simply a string. Consequently, it is easy to build up this String dynamically during runtime, i.e.

if (profileSearch.FindUserFlag)
{
    whereClauseBuilder.Append("AND upper(user.user_name) like @userName ");
    parameters.Add(new SqlParameter("@userName", profileSearch.UserName.Trim().ToUpper() + "%"));
}

You don't have this dynamic ability to build up your query using LINQ.

I have read that dynamic query construction can be done via expression trees, but it sure is a lot more complicated then just doing an IF statement when appending where clauses to your StringBuilder.

3. Lazy-loading (termed "Delay Loading") on a per-property basis. This is something that I really wanted in NHibernate for the following reason. I do a left outer join from my User to Profile on a table. The Profile table has a column "About Me", which is of type NTEXT and has the potential to be very large. For something like a Profile Summary (this is a snapshot of the Profile that does not have the full details and definitely should not have "About Me", I do not wish to pull in a potentially large column).

4. Elegant syntax without a lot of scaffolding code in your DAO. Here is one of my DAO methods pre-LINQ conversion:

///
/// This method will retrieve StateProvince objects by language.
///
///
/// The language for which we wish to find
/// all State/Province definitions.
/// Strongly-typed List of StateProvince objects, ordered by
/// CountryCode and DisplayOrder.
public IList RetrieveStateProvincesByLanguage(String language)
{
    // Strongly-typed List of StateProvince objects.
    IList stateProvinces;
    ISession session = NHibSupport.SessionFactory.OpenSession();
    StringBuilder hql = new StringBuilder();
    // Define HQL Statement
    hql.Append("from StateProvince as stateProvince ");
    hql.Append("where stateProvince.Language = :language ");
    hql.Append("order by stateProvince.CountryCode,   stateProvince.DisplayOrder");
    IQuery query = session.CreateQuery(hql.ToString());
    query.SetParameter("language", language.ToLower(), NHibernateUtil.String);
    stateProvinces = query.List();
    log.Info("Found " + stateProvinces.Count + " State/Provinces for language '" + language + "'.");
    session.Close();
    return stateProvinces;
}

And here is the same DAO method after LINQ conversion:

///
/// This method will retrieve StateProvince objects by language.
///
///
/// The language for which we wish to find
/// all State/Province definitions.
/// Strongly-typed List of StateProvince objects, ordered by
/// CountryCode and DisplayOrder.
public IEnumerable RetrieveStateProvincesByLanguage(String language)
{
    PersonQDataContext dataContext = DataContextFactory.Instance.GetDataContext();
    return from stateProv in dataContext.StateProvinces
        where stateProv.Language == language
        orderby stateProv.CountryCode, stateProv.DisplayOrder
        select stateProv;
}

Cons:

1. From my research to-date, there is no way to update the data model based on table changes. If you add a new column to the table, you have to manually right-click on the class that represents the table, choose "Add Property", and add the property.

2. Does not convert common database column naming conventions to common C# Property conventions. I didn't think it would be that difficult to have the column about_me in my profile table to come in as Profile.AboutMe. However, it comes in with the column's actual name, so I have a property Profile.about_me. This is just a minor annoyance, but it means that every time I drop a table into the data model, I have to manually go through all the properties and name them something sane.

3. ReSharper does not currently support LINQ and C# 3.0 language constructs.