How to order nested collections in Linq and EF
I would like to make a treelistview for my data.
The tree should look like this:
Accounts
-> Providers
→ Accounts
public sealed class AccountRoot
{
public AccountRoot()
{
Providers = new Collection<Hoster>();
}
public long AccountRootId { get; set; }
public ICollection<Hoster> Providers { get; set; }
}
public sealed class Hoster
{
public Hoster()
{
Accounts = new Collection<Account>();
}
[Key]
public long HosterId { get; set; }
public long AccountRootId { get; set; }
public string Name { get; set; }
public ICollection<Account> Accounts { get; set; }
}
public sealed class Account
{
[Key]
public long AccountId { get; set; }
public long HosterId { get; set; }
public Hoster Hoster { get; set; }
public string Name { get; set; }
}
I would like to order my request.
should look like
Accounts Providers AZ
Accounts AZ
what i got so far ...
var query = _entity.AccountRoot.Local
.Select(x => new AccountRoot()
{
AccountRootId = x.AccountRootId,
Providers = x.Providers.OrderBy(y => y.Name).ToList()
}).ToList();
Missing orderby
for the next nested collection.
Thanks for the help!: -)
source to share
This can be slightly different approaches depending on whether you already have a result set and just want to sort it in code, or you want to build IQueryable<>
for EF to be compiled with SQL and done with the actual sort in the database.
First, suppose you already have a collection in your code. In this case, you have an object AccountRoot
that contains a collection Providers
, each of which has a collection Accounts
. Obviously, you cannot return the same objects as you can to change the order of the collection's properties, so you just need to create new ones. I would just sort the collections, but you could create completely new objects if you need to:
var query = ...
.Select(x => new AccountRoot
{
// add copy properties here
// ....
Providers = x.Providers
.Select(y =>
{
// Here we can construct completely new entity,
// with copying all properties one by one,
// or just reorder existing collection as I do here
var result = y;
result.Accounts = y.Accounts.OrderBy(z => z.Name).ToArray();
return result;
})
.OrderBy(y => y.Name)
.ToArray()
})
.ToArray();
The second case, if you need to get it directly from SQL, is a little different since you can't use the whole thing var result = ...; ... return result
in a lambda - it won't compile to SQL. But the idea is the same - you need to build a projection from datasets. It should be something like this:
var query = ...
.Select(x => new AccountRoot
{
AccountRootId = x.AccountRootId,
// Other properties to copy
// ...
Providers = x.Providers
.Select(y => new Hoster
{
HosterId = y.HosterId,
// Other properties to copy
// ...
Accounts = y.Accounts.OrderBy(z => z.Name).ToArray(),
})
.OrderBy(y => y.Name)
.ToArray()
})
.ToArray();
source to share