OrderBy clause breaks my controller in Entity Framework Core 3

Question

So I had this little controller that is working great. The only issue, is that the StateLocation OrderId was out of order in the returned string.

[HttpGet("GetLocationsByLanguageId/{id}")]
public async Task<ActionResult<IEnumerable<LanguageList>>> GetLocationsByLanguageId(Guid id)
{
    var choicesByLanguage = await _context.LanguageList.Where(q => q.LanguageId == id)
                    .Include(p => p.StateLocation)
                    .ToListAsync();
    return choicesByLanguage;
}

So I went to the Microsoft Entity Framework site to read about ordering and found this:

https://learn.microsoft.com/en-us/ef/core/querying/related-data/eager

So I added an OrderBy clause like you see below:

[HttpGet("GetLocationsByLanguageId/{id}")]
public async Task<ActionResult<IEnumerable<LanguageList>>> GetLocationsByLanguageId(Guid id)
{
    var choicesByLanguage = await _context.LanguageList.Where(q => q.LanguageId == id)
                    .Include(p => p.StateLocation
                            .OrderBy(p => p.OrderId))
                            .ToListAsync();
    return choicesByLanguage;
}

I don't get any errors on build, but when I try to hit the site, I get this:

Expression of type 'System.Linq.IOrderedQueryable' cannot be used for return type 'System.Linq.IOrderedEnumerable

So I searched around for that found a few possible solutions here:

OrderBy and List vs. IOrderedEnumerable

But none of those worked, they just generated new errors or variations on of the original error.

I've had trouble finding any answers that help with this issue when using .Net Core 3. Most of the answers are for older versions.

Has anyone been able to solve this .Net Core Entity Framework 3.0?

Thanks!

Models:

public partial class LanguageList
{
    public LanguageList()
    {
        StateLocation = new HashSet<StateLocation>();
    }
    
    public Guid LanguageId{ get; set; }
    public string LanguageName { get; set; }

    public virtual ICollection<StateLocation> StateLocation { get; set; }
}

public partial class StateLocation
{
    public Guid StateId { get; set; }
    public Guid LanguageId { get; set; }
    public string StateName { get; set; }
    public string StateCapital { get; set; }
    public int OrderId { get; set; }

    public virtual LanguageList Language { get; set; }
}

Could you check your syntax again? You have a `)` too many in the last code block — Xerillio, Feb 17 '21 at 20:14
@Xerillio oops thanks, just a typo when I was typing it out here on SO. :) — SkyeBoniwell, Feb 17 '21 at 20:16
@MarcGravell why? The StateLocations are children of the LanguageList and they need to be ordered by their orderId. — SkyeBoniwell, Feb 17 '21 at 20:27

Serge · Accepted Answer · 2021-02-17T21:16:58.623

2

Try this:

return  await _context.LanguageList
                    .Where(q => q.LanguageId == id)
                    .Include(p => p.StateLocation.OrderBy(p => p.OrderId))
                     .ToListAsync();

You almost made this query, but since you put the extra ")" in a wrong place it gave the error.

but if you still use EF Core 3 you can try this:

var query=  await _context.LanguageList
              .Where(q => q.LanguageId == id)
        .Select(p => new
        {
            P = p,
            C = p.StateLocation.OrderBy(p => p.OrderId))
        })
    .ToListAsync();

return  query
            .Select(g => g.P)
            .FirstOrDefault();

edited Feb 17 '21 at 21:16

answered Feb 17 '21 at 20:23

Serge

40,935
4
18
45

Is this actually supported? [This](https://stackoverflow.com/questions/15378136/entity-framework-ordering-includes) indicates otherwise - it's an old post though – Xerillio Feb 17 '21 at 20:29
I think that's what I am using? – SkyeBoniwell Feb 17 '21 at 20:32
3

@SkyeBoniwell AFAIK, `.OrderBy()` inside `.Include()` is a feature introduced in EF Core 5.0. – atiyar Feb 17 '21 at 20:38
@atiyar Oh I see, I am using 3.1. Is there no way to do this in 3.1? Thanks :( – SkyeBoniwell Feb 17 '21 at 20:39

Xerillio · Answer 2 · 2021-02-17T20:49:20.180

1

You mention you're using EF Core 3, however, the documentation states that this functionality was first introduced in EF Core 5.0. That is, you cannot perform LINQ operations on the related data in a single query. Instead you need to either upgrade to EF Core 5.0 or perform the ordering after your ToListAsync call.

Example in EF Core 3:

var choicesByLanguage = await _context.LanguageList.Where(q => q.LanguageId == id)
    .Include(p => p.StateLocation)
    .ToListAsync();

foreach (var lList in choicesByLanguage)
{
    lList.StateLocation = lList.StateLocation.OrderBy(p => p.OrderId).ToList();
}

edited Feb 17 '21 at 20:49

answered Feb 17 '21 at 20:41

Xerillio

4,855
1
17
28

I tried `.ToListAsync().OrderBy(p => p.OrderId) ` but it doesn't recognize it – SkyeBoniwell Feb 17 '21 at 20:46
1

@SkyeBoniwell I added an example. The problem with your example there is that `ToListAsync()` returns the list of `LanguageList` which doesn't have the `OrderId` property - it's in a child object. – Xerillio Feb 17 '21 at 20:50

OrderBy clause breaks my controller in Entity Framework Core 3

2 Answers2