I have the following SQL Query
SELECT T0.* FROM Accounting T0 WHERE (SELECT COUNT(ParentAccountId) FROM Accounting WHERE ParentAccountId = T0.AccountId) = 0 It gives me the accounts that have no child. I'm trying to use a Lambda Expression to get the same result on my code.
List<Accounting> Items = await _context.Accounting.Where(q => q.ParentAccountId == q.AccountId).ToListAsync(); How can I place the COUNT condition on my code?
Shortest Answer:
List<MyParent> noKidParents = myDbContext.MyParents.Where(par => !par.MyChildren.Any()); Short Answer:
////using System; //// using System.Collections.Generic; //// using System.Linq; //// using System.Threading; //// using System.Threading.Tasks; //// using Microsoft.EntityFrameworkCore; public async Task<IEnumerable<MyParentEntity>> GetNoChildrenRows(CancellationToken token) { List<MyParentEntity> returnItems = await this.entityDbContext.MyParents.Where(ent => !ent.MyChildren.Any()).ToListAsync(token); return returnItems; } Longer Explained Answer:
You should not prefer "Count" answers.
You should prefer ".Any()" (or !.Any() in this case) answers.
Given POCO's
public partial class MyChildEntity { public Guid MyChildKey { get; set; } /* PK */ public Guid MyParentUUID { get; set; } /* FK to the "parent" */ public MyParentEntity ParentMyParent { get; set; } } and
public partial class MyParentEntity { public MyParentEntity() { this.MyChilds = new List<MyChildEntity>(); } public Guid MyParentKey { get; set; } /* PK */ public ICollection<MyChildEntity> MyChilds { get; set; } } and a mapping like this (mine is coded to EF Core, but the final answer (the .Where EF query below) should work the same, even if you're not using EF Core, but a DotNet Framework EF.. as long as everything is "mapped" correctly)
//// using Microsoft.EntityFrameworkCore; //// using Microsoft.EntityFrameworkCore.Metadata.Builders; public class MyChildMap : IEntityTypeConfiguration<MyChildEntity> { public const string SchemaName = "dbo"; public const string TableName = "MyChild"; public void Configure(EntityTypeBuilder<MyChildEntity> builder) { builder.ToTable(TableName, SchemaName ); builder.HasKey(k => k.MyChildKey); builder.Property(cnpk => cnpk.MyChildKey).HasColumnName("MyChildUUID"); builder.Property(req => req.MyChildKey).IsRequired(); builder.Property(cn => cn.MyParentUUID).HasColumnName("MyParentUUID"); builder.Property(req => req.MyParentUUID).IsRequired(); builder.HasIndex(ind => new { ind.MyParentUUID, ind.MyChildName }).IsUnique(true); builder.HasOne<MyParentEntity>(e => e.ParentMyParent) .WithMany(d => d.MyChilds) .HasForeignKey(e => e.MyParentUUID).HasConstraintName("MyParentUUIDFK"); } } and
public class MyParentMap : IEntityTypeConfiguration<MyParentEntity> { public const string SchemaName = "dbo"; public const string TableName = "MyParent"; public void Configure(EntityTypeBuilder<MyParentEntity> builder) { builder.ToTable(TableName, SchemaName); builder.HasKey(k => k.MyParentKey); builder.Property(cnpk => cnpk.MyParentKey).HasColumnName("MyParentUUID"); builder.Property(req => req.MyParentKey).IsRequired(); } } and a context like this:
////using Microsoft.EntityFrameworkCore; public class MyEntitiesDbContext : DbContext { public MyEntitiesDbContext (DbContextOptions<MyEntitiesDbContext > options) : base(options) { } public DbSet<MyParentEntity> MyParents { get; set; } public DbSet<MyChildEntity> MyChilds { get; set; } } Your EF code can look like this:
////using System; //// using System.Collections.Generic; //// using System.Linq; //// using System.Threading; //// using System.Threading.Tasks; //// using Microsoft.EntityFrameworkCore; public async Task<IEnumerable<MyParentEntity>> GetNoChildrenRows(CancellationToken token) { List<MyParentEntity> returnItems = await this.entityDbContext.MyParents.Where(ent => !ent.MyChildren.Any()).ToListAsync(token); return returnItems; } NOTE, in Sql Server, the EF "where" query above.... does (properly) equate to a "NOT EXISTS" query:
SELECT [m].[MyParentUUID] FROM [dbo].[MyParent] AS [m] WHERE NOT (EXISTS ( SELECT 1 FROM [dbo].[MyChild] AS [m0] WHERE [m].[MyParentUUID] = [m0].[MyParentUUID])) Try this :
List<Accounting> Items = await _context.Accounting.Where(q => q.Accounting.Where(r => r.ParentAccountId == q.AccountId).count()==0).ToListAsync(); 
q is an Accounting item, but you are using it as if it equals _context.
Accountingentity had aChildrencollection, you could write_context.Accounting.Where(q=>!q.Children.Any())WHERE NOT EXISTSin SQL, and then it becomes more obvious to use a corresponding!yourCollection.Any(q => ...)in LINQ.