Linq-to-Entities working with One-to-Many Relationship and Sorting

I would Like to sort the keywords by its sort property. I have a one-many relationship between page -> keyword.

Question: If I perform a comprehension query as:

IEnumerable<page> query = from p in contxt.pages where p.ID == myId select p;

IEnumerable<page> pge = query.First();

Could I expect a single page record set with all the page’s properties and one keyword collection?

My understanding is since there exist a one-many relationship, I don’t have to perform a join. The Linq to Entity framework should have knowledge of this relationship, and return a collection.

So this is my understanding, so when I try to sort on keyword开发者_运维技巧.sort, the keyword is out of scope:

    IEnumerable<page> query = from p in contxt.pages where p.ID == myId
orderby p.keywords. select p;

Utilizing the above understanding I thought the query is incorrect because keywords is returned as a collection, so I must perform a sort as follows:

    PageKeywords pageKeywords = new PageKeywords();
    Keywords keywords;

    IEnumerable<page> query = from p in contxt.pages 
where p.ID == vals.pageId select p;

    page pge = query.First();

    pageKeywords.keywords = new List<Keywords>();

    pageKeywords.id = pge.ID;
    pageKeywords.description = pge.descp;
    pageKeywords.title = pge.title;
    pageKeywords.showTitle = pge.showTitle;
    pageKeywords.keywords = pge.keywords.OrderBy(k => k.sort);

    foreach (var item in pageKeywords.keywords)
    {
    keywords = new Keywords();
    keywords.id = item.id;
    keywords.name = item.name;
    keywords.sort = item.sort;
    pageKeywords.keywords.Add(keywords);
    }

However this did not work. The keywords were not sorted by sort? The code above does work, however I need the keywords to be sorted by sort property. The keywords collection isn't being sorted.

I also tried:

pageKeywords.keywords.Sort((x, y) => int.Compare(x.sort, y.sort));

Which did not work.

I have tried to understand, but I am missing something? Any suggestions would be appreciable.

Try this:

var query = from p in contxt.Pages 
            where p.ID == vals.PageId 
            select new PageKeywords 
                {
                    Id = pge.Id,
                    Description = pge.Descp,
                    Title = pge.Title,
                    ShowTitle = pge.ShowTitle,
                    Keywords = pge.Keywords.OrderBy(k => k.sort)
                };

OrberBy returns a new enumerable - it does not change the source: http://msdn.microsoft.com/en-us/library/bb534966.aspx

Also, you want to sort by a property of keywords, a page may have many keywords, you need to choose one (e.g. minimum one).

Try:

IEnumerable<page> query = context.pages // Pages
    .Where(p => p.ID == myId) // Filtered by id
     // Sort by minimum sort value of all keywords of page
    .OrderBy(p.Keywords.Select(keyword => keyword.sort).Min());

Also, using .NET's guidelines for names is recommended (e.g. properties should be PascalCase and not camelCase).