How to make a Type variable work with Linq to SQL?

Question

I'm trying to make a generic function of sorts, in some place of my code I have these lines

myDataContext dc = new myDataContext();
.
.
.(some code later)
.
Sucursal sucursal = dc.Sucursal.SingleOrDefault(s => s.Id == id);

which works wonderfully. Now, the problem comes when I try to make the "generic" form

public static void FindWithId<DataBaseTable>(Table<DataBaseTable> table, int id)
    where DataBaseTable : class
{                    
   DataBaseTable t = table.SingleOrDefault(s => s.GetType().GetMember("Id").ToString() == id.ToString());
}

when executing this line

FindWithId<Sucursal>(dc.Sucursal,01);

i get the following error

El método 'System.Reflection.MemberInfo[] GetMember(System.String)' no admite la conversión a SQL.

which is roughly translated to:

Method 'System.Reflection.MemberInfo [] GetMember (System.String)' does not support the conversion to SQL.

what can I do to make this work?

Thanks!

Update Solution

I was struggling to find a solution, until i came upon this thread, it gives a very thorough answer, but to my purpose i adapted it to this:

  public class DBAccess
{
    public virtual DataBaseTable GetById<DataBaseTable>(int id, Table<DataBaseTable> table) where DataBaseTable : class
    {
        var itemParameter = Expression.Parameter(typeof(DataBaseTable), "item");
        var whereExpression = Expression.Lambda<Func<DataBaseTable, bool>>
            (
            Expression.Equal(
                Expression.Property(
                    itemParameter,
                    "Id"
                    ),
                Expression.Constant(id)
                ),
            new[] { itemParameter }
            );
        return table.Where(whereExpression).Single();
    }
}

Hope it's useful to someone :P

Answer 1

You can't that way, because you're basically trying to use reflection methods in SQL: what's passed as parameter of SingleOrDefault() will be translated to SQL.

Side note : s.GetType().GetMember("Id") returns a value of type MemberInfo, and MemberInfo.ToString() isn't what you were looking for.

Answer 2

If you only want a generic method for getting the Id property, you could change

where DataBaseTable : class

To be something like

where DataBaseTable : IEntity

Where IEntity is an interface with an Id property on it which all of your entities could implement.

The reason you get the error you do is because it is trying to convert the reflection methods into SQL, which doesn't make any sense in SQL as there are no 'methods' on tables.