CODEWITHSUNDEEP
entity-frameworklinqentity-framework-core
Bob5421
Bob5421

Reputation: 9173

Entity Framework expression to SQL translation

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

Upvotes: 0

Views: 1714

Answers (3)

pjs
pjs

Reputation: 383

Maybe add a computed attribute to the table? If you want the calculations done in the db, and you might want to use the result as a filter, this makes it all very simple.

Upvotes: 0

Svyatoslav Danyliv
Svyatoslav Danyliv

Reputation: 27461

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)
    };

Upvotes: 2

Amy B
Amy B

Reputation: 110221

You could make a function to contain the ugly code that returns a conversion expression:

public Expression<Func<EntityA, MyViewModel>> ConvertEntityA()
{
  e => new MyViewModel
  {
    field1: e.field1,
    field2: e.field2,
    field3:
      e.field4==4 ? "four" :
      e.field4==3 ? "three" :
      ...
  };
}

  var query = db.EntityA.Select(ConvertEntityA());

Upvotes: 1

Related Questions