in sql case when then how to convert to linq

[quan01994]

I'm here again。。。。。

SELECT (ISNULL(SUM(CASE WHEN oi.SaleWay='A' THEN ABS(oi.SalePrice*oi.SaleQnty) END),0) - 
        ISNULL(SUM(CASE WHEN oi.SaleWay='B' THEN ABS(oi.SalePrice*oi.SaleQnty) END),0) 
 ) AS FlowAmount FROM OrderInfo oi
LEFT JOIN (SELECT GoodsCode,GoodsLevelId FROM GoodsInfo) gi ON oi.GoodsCode = gi.GoodsCode

If you have detailed documentation, I can go see。

[sdanyliv]

Nothing special here - use standard C# operators (p ? a : b) ?? 0

[quan01994]

This seems wrong,how to fix.

var qry = from orderinfo in db.OrderInfo
            from goodsInfo in db.GoodsInfo.LeftJoin(g => g.GoodsCode == orderinfo.GoodsCode)
            where goodsInfo.GoodsLevelId == 1 && orderinfo.OrderStatus == 0
            group orderinfo by orderinfo.SaleWay into g
            select g.Key == "A" ? g.Sum(m => m.SaleQnty * m.SalePrice) : 0 - (g.Key == "B" ? g.Sum(m => m.SaleQnty * m.SalePrice) : 0);

from orderinfo in db.OrderInfo
                    from goodsInfo in db.GoodsInfo.LeftJoin(g => g.GoodsCode == orderinfo.GoodsCode)
                    where goodsInfo.GoodsLevelId == 1 && orderinfo.OrderStatus == 0
                    group orderinfo by new {goodsInfo.GoodsLevelId, orderinfo.SaleWay}
                    into g
                    select Sql.Abs(g.Where(m => m.SaleWay == "A").Sum(m => m.SaleQnty * m.SalePrice)) -
                           Sql.Abs(g.Where(m => m.SaleWay == "B").Sum(m => m.SaleQnty * m.SalePrice));

it's wrong with m.SaleWay

[sdanyliv]

Yes it is wrong. But this not

var qry = from orderinfo in db.OrderInfo
          from goodsInfo in db.GoodsInfo.LeftJoin(g => g.GoodsCode == orderinfo.GoodsCode && g.GoodsLevelId == 1 && g.OrderStatus == 0)
          select Sql.Ext.Sum(orderinfo.SaleWay == "A" ? orderinfo.SaleQnty * orderinfo.SalePrice : 0).ToValue() ?? 0 - 
                 Sql.Ext.Sum(orderinfo.SaleWay == "B" ? orderinfo.SaleQnty * orderinfo.SalePrice : 0).ToValue() ?? 0;

[quan01994]

ok ,thank you . i know how to write

[quan01994]

  var qry = from orderinfo in db.OrderInfo
                          from goodsInfo in db.GoodsInfo.LeftJoin(g => g.GoodsCode == orderinfo.GoodsCode)
                          where goodsInfo.GoodsLevelId == 1 && orderinfo.OrderStatus == 0
                          group orderinfo by new { goodsInfo.GoodsLevelId } into g
                          select g.Sum(m => m.SaleWay == "A" ? Sql.Abs(m.SaleQnty * m.SalePrice) : 0) -
                                 g.Sum(m => m.SaleWay == "B" ? Sql.Abs(m.SaleQnty * m.SalePrice) : 0);

[quan01994]

emmm， It seems that your method is more concise

[sdanyliv]

Your where condition transforms left join into inner join.

[quan01994]

LeftJoin(g => g.GoodsCode == orderinfo.GoodsCode && g.GoodsLevelId == 1 && g.OrderStatus == 0) g.GoodsLevelId == 1 && g.OrderStatus == 0 This will result in different results

your linq convert to sql

SELECT
    SUM(CASE
        WHEN [t1].[SaleWay] = N'A'
            THEN Abs(Convert(Decimal(29,10), [t1].[SaleQnty]) * [t1].[SalePrice])
        ELSE 0
    END) as [c1],
    SUM(CASE
        WHEN [t1].[SaleWay] = N'B'
            THEN Abs(Convert(Decimal(29,10), [t1].[SaleQnty]) * [t1].[SalePrice])
        ELSE 0
    END) as [c2]
FROM
    [OrderInfo] [t1]
        LEFT JOIN [GoodsInfo] [g1] ON [g1].[GoodsCode] = [t1].[GoodsCode] AND [g1].[GoodsLevelId] = 1 AND [t1].[OrderStatus] = 0

my linq to convert

SELECT
    Sum(CASE
        WHEN [t1].[SaleWay] = N'A'
            THEN Abs(Convert(Decimal(29,10), [t1].[SaleQnty]) * [t1].[SalePrice])
        ELSE 0
    END) as [c1],
    Sum(CASE
        WHEN [t1].[SaleWay] = N'B'
            THEN Abs(Convert(Decimal(29,10), [t1].[SaleQnty]) * [t1].[SalePrice])
        ELSE 0
    END) as [c2]
FROM
    [OrderInfo] [t1]
        LEFT JOIN [GoodsInfo] [g1] ON [g1].[GoodsCode] = [t1].[GoodsCode]
WHERE
    [g1].[GoodsLevelId] = 1 AND [t1].[OrderStatus] = 0
GROUP BY
    [g1].[GoodsLevelId]

all transforms left join but will get different results

[sdanyliv]

So, why do you need left join here?

[quan01994]

emmm，i want the orderinfo table as the basic. Although the goodscode does not appear to be duplicated I seem to understand the wrong sentence. :）

[sdanyliv]

So closing for now.

[quan01994]

Is DATEPART supported?

SELECT DATEPART(month, SaleDate) AS SaleDate, COUNT(OrderNo) AS  Num,SUM(SalePrice*SaleQnty) AS Sale
FROM OrderInfo
GROUP BY DATEPART(month, SaleDate)

[sdanyliv]

Sql.DatePart

[quan01994]

I found it, but how to use it ?

[sdanyliv]

Sql.DatePart(Sq.DateParts.Month, orderinfo.SaleDate)

Clone our repository and you can find almost any sample in Unit Tests.

[quan01994]

Ok

[sdanyliv]

orderinfo.SaleDate.Month also should work.

[quan01994]

 var orderinfoQry = from orderinfo in db.OrderInfo
                                   where orderinfo.SaleDate > time
                                   select new
                                   {
                                       Month = Sql.DatePart(Sql.DateParts.Month, orderinfo.SaleDate),
                                       orderinfo.SaleWay,
                                       orderinfo.SaleQnty,
                                       orderinfo.SalePrice,
                                       orderinfo.OrderNo
                                   };

SELECT
    [t1].[SaleDate],
    [t1].[SaleWay],
    [t1].[SaleQnty],
    [t1].[SalePrice],
    [t1].[OrderNo]
FROM
    [OrderInfo] [t1]
WHERE
    [t1].[SaleDate] > @time1

Not in line with my expectations

[sdanyliv]

Sometimes linq2db decides to calculate some elements on the client side especially if it is projection. But we have function Sql.AsSql to force server side evaluation. Sql.AsSql(Sql.DatePart(Sql.DateParts.Month, orderinfo.SaleDate))

[quan01994]

OK， i know . I will go to write an introduction about linq2db.EntityFrameworkCore in Chinese.

[sdanyliv]

Valuable news. Good to know that sharing knowledge produced results ;)

[quan01994]

about Sql.Ext.RowNumber
I looked at the source code ,this function is not Implemented now i can only get index from list or a better way？

var flowQry = db.ReportEmployeeCommissionByMonth.OrderBy(m => m.TotalCommision).Select(m => new CommissionEmployeeOrderItem
                {
                    TotalCommision = m.TotalCommision,
                });
                var list = flowQry.ToList().Select((m, idx) => new CommissionEmployeeOrderItem
                {
                    TotalCommision = m.TotalCommision,
                    Order = idx + 1
                }).ToList();