the subtraction has no operation with three params

quan01994 commented 6 years ago

using (var db = new HYWCRMContext(ConnectionString))
{
    db.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;

    var grossProfitQry = from oi in db.OrderInfo
        from gi in db.GoodsInfo.LeftJoin(g => g.GoodsCode == oi.GoodsCode)
        where oi.OrderStatus == 0 && oi.MemberCardId == "0415955923793" && oi.SaleDate >= beginTime
        group new { oi, gi } by Sql.DatePart(Sql.DateParts.Month, oi.SaleDate) into g
        select new
        {
            Month = g.Key,
            A = g.Sum(m => m.oi.SaleWay == "A" ? m.oi.SaleMoney - m.oi.SaleQnty * Sql.Round(m.gi.SchemePrice, 2) : 0) 
                - g.Sum(m => m.oi.SaleWay == "B" ? m.oi.SaleMoney - Sql.Abs(m.oi.SaleQnty) * Sql.Round(m.gi.SchemePrice, 2) : 0) ?? 0
                - Sql.Abs(g.Sum(m => m.oi.SaleWay == "C" ? m.oi.SaleMoney - m.oi.SaleQnty * Sql.Round(m.gi.SchemePrice, 2) : 0)) ?? 0
        };
    var flowList = grossProfitQry.ToLinqToDB().ToList();
    ConsoleList(flowList);

    var tempgrossProfitQry = from oi in db.OrderInfo
        from gi in db.GoodsInfo.LeftJoin(g => g.GoodsCode == oi.GoodsCode)
        where oi.OrderStatus == 0 && oi.MemberCardId == "0415955923793" && oi.SaleDate >= beginTime
        group new { oi, gi } by Sql.DatePart(Sql.DateParts.Month, oi.SaleDate) into g
        select new
        {
            Month = g.Key,
            A = g.Sum(m => m.oi.SaleWay == "A" ? m.oi.SaleMoney - m.oi.SaleQnty * Sql.Round(m.gi.SchemePrice, 2) : 0) ??0,
            B = g.Sum(m => m.oi.SaleWay == "B" ? m.oi.SaleMoney - Sql.Abs(m.oi.SaleQnty) * Sql.Round(m.gi.SchemePrice, 2) : 0) ?? 0,
            C = Sql.Abs(g.Sum(m => m.oi.SaleWay == "C" ? m.oi.SaleMoney - m.oi.SaleQnty * Sql.Round(m.gi.SchemePrice, 2) : 0)) ?? 0
        };
    var temp = tempgrossProfitQry.ToLinqToDB().ToList();
    ConsoleList(temp);
}

result

It seems that in the first query, the subtraction has no operation.

quan01994 commented 6 years ago

A = g.Sum(m => m.oi.SaleWay == "A" ? m.oi.SaleMoney - m.oi.SaleQnty * Sql.Round(m.gi.SchemePrice, 2) : 0) 
                            - Sql.Abs(g.Sum(m => m.oi.SaleWay == "C" ? m.oi.SaleMoney - m.oi.SaleQnty * Sql.Round(m.gi.SchemePrice, 2) : 0)) ?? 0

if two, it's run

sdanyliv commented 6 years ago

@quan01994, better to show problematic sql and what do you expect.

quan01994 commented 6 years ago

first query For example, the result I want in August is 103.98-0-235.59=-131.61 (A-B-C)

but it's 103.98

quan01994 commented 6 years ago

The generated sql is the same

DECLARE @beginTime AS SQL_VARIANT;
SET @beginTime = NULL;

SELECT
    [t2].[c1] as [c11],
    Sum(CASE
        WHEN [t2].[SaleWay] = N'A'
            THEN [t2].[SaleMoney] - Convert(Decimal(29,10), [t2].[SaleQnty]) * Round([t2].[SchemePrice], 2)
        ELSE 0
    END) as [c2],
    Sum(CASE
        WHEN [t2].[SaleWay] = N'B'
            THEN [t2].[SaleMoney] - Convert(Decimal(29,10), Abs([t2].[SaleQnty])) * Round([t2].[SchemePrice], 2)
        ELSE 0
    END) as [c3],
    Sum(CASE
        WHEN [t2].[SaleWay] = N'C'
            THEN [t2].[SaleMoney] - Convert(Decimal(29,10), [t2].[SaleQnty]) * Round([t2].[SchemePrice], 2)
        ELSE 0
    END) as [c4]
FROM
    (
        SELECT
            DatePart(Month, [t1].[SaleDate]) as [c1],
            [t1].[SaleWay],
            [t1].[SaleMoney],
            [t1].[SaleQnty],
            [g1].[SchemePrice]
        FROM
            [OrderInfo] [t1]
                LEFT JOIN [GoodsInfo] [g1] ON [g1].[GoodsCode] = [t1].[GoodsCode]
        WHERE
            [t1].[OrderStatus] = 0 AND [t1].[MemberCardId] = N'0415955923793' AND
            [t1].[SaleDate] >= @beginTime
    ) [t2]
GROUP BY
    [t2].[c1]

sdanyliv commented 6 years ago

Show me problematic query. Not working. They should be not the same.

quan01994 commented 6 years ago

It's my program.cs

    internal class Program
    {
        private const string ConnectionString = "connectionString";
        private static void Main(string[] args)
        {
            var serviceProvider = BuildDi();
            var loggerFactory = serviceProvider.GetRequiredService<ILoggerFactory>();

            var beginTime = new DateTime(2018, 4, 1);

            using (var db = new HYWCRMContext(ConnectionString,loggerFactory))
            {
                db.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
                LinqToDBForEFTools.Implementation = new MyImplDefault();

                var grossProfitQry = from oi in db.OrderInfo
                    from gi in db.GoodsInfo.LeftJoin(g => g.GoodsCode == oi.GoodsCode)
                    where oi.OrderStatus == 0 && oi.MemberCardId == "0415955923793" && oi.SaleDate >= beginTime
                    group new { oi, gi } by Sql.DatePart(Sql.DateParts.Month, oi.SaleDate) into g
                    select new
                    {
                        Month = g.Key,
                        A = g.Sum(m => m.oi.SaleWay == "A" ? m.oi.SaleMoney - m.oi.SaleQnty * Sql.Round(m.gi.SchemePrice, 2) : 0) 
                            - g.Sum(m => m.oi.SaleWay == "B" ? m.oi.SaleMoney - Sql.Abs(m.oi.SaleQnty) * Sql.Round(m.gi.SchemePrice, 2) : 0) ?? 0
                            - Sql.Abs(g.Sum(m => m.oi.SaleWay == "C" ? m.oi.SaleMoney - m.oi.SaleQnty * Sql.Round(m.gi.SchemePrice, 2) : 0)) ?? 0
                    };
                var flowList = grossProfitQry.ToLinqToDB().ToList();
                ConsoleList(flowList);

                var tempgrossProfitQry = from oi in db.OrderInfo
                    from gi in db.GoodsInfo.LeftJoin(g => g.GoodsCode == oi.GoodsCode)
                    where oi.OrderStatus == 0 && oi.MemberCardId == "0415955923793" && oi.SaleDate >= beginTime
                    group new { oi, gi } by Sql.DatePart(Sql.DateParts.Month, oi.SaleDate) into g
                    select new
                    {
                        Month = g.Key,
                        A = g.Sum(m => m.oi.SaleWay == "A" ? m.oi.SaleMoney - m.oi.SaleQnty * Sql.Round(m.gi.SchemePrice, 2) : 0),
                        B = g.Sum(m => m.oi.SaleWay == "B" ? m.oi.SaleMoney - Sql.Abs(m.oi.SaleQnty) * Sql.Round(m.gi.SchemePrice, 2) : 0) ?? 0,
                        C = Sql.Abs(g.Sum(m => m.oi.SaleWay == "C" ? m.oi.SaleMoney - m.oi.SaleQnty * Sql.Round(m.gi.SchemePrice, 2) : 0)) ?? 0
                    };
                var temp = tempgrossProfitQry.ToLinqToDB().ToList();
                ConsoleList(temp);
            }
        }

        private static IServiceProvider BuildDi()
        {
            var services = new ServiceCollection();
            services.AddSingleton<ILoggerFactory, LoggerFactory>();
            services.AddSingleton(typeof(ILogger<>), typeof(Logger<>));
            services.AddLogging((builder) => builder.SetMinimumLevel(LogLevel.Trace));
            var serviceProvider = services.BuildServiceProvider();
            var loggerFactory = serviceProvider.GetRequiredService<ILoggerFactory>();
            //configure NLog
            loggerFactory.AddNLog(new NLogProviderOptions { CaptureMessageTemplates = true, CaptureMessageProperties = true });
            NLog.LogManager.LoadConfiguration("nlog.config");
            return serviceProvider;
        }

        public static void ConsoleList<T>(T list) where T : IList
        {
            foreach (var item in list)
            {
                var type = item.GetType();
                if (type.IsValueType)
                {
                    Console.WriteLine(item);
                }
                else
                {
                    Console.WriteLine(JsonConvert.SerializeObject(item));
                }
            }
        }
    }

log

2018/09/29 10:27:45.916|INFO|Entity Framework Core 2.1.2-rtm-30932 initialized 'HYWCRMContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: RowNumberPaging  |Microsoft.EntityFrameworkCore.Infrastructure|version=2.1.2-rtm-30932, contextType=HYWCRMContext, provider=Microsoft.EntityFrameworkCore.SqlServer, options=RowNumberPaging , EventId_Id=10403, EventId_Name=Microsoft.EntityFrameworkCore.Infrastructure.ContextInitialized, EventId=Microsoft.EntityFrameworkCore.Infrastructure.ContextInitialized
2018/09/29 10:27:47.107|INFO|BeforeExecute
--  SqlServer.2008
DECLARE @beginTime DateTime2
SET     @beginTime = '2018-04-01T00:00:00'

SELECT
    [t2].[c1] as [c11],
    Sum(CASE
        WHEN [t2].[SaleWay] = N'A'
            THEN [t2].[SaleMoney] - Convert(Decimal(29,10), [t2].[SaleQnty]) * Round([t2].[SchemePrice], 2)
        ELSE 0
    END) as [c2],
    Sum(CASE
        WHEN [t2].[SaleWay] = N'B'
            THEN [t2].[SaleMoney] - Convert(Decimal(29,10), Abs([t2].[SaleQnty])) * Round([t2].[SchemePrice], 2)
        ELSE 0
    END) as [c3],
    Sum(CASE
        WHEN [t2].[SaleWay] = N'C'
            THEN [t2].[SaleMoney] - Convert(Decimal(29,10), [t2].[SaleQnty]) * Round([t2].[SchemePrice], 2)
        ELSE 0
    END) as [c4]
FROM
    (
        SELECT
            DatePart(Month, [t1].[SaleDate]) as [c1],
            [t1].[SaleWay],
            [t1].[SaleMoney],
            [t1].[SaleQnty],
            [g1].[SchemePrice]
        FROM
            [OrderInfo] [t1]
                LEFT JOIN [GoodsInfo] [g1] ON [g1].[GoodsCode] = [t1].[GoodsCode]
        WHERE
            [t1].[OrderStatus] = 0 AND [t1].[MemberCardId] = N'0415955923793' AND
            [t1].[SaleDate] >= @beginTime
    ) [t2]
GROUP BY
    [t2].[c1]
 |LinqToDB|EventId_Id=1, EventId_Name=, EventId=1
2018/09/29 10:27:47.179|INFO|Query Execution Time (AfterExecute) : 00:00:00.0693244
 |LinqToDB|EventId_Id=2, EventId_Name=, EventId=2
2018/09/29 10:27:47.233|INFO|Total Execution Time (Completed): 00:00:00.3354490. Rows Count: 6.
 |LinqToDB|EventId_Id=3, EventId_Name=, EventId=3
2018/09/29 10:27:47.374|INFO|BeforeExecute
--  SqlServer.2008
DECLARE @beginTime DateTime2
SET     @beginTime = '2018-04-01T00:00:00'

SELECT
    [t2].[c1] as [c11],
    Sum(CASE
        WHEN [t2].[SaleWay] = N'A'
            THEN [t2].[SaleMoney] - Convert(Decimal(29,10), [t2].[SaleQnty]) * Round([t2].[SchemePrice], 2)
        ELSE 0
    END) as [c2],
    Sum(CASE
        WHEN [t2].[SaleWay] = N'B'
            THEN [t2].[SaleMoney] - Convert(Decimal(29,10), Abs([t2].[SaleQnty])) * Round([t2].[SchemePrice], 2)
        ELSE 0
    END) as [c3],
    Sum(CASE
        WHEN [t2].[SaleWay] = N'C'
            THEN [t2].[SaleMoney] - Convert(Decimal(29,10), [t2].[SaleQnty]) * Round([t2].[SchemePrice], 2)
        ELSE 0
    END) as [c4]
FROM
    (
        SELECT
            DatePart(Month, [t1].[SaleDate]) as [c1],
            [t1].[SaleWay],
            [t1].[SaleMoney],
            [t1].[SaleQnty],
            [g1].[SchemePrice]
        FROM
            [OrderInfo] [t1]
                LEFT JOIN [GoodsInfo] [g1] ON [g1].[GoodsCode] = [t1].[GoodsCode]
        WHERE
            [t1].[OrderStatus] = 0 AND [t1].[MemberCardId] = N'0415955923793' AND
            [t1].[SaleDate] >= @beginTime
    ) [t2]
GROUP BY
    [t2].[c1]
 |LinqToDB|EventId_Id=4, EventId_Name=, EventId=4
2018/09/29 10:27:47.374|INFO|Query Execution Time (AfterExecute) : 00:00:00.0049678
 |LinqToDB|EventId_Id=5, EventId_Name=, EventId=5
2018/09/29 10:27:47.386|INFO|Total Execution Time (Completed): 00:00:00.0125142. Rows Count: 6.
 |LinqToDB|EventId_Id=6, EventId_Name=, EventId=6
2018/09/29 10:27:47.386|DEBUG|'HYWCRMContext' disposed. |Microsoft.EntityFrameworkCore.Infrastructure|contextType=HYWCRMContext, EventId_Id=10407, EventId_Name=Microsoft.EntityFrameworkCore.Infrastructure.ContextDisposed, EventId=Microsoft.EntityFrameworkCore.Infrastructure.ContextDisposed

they are same

quan01994 commented 6 years ago

I know the reason. ?? lower precedence than - . Causing a change in semantics.

sdanyliv commented 6 years ago

@quan01994, can you try this in projection of the first quey?

A = Sql.AsSql(.....)

quan01994 commented 6 years ago

SELECT
    [t2].[c1] as [c11],
    Coalesce(Sum(CASE
        WHEN [t2].[SaleWay] = N'A'
            THEN [t2].[SaleMoney] - Convert(Decimal(29,10), [t2].[SaleQnty]) * Round([t2].[SchemePrice], 2)
        ELSE 0
    END) - Sum(CASE
        WHEN [t2].[SaleWay] = N'B'
            THEN [t2].[SaleMoney] - Convert(Decimal(29,10), Abs([t2].[SaleQnty])) * Round([t2].[SchemePrice], 2)
        ELSE 0
    END), 0 - Abs(Sum(CASE
        WHEN [t2].[SaleWay] = N'C'
            THEN [t2].[SaleMoney] - Convert(Decimal(29,10), [t2].[SaleQnty]) * Round([t2].[SchemePrice], 2)
        ELSE 0
    END)), 0) as [c2]
FROM
    (
        SELECT
            DatePart(Month, [t1].[SaleDate]) as [c1],
            [t1].[SaleWay],
            [t1].[SaleMoney],
            [t1].[SaleQnty],
            [g1].[SchemePrice]
        FROM
            [OrderInfo] [t1]
                LEFT JOIN [GoodsInfo] [g1] ON [g1].[GoodsCode] = [t1].[GoodsCode]
        WHERE
            [t1].[OrderStatus] = 0 AND [t1].[MemberCardId] = N'0415955923793' AND
            [t1].[SaleDate] >= @beginTime
    ) [t2]
GROUP BY
    [t2].[c1]

quan01994 commented 6 years ago

but i get error result.

quan01994 commented 6 years ago

var grossProfitQry = from oi in db.OrderInfo
                    from gi in db.GoodsInfo.LeftJoin(g => g.GoodsCode == oi.GoodsCode)
                    where oi.OrderStatus == 0 && oi.MemberCardId == "0415955923793" && oi.SaleDate >= beginTime
                    group new {oi, gi} by Sql.DatePart(Sql.DateParts.Month, oi.SaleDate)
                    into g
                    select new
                    {
                        Month = g.Key,
                        A = g.Sum(m => m.oi.SaleWay == "A"
                                ? m.oi.SaleMoney - m.oi.SaleQnty * Sql.Round(m.gi.SchemePrice, 2)
                                : 0)
                            - (g.Sum(m =>
                                m.oi.SaleWay == "B"
                                    ? m.oi.SaleMoney - Sql.Abs(m.oi.SaleQnty) * Sql.Round(m.gi.SchemePrice, 2)
                                    : 0) ?? 0)
                            - (Sql.Abs(g.Sum(m =>
                                m.oi.SaleWay == "C"
                                    ? m.oi.SaleMoney - m.oi.SaleQnty * Sql.Round(m.gi.SchemePrice, 2)
                                    : 0)) ?? 0)
                    };
                var flowList = grossPro

Will it be used??The expression in parentheses can solve the problem. but The generated sql is the same

sdanyliv commented 6 years ago

@quan01994, analyse yourself why SQL fails. I do not have your model, data and database to check what happened here.

If it is because bad parentnes, i will fix that easily.

quan01994 commented 6 years ago

My mistake

decimal? a = 1;
Console.WriteLine(a??0 - a ?? 0);  // result is 1

so ,Produced this error。

 A = Sql.AsSql(g.Sum(m => m.oi.SaleWay == "A"
                                ? m.oi.SaleMoney - m.oi.SaleQnty * Sql.Round(m.gi.SchemePrice, 2)
                                : 0)
                            - (g.Sum(m =>
                                m.oi.SaleWay == "B"
                                    ? m.oi.SaleMoney - Sql.Abs(m.oi.SaleQnty) * Sql.Round(m.gi.SchemePrice, 2)
                                    : 0) ?? 0)
                            - (Sql.Abs(g.Sum(m =>
                                m.oi.SaleWay == "C"
                                    ? m.oi.SaleMoney - m.oi.SaleQnty * Sql.Round(m.gi.SchemePrice, 2)
                                    : 0)) ?? 0))

it can generate correct sql and get correct result

A = Sql.AsSql(g.Sum(m => m.oi.SaleWay == "A"
                                ? m.oi.SaleMoney - m.oi.SaleQnty * Sql.Round(m.gi.SchemePrice, 2)
                                : 0)
                            - g.Sum(m =>
                                m.oi.SaleWay == "B"
                                    ? m.oi.SaleMoney - Sql.Abs(m.oi.SaleQnty) * Sql.Round(m.gi.SchemePrice, 2)
                                    : 0) ?? 0
                            - Sql.Abs(g.Sum(m =>
                                m.oi.SaleWay == "C"
                                    ? m.oi.SaleMoney - m.oi.SaleQnty * Sql.Round(m.gi.SchemePrice, 2)
                                    : 0)) ?? 0)

it can generate correct sql and get correct result , but Not the result I expected

linq2db / linq2db.EntityFrameworkCore

the subtraction has no operation with three params #10