Linq Select by PK?

Question

Is it possible to perform a LinQ select by PK dynamically ?

Something like that :

public T FindByPK(IEnumerable<T> table, T itemToFind)
{

}

T is a mapping from table (LinQ to SQL autogenerated).

I tryed using reflection but I don't know how to get the attribute "IsPrimaryKey" and then perform the LinQ because sometimes the PK contains more than one column.

EDIT after Answer by chridam

So, I can now do a GetByPK but it assumes that the PK is only one column...

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);
    }
}

I changed the code like this to be able to get all columns of the primary keys and also, the object pk has to be a row of the table (because I don't know what are the pk columns)

public static T GetByPk<T>(this DataContext context, T row) where T : class
        {
            var table = context.GetTable<T>();
            var mapping = context.Mapping.GetTable(typeof(T));

            var pkfield = mapping.RowType.DataMembers.Where(d => d.IsPrimaryKey);
            if (!pkfield.Any())
                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??), Expression.Property(param, row??)), param);
            return table.SingleOrDefault(predicate);
        }

So, I am stuck at Expression.Property who need only one property and not a group of many.

are you using EntityFramework? – Asif Mushtaq Oct 26 '12 at 08:52 — Asif Mushtaq, Oct 26 '12 at 08:52
No, I just use a dbml file ;) – Ronan Lamour Oct 26 '12 at 09:08 — Ronan Lamour, Oct 26 '12 at 09:08

score 3 · Accepted Answer · edited May 23 '17 at 11:44

3

You could try writing an extension method onto your DataContext object that facilitates the pulling of records from the database by their Primary Key, as outlined by Chris Sainty in his blog post LINQ to SQL: Generic Primary Key function :

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);
    }
}

Then call the method by way of:

MyDataContext db = new MyDataContext();
Product p = db.GetByPk<Product>(1);

You might also want to have a look at Denis Troller's answer as an alternative.

edited May 23 '17 at 11:44

Community

1
1

answered Oct 26 '12 at 08:52

chridam

100,957
23
236
235

1

Let's try this because Denis Troller's answer is from PK with only one column ^^ – Ronan Lamour Oct 26 '12 at 09:02
Your example is also for only one column as PK and I found a lot of examples like this but didn't find for when the PK contains more than one column =) – Ronan Lamour Oct 26 '12 at 09:07
You could modify the mapping object `pkfield`, instead of it returning `SingleOrDefault`, you could just return `mapping.RowType.DataMembers.Any(d => d.IsPrimaryKey)`; – chridam Oct 26 '12 at 09:26
Yes, that's what I did (`mapping.RowType.DataMembers.Where` then `if (pkfield.Any())` but then, I can't do `Expression.Property(param, pkfield.Name)` because it become a collection :) – Ronan Lamour Oct 26 '12 at 10:13
You're almost there, I think. Trying to figure out also how we can change the predicate object to map multiple columns :S – chridam Oct 26 '12 at 10:16
I think that it's harder than I figured because it look like all examples found on internet are presuming that primary key contain only one column. – Ronan Lamour Oct 26 '12 at 12:01
That could be a breakthrough if a solution was found. Let me get on this should time permits and let me know if you find any success but I think you should be in the right frame. – chridam Oct 26 '12 at 12:18
1

Ok, i did it ! I am going to look if I can do better and then post my solution here. – Ronan Lamour Oct 26 '12 at 15:52

Linq Select by PK?

1 Answers1