Check if id's in a comma-separated string match any id's in another string array

Question

I have two tables in DB Master Setup Approval and Order Details and I want check On any Master Approval Setup, this purchase order will go on relying CostCenter.

Table Master Setup Approval:

|-------|---------|-------------|-------------|---------------|---------------------|
|    ID |    Name |   CRG_COM_ID|  CRG_BRN_ID |ApprovalTypeId |CostCenter(string)   |
|-------|---------|-------------|-------------|---------------|---------------------|                            
|    1  | Setup1  |     1       |     1       |     1         |    "1,2,5,7"        |
|-------|---------|-------------|-------------|---------------|---------------------|
|    2  | Setup2  |     1       |     1       |     1         |     "1,3,6"         |     
|-------|---------|-------------|-------------|---------------|---------------------|

Table OrderDetails :

|-------|---------|-------------|-------------|------------------|
|    ID |    Name |   CRG_COM_ID|  CRG_BRN_ID |CostCenterID(long)|
|-------|---------|-------------|-------------|------------------|                            
|    1  | Item1   |     1       |     1       |       1          |
|-------|---------|-------------|-------------|------------------|
|    2  | Item2   |     1       |     1       |       7          |     
|-------|---------|-------------|-------------|------------------|

This is my code:

var orderDetails = db.OrderDetails.Where(c => c.OrderId == orderId);
var  costc = orderDetails.Select(c => c.CostCenterId.Value).ToList().ConvertAll<string>(delegate (long i) { return i.ToString(); });   
var ApprovalProcess_Count12 = db.MasterSetupApproval.Where(x =>
    x.CRG_COM_ID == order.CompanyId &&
    (x.CRG_BRN_ID == null || x.CRG_BRN_ID == order.BranchId) &&
    x.ApprovalTypeId == (int)ApprovalTypes.PO &&
    x.CostCenter.Split(',').Select(aee => aee).Any(val => costc.Contains(val))
).ToList();

I am getting the following error:

LINQ to Entities does not recognize the method 'System.String[] Split(Char[])' method, and this method cannot be translated into a store expression.

Output should be:

|-------|---------|-------------|-------------|---------------|---------------------|
|    ID |    Name |   CRG_COM_ID|  CRG_BRN_ID |ApprovalTypeId  |CostCenter(string)   |
|-------|---------|-------------|-------------|---------------|---------------------|                            
|    1  | Setup1  |     1       |     1       |     1         |    "1,2,5,7"        |
|-------|---------|-------------|-------------|---------------|---------------------|

Rajeev Pande · Accepted Answer · 2020-05-25 13:41:40Z

1

Assuming you are working with a badly-designed DB (as pointed out by Crowcoder) that comma-separated values should not be present in a database, you may refer this to tackle your way through.

HTH!

answered May 25, 2020 at 13:41

Rajeev Pande

4863 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Check if id's in a comma-separated string match any id's in another string array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related