CODEWITHSUNDEEP
c#.netlinqlinq-to-sql
EdF
EdF

Reputation: 549

Using primary keys in linqtoSQL

I am trying to write some generic linqtoSQL which performs operation on entities based on their primary keys. Some entities I am working with have composite primary keys.

Fundamentally I want to be able to do things such as:

if( PrimaryKey(foo) == PrimaryKey(bar) ) ...

or,

from oldEntity in oldTableOrCollection
join newEntity in newTableOrCollection
on PrimaryKeyOf(oldEntity) equals PrimaryKeyOf(newEntity)
select new {oldEntity, newEntity}

with only the requirement that the entities have primary keys.

Here's what I've found so far:

It's possible to do things such as

var query = from row in oneTableOfRows
            join otherRow in anotherTableOfRows on
            new { row.Column1, row.Column2 }
            equals
            new { otherRow.Column1, otherRow.Column2}
            select ...;

in which linqtosql will use the anonymous types for comparison (provided the property names match).

I can then abstract the method for selecting the columns on which to join to allow generic row types:

void DoQuery<RowType,KeyType>(Table<RowType> oneTableOfRows,
                         Table<RowType> anotherTableOfRows,
                         Func<RowType, KeyType> keySelector)
{
    var query = from row in oneTableOfRow
            join otherRow in anotherTableOfRows on
            keySelector(row)
            equals
            keySelector(otherRow)
            select ...;
}

...

Func<rowType, keyType> myKeySelector = row => new { row.Column1, row.Column2 };

DoQuery(table, otherTable, myKeySelector);

What I'm trying to move to now, is where the keySelector will select the primary key of any RowType. I am using a custom template based on http://www.codeplex.com/l2st4 to generate my entities (which itself is fairly similar to the default in VS). I'm hoping to ideally give each generated RowType the ability to select its primary key as gathered from the dbml. So far the ouptut looks like the following:

public interface IPrimaryKeyEntity<PrimaryKeyType>
{
    PrimaryKeyType PrimaryKey { get; }
}

//Here, Product is a table with composite primary key based on its ProductID and it's ProductPriceVersionID columns

//I've tried using both class and struct as the primary key type for entities
public class ProductPrimaryKey
{
    public Guid ProductID;
    public Guid ProductPriceVersionID;
}

public partial class Product : IPrimaryKeyEntity<ProductPrimaryKey>,
                               INotifyPropertyChanging, INotifyPropertyChanged
{
    #region IPrimaryKeyEntity Implementation
public ProductPrimaryKey PrimaryKey
    {
        get
        { return new ProductPrimaryKey()
                     {
                         ProductID = this.ProductID,
                         ProductPriceVersionID = this.ProductPriceVersionID
                     };
        }
    }
#endregion

    ...
    //Rest is mostly standard LinqToSql entity generation
}

Going back to the original aim, I can now compile the following for all my primary key entities:

from oldEntity in oldTableOrCollection
join newEntity in newTableOrCollection
on oldEntity.PrimaryKey equals newEntity.PrimaryKey
select new {oldEntity, newEntity}

However, runtime, I get the infamous [PrimaryKey] "has no supported translation to SQL" exception.

I understand that to translate to SQL it is necessary to use Expression's however I am pretty unfamiliar with Linq.Expressions and have had no progress yet trying to apply it to my situation...

If anyone can improve on what I've got so far or has a better method in general I'd be grateful to know....

Cheers

Upvotes: 3

Views: 789

Answers (1)

usr
usr

Reputation: 171178

The problem is that you are calling a function here:

join ... on oldEntity.PrimaryKey equals newEntity.PrimaryKey

A property-get is a function call. Linq to SQL does not know this function so it cannot translate it.

The only way to make this work is to build an expression that corresponds to this SQL:

        join otherRow in anotherTableOfRows on
        new { row.Column1, row.Column2 }
        equals
        new { otherRow.Column1, otherRow.Column2}

There is no other way, I can assure you. You can do it like this: First, you declare a custom tuple class:

class CustomTuple2<T1,T2>
{
 public T1 Item1 { get; set; }
 public T2 Item2 { get; set; }
}

You do this for all possible member counts (for example 8 or 16).

Let's look at how a join gets translated:

IQueryable<T1> t1 = ...; //table 1
IQueryable<T2> t2 = ...; //table 2
var joined = t1.Join(t2, _t1 => _t1.Key, _t2 => _t2.Key);

The lambda parameters are expressions. You need to build these two expressions using expression trees. This is the magic!

For example, the first lambda:

var param = Expression.Parameter(typeof(T1), "_t1");
var key = Expression.Property(param, "Key");
var lambda1 = Expression.Lambda(param, key);

Do the same for the second lambda. (Remember, that this was pseudo-code. I don't exactly recall how everything was called.)

Finally, invoke the Join:

var joined = typeof(Queryable).GetMethod("Join").Invoke(t1, t2, lambda1, lambda2);

So that is how you build a join at runtime! Please note, that all of this was pseudo-code. It will take you some research to figure the APIs out and make it work, but you can surely do it.

Also, if you have a composite key, you will need the mentioned tuple class to project the key members into.

Upvotes: 2

Related Questions