如何使用c#中的linq检查存储在数据基列中具有给定值的逗号分隔值

I have a table named product and category in my sql server database. Product table contains the column category_id to store mapping categories. One product may mapped to any number of categories ex.category_id(1,25,44,11,21), mapping ids will be stored as comma separated string.

我在sql server数据库中有一个名为product和category的表。Product表包含用于存储映射类别的列category_id。一个产品可以映射到任意数量的category(1,25,44,11,21)，映射id将被存储为逗号分隔的字符串。

Now I want to retrieve the products from product table where the product contains the selected category from dropdown menu using linq.

现在我想从product表中检索产品，其中产品使用linq从下拉菜单中包含所选的类别。

For example i want to retrieve the products where category id =1. My linq query is

例如，我想检索category =1的产品。我的linq查询

var prodlst = (from p in db.Products
           where (p.PCategory_Id.EndsWith(""+categoryid+"")||
           p.PCategory_Id.Contains("," + categoryid + ",") ||
           p.PCategory_Id.Contains("" + categoryid + ",") ||
           p.PCategory_Id.Contains("," + categoryid + ""))
           select new ProductBO
           {
             Id = p.Id,
             Product_Name = p.Name,
             Price = p.Price
    }).ToList();

It retrieves all the values where category id = 1 or 11 or 21, but i want to retrieve only if the category id is exactly 1

它检索类别id为1、11或21的所有值，但我只想检索类别id为1的值

3 个解决方案

#1

i am not sure if EntityFramework can translate this. If it doesn't try make it enumerable.

我不确定EntityFramework是否可以翻译这个。如果它不尝试让它可枚举。

db.Products.Where(t=> PCategory_Id.Split(',').Select(int.Parse).Contains(categoryid));

db.Products.AsEnumerable().Where(t=> PCategory_Id.Split(',').Select(int.Parse).Contains(categoryid));

#2

This is in the way of your syntax. Only the 2nd clause needs to be 'Contains' (also I changed Equals to ==) :

这是语法上的问题。只需要第二个子句为“Contains”(我也将等号改为==):

var prodlst = (from p in db.Products
       where ( (p.PCategory_Id == categoryid) ||
       p.PCategory_Id.Contains("," + categoryid + ",") ||
       p.PCategory_Id.StartsWith(categoryid + ",") ||
       p.PCategory_Id.EndsWith("," + categoryid))
       select new ProductBO
       {
         Id = p.Id,
         Product_Name = p.Name,
         Price = p.Price
}).ToList();

#3

var prodlst = (from p in db.Products
                               where (p.PCategory_Id == ""+categoryid ||
                               p.PCategory_Id.Contains("," + categoryid + ",") ||
                               p.PCategory_Id.StartsWith(categoryid + ",") ||
                               p.PCategory_Id.EndsWith("," + categoryid))
                               select new ProductBO
                               {
                                   Id = p.Id,
                                   Product_Name = p.Name,
                                   Price = p.Price
                               }).ToList();

#1