Here is an extension method you can pop onto your DataContext object to facilitate the pulling of records from the database by their Primary Key.
public static class DataContextHelpers { public static T GetByPk<T>(this DataContext context, object pk) where T : class { var table = context.GetTable<T>(); var mapping = context.Mapping.GetTable(typeof(T)); var pkfield = mapping.RowType.DataMembers.SingleOrDefault(d => d.IsPrimaryKey); if (pkfield == null) throw new Exception(String.Format("Table {0} does not contain a Primary Key field", mapping.TableName)); var param = Expression.Parameter(typeof(T), "e"); var predicate = Expression.Lambda<Func<T, bool>>(Expression.Equal(Expression.Property(param, pkfield.Name), Expression.Constant(pk)), param); return table.SingleOrDefault(predicate); } }
MyDataContext db = new MyDataContext();
Product p = db.GetByPk<Product>(1);
So what does this code do, first we get a reference to the LINQ-to-SQL meta data store for the table we are querying, then pull out the Primary Key field for the table. It then builds a lambda expression tree that compares the Primary Key field of the parameter (that will be passed to the expression later) against the constant id passed to the function. This expression is then passed into LINQ-to-SQL where it can be decomposed and turned into SQL code. This is effectively the same as writing the lambda "e=>e.PK == id", except that we work out the name for PK at run-time.
I have attached this as an extension method on the DataContext, but if you are using a base class for your entities, or writing a generic business object, you should be able to manipulate this fairly easily to do as you wish.
I have only scratched the surface of what may be possible with this technique of building code through Expression Tress, there is bound to be some interesting work done in this area as people get more and more accustomed to the concept.
5 comments:
Sweet! Works great...
This is one of the best generic templates for manipulating LINQ data.
Most of the other examples out there use delegates instead of expressions, and the result is catastrophic: the entire table is loaded in memory and the filtering is done in your application, insted of sql server.
awesome...thanks
Post a Comment