我有一個 SQL 查詢，我將其轉換為 linq Query ，但結果與我做錯的地方不同？
SQL 查詢
Select
UP.UserID, UP.TotalAmount, UP.BilledAmount, UP.DiscountAmount, UP.PurchasedOn, UPI.*, UPIA.AddonID, UPIA.UserPurchaseItemAddonID, EA.Amount AddonAmount
From UserPurchases UP
Inner Join UserPurchaseItems UPI
On UP.UserPurchaseID = UPI.UserPurchaseID
Left Join UserPurchaseItemAddons UPIA
On UPIA.UserPurchaseItemID = UPI.UserPurchaseItemID AND UPIA.IsDeleted = 0
Left Join ExtraAddons EA
On EA.AddonID = UPIA.AddonID AND EA.IsActive = 1
Where UP.UserPurchaseID = 10287 AND UP.StatusID = 2 AND UPI.IsDeleted = 0 AND UP.IsDeleted = 0
上面的查詢回傳兩行是正確的。
Linq 查詢
var result = (from f in context.UserPurchases
join s in context.UserPurchaseItems on f.UserPurchaseId equals s.UserPurchaseId
join us in context.UserPurchaseItemAddons on s.UserPurchaseItemId equals us.UserPurchaseItemId into g
from e in g.DefaultIfEmpty()
join ea in context.ExtraAddons on e.AddonId equals ea.AddonId into k
from m in k.DefaultIfEmpty()
where f.UserPurchaseId == userPurchaseID
&& f.StatusId == 2 && s.IsDeleted == false && f.IsDeleted == false && m.IsActive == true
select new CancelItemAndAddons
{
UserID = f.UserId,
TotalAmount = f.TotalAmount,
BilledAmount = f.BilledAmount,
DiscountAmount = f.DiscountAmount,
PurchasedOn = f.PurchasedOn,
Amount = s.Amount,
OldPrice = s.OldPrice,
SpecialPriceMenuItemID = s.SpecialPriceMenuItemId,
BranchItemVariantID = s.BranchItemVariantId,
BranchItemBusinessCampaignID = s.BranchItemBusinessCampaignId,
UserPurchaseItemID = s.UserPurchaseItemId,
UserPurchaseID = s.UserPurchaseId
}).ToList();
這是在代碼中回傳 0 條記錄。
從 linq 生成的 Sql 查詢如下
DECLARE @__userPurchaseID_0 int = 10287;
SELECT [u].[UserID], [u].[TotalAmount], [u].[BilledAmount], [u].
[DiscountAmount], [u].[PurchasedOn], [u0].[Amount], [u0].[OldPrice], [u0].[SpecialPriceMenuItemID], [u0].[BranchItemVariantID], [u0].[BranchItemBusinessCampaignID], [u0].[UserPurchaseItemID], [u0].[UserPurchaseID]
FROM [UserPurchases] AS [u]
INNER JOIN [UserPurchaseItems] AS [u0] ON [u].[UserPurchaseID] = [u0].[UserPurchaseID]
LEFT JOIN [UserPurchaseItemAddons] AS [u1] ON [u0].[UserPurchaseItemID] = [u1].[UserPurchaseItemID]
LEFT JOIN [ExtraAddons] AS [e] ON [u1].[AddonID] = [e].[AddonID]
WHERE (((([u].[UserPurchaseID] = @__userPurchaseID_0) AND ([u].[StatusID] = 2)) AND ([u0].[IsDeleted] = CAST(0 AS bit))) AND ([u].[IsDeleted] = CAST(0 AS bit))) AND ([e].[IsActive] = CAST(1 AS bit))
我所看到的不同之處AND在于 linq 查詢中缺少左連接運算子。我如何應用ANDOperatorleft joinwhere
型號 UserPurchase
public partial class UserPurchase
{
public UserPurchase()
{
Conversations = new HashSet<Conversation>();
ExternalUserPurchases = new HashSet<ExternalUserPurchase>();
UserPointConsumptions = new HashSet<UserPointConsumption>();
UserPurchaseItemActivities = new HashSet<UserPurchaseItemActivity>();
UserPurchaseItemComments = new HashSet<UserPurchaseItemComment>();
UserPurchaseItemParentUserPurchases = new HashSet<UserPurchaseItem>();
UserPurchaseItemUserPurchases = new HashSet<UserPurchaseItem>();
UserPurchaseShares = new HashSet<UserPurchaseShare>();
UserPurchaseStories = new HashSet<UserPurchaseStory>();
}
public int UserPurchaseId { get; set; }
public int? BusinessId { get; set; }
public int? BusinessBranchTableId { get; set; }
public int BusinessBranchId { get; set; }
public int StatusId { get; set; }
public int UserId { get; set; }
public DateTime PurchasedOn { get; set; }
public int PaymentTypeId { get; set; }
public decimal? TotalAmount { get; set; }
public decimal? DiscountAmount { get; set; }
public decimal? GiftFeeAmount { get; set; }
public decimal? BilledAmount { get; set; }
public int? CurrencyId { get; set; }
public bool? IsFavorite { get; set; }
public DateTime? CreatedOn { get; set; }
public int? CreatedBy { get; set; }
public DateTime? ModifiedOn { get; set; }
public int? ModifiedBy { get; set; }
public bool IsDeleted { get; set; }
public string? SpecialInstruction { get; set; }
public string? TableNo { get; set; }
public bool IsGift { get; set; }
public int? GiftedTo { get; set; }
public string? GiftMessage { get; set; }
public long? OrderId { get; set; }
public decimal? ConsumedPoints { get; set; }
public int? OrderTypeId { get; set; }
public string? OrderNo { get; set; }
public virtual BusinessBranch BusinessBranch { get; set; } = null!;
public virtual BusinessBranchTable? BusinessBranchTable { get; set; }
public virtual OrderType? OrderType { get; set; }
public virtual PaymentType PaymentType { get; set; } = null!;
public virtual Status Status { get; set; } = null!;
public virtual User User { get; set; } = null!;
public virtual ICollection<Conversation> Conversations { get; set; }
public virtual ICollection<ExternalUserPurchase> ExternalUserPurchases { get; set; }
public virtual ICollection<UserPointConsumption> UserPointConsumptions { get; set; }
public virtual ICollection<UserPurchaseItemActivity> UserPurchaseItemActivities { get; set; }
public virtual ICollection<UserPurchaseItemComment> UserPurchaseItemComments { get; set; }
public virtual ICollection<UserPurchaseItem> UserPurchaseItemParentUserPurchases { get; set; }
public virtual ICollection<UserPurchaseItem> UserPurchaseItemUserPurchases { get; set; }
public virtual ICollection<UserPurchaseShare> UserPurchaseShares { get; set; }
public virtual ICollection<UserPurchaseStory> UserPurchaseStories { get; set; }
}
型號 UserPurchaseItem
public partial class UserPurchaseItem
{
public UserPurchaseItem()
{
UserPurchaseItemReviews = new HashSet<UserPurchaseItemReview>();
UserPurchasePoints = new HashSet<UserPurchasePoint>();
}
public int UserPurchaseItemId { get; set; }
public int UserPurchaseId { get; set; }
public int BranchItemVariantId { get; set; }
public int? BranchItemBusinessCampaignId { get; set; }
public int Quantity { get; set; }
public DateTime CreatedOn { get; set; }
public int CreatedBy { get; set; }
public DateTime? ModifiedOn { get; set; }
public int? ModifiedBy { get; set; }
public int? ReferralId { get; set; }
public bool? IsAllowedShare { get; set; }
public bool? IsDeleted { get; set; }
public int? ParentUserPurchaseId { get; set; }
public int? UsedQuantity { get; set; }
public int? RecommendedId { get; set; }
public decimal? Amount { get; set; }
public bool IsStamping { get; set; }
public decimal? OldPrice { get; set; }
public int? SpecialPriceMenuItemId { get; set; }
public virtual BranchItemBusinessCampaign? BranchItemBusinessCampaign { get; set; }
public virtual BranchItemVariant BranchItemVariant { get; set; } = null!;
public virtual UserPurchase? ParentUserPurchase { get; set; }
public virtual UserPurchase UserPurchase { get; set; } = null!;
public virtual ICollection<UserPurchaseItemReview> UserPurchaseItemReviews { get; set; }
public virtual ICollection<UserPurchasePoint> UserPurchasePoints { get; set; }
}
模型 UserPurchaseItemAddon
public partial class UserPurchaseItemAddon
{
public int UserPurchaseItemAddonId { get; set; }
public int UserPurchaseItemId { get; set; }
public int BranchItemVariantId { get; set; }
public int AddonId { get; set; }
public int UserId { get; set; }
public bool? IsDeleted { get; set; }
public string? Reason { get; set; }
public DateTime? ModifiedOn { get; set; }
public int? ModifiedBy { get; set; }
}
模型 ExtraAddon
public partial class ExtraAddon
{
public ExtraAddon()
{
BranchExtraAddons = new HashSet<BranchExtraAddon>();
ExternalAddons = new HashSet<ExternalAddon>();
InverseParentAddon = new HashSet<ExtraAddon>();
ItemExtraAddons = new HashSet<ItemExtraAddon>();
}
public int AddonId { get; set; }
public int? BusinessId { get; set; }
public int? ParentAddonId { get; set; }
public string NameAr { get; set; } = null!;
public string NameEn { get; set; } = null!;
public string? DescriptionAr { get; set; }
public string? DescriptionEn { get; set; }
public bool? IsRequired { get; set; }
public int? MaxAddOns { get; set; }
public decimal? Amount { get; set; }
public int? CurrencyId { get; set; }
public bool? IsActive { get; set; }
public DateTime? CreatedOn { get; set; }
public int? CreatedBy { get; set; }
public DateTime? ModifiedOn { get; set; }
public int? ModifiedBy { get; set; }
public int? MinAddOns { get; set; }
public string? LockedFields { get; set; }
public virtual Currency? Currency { get; set; }
public virtual ExtraAddon? ParentAddon { get; set; }
public virtual ICollection<BranchExtraAddon> BranchExtraAddons { get; set; }
public virtual ICollection<ExternalAddon> ExternalAddons { get; set; }
public virtual ICollection<ExtraAddon> InverseParentAddon { get; set; }
public virtual ICollection<ItemExtraAddon> ItemExtraAddons { get; set; }
}
uj5u.com熱心網友回復：
嘗試以下查詢。沒有那么多導航屬性被重用。
var query =
from f in context.UserPurchases
from s in f.UserPurchaseItemUserPurchases
from e in context.UserPurchaseItemAddons
.Where(e => s.UserPurchaseItemId == e.UserPurchaseItemId && !e.IsDeleted)
.DefaultIfEmpty() // left join
from m in context.ExtraAddons
.Where(m => e.AddonId == m.AddonId && m.IsActive)
.DefaultIfEmpty() // left join
where f.UserPurchaseId == userPurchaseID
&& f.StatusId == 2 && !s.IsDeleted && !f.IsDeleted
select new CancelItemAndAddons
{
UserID = f.UserId,
TotalAmount = f.TotalAmount,
BilledAmount = f.BilledAmount,
DiscountAmount = f.DiscountAmount,
PurchasedOn = f.PurchasedOn,
Amount = s.Amount,
OldPrice = s.OldPrice,
SpecialPriceMenuItemID = s.SpecialPriceMenuItemId,
BranchItemVariantID = s.BranchItemVariantId,
BranchItemBusinessCampaignID = s.BranchItemBusinessCampaignId,
UserPurchaseItemID = s.UserPurchaseItemId,
UserPurchaseID = s.UserPurchaseId
}
請注意，m結果e投影中沒有使用左連接結果，查詢有問題。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/529755.html