EF Core - Custom DBFunction - Usage with Linq Dynamic

Question

I have a simple DbFunction to convert a date string column (mm-dd-yyyy) to datetime before ordering. The DbFunction is set up in the DatabaseContext class as follows.

[DbFunction("ConvertToDateTime", "dbo")]
public static DateTime? ConvertToDateTime(string? dateString)
{
    throw new NotSupportedException();
}

This is working as expected. I can now order like

query.OrderBy(x => DatabaseContext.ConvertToDateTime(x.CustomFields.Field01))

I also need to incorporate Linq.Dynamic here. But, I can't figure out how. I need something like

query.OrderBy($"DatabaseContext.ConvertToDateTime(x.CustomFields.Field{colNumber})")

Is this possible?

I tried adding a CustomTypeProvider and DynamicLinqType attribute on DatabaseContext class but no avail.

public class MyCustomTypeProvider : DefaultDynamicLinqCustomTypeProvider
{
    public MyCustomTypeProvider() : base() { }

    public override HashSet<Type> GetCustomTypes()
    {
        var types = base.GetCustomTypes();
        types.Add(typeof(DatabaseContext));
        return types;
    }
}

I managed to do it without Linq.Dynamic by building the lambda expression dynamically

// building expression: x => DatabaseContext.ConvertToDateTime(x.CustomFields.Field##)
var param = Expression.Parameter(typeof(Entity), "x");
var member = Expression.Property(Expression.Property(param, nameof(Entity.CustomFields)), "Field" + colNumber); // colNumber = "00" to "99"
var method = typeof(DatabaseContext).GetMethod(nameof(DatabaseContext.ConvertToDateTime), [typeof(string)]);
var call = Expression.Call(method!, member);
var lambda = Expression.Lambda<Func<Entity, DateTime?>>(call, param);

if (first)
    query = order.Descending
        ? query.OrderByDescending(lambda)
        : query.OrderBy(lambda);
else
    query = order.Descending
        ? query.ThenByDescending(lambda)
        : query.ThenBy(lambda);

But, this feels so cumbersome and unreadable. This would have been so much easier if I could manage it with Linq.Dynamic.

convert a date string column (mm-dd-yyyy) that's not a date string column. That's a critical design bug. You have no idea what that string field contains, you only hope. You have no idea what 04-07-2025 means - July 4th or April 7th? Fix the critical bug instead of trying to cover it up. The SQL Server provider doesn't have date parsing functions for a reason — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 29 at 10:19
BTW Linq.Dynamic is a third-party library, not part of EF Core at all. It won't know about your custom function. If you can't fix the bad column type (eg if it already contains suspicious data) you can create a view or a computed column that converts the text into a date column. You don't need a custom function, TRY_CONVERT(date,thatField,110) should work. You can index computed columns and even views. You can't index custom function cals. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 29 at 10:27
This is an existing database that I have no control over. The columns can be date, number, or text dynamically and once the column type configured, they are being validated through code. So we can assume data is always in 'mm-dd-yyyy' format if not null. TRY_CONVERT(DATETIME2, @dateString) is what the db function does. — FLash
– FLash, Commented Oct 29 at 10:31
Until someone enters data using a different locale than the one you hoped. You can't assume, you can only hope. Even TRY_CONVERT(DATETIME2, @dateString) depends on having the correct locale because it doesn't specify the style 110. In any case you were able to add a function. Can you add a view as well? That would be a better solution. If you're really certain about the contents you can extract the substrings and order by these instead of trying to parse. Substring is mapped out of the box. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 29 at 10:55
Are you trying to get the code from this GH issue to work? I haven't found anything else that shows dynamic database function calls in the repo. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 29 at 11:19

FLash · Accepted Answer · 2025-10-29 12:23:37Z

2

A config was restricting the order-by to property or field. Just needed to set RestrictOrderByToPropertyOrField = false; along with the CustomTypeProvider.

var config = new ParsingConfig
{
    RestrictOrderByToPropertyOrField = false, // set this as false
    CustomTypeProvider = new MyCustomTypeProvider()
};

// colNumber = "00" to "99"
string expr = $"DatabaseContext.ConvertToDateTime(CustomFields.Field{colNumber})) {(order.Descending ? "DESC" : "ASC")}";
query = first ? query.OrderBy(config, expr) : query.ThenBy(config, expr);

answered Oct 29 at 12:23

FLash

80412 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

EF Core - Custom DBFunction - Usage with Linq Dynamic

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related