Преобразование выражения LINQ в хранимую процедуру SQL Server
В серверной части .NET моего мобильного приложения Azure мне нужно найти все строки, в которых столбец, состоящий из строки с разделителями-запятыми, содержит все строки в списке. Я использую следующее протестированное выражение LINQ:
List<Items> filteredItems = _context.Items
.Where(item => colorsToFilter.All(color => item.Colors.Contains(color)))
.ToList();
Однако EF6 не поддерживает .All в выражении LINQ. Поэтому я пытаюсь преобразовать это выражение в хранимую процедуру SQL, которая принимает список строк в качестве параметра.
Примеры столбцов из обеих таблиц, используемых в запросе:
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
Все предметы в ColorsToFilter (List<string>) должны быть в Colors. Таким образом, в этом примере будут возвращены строки 1, 3 и 4.
Как бы я сделал это в SQL, используя хранимую процедуру с параметром таблицы, представляющим список?
Является ли ColorsToFilter / colorsToFilterList<string> или таблицей, или IQueryable из базы данных в вашем выражении LINQ? Как его можно протестировать, если он не работает в EF6? Лучше всего использовать средство записи Expression для создания предиката, приемлемого для EF, при условии, что colorsToFilter — это короткий список.
Я протестировал его в проекте C# локально не в базе данных, а в локальном представлении таблицы. ColorsToFilter — это List<string>, который будет передан в хранимую процедуру с использованием параметра таблицы.
Ответы 2
Поскольку вы используете All, это на самом деле просто — вы можете просто объединить Where условия в своем запросе и остаться в EF:
var q = _context.Items.AsQueryable();
foreach (var cf in colorsToFilter) {
q = q.Where(item => item.Colors.Contains(cf));
}
var filteredItems = q.ToList();
Снова используя LINQ, вы можете сделать это в одном выражении:
var filteredItems = colorsToFilter.Aggregate(_context.Items.AsQueryable(), (q, f) => q.Where(item => item.Colors.Contains(f))).ToList();
Обратите внимание, что Contain будет принимать подцвета - если это неприемлемо, вам нужно использовать более жесткие условия, такие как (","+item.Colors+",").Contains(","+cf+","), чтобы, например. синий не будет соответствовать голубому.
Спасибо, это был просто общий пример, основанный на том, что я пытаюсь сделать. К счастью, у меня не будет этой проблемы с данными, которые я использую, но это полезно знать.
Лично я бы расположил структуру своей таблицы так, чтобы данные, которые я хотел запросить таким образом, не находились в списке csv в столбце таблицы.
Если вы решили использовать хранимую процедуру sql, вы можете использовать приведенную ниже. Вы можете создать тип таблицы и передать его в приложение, затем вы можете использовать функцию string_split, чтобы превратить ваш список csv в таблицу, которую вы можете запросить, и оператор all, чтобы проверить, есть ли все в вашем параметре таблицы. Последний бит, когда я вызываю процедуру, которую вы бы не сделали, вы бы вместо этого вызвали хранимую процедуру в своем С#, в С# вы можете создать таблицу данных с одним строковым столбцом с именем StringValue, заполнить его своим списком цветов фильтра и отправить его как sql (обратите внимание, что вам нужно указать имя вашего пользовательского типа, иначе вы получите сообщение об ошибке.)
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
Я не был уверен, но представитель Microsoft, с которым я связался, сказал, что я не смогу сделать это с помощью LINQ и вместо этого должен рассмотреть возможность использования хранимой процедуры. Спасибо вам за помощь.
Вам нужно начать свой SP ... написание полного SP - слишком широкий вопрос для SO.