Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Expression Tree - Math.Max replacement

When I use expression trees to replace a method, such as Math.Max, it looks like it successfully replaces it in the expression tree. But when I go to use it in Entity Framework, it throws an exception about not supporting Math.Max for Entity Framework. But I am explicitly replacing it.

Does anyone know why? And a way to fix the code?


using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;

namespace ConsoleApplication1
{
    public static class CalculateDatabase
    {
        public static void Main(string[] args)
        {
            var calcs = GetCalculateToAmounts(GetTestItems(), 0.5m).ToList();
        }

        public static IQueryable<Item> GetTestItems()
        {
            var items = new List<Item>();
            items.Add(new Item()
            {
                DoNotItem = true,
                ReductionAmount = 2,
                PreviousDiscountAmount = 3,
                CurrentDiscountAmount = 10,
                CurrentAmount = 100,
                PreviousAmount = 50,
                CurrentBillAmount = 75
            });

            return items.AsQueryable();
        }

        public class Item
        {
            public bool DoNotItem { get; set; }
            public decimal ReductionAmount { get; set; }
            public decimal PreviousDiscountAmount { get; set; }
            public decimal CurrentDiscountAmount { get; set; }
            public decimal CurrentAmount { get; set; }
            public decimal PreviousAmount { get; set; }
            public decimal CurrentBillAmount { get; set; }
        }

        public static IQueryable<CalculateToAmount> GetCalculateToAmounts(this IQueryable<Item> entityItems, decimal percentage)
        {
            return entityItems.Select(CalculateAmountExpression(percentage));
        }

        public class CalcType
        { }

        public class CalculateToAmount
        {
            public CalcType CalcType { get; set; }
            public Item Item { get; set; }
            public decimal ItemAmount1 { get; set; }
            public decimal ItemAmount2 { get; set; }
            public decimal ItemAmount3 { get; set; }
            public decimal Bonus { get; set; }
            public decimal Discounts { get; set;  }
            public decimal Total { get; set; }
        }

        private static Expression<Func<Item, CalculateToAmount>> CalculateAmountExpression(this decimal percentage)
        {
            Expression<Func<Item, CalculateToAmount>> lambda = item => new CalculateToAmount()
            {
                Item = item,

                Bonus = item.DoNotItem
                    ? 0 
                    : item.CurrentBillAmount * (1 - percentage) + item.ReductionAmount,
                Discounts = item.PreviousDiscountAmount + item.CurrentDiscountAmount,
                Total = Math.Max(item.CurrentAmount + item.PreviousAmount, item.CurrentBillAmount)
            };

            var test = MathModifier.Modify(lambda);
            return test;
        }

        public class MathModifier : ExpressionVisitor
        {
            protected override Expression VisitMethodCall(MethodCallExpression node)
            {
                var isMinMethod = node.Method.Name.Equals("Min", StringComparison.InvariantCultureIgnoreCase);
                var isMaxMethod = node.Method.Name.Equals("Max", StringComparison.InvariantCultureIgnoreCase);

                if (!isMinMethod && !isMaxMethod)
                    return base.VisitMethodCall(node);

                var left = node.Arguments[0];
                var right = node.Arguments[1];

                var minMaxReplaceMethod =
                    isMinMethod
                        ? Expression.Condition(Expression.LessThan(left, right), left, right)
                        : Expression.Condition(Expression.GreaterThan(left, right), left, right);

                return minMaxReplaceMethod;
            }

            public static Expression<Func<TIn, TOut>> Modify<TIn, TOut>(Expression<Func<TIn, TOut>> expression)
            {
                var modifier = new MathModifier();
                return (Expression<Func<TIn, TOut>>)modifier.Visit(expression);
            }
        }
    }
}

If you call

var calcs = GetCalculateToAmounts(GetTestItems(), 0.5).ToList()

it will work. But if you replace the above GetTestItems() with an entity framework _dbContext.Items, it will not work.

To test this code, you would need to add Item structure to the EF project, make a migration, and push it into the database.

I wish I could make this less technical so this question could be answered by a wider variety of people. Hopefully the bounty is adequate for an the answer. If not, please pm me.

like image 468
TamusJRoyce Avatar asked Jul 30 '15 08:07

TamusJRoyce


1 Answers

I am running exactly your provided code with EF 6. The entity Item named Table1. The code runs successfully, the relpacement of Math.Max is successfully done.

Tracing the SQL with SQL Profiler it generates and sends the following SQL against the database:

exec sp_executesql N'SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[DoNotItem] AS [DoNotItem], 
[Extent1].[ReductionAmount] AS [ReductionAmount], 
[Extent1].[PreviousDiscountAmount] AS [PreviousDiscountAmount], 
[Extent1].[CurrentDiscountAmount] AS [CurrentDiscountAmount], 
[Extent1].[CurrentAmount] AS [CurrentAmount], 
[Extent1].[PreviousAmount] AS [PreviousAmount], 
[Extent1].[CurrentBillAmount] AS [CurrentBillAmount], 
CASE WHEN ([Extent1].[DoNotItem] = 1) THEN cast(0 as decimal(18)) ELSE ([Extent1].[CurrentBillAmount] * (cast(1 as decimal(18)) - @p__linq__0)) + [Extent1].[ReductionAmount] END AS [C1], 
[Extent1].[PreviousDiscountAmount] + [Extent1].[CurrentDiscountAmount] AS [C2], 
CASE WHEN (([Extent1].[CurrentAmount] + [Extent1].[PreviousAmount]) > [Extent1].[CurrentBillAmount]) THEN [Extent1].[CurrentAmount] + [Extent1].[PreviousAmount] ELSE [Extent1].[CurrentBillAmount] END AS [C3]
FROM [dbo].[Table1] AS [Extent1]',N'@p__linq__0 decimal(1,1)',@p__linq__0=0.5

Conclusion: The code you provided is working, the expression tree manipulation is working, your conditional logic in the tree is translated to a SQL CASE expression.

Possibly your EF test is not isolated carefully enough and the Max.Math related EF exception is coming from other part of the code and/or indirectly from a fired event, overload etc.

like image 135
g.pickardou Avatar answered Nov 14 '22 02:11

g.pickardou