Linq2Sql and lambda returns "Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL"

Why does...

Func<IQueryable<CampaignCodePaths>> table = () => CampaignCodePaths;

Func<CampaignCodePaths, int> propertySelector = e => e.Id;

int key = 1;

Func<CampaignCodePaths, bool> whereSelector = e => propertySelect开发者_开发技巧or(e).Equals(key);

table().Where(whereSelector).FirstOrDefault();

...work but...

Func<IQueryable<CampaignCodePaths>> table = () => CampaignCodePaths;

Func<CampaignCodePaths, int> propertySelector = e => e.Id;

int key = 1;

table().Where(e => propertySelector(e).Equals(key)).FirstOrDefault();

...returns exception:

Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL

?

UPDATE

To clarify:

CampaignCodePath Get(Func<IQueryable<CampaignCodePaths>> table, Func<CampaignCodePaths, int> selector, int key)
{
    return table().Where(/*How to I create this expression from selector and key? */).FirstOrDefault();
}

...

Get(() => CampaignCodePaths, e => e.Id, 1)

Your first piece of code is performing all the filtering in .NET because you're using a delegate instead of an expression tree - it's not even trying to convert the filter into SQL. In other words, it's not that the first "works" and the second doesn't - it's that the first doesn't fail because it doesn't really try to do what you're expecting, whereas the second does.

The second form is calling Queryable.Where(IQueryable<T>, Expression<...>) whereas the first is calling Enumerable.Where(IEnumerable<T>, Func<...>).

If you change your code to:

Expression<Func<CampaignCodePaths, bool>> filter = e => e.Id.Equals(key);
table().Where(filter).FirstOrDefault();

then it should be fine.

EDIT: Responding to your edit, I think you want something like:

CampaignCodePath Get(Func<IQueryable<CampaignCodePaths>> table,
                     Expression<Func<CampaignCodePaths, int> selector>,
                     int key)
{
    Expression equal = Expression.Equal(selector, Expression.Constant(key));
    var lambda = Expression.Lambda<Expression<Func<CampaignCodePaths, bool>>
        (equal, selector.Parameters);
    return table().Where(lambda).FirstOrDefault();
}