CODEWITHSUNDEEP
postgresqlnpgsql.net-6.0c#-10.0ef-core-6.0
Diego Bianchi
Diego Bianchi

Reputation: 742

EF CORE PostgreSQL - Check if ALL elements of a List are contained in another List

I am looking for a way in Entity framework to check if all elements of a List are contained in another List.

My application is a .NET 6, i'm using Npgsql for querying my PostgreSQL db, i have a List of IDs and a class "Company" related to another table "Certifications".

The company class looks like that:

    public class Company
    {
        public int ID { get; set; }
        public List<Certification> Certifications { get; set; }
        ...
    }

and my certifications class looks like that:

public class Certification
{
    public int ID { get; set; }
    public int? CompanyID { get; set; }
    public CertificationType Type { get; set; }

    public int CategoryID { get; set; }
    public virtual Category Category { get; set; }

    public int? RankingID { get; set; }
    public virtual Ranking Ranking { get; set; }
    ...
}

The query is a pagination query to show the companies, it must be executed on the DB (no client side query), i'm trying to write it with LINQ but i'm getting the following error:

The LINQ expression 'opt => DbSet() .Where(c0 => EF.Property<int?>(EntityShaperExpression: ALPHUBs.ML.Models.Company ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember IsNullable: False , "ID") != null && object.Equals( objA: (object)EF.Property<int?>(EntityShaperExpression: ALPHUBs.ML.Models.Company ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember IsNullable: False , "ID"), objB: (object)EF.Property<int?>(c0, "CompanyID"))) .Select(c0 => c0.CategoryID) .Any(p => p == opt)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

I've tried in those ways:

IQueryable<Company> query = this._context.Companies.AsNoTracking()
                                                    .Include(c => c.Certifications)
                                                    .AsSplitQuery();

List<int> categoryList = new List<int>() { 1, 3, 5 };

//ATTEMPT 1
query.Where(c => categoryList.All(opt => c.Certifications.Select(cert => cert.CategoryID).Contains(opt)));
//ATTEMPT 2
query.Where(c => categoryList.Except(c.Certifications.Select(cert => cert.CategoryID)).Count() == 0);

The only way i can make it work is this:

 c.Certifications.Any(cert => categoryList.Contains(cert.CategoryID));

But this give me the wrong result, because i need all the companies that have all the given certifications, not the companies that have at least one of the given certifications.

Upvotes: 1

Views: 717

Answers (1)

Shay Rojansky
Shay Rojansky

Reputation: 16722

How about something like the following:

var categoryList = new List<int> { 1, 3, 5 };
_ = ctx.Companies
    .Where(c => c.Certifications.Count(c => categoryList.Contains(c.ID)) == categoryList.Count)
    .ToList();

This assumes that for a given Company there will only be at most one Certification with a given ID.

Upvotes: 2

Related Questions