Entity Framework - Get parents based on child collection-CodePudding

I should retrieve Tickets where, for each, the last Messages has TeamId not null.

With code:

var test1 = await _context.Tickets.Include(x => x.Messages)
            .Where(x => x.Messages.OrderByDescending(y => y.InsertDate).First().TeamId != null).ToListAsync();

var test2 = await _context.Tickets.Include(x => x.Messages)
            .Where(x => x.Messages.Last().TeamId != null).ToListAsync();

both returns all tickets, even those containing only one element in Messages with TeamId null.

What is the right query to get Tickets where the last element of does not have the TeamId null?

CodePudding user response：

If you are using EF core 5, then its possible, that filtered include would work, although I don't remember if the First() is supported. Give it a try.

Also, I do not have the way to check this code - I'm typing from mobile, so you should validate it.

var test1 = await _context.Tickets
.Include(x => x.Messages.Where(x => x.Messages.OrderByDescending(y => y.InsertDate).First().TeamId != null))
.Where(x => x.Messages != null)
.ToListAsync();

CodePudding user response：

The issue looks related to the new ef 6 https://github.com/dotnet/efcore/issues/26744