RSS 2.0 | Atom 1.0

Sign In


# Wednesday, September 05, 2007
Calling custom methods in LINQ-to-SQL

This was sparked by the issues raised by Rob Conery here. Basically, if you have some semi-complicated function that you need to apply to a LINQ-to-SQL query, how can you do it? This is somewhat covered by TomasP.NET: Building LINQ Queries at Runtime in C# and Joseph Albahari: Dynamically building LINQ expression predicates. I recommend those two articles; they're very good. I'm going to write a bit about it too. Some of it might be redundant, some of the ideas I took from those articles. The code is all mine.

So, let's say we want to get all accounts where the square root of the account ID is even. This will serve as our placeholder for a totally contrived example. Just calling our method in our LINQ query won't work because the LINQ-to-SQL code isn't going to know what to do with it. A method is just an opaque block of code with a name. Here's an example:

static void Main(string[] args) {
    MembersDataContext dc = new MembersDataContext();
    var q = dc.Accounts.Where(a => IsRightAccount(a));
static bool IsRightAccount(Account a) {
    return Math.Sqrt(a.AccountId) % 2 == 0;

This code crashes with: Unhandled Exception: System.NotSupportedException: Method 'Boolean IsRightAccount(ConsoleApplication1.Account)' has no supported translation to SQL. Which should be expected, as LINQ to SQL cannot know what goes on inside that method and thus can't translate it.

Let's change things to make IsRightAccount be a Func delegate (from a lambda expression):

static Func<Account, bool> IsRightAccount = a => Math.Sqrt(a.AccountId) % 2 == 0;

Now we get: Unhandled Exception: System.NotImplementedException: The method or operation is not implemented. At System.Data.Linq.SqlClient.QueryConverter.VisitInvocation(InvocationExpression invoke). That's a somewhat strange exception, as I'd expect it to be a bit more helpful. At any rate, I'd expect it to crash, because that is just a delegate to a method. It's still an opaque block of code.

Enter the magic Tree of Expressions: Expression<T>. As I mentioned in my last post, Expression Trees provide "introspection" (reflection against code). In the examples above, the lambda that calls IsRightAccount for the Where clause actually turned into an InvocationExpression that represents a call to the delegate provided. Hence me saying that it is "opaque". What we need is to make sure that our code (our IsRightAccount calculation) is visible as data. When it's visible as data, then LINQ-to-SQL can go and say "Oh, you want to take the square root of the account ID, mod it by 2, and see if that's zero… now THAT I can do in SQL".

Declaring an Expression Tree is really simple. First, make sure you import System.Linq.Expressions if you don't want to fully qualify the name. Then, declare your tree just like any lambda Func, except this time make the type Expression<MyFunc>:

static Expression<Func<Account, bool>> IsRightAccount = a => Math.Sqrt(a.AccountId) % 2 == 0;

We will also change our Where clause to accommodate the fact that we are not calling a method:

var q = dc.Accounts.Where(IsRightAccount);

And presto! Our program now shows a SQL conversion:

SELECT [t0].[AccountId], [t0].[Email]
FROM [dbo].[Accounts] AS [t0]
WHERE (SQRT(CONVERT(Float,[t0].[AccountId])) % @p0) = @p1

Things get a bit more complicated when we try to stack expressions together. As far as I know, we must create the Expression manually (using the Expression static methods); the compiler won't help out. If anyone knows a built-in way around this, please let me know. Otherwise, see the links at the top of this article for more information and other workarounds.

This also applies if you have complex logic that doesn't directly map to a item -> bool predicate expression. In those cases, you can still encapsulate most of your code by using the compiler to generate the bulk of the Expression, and then just wrap it with a bit of hand-created expression. In my LINQ to the CRUD article, the code attached uses this approach to generate queries for the select/delete commands. Again, I will note that TomasP has written expansions so you can just write myCoolExpression.Expand(arg) rather than building everything by hand.

If you know of any other links or work done in this area, I'm very interested in seeing other approaches. Thanks!

Wednesday, September 05, 2007 11:58:49 PM UTC  #    Comments [2]  |  Trackback

Saturday, September 08, 2007 12:24:02 AM UTC
Pingback from http://oakleafblog.blogspot.com/2007/09/linq-and-entity-framework-updates-for.html.
Saturday, September 08, 2007 8:22:11 AM UTC
Michael, I'm doing something very similar. Instead of extending the LINQ generated classes or their bases I'm wrapping with a business object instead:

public class BusinessBase<TEntity, TContext>


public class busCustomer<CustomerEntity,TimeTrakkerContext>

The business object holds the context and optionally manages disconnected operation so you can do either of these:

busCustomer Customer = new busCustomer();

CustomerEntity cust = Customer.Load(1);

... make changes

Customer.Save(); && Connected with SubmitChanges


Customer.Save(cust); && disconnected via .Attach

In many situations the connected approach actually works just fine and it is more efficient because it batches and there's no extra code that has to reattach entities behind the scenes.

I've found this works really well and in my case at least doesn't add any complexities since I already use an separate business layer anyway to remove all data access/LINQ data code from the front end anyway. Haven't had enough time using this stuff yet though to see all possible pitfalls though...

BTW, I had a real hard time getting this post to post. In fact, first try wiped out my post, the second try failed with missing postback options (I hit the back key after the first failure).
Please login with either your OpenID above, or your details below.
Home page

Comment (HTML not allowed)  

Enter the code shown (prevents robots):

Live Comment Preview