c# - Filter based on a string value in List<string> column in a table Entity Framework Core

Question

I have a table with the following structure (code first approach using Entity Framework Core) in PostgreSQL

public class Product_Order
{

    [Key]
    public string product_number { get; set; }
    public string customer_product_number { get; set; }
    public List<string> product_statuses { get; set; }
    public bool is_test { get; set; } = false;
    public DateTime created_at { get; set; } = DateTime.UtcNow;
    public DateTime updated_at { get; set; } = DateTime.UtcNow;
    public string created_by { get; set; } = "system";
    public string updated_by { get; set; } = "system";
}

Now, the product_statuses column usually contains of a list of statuses - ready, pickedup, scheduled, closed, cancelled.

I need to come up with a solution which returns me a list of product orders which DOES NOT CONTAIN orders which are closed or cancelled.

Here's the solution that I have at the moment which is not filtering as expected

_context.Product_Order.Where(t => t.is_test && !t.statuses.Contains("closed") && !t.statuses.Contains("cancelled")).ToList();

question from:https://stackoverflow.com/questions/65949412/filter-based-on-a-string-value-in-liststring-column-in-a-table-entity-framewor

Answer 1

I think your code is ok for your data structure to find that information. I have created a dummy class and list to replicate your data and list. And I was able to find data by using you code. Sample Code given below what I have tested =>

void Test()
{
            List<Product_Order> items = new List<Product_Order>();
            var temp = new Product_Order() { product_number = "001", isTest = true };
            temp.product_statuses = new List<string>();
            temp.product_statuses.Add("good");
            temp.product_statuses.Add("greate");
            temp.product_statuses.Add("new");
            items.Add(temp);
            temp = new Product_Order() { product_number = "002", isTest = true };
            temp.product_statuses = new List<string>();
            temp.product_statuses.Add("good");
            temp.product_statuses.Add("bad");
            temp.product_statuses.Add("notnew");
            items.Add(temp);
            temp = new Product_Order() { product_number = "003", isTest = true };
            temp.product_statuses = new List<string>();
            temp.product_statuses.Add("n/a");
            temp.product_statuses.Add("bad");
            temp.product_statuses.Add("Closed");
            items.Add(temp);
            temp = new Product_Order() { product_number = "004", isTest = false };
            temp.product_statuses = new List<string>();
            temp.product_statuses.Add("n/a");
            temp.product_statuses.Add("bad");
            temp.product_statuses.Add("Cancelled");
            items.Add(temp);
            var finalOutput = items.Where(c => c.isTest == true && !c.product_statuses.Where(v => v.ToLower() == "closed").Any() && !c.product_statuses.Where(v => v.ToLower() == "cancelled").Any()).ToArray();
}

public class Product_Order
{
        public string product_number { get; set; }
        public bool isTest { get; set; }
        public List<string> product_statuses { get; set; }
}

Finally , I think it is your data what not wright with you lambda expression. So, I modified for you a little bit.And that is
FINAL ANSWER:

var finalOutput = _context.Product_Order.Where(c => c.isTest == true && !c.product_statuses.Where(v => v.ToLower() == "closed").Any() && !c.product_statuses.Where(v => v.ToLower() == "cancelled").Any()).ToArray();

Please check my code and let me know.