ExtJSdeveloper

This question highlights my lack of knowledge in writing a generic method.

All tables in my database have an integer primary key called í░Idí▒. So I tried to enhance my DataContext class with a generic method to fetch a single row by the primary key. This is what I coded but the c# compiler objected, I am close to a solution

public partial class MyDataContext : System.Data.Linq.DataContext

{

public T FetchSingle<T>( int primaryKey )

{

return this.GetTable<T>().Single<T>( o => o.Id == primaryKey );

}

Desired usage:

Customer c = dbContext.FetchSingle<Customer>( keyValue );



Re: LINQ Project General Newbe generic troubles FetchSingle<T>( int primaryKey )

Joe Albahari

An interesting problem. The compiler complains, basically, because "o.ID" is a duck-typed expression. A workaround is to build the expression and query dynamically:

public T FetchSingle<T> (int primaryKey) where T : class
{
IQueryable<T> table = GetTable<T>();

ParameterExpression p = Expression.Parameter (typeof (T), "o");
MemberExpression member = Expression.PropertyOrField (p, "ID");

ConstantExpression pKey = Expression.Constant (primaryKey);
BinaryExpression comparison = Expression.Equal (member, pKey);

LambdaExpression lambda = Expression.Lambda (comparison, p);

MethodCallExpression methodCall =
Expression.Call (typeof (Queryable), "Single", new[] { table.ElementType }, table.Expression, lambda);

return (T) table.Provider.Execute (methodCall);
}

A second solution would be to use the "dynamic query" sample.

A third solution would be to define an interface with an "ID" property, and then use that as a generic constraint, i.e.:

public T FetchSingle<T>( int primaryKey ) where T : ITableWithID

This would make your example compile, although I'm not certain that it would actually run. (I'm not sure that LINQ to SQL can handle an expression binding to an interface member). Another headache with this approach is that you'd have to implement that interface in all your entity tables.

Regards
Joe







Re: LINQ Project General Newbe generic troubles FetchSingle<T>( int primaryKey )

Keith Farmer

As Joe hints in his third suggestion, C# generics are *not* the same as C++ templates. That is, generics are not just a fancy way to to search-and-replace code generation.

So the way member access works for generically-specified types (T, in your case) is that the least-interesting assumptions are made about the object, unless otherwise stated. The way you do so is with the "where" constraint, which informs the compiler about what APIs are available. In Joe's example, he constrained it to a hypothetical ITableWithID interface, which would (nominally) include a member named Id. Without this constraint, the compiler has no way of determining that T has any particular member beyond the most basic.

There's a set of articles on MSDN you may find useful: http://msdn2.microsoft.com/en-US/library/512aeb7t(vs.80).aspx






Re: LINQ Project General Newbe generic troubles FetchSingle<T>( int primaryKey )

ExtJSdeveloper

Thanks Joe, your first solution worked out of the box. A test loop showed that DataContext entity caching works as expected and the generic method could be called 4000 times a second with just a single select traced in the SQL Profiler.

Would it be possible to add query caching to a generic method implemented using the "where T : ClassWithID" model and simpler code My thought is a static dictionary with queries indexed by T's Type.Name.





Re: LINQ Project General Newbe generic troubles FetchSingle<T>( int primaryKey )

Keith Farmer

Why use Type.Name Just use an IDictionary and key off the type itself:

Code Snippet

// dictionary key is Type, value is a compiled query -- signature of compiled query depends on query

IDictionary<Type, ...> cachedQueries = new Dictionary<Type, ...>();

public T FetchSingle<T> (int primaryKey) where T : class
{
if (!cachedQueries.ContainsKey(typeof(T))

{

// create a compiled query using Joe's code

}


return (T) cachedQueries[typeof(T))](primaryKey);
}






Re: LINQ Project General Newbe generic troubles FetchSingle<T>( int primaryKey )

ExtJSdeveloper

Keith I take your point about keying the dictionary off T rather than T.Name, thankyou.

I did some profiling of Joe's routine and with the entity already cached the total runtime for 10,000 calls is 1,984ms. The cumulative figures shown below highlight that the final execute of the query and fetch from the cache accounts for just 12% of execution time (no DB access in this case).

These numbers emphasize the benefit of query caching but I cannot see how to rearrange the generic method so that the primary key value is passed into the prepared query at the final Execute() call. This is all too close to the LINQ wire for me.

public T Get<T>( int primaryKey ) where T : class

// Purpose: return dataContext.Customers.Single<Customer>( o => o.Id == id );

{

IQueryable<T> table = GetTable<T>();

ParameterExpression p = Expression.Parameter( typeof( T ), "o" );

MemberExpression member = Expression.PropertyOrField( p, "ID" );

ConstantExpression pKey = Expression.Constant( primaryKey );

48 ms BinaryExpression comparison = Expression.Equal( member, pKey );

600 ms LambdaExpression lambda = Expression.Lambda( comparison, p );

1765ms MethodCallExpression methodCall = Expression.Call( typeof( Queryable ),

"Single", new[] { table.ElementType }, table.Expression, lambda );

1984ms return (T)table.Provider.Execute( methodCall );

}





Re: LINQ Project General Newbe generic troubles FetchSingle<T>( int primaryKey )

Joe Albahari

The simplest way to achieve this is to cache the MethodCallExpressions as follows:

static Dictionary<Type, MethodCallExpression> _pKeyExpr = new Dictionary<Type, MethodCallExpression>();

public T FetchSingle<T> (int primaryKey) where T : class
{
IQueryable<T> table = GetTable<T>();

MethodCallExpression methodCall;
if (!_pKeyExpr.TryGetValue (typeof (T), out methodCall))
{
ParameterExpression p = Expression.Parameter (typeof (T), "o");
MemberExpression member = Expression.PropertyOrField (p, "ID");

ConstantExpression pKey = Expression.Constant (primaryKey);
BinaryExpression comparison = Expression.Equal (member, pKey);

LambdaExpression lambda = Expression.Lambda (comparison, p);

methodCall = Expression.Call (typeof (Queryable), "Single", new[] { table.ElementType }, table.Expression, lambda);

_pKeyExpr [typeof (T)] = methodCall;
}

return (T) table.Provider.Execute (methodCall);
}

For brevity, I've omitted the locking that you'd inevitably need on the dictionary. Tell me if you'd to see an example with locking in place.

Taking this a step further, in terms of performance, you could instead cache compiled queries:

static Hashtable _compiledPKeyQueries = new Hashtable();

public T FetchSingle<T> (int primaryKey) where T : class
{
var cc = _compiledPKeyQueries [typeof (T)] as Func<MyDataContext, int, T>;
if (cc != null) return cc (this, primaryKey);

IQueryable<T> table = GetTable<T>();

ParameterExpression p = Expression.Parameter (typeof (T), "o");
MemberExpression member = Expression.PropertyOrField (p, "ID");

ConstantExpression pKey = Expression.Constant (primaryKey);
BinaryExpression comparison = Expression.Equal (member, pKey);

LambdaExpression lambda = Expression.Lambda (comparison, p);

MethodCallExpression methodCall =
Expression.Call (typeof (Queryable), "Single", new[] { table.ElementType }, table.Expression, lambda);

ParameterExpression dataContextParam = Expression.Parameter (typeof (MyDataContext), "dataContext");
ParameterExpression pKeyParam = Expression.Parameter (typeof (int), "pKey");

Expression<Func<UserQuery, int, T>> compiledQueryExpr =
(Expression<Func<UserQuery, int, T>>) Expression.Lambda (methodCall, dataContextParam, pKeyParam);

cc = CompiledQuery.Compile (compiledQueryExpr);

_compiledPKeyQueries [typeof (T)] = cc;

return cc (this, primaryKey);
}

In the code in bold, we dynamically construct the following compiled query:

var cc = CompiledQuery.Compile (
(MyDataContext dataContext, int pKey) => dynamically constructed expression
);

and then cache cc in the Hashtable. (You could instead use a generic Dictionary<Type, object> although in this case I find Hashtable's lookup semantics easier to work with).

It would be interesting to compare the performance of each of these approaches.

Cheers
Joe

P.S. If you're new to LINQ and generics, this is jumping in at the deep end. Most LINQ code is nowhere near this hard!





Re: LINQ Project General Newbe generic troubles FetchSingle<T>( int primaryKey )

Joe Albahari

Another, simpler, option is to bypass LINQ altogether:

public T FetchSingle<T> (int primaryKey) where T : class
{
return ExecuteQuery<T> ("select * from " + typeof (T).Name + " where ID={0}", primaryKey).Single();
}

In this case, LINQ is perhaps more hassle than it's worth!

Joe.




Re: LINQ Project General Newbe generic troubles FetchSingle<T>( int primaryKey )

ExtJSdeveloper

Joe thankyou for your continued coding endeavors in the FetchSingle<>() function.

The performance of your stage-1 caching example was great, 10,000 iteration test reduced from 1984ms to 150ms. However two problems came to light.

1 - The second test run based on a new DataContext tripped up with an exception after the query from the cache was reused. The exception complained that items from another DataContext were being used.

2 - The second problem is more fundamental, it seems the primaryKey call from the first call gets embedded into the query so other key values are ignored on subsequent calls.

I tried the compiled query example but this did not compile because types UserQuery and CompiledQuery.Compile() could not be found. Anyhow the non cached example is ok at 0.2ms per call.





Re: LINQ Project General Newbe generic troubles FetchSingle<T>( int primaryKey )

Joe Albahari

Indeed - I see now that the first example makes no sense. The second example, however, with CompiledQuery, should work.

CompiledQuery is in System.Data.Linq. I presume you're using Beta 2

UserQuery should be MyDataContext (replace this with the name of your custom typed DataContext class).

Cheers
Joe





Re: LINQ Project General Newbe generic troubles FetchSingle<T>( int primaryKey )

Olmo

A little ussability point:

Instead of,

public partial class MyDataContext : DataContext

{

public T FetchSingle<T>( int primaryKey )

{

// whatever solution you all have found

}

}

wouldn't be better to write:

public static class TableExtensions

{

public static void GetByID<T>(this Table<T> table, int primaryKey ) where T : class
{
// whatever solution you all have found
}

}

So when you use the code instead of

db.FetchSingle<Person>(idPerson);

you use

db.Persons.GetById(idPerson);

It's easier to remember and better integrated with the whole DataContext, isn't it

Adding value

Olmo.