Linq to SQL - Writing a generic "GetObjectFromId" method

Dominic Pettifer

I'm trying to write a generic Ling to SQL GetObjectById() method for getting a data object from the database based on a primary key id value, so that instead of having to write... MyDataContext dataContext = new MyDataContext(); Product myProduct = dataContext.Products.SingleOrDefault<Product>(p => p.Id == productId); ...I instead write... Product myProduct = Product.FromId(productId); ...which I think is much cleaner. Now I could simply write individual 'FromId' methods in each mapped data class, but where's the fun in that. So I tried to write a more generic one size fits all method for doing this and came up with the following...

public abstract class BaseTable<T>
{
public static T FromId(int id)
{
MyDatabaseDataContext dataContext = DatabaseContextHelper.GetDatabaseContext();

    StringBuilder sql = new StringBuilder();
    sql.Append("SELECT ");

    MetaTable metaTable = dataContext.Mapping.MappingSource.GetModel(typeof(TopicDatabaseDataContext))
                                    .GetMetaType(typeof(T)).Table;

    foreach (MetaDataMember dm in metaTable.RowType.DataMembers)
    {
        if (dm.DbType != null)
        {
            sql.Append(dm.MappedName).Append(",");
        }
    }

    sql.Remove(sql.Length - 1, 1);

    sql.Append(" FROM ").Append(metaTable.TableName).Append(" WHERE Id = ").Append(id);

    return dataContext.ExecuteQuery<T>(sql.ToString()).FirstOrDefault<T>();
}

}

I need to ensure all the mapped data classes extend BaseTable<T> but this does seem to work, however, I was wondering if this was the best way of doing this. Wouold this method create any problems, and is there a more efficient way of doing it. I know this is probably overkill just so I can go from dataContext.Products.SingleOrDefault<Product>(p => p.Id == productId) to Product.FromId(productId) but it's just for a bit of fun, I enjoy the challenge. :) Many thanks!

Dominic Pettifer Blog: www.dominicpettifer.co.uk

Mark Churchill

Nothing wrong with that. Diamond Binding puts these static methods in our own class heirachy, and provides an optional base class that wraps them. Most people choose to use the base class for convenience (its easy, and the 'Inherit from ActiveRecordBase' defaults to checked in the UI) - but some people don't like to 'burn a base class' just to get a bunch of methods that don't really have to be there. Linq might be cool, but theres no point using it for a simple get by ID. To be honest we haven't had a lot of demand for it - OQL and Expressions seems to handle most queries people would need - and most queries are either .FindById(), .FindByProperty() or .FindAll()

Mark Churchill Director Dunn & Churchill Diamond Binding: Zero to Data Layer in 3 mins

Pete OHanlon

This is all well and good, but could you consider buying some advert space here at CodeProject? You seem to spend an awful lot of time telling us what your product does (and I assume you're not giving it away for free). If it's that good, pay for some space rather than posting replies to questions that (in effect) say don't use this, buy our product and use it instead.

Deja View - the feeling that you've seen this post before.

My blog | My articles

Mark Churchill

We do look at our traffic analytics fairly often and would advertise here if the projected ROI figures changed. At the moment we're happy letting codeproject folks download the free personal edition.

Mark Churchill Director Dunn & Churchill Diamond Binding: Zero to Data Layer in 3 mins