Convert LINQ to SQL to SQL Custom Method

Question

Is there a way to translate an expression into SQL for use with LINQ to SQL ?

For example, I have a method that compares two values.

Example:

MyComparer.Compare(value1, value2, ">") return value1 > value2 MyComparer.Compare(value1, value2, "=") return value1 == value2 MyComparer.Compare(value1, value2, "<=") return value1 <= value2 

And I need a query like:

 var list = from i in dataContext.items where MyComparer.Compare(i.value, someValue, "some operator") select ... 

This will not work, because obviously MyComparer does not translate to SQL.

This may be a twisted question, but how can I translate this method into SQL or is it possible?

Answer 1

The most pragmatic option can be made up:

 // build the core query var list = from i in dataContext.items // most of your query select ... // compose the value-filter if required switch(someOperator) { case "=": list = list.Where(row => row.value1 == row.value2); break; case ">": list = list.Where(row => row.value1 > row.value2); break; case "<": list = list.Where(row => row.value1 < row.value2); break; } // now use "list" 

If you need something reusable, you have to get the dirty building (and possibly parsing) of the expression trees ( System.Linq.Expression ). Not trivial.