Entity Framework (.NET Full Framework) Ordering Includes
It seems you cannot sort the children collection in your query.
Either sort after the query or load the children in a second query.
Similar question and answer here
OrderBy in Include child using EF Core
Starting with Entity Framework Core 5.0, you can sort (OrderBy
) and filter (Where
) directly in the Include
statement (with some restrictions).
See the Microsoft Documentation.
Your statement can therefore be simplified like this:
public async Task<Parent> GetParent(int id)
{
return await context.Parents
.Include(p => p.Children.OrderBy(c => c.Sequence))
.SingleOrDefaultAsync(p => p.Id == id);
}
This is a nice step forward for EF Core in my opinion.
Is it possible to order by a joined column in Entity Framework Core?
Try the following query, I hope understand your question.
var query =
from a in _dbContext.As
from ab in _dbContext.ABs
.Where(ab => ab.AId == ab.AId)
.Where(ab => ab.BId == 1)
.Orderby(ab => ab.Ordering)
.Take(1)
orderby ab.Ordering
select a;
Linq - Order by in Include
As @Chris Pratt mentioned once you are doing new Customer inside the select you are creating a new model. You are discarding the models build by the EntityFramework. My suggestion would be have the query just:
query = _context.Customers
.Include(x => x.CustomerStatus)
.ThenInclude(x => x.StatusNavigation);
Like this you would have an IQueryable object which it would not be executed unless you do a select from it:
var customer3 = query.FirstOrDefault(x=>x.Id==3)
Which returns the customer and the interlinked tables (CustomerStatus and StatusNavigation). Then you can create the object that you want:
var customer = new Customers()
{
Id = customer3.Id,
Address = customer3.Address,
Contact = customer3.Contact,
Name = x.Name,
CustomerStatus = new List<CustomerStatus>
{
customer3.CustomerStatus.OrderByDescending(y => y.Date).FirstOrDefault()
}
})
In this way you can reuse the query for creating different response objects and have a single querying to database, but downside is that more memory is used then the original query (even though it shouldn't be too much of an issue).
If the model that is originally return from database doesn't meet the requirements (i.e. you always need to do: CustomerStatus = new List {...} ) it might indicate that the database schema is not well defined to the needs of the application, so a refactoring might be needed.
Filtering on Include in EF Core
Entity Framework core 5 is the first EF version to support filtered Include
.
How it works
Supported operations:
Where
OrderBy(Descending)/ThenBy(Descending)
Skip
Take
Some usage examples (from the original feature request and the github commmit)
:
Only one filter allowed per navigation, so for cases where the same navigation needs to be included multiple times (e.g. multiple ThenInclude on the same navigation) apply the filter only once, or apply exactly the same filter for that navigation.
context.Customers
.Include(c => c.Orders.Where(o => o.Name != "Foo")).ThenInclude(o => o.OrderDetails)
.Include(c => c.Orders).ThenInclude(o => o.Customer)
or
context.Customers
.Include(c => c.Orders.Where(o => o.Name != "Foo")).ThenInclude(o => o.OrderDetails)
.Include(c => c.Orders.Where(o => o.Name != "Foo")).ThenInclude(o => o.Customer)
Another important note:
Collections included using new filter operations are considered to be loaded.
That means that if lazy loading is enabled, addressing one customer's Orders
collection from the last example won't trigger a reload of the entire Orders
collection.
Also, two subsequent filtered Include
s in the same context will accumulate the results. For example...
context.Customers.Include(c => c.Orders.Where(o => !o.IsDeleted))
...followed by...
context.Customers.Include(c => c.Orders.Where(o => o.IsDeleted))
...will result in customers
with Orders
collections containing all orders.
Filtered Include and relationship fixup
If other Order
s are loaded into the same context, more of them may get added to a customers.Orders
collection because of relationship fixup. This is inevitable because of how EF's change tracker works.
context.Customers.Include(c => c.Orders.Where(o => !o.IsDeleted))
...followed by...
context.Orders.Where(o => o.IsDeleted).Load();
...will again result in customers
with Orders
collections containing all orders.
The filter expression
The filter expression should contain predicates that can be used as a stand-alone predicate for the collection. An example will make this clear. Suppose we want to include orders filtered by some property of Customer
:
context.Customers.Include(c => c.Orders.Where(o => o.Classification == c.Classification))
It compiles, but it'll throw a very technical runtime exception, basically telling that o.Classification == c.Classification
can't be translated because c.Classification
can't be found. The query has to be rewritten using a back-reference from Order
to Customer
:
context.Customers.Include(c => c.Orders.Where(o => o.Classification == o.Customer.Classification))
The predicate o => o.Classification == o.Customer.Classification)
is "stand alone" in the sense that it can be used to filter Orders
independently:
context.Orders.Where(o => o.Classification == o.Customer.Classification) // No one would try 'c.Classification' here
This restriction may change in later EF versions than the current stable version (EF core 5.0.7).
What can (not) be filtered
Since Where
is an extension method on IEnumerable
it's clear that only collections can be filtered. It's not possible to filter reference navigation properties. If we want to get orders and only populate their Customer
property when the customer is active, we can't use Include
:
context.Orders.Include(o => o.Customer.Where( ... // obviously doesn't compile
Filtered Include vs filtering the query
Filtered Include
has given rise to some confusion on how it affects filtering a query as a whole. The rule of the thumb is: it doesn't.
The statement...
context.Customers.Include(c => c.Orders.Where(o => !o.IsDeleted))
...returns all customers from the context, not only the ones with undeleted orders. The filter in the Include
doesn't affect the number of items returned by the main query.
On the other hand, the statement...
context.Customers
.Where(c => c.Orders.Any(o => !o.IsDeleted))
.Include(c => c.Orders)
...only returns customers having at least one undeleted order, but having all of their orders in the Orders
collections. The filter on the main query doesn't affect the orders per customer returned by Include
.
To get customers with undeleted orders and only loading their undeleted orders, both filters are required:
context.Customers
.Where(c => c.Orders.Any(o => !o.IsDeleted))
.Include(c => c.Orders.Where(o => !o.IsDeleted))
Filtered Include and projections
Another area of confusion is how filtered Include
and projections (select new { ... }
) are related. The simple rule is: projections ignore Include
s, filtered or not. A query like...
context.Customers
.Include(c => c.Orders)
.Select(c => new { c.Name, c.RegistrationDate })
...will generate SQL without a join to Orders
. As for EF, it's the same as...
context.Customers
.Select(c => new { c.Name, c.RegistrationDate })
It gets confusing when the Include
is filtered, but Orders
are also used in the projection:
context.Customers
.Include(c => c.Orders.Where(o => !o.IsDeleted))
.Select(c => new
{
c.Name,
c.RegistrationDate,
OrderDates = c.Orders.Select(o => o.DateSent)
})
One might expect that OrderDates
only contains dates from undeleted orders, but they contain the dates from all Orders
. Again, the projection completely ignores the Include
. Projection and Include
are separate worlds.
How strictly they lead their own lives is amusingly demonstrated by this query:
context.Customers
.Include(c => c.Orders.Where(o => !o.IsDeleted))
.Select(c => new
{
Customer = c,
OrderDates = c.Orders.Select(o => o.DateSent)
})
Now pause for a moment and predict the outcome...
The not so simple rule is: projections don't always ignore Include
. When there is an entity in the projection to which the Include
can be applied, it is applied. That means that Customer
in the projection contains its undeleted Orders
, whereas OrderDates
still contains all dates. Did you get it right?
OrderBy clause breaks my controller in Entity Framework Core 3
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();
Entity Framework loading child collection with sort order
You cannot achieve it directly because neither eager or lazy loading in EF supports ordering or filtering.
Your options are:
- Sort data in your application after you load them from database
- Execute separate query to load child records. Once you use separate query you can use
OrderBy
The second option can be used with explicit loading:
var parent = context.Parents.First(...);
var entry = context.Entry(parent);
entry.Collection(e => e.Children)
.Query()
.OrderBy(c => c.SortOrder)
.Load();
Related Topics
ASP.NET 2012 Unobtrusive Validation with Jquery
Limiting Double to 3 Decimal Places
Selecting a Textbox Item in a Listbox Does Not Change the Selected Item of the Listbox
How to Programmatically Set Cell Value in Datagridview
Query Microsoft Access Mdb Database Using Linq and C#
Multi-Select Dropdown List in ASP.NET
How to Forcefully Propagate Role Changes to Users with ASP.NET Identity 2.0.1
Is There Windows System Event on Active Window Changed
How to Do .Net Binary Serialization of an Object When You Don't Have the Source Code of the Class
Running Msbuild Programmatically
How to Use Webbrowser Control Documentcompleted Event in C#
How to Round Up Value C# to the Nearest Integer
Hashset That Preserves Ordering
Need to Perform Wildcard (*,, etc) Search on a String Using Regex