0

I am having a problem ordering data by table Iscinstances row Name in table relationships, the Iscinstances table is JOIN in LinkUsersToIscinstances table, below is my query for the table relationships.

The problem is that OrderBy a.LinkUsersToIscinstances.Iscinstances.Name is an error, how to fix the order query ?

var customer = await _context.Users
                            .Where(c => c.UserId  == UserId && c.LastActivityDate > date && c.IsApproved == num)
                            .Include(c => c.Profile)
                            .Include(a => a.LinkUsersToIscinstances).ThenInclude(b => b.Iscinstances)
                            .OrderBy(a => a.LinkUsersToIscinstances.Iscinstances.Name).ThenBy(c => c.LastName).ThenBy(c => c.FirstName)
                            .ToListAsync();

I am trying to order data by table row name in table relationships, I want to get the query to order them by table Iscinstances Name, table Users LastName, table Users FirstName

This are my classis table relationships

UserProfile

    public virtual ICollection<LinkUsersToIscinstance> LinkUsersToIscinstances { get; set; }
    public virtual ICollection<User> Users { get; set; }

LinkUsersToIscinstance

    public virtual UserProfile Profile { get; set; }
    public virtual Iscinstance Iscinstances { get; set; }
    public virtual User Users { get; set; }

Iscinstance

    public virtual ICollection<LinkUsersToIscinstance> LinkUsersToIscinstances { get; set; }

User

    public virtual UserProfile Profile { get; set; }
    public virtual ICollection<LinkUsersToIscinstance> LinkUsersToIscinstances { get; set; }
Ken
  • 53
  • 6
  • "OrderBy a.LinkUsersToIscinstances.Iscinstances.Name is an error" is it? what error? – Jeremy Lakeman Apr 06 '22 at 00:35
  • I cannot understand how do you plan to order by `LinkUsersToIscinstances` navigation property with is a collection property. Looks like a nonsense. – Svyatoslav Danyliv Apr 06 '22 at 08:12
  • its not ordering them by `LinkUsersToIscinstances`, its ordering them by `Iscinstances` row `Name`, table `Iscinstances` is JOIN in `LinkUsersToIscinstances` table relationship, so if you just also put `a.Iscinstances.Name` it will be an error @SvyatoslavDanyliv – Ken Apr 06 '22 at 14:22
  • `ICollection does not contain a definition for Iscinstances and no extention method Iscinstances acception the first argument type` is the error @JeremyLakeman – Ken Apr 06 '22 at 14:27
  • Nothing changed after your comment. `LinkUsersToIscinstances` is a collection and you want to Order by ALL items in this collection even you want to order by `Iscinstances.Name`, which is also wrong because `Iscinstances` is also collection. What you can do is order by first item in collection. – Svyatoslav Danyliv Apr 06 '22 at 14:31
  • I'll update the post and include my classic relationships @JeremyLakeman – Ken Apr 06 '22 at 14:33
  • All what you can do here, only order by first item `.OrderBy(a => a.LinkUsersToIscinstances.FirstOrDefault().Iscinstances.Name)…` – Svyatoslav Danyliv Apr 06 '22 at 14:51
  • @SvyatoslavDanyliv it is not actually working because it is only selecting top 1 form Name by order – Ken Apr 06 '22 at 16:48
  • How to order by a collection of objects - this is the question that you have to answer for yourself. Or better write SQL which do needed order and we can understand then. But, it should be a case, and you want to just order `LinkUsersToIscinstances` in result? – Svyatoslav Danyliv Apr 06 '22 at 18:36
  • @SvyatoslavDanyliv very sorry for my english if you did not understand but I want it like this, this output of `LinkUsersToIscinstance ` `"LinkUsersToIscinstance ": [ { ... "iscinstance": [ { ... } ] } ]` inside the `LinkUsersToIscinstance ` will list all the data from `iscinstance` right?, i want it to be order the list of `iscinstance` data by Name – Ken Apr 06 '22 at 20:50
  • old EF6 or new EF Core? – vernou Apr 06 '22 at 21:25

1 Answers1

1

Try the following query.

var customer = await _context.Users
    .Where(c => c.UserId  == UserId && c.LastActivityDate > date && c.IsApproved == num)
    .Include(c => c.Profile)
    .Include(a => a.LinkUsersToIscinstances.OrderBy(x => x.Iscinstances.Name)).ThenInclude(b => b.Iscinstances)
    .OrderBy(c => c.LastName).ThenBy(c => c.FirstName)
    .ToListAsync();
Svyatoslav Danyliv
  • 21,911
  • 3
  • 16
  • 32
  • it work if I don't include the `Select` method but in my case now I included `Select` method I have put the `.OrderBy(x => x.Iscinstances.Name)` or `.OrderBy(x => x.Name)` inside the `Select` method , thanks for helping me out, really appreciate your help I can use this method on my other query that don't include `Select` method – Ken Apr 06 '22 at 21:38
  • Remember that `Select` and `Include` two mutual exclusive operators, that's why after `Select` your ordering and Includes are discarded. – Svyatoslav Danyliv Apr 07 '22 at 05:18