LINQ Query is not returning the same result as SQL Query-CodePudding

I have a SQL query which I am converting in to linq Query , but the result is not same where I'm doing wrong?

SQL Query

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

The above query is returning two rows which is correct.

Linq Query

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

This is returning 0 records in code.

The Sql Query generated from linq is below

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

The difference what I have seen is that the AND operator with left join is missing in linq query . how can I apply AND Operator with left join , I have applied it on where condition which is actually wrong

Models 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; }
}

Models 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; }
}

Models 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; }
}

Models 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; }
}

CodePudding user response：

Try the following query. Not so many navigation properties are reused.

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                   
    }

Note that m and e left join result is not used in result projection, something wrong with query.