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

Forgive me that i am a relative MVC newbie..What part did you want me to show you? I am using a Database first model.. — Christopher Pettigrew
– Christopher Pettigrew, Commented Sep 22, 2015 at 23:09

Ppp · Accepted Answer · 2015-09-23 02:41:29Z

1

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.

edited Sep 23, 2015 at 2:41

answered Sep 23, 2015 at 2:22

Ppp

1,0159 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ocuenca · Accepted Answer · 2015-09-23 14:04:01Z

0

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.

edited Sep 23, 2015 at 14:04

answered Sep 22, 2015 at 23:24

ocuenca

39.5k12 gold badges95 silver badges107 bronze badges

2 Comments

Christopher Pettigrew Over a year ago

Thank you for this. Very helpful. What would be the object result? I'm assuming it wouldn't be either package or package location price. Would I have to bind the results to a custom model first before I pass it to my view?

ocuenca Over a year ago

You are going to get from this query a list of packages. If you want you can also create a custom class to save the projection of your query and use the result in your view

Collectives™ on Stack Overflow

Convert SQL to C# Entity Framework Linq

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related