Convert SQL to C# Entity Framework Linq

Question

What i am essentially trying to do is get all of the packages that have NOT been assigned a package location price for a specific location...

I have the following SQL:

  SELECT *  FROM Package
  LEFT JOIN PackageLocationPrices ON Package.Id = PackageLocationPrices.PackageId
  Where PackageLocationPrices.LocationId IS NULL

How can i convert this into Linq to entities?

I have tried something like this:

this.db.Packages.Include(p => p.PackageLocationPrices).Where(p => p.Id == p.PackageLocationPrices.????).ToList();

I am able to join package location prices but i am unable to get the properties of the packagelocationprices to do the SQL above? Below is my schema...The PackageLocationPrices.PackageId is a foreign key of Package.Id

Package Entitiy:

    public partial class Package
{
    public Package()
    {
        this.DiscountCodes = new HashSet<DiscountCode>();
        this.PackageLocationPrices = new HashSet<PackageLocationPrice>();
        this.Memberships = new HashSet<Membership>();
    }

    public int Id { get; set; }
    public string Name { get; set; }
    public int PackageOrder { get; set; }
    public int PackageTypeId { get; set; }
    public int PackagePeriodDays { get; set; }
    public int PackagePeriodMonths { get; set; }
    public int PackageSuspensionLimit { get; set; }
    public int PackageSuspensionLimitIfAdminOverride { get; set; }
    public int PackageSuspensionMinLength { get; set; }
    public int PackageSuspensionMaxLength { get; set; }
    public int PackageSuspensionsMaxLengthCombined { get; set; }
    public int PackagePaymentHolidayLimit { get; set; }
    public int PackagePaymentHolidayMinLength { get; set; }
    public int PackagePaymentHolidayMaxLength { get; set; }
    public int PackageVisitLimit { get; set; }
    public bool PackageIsActive { get; set; }
    public bool PackageIsReoccuring { get; set; }
    public bool PackagePayInFull { get; set; }
    public bool PackageIsSession { get; set; }
    public System.DateTime CreatedDate { get; set; }
    public System.DateTime ModifiedDate { get; set; }
    public string CreatedBy { get; set; }
    public string ModifiedBy { get; set; }

    public virtual AspNetUser AspNetUserCreatedBy { get; set; }
    public virtual AspNetUser AspNetUserModifiedBy { get; set; }
    public virtual ICollection<DiscountCode> DiscountCodes { get; set; }
    public virtual PackageType PackageType { get; set; }
    public virtual ICollection<PackageLocationPrice> PackageLocationPrices { get; set; }
    public virtual ICollection<Membership> Memberships { get; set; }
}

Package Location Price Entity:

    public partial class PackageLocationPrice
{
    public int Id { get; set; }
    public int LocationId { get; set; }
    public int PackageId { get; set; }
    public decimal MonthlyPrice { get; set; }
    public decimal TotalPrice { get; set; }
    public System.DateTime CreatedDate { get; set; }
    public System.DateTime ModifiedDate { get; set; }
    public string CreatedBy { get; set; }
    public string ModifiedBy { get; set; }

    public virtual AspNetUser AspNetUserCreatedBy { get; set; }
    public virtual AspNetUser AspNetUserModifiedBy { get; set; }
    public virtual Location Location { get; set; }
    public virtual Package Package { get; set; }
}

Answer 1

I think you can create your query from another perspective:

var query=(from pl in db.PackageLocationPrices
           where pl.LocationId == null
           select pl.Package).ToList();

If you have disabled lazy loading then also need to use the Include extension method:

var query=(from pl in db.PackageLocationPrices.Include(p=>p.Package)
           where pl.LocationId == null
           select pl.Package).ToList();

Using method syntax would be this way:

var query=db.PackageLocationPrices.Include(p=>p.Package)
                                  .Where(pl=>pl.LocationId == null)
                                  .Select(pl=>pl.Package)
                                  .ToList();

If you want as result both Package and PackageLocationPrice, then do this:

 var query=db.PackageLocationPrices.Include(p=>p.Package)
                                   .Where(pl=>pl.LocationId == null)
                                   .ToList();

With this last query your are going to get a list of PackageLocationPrice, and if you want see the related Package for a givenPackageLocationPrice, you can use the Package navigation property.

Answer 2

var result = (from p in Package
            join q in PackageLocationPrices on p.Id equals q.PackageId into pq
            from r in pq.DefaultIfEmpty()
            select new {p, r}).ToList();

This should return something exactly like your SQL query.