LinqToSql OrderBy has no Effect

Question

I am using a LinqToSql-DataSource for a GridView in this way:

                wsv.wsv2DataContext db = new wsv.wsv2DataContext();
                e.KeyExpression = "id";
                e.QueryableSource = (from mitgliedschaft in db.mitgliedschaft

                                     join person in db.person on mitgliedschaft.person_id equals person.id
                                     join institution in db.institution on mitgliedschaft.verein_id equals institution.id

                                     select new
                                     {
                                         vorname = person.vorname,
                                         nachname = person.nachname,
                                         nameVerein = institution.name,
                                         vereinid = mitgliedschaft.verein_id,
                                         id = mitgliedschaft.id,
                                         verbandsMitgliedsNummer = person.verbandsMitgliedsNummer,
                                         strasse = person.strasse,
                                         plz = person.plz,
                                         ort = person.ort,
                                         geburtsdatum = person.geburtsdatum,
                                         geschlechtid = person.geschlechtid,
                                         statusid = mitgliedschaft.statusid,
                                         bezirk_id = mitgliedschaft.bezirk_id,
                                         kreis_id = mitgliedschaft.kreis_id,
                                         person_id = mitgliedschaft.person_id.Value,
                                         deletedFlag = mitgliedschaft.deletedFlag,
                                         stammverein = mitgliedschaft.stammVerein,
                                         eintrittsdatum = mitgliedschaft.eintritt
                                     }).GroupBy(p => p.person_id).Select(p => p.First());
            }

Now i want to order the Selection. At first the "stammVerein"-Column of Table "mitgliedschaft" descending AND the Column "eintritt" of Table "mitgliedschaft". I have tried several ways:

                wsv.wsv2DataContext db = new wsv.wsv2DataContext();
                e.KeyExpression = "id";
                e.QueryableSource = (from mitgliedschaft in db.mitgliedschaft

                                     join person in db.person on mitgliedschaft.person_id equals person.id
                                     join institution in db.institution on mitgliedschaft.verein_id equals institution.id

                                     orderby mitgliedschaft.stammVerein descending, mitgliedschaft.eintritt

                                     select new
                                     {
                                         ...

                                     }).GroupBy(p => p.person_id).Select(p => p.First());
            }

AND:

                wsv.wsv2DataContext db = new wsv.wsv2DataContext();
                e.KeyExpression = "id";
                e.QueryableSource = (from mitgliedschaft in db.mitgliedschaft

                                     join person in db.person on mitgliedschaft.person_id equals person.id
                                     join institution in db.institution on mitgliedschaft.verein_id equals institution.id

                                     select new
                                     {
                                         ...

                                     }).GroupBy(p => p.person_id).Select(p => p.First()).OrderByDescending(stamm => stamm.stammverein).ThenBy(eintritt => eintritt.eintrittsdatum);
            }

AND:

                wsv.wsv2DataContext db = new wsv.wsv2DataContext();
                e.KeyExpression = "id";
                e.QueryableSource = (from mitgliedschaft in db.mitgliedschaft

                                     join person in db.person on mitgliedschaft.person_id equals person.id
                                     join institution in db.institution on mitgliedschaft.verein_id equals institution.id

                                     select new
                                     {
                                         ....

                                     }).OrderByDescending(stamm => stamm.stammverein).ThenBy(eintritt => eintritt.eintrittsdatum).GroupBy(p => p.person_id).Select(p => p.First());

But nothing of this has any Effects ! I am very new in this kind of DataSource and Linq.

Can anyone help me achieving this order ?

Answer 1

Items within a grouped result will not retain their order. Depending on how you want to factor in the ordering, you will need to do it after the group by, and before, and/or after your First...

To accomplish this, it will be easiest if you map the relationships in EF with navigation properties rather than substituting SQL with Linq QL (joins and such)

Using the following base query:

var query = db.mitgliedschaft
    .GroupBy(m => m.Person); // Group by related entity, not ID

For instance, after the group by, you will have sets of records grouped by Person. If you want the first Person with an earliest related record:

var result = query.OrderByDescending(g => g.Key.mitgliedschafts.Max(stamm => stamm.stammverein)
    .ThenBy(stamm => stamm.eintritt.eintrittsdatum)
    .First();

This is taking a wild guess at your schema & entity relationships, but hopefully it will help you work out something that fits. I can only guess at what eintritt is and how it relates to your entity model.

The initial query takes just your base entities that you want to group, and groups them by the related entity. The result of that grouping will be a set of Grouped mitgliedschafts with a key being the Person. To Order those groups by the person with the most recent mitgliedschafts we use an orderby on the Key's associated mitgliedschafts using the Max value for the collection given a descending order request.

The First then gives us the first grouped collection of mitgliedschafts.

Then if you want to sort the resulting list of mitgliedschafts after getting the person with the most recent one:

var result = query.OrderByDescending(g => g.Key.mitgliedschafts.Max(stamm => stamm.stammverein)
    .ThenBy(stamm => stamm.eintritt.eintrittsdatum)
    .First().OrderByDescending(stamm => stamm.stammverein)
    .ThenBy(stamm => stamm.eintritt.eintrittsdatum)
    .ToList();

The 2nd set of OrderBy clauses apply to the selected group, or the mitgliedschafts.

To compose the desired view model, Insert a Select() to build the view model from the mitgliedschafts before the ToList().

With the navigation properties this can probably be done without resorting to a group by. On a hunch, something like this should return something similar:

var query = db.Person
    .OrderByDescending(p => p.mitgliedschafts.Max(stamm => stamm.stammverien))
    .ThenBy(stamm => stamm.eintritt.eintrittsdatum)
    .SelectMany(p => p.mitgliedschafts)
    .OrderByDescending(stamm => stamm.stammverien)
    .ThenBy(stamm => stamm.eintritt.eintrittsdatum)
    .Select(stamm => new { ... })
    .ToList();

Anyhow, hopefully that gives you some ideas on things to try if you have the navigation properties mapped or can set those up.