I have some difficulty to add some filter condition for included items in my LINQ query. My query is like
var item = _Context.Order.Include("Inner")
.Include("Inner.first")
.Include("Inner.second")
.Where(x => ( !(x.IsDeleted) && (x.IsActive) &&
(x.itemid == id))).FirstOrDefault();
In the above code "Inner" is another list of item. Now i need to filter inner items. I only need inner item with filter condition inner.isDeleted = true.
Query should return a class like,
public class Order
{
public string Name { get; set; }
public List<InnerDetails> Inner{ get; set; }
public bool IsDeleted { get; set; }
}
and InnerDetails class like
public class InnerDetails
{
public string Sample { get; set; }
public bool IsDeleted { get; set; }
public int firstId { get; set; }
public int secondID { get; set; }
public First first{ get; set; }
public Second second{ get; set; }
}
Can anyone suggest me a better approach to do this because i am new in LINQ and EF?
Disclaimer: I'm the owner of the project Entity Framework Plus
EF+ Query IncludeFilter feature allows filtering related entities.
var item = _Context.Order
.IncludeFilter(x => x.Inner.Where(y => y.IsDeleted))
.IncludeFilter(x => x.Inner.Where(y => y.IsDeleted).Select(y => y.first))
.IncludeFilter(x => x.Inner.Where(y => y.IsDeleted).Select(y => y.second))
.Where(x => ( !(x.IsDeleted) && (x.IsActive) &&
(x.itemid == id))).FirstOrDefault();
Note: You cannot mix Include & IncludeFilter.
Wiki: EF+ Query IncludeFilter
EDIT: Answer sub-question
But we can achieve this using EF only
Yes, under the hood, my library uses a similar solution as projection
var item = _Context.Order.Select(x => new {
Order = x,
Inner = x.Inner.Where(y => y.IsDeleted),
first = x.Inner.Where(y => y.IsDeleted).Select(y => y.first)
second = x.Inner.Where(y => y.IsDeleted).Select(y => y.second)
})
.Where(x => ( !(x.IsDeleted) && (x.IsActive) && (x.itemid == id)))
.FirstOrDefault()
.Select(x => x.Order)
.FirstOrDefault();
Note: Code have not been tested
EDIT: Answer comment
I came across this issue in EF Core. Are you going to implement IncludeFilter also in the EF+Core version
Starting from the v1.10.0, the IncludeFilter is now supported in EF Core 2.x
See: Release Note
EDIT: Answer sub-question
How can I ThenInclude after filtering
We do not have a ThenInclude yet.
So you need to use IncludeFilter again with all filter and navigate through the list or entity you want to include.
You can achieve this in EF Core itself:
var item =
ctx.Order.Include("Inner")
.Select(x => x.Inner
.Where(innerItem => innerItem.IsDeleted))
.FirstOrDefault();
Or,
var item =
ctx.Order.Include(o=> o.Inner
.Where(x => x.IsDeleted));