Look at this IQueryable:

var query = from e in db.EntityA
            select new MyViewModel 
                       {
                           field1: e.field1,
                           field2: e.field2,
                           field3: (e.field4 == 4 ? "four" : e.field4 == 3 : "three":....)
                       }

I want the field3 calculation to be made in SQL, because I may filter or order on this field.

At this step, it works fine but the code is ugly.

Here is what I want to do:

public String SomeFunction(EntityA e)
{
    if (e.field4 == 4)
    {
       return "four";
    }
    ...
}

var query = from e in db.EntityA
            select new MyViewModel 
                       {
                           field1: e.field1,
                           field2: e.field2,
                           field3: SomeFunction(e)
                       }

It does not work because EF is not able to translate my function to a SQL expression.

My question is: how can I help EF to do that ?

I need to use this expression in many queries. So I have tried this:

public String SomeFunction(EntityA e)
{
    return (e.field4 == 4 ? "four" : e.field4 == 3 : "three":.... );
}

I don’t why it does not work because it is exactly the same expression!

Other question: is there a way for me to create EF "add-ons"?

Thanks a lot for your help

>Solution :

You cannot do that elegantly without third party extensions. I would propose LINQKit for such task

It needs just configuring DbContextOptions:

builder
    .UseSqlServer(connectionString)
    .WithExpressionExpanding(); // enabling LINQKit extension

Write helper function:

public static class MyExensions
{
    [Expandable(nameof(SomeFunctionImpl))]
    public static string SomeFunction(int value)
        => throw new InvalidOperationException();

    private static Expression<Func<int, string>> SomeFunctionImpl()
    {
        return value => 
            value == 4 ? "four" : 
            value == 3 ? "three" : .... ;
    }
}

And usage in LINQ Queries:

var query = 
    from e in db.EntityA
    select new MyViewModel 
    {
        field1 = e.field1,
        field2 = e.field2,
        field3 = MyExensions.SomeFunction(e.field4)
    };