[私人小抄] LinQ 相關
var items =(from OT in OrderTag
select new
{
OrderTagId = OT.Guid,
Name = OT.Name,
UseCount = (
from OTM in OrderTagMapping
where OTM.OrderTagId == OT.Guid
select OTM.OrderTagId
).Count()
}).ToList().Dump();
SELECT
1 AS [C1],
[Project1].[Guid] AS [Guid],
[Project1].[Name] AS [Name],
[Project1].[C1] AS [C2]
FROM ( SELECT
[Extent1].[Guid] AS [Guid],
[Extent1].[Name] AS [Name],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[OrderTagMapping] AS [Extent2]
WHERE [Extent2].[OrderTagId] = [Extent1].[Guid]) AS [C1]
FROM [dbo].[OrderTag] AS [Extent1]
) AS [Project1]
複雜的LinQ語法
var productList =
(from PB in db.Travel_ProductBaseInfo
join PSM in db.Travel_ProductSupplierMapping
on new { ProductId = PB.Guid, SupplierId = supplierId, Status = PB.Status } equals new { ProductId = PSM.ProductId, SupplierId = PSM.SupplierId, Status = "Y" }
join PA in db.Travel_ProductAdvInfo
on PB.Guid equals PA.ProductId
join PS in db.Travel_ProductSpecMapping
on PB.Guid equals PS.ProductId
join PT in db.Travel_ProductType
on PS.Guid equals PT.ProductSpecId
//where PB.CountryFilterId != tw
select new ProductInfoBySyncPriceDto
{
ProductId = PB.Guid,
ProductName = PB.Name,
SupplierSaleStartDate = PA.SupplierSaleStartDate,
ProductSpecId = PS.Guid,
ProductTypeId = PT.Guid,
SupplierProductId = PA.SupplierProductId,
SupplierSpecId = PS.SupplierSpecId,
SupplierTypeId = PT.SupplierTypeId
}
).ToList();
//1.先找出提高價格的ProductTypeId
//2.再找出Spec跟Product
//3.取得最低 提高價格B2C 若比 SpecBasicPrice 高 就是異常
var list =
(from SSM in (
from GCD in (
from CD in db.Travel_ProductTypeClendarDtail
where CD.IsOrigLossMoney == true
group CD by CD.ProductTypeId into g
select new { ProductTypeId = g.Key, B2C = g.Min(x => x.B2bPrice + x.Profit) }
)
join T in db.Travel_ProductType
on GCD.ProductTypeId equals T.Guid
join SM in db.Travel_ProductSpecMapping
on T.ProductSpecId equals SM.Guid
join TM in db.Travel_ProductTypeMapping
on GCD.ProductTypeId equals TM.ProductTypeId
join PB in db.Travel_ProductBaseInfo
on TM.ProductId equals PB.Guid
where PB.Status == "Y" && SM.Status == "Y" && SM.IsSoldOut == "N"
select new { SpecGuid = SM.Guid, SpecBasicPrice = SM.SpecBasicPrice, B2C = GCD.B2C, ProductId = PB.Guid, ProductBasicPrice = PB.BasicPrice }
).ToList()
group SSM by SSM.SpecGuid into GSM
select new OrigLossMoneyProductAndPriceIssueDto
{
ProductId = GSM.FirstOrDefault().ProductId,
SpecId = GSM.Key,
SpecBasicPrice = GSM.Min(x => x.SpecBasicPrice),
B2C = GSM.Min(x => x.B2C),
ProductBasicPrice = GSM.FirstOrDefault().ProductBasicPrice
}
).ToList()
.Where(x => x.B2C > x.SpecBasicPrice).ToList();
//1.先找出提高價格的ProductTypeId
//2.再找出Spec跟Product
//3.取得最低 提高價格B2C 若比 SpecBasicPrice 高 就是異常
var list =
(from SSM in (
from PB in db.Travel_ProductBaseInfo
join TM in db.Travel_ProductTypeMapping
on PB.Guid equals TM.ProductId
join T in db.Travel_ProductType
on TM.ProductTypeId equals T.Guid
join SM in db.Travel_ProductSpecMapping
on T.ProductSpecId equals SM.Guid
join GCD in (
from CD in db.Travel_ProductTypeClendarDtail
where CD.IsOrigLossMoney == true
group CD by CD.ProductTypeId into g
select new { ProductTypeId = g.Key, B2C = g.Min(x => x.B2bPrice + x.Profit) }
)
on T.Guid equals GCD.ProductTypeId
where PB.Guid == productId && SM.Status == "Y" && SM.IsSoldOut == "N"
select new { SpecGuid = SM.Guid, SpecBasicPrice = SM.SpecBasicPrice, B2C = GCD.B2C, ProductId = PB.Guid, ProductBasicPrice = PB.BasicPrice }
).ToList()
group SSM by SSM.SpecGuid into GSM
select new OrigLossMoneyProductAndPriceIssueDto
{
ProductId = GSM.FirstOrDefault().ProductId,
SpecId = GSM.Key,
SpecBasicPrice = GSM.Min(x => x.SpecBasicPrice),
B2C = GSM.Min(x => x.B2C),
ProductBasicPrice = GSM.FirstOrDefault().ProductBasicPrice
}
).ToList()
.Where(x => x.B2C > x.SpecBasicPrice).ToList();
如果內容有誤請多鞭策謝謝