聊聊程式

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();