DECLARE @beginTime DateTime2
SET @beginTime = '2018-07-01T00:00:00'
DECLARE @endTime DateTime2
SET @endTime = '2018-08-01T00:00:00'
DECLARE @p1 NVarChar(4000) -- String
SET @p1 = N'8011'
SELECT
Sum([t3].[RechargeAmount]) as [c1],
[t1].[GoodsBrandCode],
Sum([t3].[RechargeQty]) as [c2]
FROM
[MemberTendRechargeOrderDetails] [t3]
left JOIN [GoodsInfo] [t1] on t3.GoodsCode=t1.GoodsCode
LEFT JOIN [MemberTendRechargeOrder][t2]
ON [t2].RechargeNo = [t3].RechargeNo
WHERE
[t2].[RechargeDate] > @beginTime AND [t2].[RechargeDate] < @endTime AND
[t2].[SaleEmpCode] = @p1
GROUP BY
[t1].[GoodsBrandCode]
i wirte this code
using (var Db = new HYWCRMContext(ConnectionString, loggerFactory))
{
Db.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
LinqToDBForEFTools.Implementation = new MyImplDefault();
var tempQry1 =
from gi in Db.GoodsInfo
from mtro in Db.MemberTendRechargeOrder
from mtrod in Db.MemberTendRechargeOrderDetails
.LeftJoin(m => m.RechargeNo == mtro.RechargeNo).LeftJoin(m => m.GoodsCode == gi.GoodsCode)
where mtro.RechargeDate > beginTime && mtro.RechargeDate < endTime
select new { mtro, mtrod,gi };
if (!string.IsNullOrWhiteSpace(goodsCategoryCode))
{
tempQry1 = tempQry1.Where(m => m.gi.GoodsCategoryCode.Substring(0, 2) == goodsCategoryCode);
}
if (!string.IsNullOrWhiteSpace(empCodes[2]))
{
tempQry1 = tempQry1.Where(m => m.mtro.SaleEmpCode == empCodes[2]);
}
var depositQry =
from a in tempQry1
group a.mtrod by a.gi.GoodsBrandCode into g
select new
{
SaleAmout = g.Sum(m => m.RechargeAmount),
GoodsBrandCode = g.Key,
SaleQnty = g.Sum(m => m.RechargeQty),
};
var list = depositQry.ToLinqToDB().ToList();
}
it's generated sql
DECLARE @beginTime DateTime2
SET @beginTime = '2018-07-01T00:00:00'
DECLARE @endTime DateTime2
SET @endTime = '2018-08-01T00:00:00'
DECLARE @p1 NVarChar(4000) -- String
SET @p1 = N'8011'
SELECT
Sum([t2].[RechargeAmount]) as [c1],
[t3].[GoodsBrandCode],
Sum([t2].[RechargeQty]) as [c2]
FROM
[GoodsInfo] [t3]
CROSS JOIN [MemberTendRechargeOrder] [t1]
LEFT JOIN (
SELECT
[m1].[RechargeAmount],
[m1].[RechargeQty],
[m1].[GoodsCode]
FROM
[MemberTendRechargeOrderDetails] [m1]
WHERE
[m1].[RechargeNo] = [t1].[RechargeNo]
) [t2] ON [t2].[GoodsCode] = [t3].[GoodsCode]
WHERE
[t1].[RechargeDate] > @beginTime AND [t1].[RechargeDate] < @endTime AND
[t1].[SaleEmpCode] = @p1
GROUP BY
[t3].[GoodsBrandCode]
LeftJoin shoud be declared exactly after recordset that needs to be joined. In your case it is not. You have to move LeftJoin to MemberTendRechargeOrder recordset.
i want .
i wirte this code
it's generated sql
and throw this exception
我该怎么写这部分代码。 How can I write this part of the code?