Converting LINQ Expression to SQL Server stored procedure

Question

In my Azure Mobile App's .NET back end, I need to find all rows in which a column consisting of comma delimited string contains all strings in a list. I'm using the following LINQ expression which has been tested:

List<Items> filteredItems = _context.Items
                                    .Where(item => colorsToFilter.All(color => item.Colors.Contains(color)))
                                    .ToList();

However, EF6 does not support the .All in a LINQ expression. Therefore, I am trying to convert this expression into a SQL stored procedure which takes a list of strings as parameter.

Examples of the columns from both tables used in the query would be:

Colors:                            ColorsToFilter:

1     blue,green,red               1     blue
2     green                        2     green
3     blue,green,red,black         3     red
4     orange,blue,green,red,gray

All items in ColorsToFilter (List<string>) must be in Colors. So rows 1, 3, and 4 would be returned in this example.

How would I do this in SQL using a stored procedure with a table parameter representing the list?

Answer 1

Personally I would layout my table structure so that data i wanted to query in that manner was not in a csv list in a table column.

If you are determined to use a sql stored procedure you can use the below. You can create a table type and pass it into the application you can then use the string_split function to turn your csv list into a table you can query and the all operator to check if it everything in your table parameter is there. The last bit where i call the procedure you would not do you would instead call the stored procedure in your c#, in c# you can create a datatable with a single string column called StringValue populate it with your list of filter colours and submit it as a sql parameter(note you need to tell it the name of your user defined type or you will get an error.)

   CREATE DATABASE Mycolours;
go
USE Mycolours
GO

/****** Object:  Table [dbo].[Colours]    Script Date: 24/04/2019 23:13:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Colours](
    [ColourID] [int] NOT NULL,
    [ColourList] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO
GO
insert into [Colours](ColourID,ColourList) values(1,'blue,green,red'),(2,'green'),(3,'blue,green,red,black'),(4,'orange,blue,green,red,gray')
/****** Object:  StoredProcedure [dbo].[SPGetAllMyColours]    Script Date: 24/04/2019 23:14:10 ******/
SET ANSI_NULLS ON
GO
GO

/****** Object:  UserDefinedTableType [dbo].[StringValues]    Script Date: 24/04/2019 23:14:32 ******/
CREATE TYPE [dbo].[StringValues] AS TABLE(
    [StringValue] [nvarchar](50) NOT NULL,
    PRIMARY KEY CLUSTERED 
(
    [StringValue] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO


SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[SPGetAllMyColours]
    @colours dbo.StringValues READONLY
AS
BEGIN
    select * from colours where not exists(select StringValue from @colours where StringValue = all(select value from string_split(colours.ColourList,',')));
END
GO



GO

DECLARE @return_value int
declare @filterColours dbo.StringValues;

insert into @filterColours(StringValue) values('blue'),('green'),('red');

EXEC    @return_value = [dbo].[SPGetAllMyColours] @filterColours

SELECT  'Return Value' = @return_value

GO

Answer 2

Since you are using All, this is actually straightforward - you can just concatenate Where conditions on your query and stay in EF:

var q = _context.Items.AsQueryable();
foreach (var cf in colorsToFilter) {
    q = q.Where(item => item.Colors.Contains(cf));
}

var filteredItems = q.ToList();

Using LINQ again, you can actually do it in one statement:

var filteredItems = colorsToFilter.Aggregate(_context.Items.AsQueryable(), (q, f) => q.Where(item => item.Colors.Contains(f))).ToList();