How to rewrite a method so that it could be executed in LINQ to Entities?

Question

I have the following helper method:

public static double CalculateKilosPerMeter(double diameter, double thickness)
{
    return (diameter - thickness) * thickness * Math.PI * 0.008;
}

I use it when creating an instance of a Model class for a specific entity.

return pt => new ViewablePipeTypeModel()
{
    Id = pt.Id,
    KilosPerMeter = Logic.CalculateKilosPerMeter(pt.Diameter, pt.Thickness)
};

When I execute the code above, I get the following exception:

 LINQ to Entities does not recognize the method 'Double CalculateKilosPerMeter(Double, Double)' method, and this method cannot be translated into a store expression

So, I suppose this is because EF requires an ExpressionFunc.

How can I convert my method so it runs under LINQ to Entities?

Have you tried expanding it inline? Ie putting the calculation directly in the LINQ expression instead of invoking the helper method? — Tormod, Mar 20 '15 at 19:37
Yes, that works, but I need to do the same calculation more than once that's why I wanted to separate it in a method. — Yulian, Mar 20 '15 at 19:42
@Tormod has right, the problem is EF doesn't know how to translate your method to an sql expression, so you could do that calculus inline, otherwise,if you want to call a custom method in the `Select`, then you need to call the `AsEnumerable` method before, this way you are going to work wiht Linq to Object, wich support that action. — ocuenca, Mar 20 '15 at 19:47
Have a look here: http://stackoverflow.com/questions/2042668/given-linq-to-entities-does-not-support-custom-methods-how-do-you-stay-dry — Maciej Los, Mar 20 '15 at 19:48
@octavioccl yeap, I'm aware of that, but I was wondering if there's way I can convert this to a Expression> and then invoke this thing somehow. — Yulian, Mar 20 '15 at 19:49
@Yulian, yes, it is possible, follow the link i provided in previous comment ;) — Maciej Los, Mar 20 '15 at 19:53
@MaciejLos I've read it. I created the method appropriately but then I don't know how to use it interchangeably, according to the code that I provided above. Please, give me some more clues — Yulian, Mar 20 '15 at 19:55
Another useful link: http://stackoverflow.com/questions/11164655/integrating-custom-method-into-linq-to-entities-query — Maciej Los, Mar 20 '15 at 20:11
I get the same error that user547794 described in his/her comment in the question you posted. — Yulian, Mar 20 '15 at 20:26
@Yulian, i posted an answer. Please, check it. More: http://stackoverflow.com/questions/4867100/workarounds-for-using-custom-methods-extension-methods-in-linq-to-entities — Maciej Los, Mar 20 '15 at 20:33

score 0 · Answer 1 · answered Mar 20 '15 at 23:55

You can obtain anonymous type records first with all the data needed, and then use AsEnumerable() to use regular LINQ to Objects.

var objs = ... pt => new
{
    Id = pt.Id,
    Diam = pt.Diameter,
    Thick = pt.Thickness
};

var res = objs.AsEnumerable().Select(p => new ViewablePipeTypeModel()
{
    Id = p.Id,
    KilosPerMeter = Logic.CalculateKilosPerMeter(p.Diam, p.Thick)
};

score 0 · Answer 2 · answered Mar 21 '15 at 00:13

Create a compiled query and move your logic into that. It will produce a function that you can use within your queries as if it was a regular function.

Func<MyDataContext, double, double, double> calculateKilosPerMeter =
    CompiledQuery.Compile((MyDataContext dc, double diameter, double thickness) =>
        (diameter - thickness) * thickness * Math.PI * 0.008
    );
var query =
    from pt in dc.PipeTypes
    where ...
    select new ViewablePipeTypeModel()
    {
        Id = pt.Id,
        KilosPerMeter = calculateKilosPerMeter(dc, pt.Diameter, pt.Thickness),
    };

Maciej Los · Answer 3 · 2015-03-20T20:37:42.593

Method:

public Expression<Func<PipeType, double>> KilosPerMeter
{
    return f=>(f.diameter - f.thickness) * f.thickness * Math.PI * 0.008;
}

Usage:

public void DoSomething()
{
  using(var vptm = new ViewablePipeTypeModel())
  {
      var qry = from pt in vptm
          select new
          {
            Id = pt.Id,
            KpM = pt.KilosPerMeter()
          };
  }
}

Note: i have no chance to test it.

I'd suggest to read this article: Calling Functions in LINQ to Entities Queries

How to rewrite a method so that it could be executed in LINQ to Entities?

3 Answers3