LINQ to SQL translation to SQL of custom method

c#, linq-to-sql

Solution

The most pragmatic option may be compositon:

// 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 re-usable, you're going to have to get your hands dirty building (and probably parsing) expression-trees (`System.Linq.Expression`). Not trivial.

Problem

Is there a way to translate an expression to SQL to 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 would like a query like: ``` var list = from i in dataContext.items where MyComparer.Compare(i.value, someValue, "some operator") select ... ``` This won't work because, obviously, `MyComparer` doesn't translate to SQL. Maybe this is a twisted question, but how can I translate this method to SQL or is this possible?

Original source