Is there a generic way to get a Linq2SQL Entity by its primary key?

Question

Ok, the short version is that I have a Linq entity from a LinqDataSourceUpdateEventArgs and I need to handle the Update manually because MS was stupid.

I can get the Table object from the data context, which allows me to, well, this:

var newObj = e.NewObject;

var table = FormContext.GetTable(e.NewObject.GetType());

table.Attach(newObj, e.OriginalObject);

if (BuildingObject != null)
    BuildingObject(sender, new HeirarchicalBuildObjectEventArgs(newObj));


FormContext.SubmitChanges();

Unfortunately, I get the exception "Cannot add an entity with a key that is already in use."

Of course, the funny part is I get that on FormContext.SubmitChanges(), NOT on table.Attach()... which makes no sense to me, but whatever.

I'm thinking I need to actually get the object from the context, and attach using that instead of e.OriginalObject... OR, as a last resort, I need to get the original object and write a loop which copies the value of every property into the one I get from the data context.

Either way, I need to look up an object by it's primary key without knowing the type of the object. Is there a way to do that?

EDIT: Ok, took a look through .NET Reflector and I'm noticing that, among other things, LinqDataSourceView attaches the OLD data object and then copies all the values into it... but that apparently skips the associations. Going to try attaching the old object and copying values over, I guess...

The really funny part? I wrote a function to copy properties from one entity instance to another a long time ago, and it contains this comment:

//We can't copy associations, and probably shouldn't

Sometimes I wish my comments were more thorough...

EDIT EDIT: Ok, so once again the correct answer is: I asked the wrong question!

The correct code is:

        var newObj = e.NewObject;

        var table = FormContext.GetTable(e.NewObject.GetType());

        if (BuildingObject != null)
            BuildingObject(sender, new HeirarchicalBuildObjectEventArgs(newObj));

        table.Attach(newObj, e.OriginalObject);

        FormContext.SubmitChanges();


        e.Cancel = true;

I originally was trying to attach after BuildingObject, but got some other error and moved the attach statement in an effort to correct it. (I think because I was calling the wrong version of Attach. Or maybe I had the arguments reversed...)

Answer 1

Try something like the following to get the Entity by ID:

(Where TLinqEntity is the type of class that is generated by LinqToSql...and is a Generic Parameter in the class itself.)

    protected TLinqEntity GetByID(object id, DataContext dataContextInstance)
    {
        return dataContextInstance.GetTable<TLinqEntity>()
            .SingleOrDefault(GetIDWhereExpression(id));
    }

    static Expression<Func<TLinqEntity, bool>> GetIDWhereExpression(object id)
    {
        var itemParameter = Expression.Parameter(typeof(TLinqEntity), "item");
        return Expression.Lambda<Func<TLinqEntity, bool>>
            (
            Expression.Equal(
                Expression.Property(
                    itemParameter,
                    TypeExtensions.GetPrimaryKey(typeof(TLinqEntity)).Name
                    ),
                Expression.Constant(id)
                ),
            new[] { itemParameter }
            );
    }

    static PropertyInfo GetPrimaryKey(Type entityType)
    {
        foreach (PropertyInfo property in entityType.GetProperties())
        {
            var attributes = (ColumnAttribute[])property.GetCustomAttributes(typeof(ColumnAttribute), true);
            if (attributes.Length == 1)
            {
                ColumnAttribute columnAttribute = attributes[0];
                if (columnAttribute.IsPrimaryKey)
                {
                    if (property.PropertyType != typeof(int))
                    {
                        throw new ApplicationException(string.Format("Primary key, '{0}', of type '{1}' is not int",
                            property.Name, entityType));
                    }
                    return property;
                }
            }
        }
        throw new ApplicationException(string.Format("No primary key defined for type {0}", entityType.Name));
    }

---

This is the Update Method (thanks to Marc Gravell):

    public virtual void Update(DataContext dataContext, TLinqEntity obj)
    {
        // get the row from the database using the meta-model
        MetaType meta = dataContext.Mapping.GetTable(typeof(TLinqEntity)).RowType;
        if (meta.IdentityMembers.Count != 1)
            throw new InvalidOperationException("Composite identity not supported");
        string idName = meta.IdentityMembers[0].Member.Name;
        var id = obj.GetType().GetProperty(idName).GetValue(obj, null);

        var param = Expression.Parameter(typeof(TLinqEntity), "row");
        var lambda = Expression.Lambda<Func<TLinqEntity, bool>>(
            Expression.Equal(
                Expression.PropertyOrField(param, idName),
                Expression.Constant(id, typeof(int))), param);

        object dbRow = dataContext.GetTable<TLinqEntity>().Single(lambda);

        foreach (MetaDataMember member in meta.DataMembers)
        {
            // don't copy ID or timstamp/rowversion
            if (member.IsPrimaryKey || member.IsVersion) continue;
            // (perhaps exclude associations too)

            member.MemberAccessor.SetBoxedValue(
                ref dbRow, member.MemberAccessor.GetBoxedValue(obj));
        }
        dataContext.SubmitChanges();
    }

Answer 2

I often use implementation of generic repository from Sutekishop, open source e-commerce web shop built with asp.net mvc and L2S.
It has nice GetByID for generic type T, which relies on L2S attributes on model classes. This is the part that does the job:

public virtual T GetById(int id)
{
    var itemParameter = Expression.Parameter(typeof(T), "item");

    var whereExpression = Expression.Lambda<Func<T, bool>>
        (
        Expression.Equal(
            Expression.Property(
                itemParameter,
                typeof(T).GetPrimaryKey().Name
                ),
            Expression.Constant(id)
            ),
        new[] { itemParameter }
        );
     return GetAll().Where(whereExpression).Single();
}

and extension method that looks for primary key property; as you can see it expects "Column" attribute with "IsPrimaryKey" on class property. Extension methods:

public static PropertyInfo GetPrimaryKey(this Type entityType) {
    foreach (PropertyInfo property in entityType.GetProperties()) {
        if (property.IsPrimaryKey()) {
            if (property.PropertyType != typeof (int)) {
                throw new ApplicationException(string.Format("Primary key, '{0}', of type '{1}' is not int", property.Name, entityType));
            }
            return property;
        }
    }
    throw new ApplicationException(string.Format("No primary key defined for type {0}", entityType.Name));
} 

public static TAttribute GetAttributeOf<TAttribute>(this PropertyInfo propertyInfo) {
    object[] attributes = propertyInfo.GetCustomAttributes(typeof(TAttribute), true);
    if (attributes.Length == 0)
        return default(TAttribute);
    return (TAttribute)attributes[0];
}

public static bool IsPrimaryKey(this PropertyInfo propertyInfo) {
    var columnAttribute = propertyInfo.GetAttributeOf<ColumnAttribute>();
    if (columnAttribute == null) return false;
    return columnAttribute.IsPrimaryKey;
}

All credits for this code goes to Mike Hadlow! Whole implementation can be found in sutekishop source