CODEWITHSUNDEEP
c#linqentity-frameworklinq-to-entities
Jonathan Wood
Jonathan Wood

Reputation: 67193

LINQ (to Entities) Query with Sorting and Grouping

I seem to need a LINQ (to Entities against MS SQL Server) query that is a bit over my head.

My schema looks like this:

Database Schema Diagram

My task is to display all ProductPricing rows for a specific ProductItem, sorted by ProductPricing.EffectiveDate, and grouped by ProductPricing.CompaniesId.

Furthermore, I need to order the groups by ProductItemCompanies.SortOrder.

Note that ProductPricing.CompaniesId can be null, and that all ProductPricing rows where CompaniesId is null need to be grouped as a single group that is placed before all other groups.

I have made numerous attempts but I'm not clear enough of LINQ joins or grouping to get it right. My current code looks like this, but it's neither correct or even compiles.

(Note that DefaultProductItem is the ProductItems I'm displaying ProductPricings for.)

var vendors = from pp in this.DefaultProductItem.ProductPricings
              orderby pp.EffectiveDate
              join pic in this.DefaultProductItem.ProductItemCompanies on pic.CompanyId equals pp.CompanyId &&
                  pic.ProductItemId equals this.DefaultProductItem.Id
              group pp by pp into v
              select v;

UPDATE:

With Tim.Tang's help, I have the following query that runs:

var vendors = from pp in DefaultProductItem.ProductPricings
              join pic in DefaultProductItem.ProductItemCompanies
              on pp.CompanyId equals pic.CompanyId into left
              from pic in left.DefaultIfEmpty()
              orderby pp.EffectiveDate descending, pic == null ? -1 : pic.SortOrder
              group pp by pp.CompanyId into v
              select v;

This is getting close, however it does not sort the resulting groups by ProductItemCompanies.SortOrder. Although the above query does sort by this value, that doesn't appear to affect the groups.

I know I could easily sort the groups after the fact, but I'm looking to make that part of the original query for efficiency.

Upvotes: 0

Views: 88

Answers (1)

Tim.Tang
Tim.Tang

Reputation: 3188

here is my suggestion,hope it can help:

var vendors = from pp in this.DefaultProductItem.ProductPricings
              join pic in this.DefaultProductItem.ProductItemCompanies.Where(x=>x.ProductItemId == this.DefaultProductItem.Id)
              on pp.CompanyId equals pic.CompanyIdinto left
              from pic in left.DefalutIfEmpty()
              orderby pp.EffectiveDate, pic==null?0:pic.SortOrder  //sort order is INT?
              group pp by pp.CompaniesId into v
              select v;

Upvotes: 1

Related Questions