PLEASE HELP!!! OrderBy through a relationship (LINQ to SQL)

Question

 

I have been searching desperately for how to solve this.  It sounds like it would be a common problem, but I can't find any help.  Here it is:

 

We have a Product table and a Category table.  One Category has 1:N Products.

Both tables have a "DisplayOrder" field which specifies the order index that the record should be returned.  I am trying to create a LINQ to SQL query that will return the both entities sorted by their DisplayOrder field.

 

I can sort the Category table easily, but sorting the Products table through the relationship is impossible!

 

Category A   |  9

Category B   |  0

Category C   |  1

 

Product A | Category B | 1

Product B | Category B | 0

 

 

Thanks for ANY help - I'm desperate...

Answer 1

There are a couple of ways to do this.

The first is using DataLoadOptions. First, you create a DataLoadOptions object, and call AssociateWith on it - this tells the DataContext how to filter/order a particular relationship:

var options = new DataLoadOptions();

options.AssociateWith <Category> (c => c.Products.OrderBy (p => p.DisplayOrder));

dataContext.LoadOptions = options;

Then, when you run queries, the association property will be ordered automatically:

var query =

from c in dataContext.Categories
where ...
orderby c.DisplayOrder
select c;

// c.Products will be ordered by DisplayOrder.

The second approach is to project the child EntitySet (c.Products) explicitly. You don't then need DataLoadOptions:

var query =
from c in dataContext.Categories
where ...
orderby c.DisplayOrder
select new
{

c.Name,
c.Description,
Products = c.Products.OrderBy (p => p.DisplayOrder)

};

Joe

Answer 2

You are my hero.  Thanks!

Answer 3

Is there any way to do this either a) not in code-behind, but in the linqdatasource web control, or b) somewhere other than each page this is needed (e.g. globally)?

Thanks a million!

Answer 4

you can put it in the OnCreated method, this is always called when it's created...

 

    partial class NorthwindDataContext
    {

        partial void OnCreated()
        {
            System.Data.Linq.DataLoadOptions dl = new System.Data.Linq.DataLoadOptions();
            dl.LoadWith<Category>(c => c.Products);
            this.LoadOptions = dl;           
        }
    }

check out this blog, includes this and other interesting things regarding this stuff...of course you'd use the associatewith not loadwith which is for dealing with lazy loading.

 

Answer 5

What if you had another table under Products, such as ProductKeywords, and you wanted to THEN order by a ProductKeywords property, such as theKeyWord?

ex:

var query =
from c in dataContext.Categories
where ...
orderby c.DisplayOrder
select new
{

c.Name,
c.Description,
Products = c.Products.OrderBy (p => p.DisplayOrder),

TheProductKeywords = c.Products.(what goes here?).OrderBy(p => p.theKeyWord)

};

---

askf