How do I tell linq2db how to translate a given expression, ie Split(char) into SQL when it does not know how to do so?

Question

I am using linq2db and while it works well enough for most CRUD operations I have encountered many expressions that it just cannot translate into SQL.

It has gotten to the point where unless I know in advance exactly what kinds of expressions will be involved and have successfully invoked them before, I am worried that any benefit derived from linq2db will be outweighed by the cost of trying to find and then remove (or move away from the server side) the offending expressions.

If I knew how to tell linq2db how to parse an Expression<Func<T,out T>> or whatnot into SQL whenever on an ad-hoc, as-it-is-needed basis, then I would be much more confident and I could do many things using this tool.

Take, for instance, String.Split(char separator), the method that takes a string and a char to return a string[] of each substring between the separator.

Suppose my table Equipment has a nullable varchar field Usages that contains lists of different equipment usages separated by commas.

I need to implement IList<string> GetUsages(string tenantCode, string needle = null) that will give provide a list of usages for a given tenant code and optional search string.

My query would then be something like:

var listOfListOfStringUsages =
    from et in MyConnection.GetTable<EquipmentTenant>()
    join e in MyConnection.GetTable<Equipment>() on et.EquipmentId = e.EquipmentId
    where (et.TenantCode == tenantCode)
    where (e.Usages != null)
    select e.Usages.Split(','); // cannot convert to sql here

var flattenedListOfStringUsages = 
    listOfListOfStringUsages.SelectMany(strsToAdd => strsToAdd)
                            .Select(str => str.Trim())
                            .Distinct();

var list = flattenedListOfStringUsages.ToList();

However, it would actually bomb out at runtime on the line indicated by comment.

I totally get that linq2db's creators cannot possibly be expected to ship with every combination of string method and major database package.

At the same time I feel as though could totally tell it how to handle this if I could just see an example of doing just that (someone implementing a custom expression).

So my question is: how do I instruct linq2db on how to parse an Expression that it cannot parse out of the box?

Answer 1

A few years ago I wrote something like this:

public class StoredFunctionAccessorAttribute : LinqToDB.Sql.FunctionAttribute
{
    public StoredFunctionAccessorAttribute()
    {
        base.ServerSideOnly = true;
    }

    // don't call these properties, they are made private because user of the attribute must not change them
    // call base.* if you ever need to access them
    private new bool ServerSideOnly { get; set; }
    private new int[] ArgIndices { get; set; }
    private new string Name { get; set; }
    private new bool PreferServerSide { get; set; }

    public override ISqlExpression GetExpression(System.Reflection.MemberInfo member, params ISqlExpression[] args)
    {
        if (args == null)
            throw new ArgumentNullException("args");

        if (args.Length == 0)
        {
            throw new ArgumentException(
                "The args array must have at least one member (that is a stored function name).");
        }

        if (!(args[0] is SqlValue))
            throw new ArgumentException("First element of the 'args' argument must be of SqlValue type.");

        return new SqlFunction(
            member.GetMemberType(),
            ((SqlValue)args[0]).Value.ToString(),
            args.Skip(1).ToArray());
    }
}

public static class Sql
{
    private const string _serverSideOnlyErrorMsg = "The 'StoredFunction' is server side only function.";

    [StoredFunctionAccessor]
    public static TResult StoredFunction<TResult>(string functionName)
    {
        throw new InvalidOperationException(_serverSideOnlyErrorMsg);
    }

    [StoredFunctionAccessor]
    public static TResult StoredFunction<TParameter, TResult>(string functionName, TParameter parameter)
    {
        throw new InvalidOperationException(_serverSideOnlyErrorMsg);
    }
}

...

[Test]
public void Test()
{
    using (var db = new TestDb())
    {
        var q = db.Customers.Select(c => Sql.StoredFunction<string, int>("Len", c.Name));
        var l = q.ToList();
    }
}

(and of course you can write your wrappers around Sql.StoredFunction() methods to get rid of specifying function name as a string every time)

Generated sql (for the test in the code above):

SELECT
    Len([t1].[Name]) as [c1]
FROM
    [dbo].[Customer] [t1]

PS. We use linq2db extensively in our projects and completely satisfied with it. But yes, there is a learning curve (as with almost everything serious we learn) and one needs to spend some time learning and playing with the library in order to feel comfortable with it and see all the benefits it can give.

Answer 2

Try listOfListOfStringUsages.AsEnumerable(). It will enforce executing SelectMany on client side.

UPDATE:

I used the following code to reproduce the issue:

var q =
    from t in db.Table
    where t.StringField != null
    select t.StringField.Split(' ');

var q1 = q
    //.AsEnumerable()
    .SelectMany(s => s)
    .Select(s => s.Trim())
    .Distinct()
    .ToList();

It's not working. But if I uncomment .AsEnumerable(), it works just fine.