String intersection using EF Core, Linq / SQL Server

It's actually pretty easy thanks to of EF-core's smooth support for mapping database functions. In this case we need a table-valued function (TVF) that can be called in C# code.

It starts with adding a TVF to the database, which, when using migrations, requires an addition to migration code:

ALTER FUNCTION [dbo].[SplitCsv] (@string nvarchar(max))
RETURNS TABLE
AS
    RETURN SELECT ROW_NUMBER() OVER (ORDER BY Item) AS ID, Item
    FROM (SELECT Item = [value] FROM string_split(@string, ',')) AS items

This TVF is mapped to a simple class in EF's class model:

class CsvItem
{
    public long ID { get; set; }
    public string? Item { get; set; }
}

For this mapping to succeed the SQL function always returns unique ID values.

Then a method, only to be used in expressions, is added to the context:

public IQueryable<CsvItem> SplitCsv(string csv) => FromExpression(() => SplitCsv(csv));

...and added to the model:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasDbFunction(typeof(MyContext)
        .GetMethod(nameof(SplitCsv), new[] { typeof(string) })!);
}

That's all! Now you can execute queries like these (in Linqpad, db is a context instance):

db.Meetings.Where(m => db.SplitCsv(m.DbColumnCommaSeparated).Any(i => i.Item == "c")).Dump();

var csv = "a,d,d";
db.Meetings.Where(m => db.SplitCsv(m.DbColumnCommaSeparated)
    .Intersect(db.SplitCsv(csv)).Any()).Dump();

Generated SQL:

  SELECT [m].[Id], [m].[DbColumnCommaSeparated], [m].[MeetingName]
  FROM [Meetings] AS [m]
  WHERE EXISTS (
      SELECT 1
      FROM [dbo].[SplitCsv]([m].[DbColumnCommaSeparated]) AS [s]
      WHERE [s].[Item] = N'c')

  SELECT [m].[Id], [m].[DbColumnCommaSeparated], [m].[MeetingName]
  FROM [Meetings] AS [m]
  WHERE EXISTS (
      SELECT 1
      FROM (
          SELECT [s].[ID], [s].[Item]
          FROM [dbo].[SplitCsv]([m].[DbColumnCommaSeparated]) AS [s]
          INTERSECT
          SELECT [s0].[ID], [s0].[Item]
          FROM [dbo].[SplitCsv](@__csv_1) AS [s0]
      ) AS [t])

One remark. Although it can be done, I wouldn't promote it. It's usually much better to store such csv values as a table in the database. It's much easier in maintenance (data integrity!) and querying.

What I can suggest with this particular database design is of course based on EF Core Mapping a queryable function to a table-valued function, similar to @GertArnold suggestion. However since the built-in SqlServer SPLIT_STRING function is already TVF, it can be mapped directly thus eliminating the need of custom db function and migration.

First, we'll need a simple keyless entity type (class) to hold the result, which according to the docs is a table with record having a single string column called "value":

[Keyless]
public class StringValue
{
    [Column("value")]
    public string Value { get; set; }
}

Next is the function itself. It could be defined as instance method f your db context, but I find it most appropriate to be a static method of some custom class, for instance called SqlFunctions:

public static class SqlFunctions
{
    [DbFunction(Name = "STRING_SPLIT", IsBuiltIn = true)]
    public static IQueryable<StringValue> Split(string source, string separator)
        => throw new NotSupportedException();
}

Note that this is just a "prototype" which never is supposed to be called (hence the throw in the "implementation") and just describes the traslation of actual db function call inside the query. Also all these attributes are arbitrary and the name, built-in etc. attributes can be configured fluently. I've put them here just for clarity and simplicity, since they are enough in this case and don't need the flexibility provided by fluent API.

Finally you have to register the db function for your model, by adding the following to the OnModelCreating override:

modelBuilder.HasDbFunction(() => SqlFunctions.Split(default, default));

The HasDbFunction overload used is the simplest and typesafe way of providing the information about your method using strongly typed expression rather than reflection.

And that's it. Now you can use

var query = db.Set<Meeting>()
    .Where(m => SqlFunctions.Split(m.DbColumnCommaSeparated, ",")
        .Any(e => ArrayOfInputString.Contains(e.Value)));

which will be translated to something like this:

SELECT [m].[Id], [m].[DbColumnCommaSeparated], [m].[MeetingName]
FROM [Meeting] AS [m]
WHERE EXISTS (
    SELECT 1
    FROM STRING_SPLIT([m].[DbColumnCommaSeparated], N',') AS [s]
    WHERE [s].[value] IN (N'a', N'b', N'c'))

with IN clause being different depending of the ArrayOfInputString content. I'm kind of surprised it does not get parameterized as in a "normal" Contains translation, but at least it gets translated to something which can be executed server side.

One thing to note is that you need to flip ArrayOfInputString and the split result set in the LINQ query, since there is another limitation of EF Core preventing translation of anything else but Contains method of in memory collections. Since here you are looking for intersection, it really doesn't matter which one is first, so putting the queryable first avoids that limitation.

Now that you have a solution for this db design, is it good or not. Well, this seems to be arbitrary and opinion based, but in general using normalized tables and joins is preferred, since it allows db query optimizers to use indexes and statistics when generating execution plans. Joins are very efficient since they almost always use efficient indexed scans, so in my (and most of the people) opinion you should not count them when doing the design. In this particular case though I'm not sure if normalized detail table with indexed single text value would produce better execution plan than the above (which due to the lack of information would do a full table scan evaluating the filter for each row), but it's worth trying, and I guess it won't be worse at least.

Also, all this applies to relational databases. Non relational databases can in fact contain "embedded" arrays or lists of values, which then can be used to store and process such data instead of comma separated string. In either cases, I would prefer normalized design, storing a list of values either as "embedded" or in related detail table instead of single comma separated string. But again, that's just e general opinion/preference. The single string (containing tags list for instance) is valid approach which may outperform the other for some operations, so you whatever is appropriate for you. Also note that SPLIT_STRING is not a standard db function, so in case you need to work with other database than SqlServer, you'll have a problem of finding similar function if it exists at all.

EF Core has FromSqlRaw why not using it ?

here is an extension method that would work with DbSet<T> :

public static class EntityFrameworkExtensions
{
    private const string _stringSplitTemplate = "SELECT * FROM {0} t1 WHERE EXISTS (SELECT 1 FROM STRING_SPLIT(t1.{1}, ',') s WHERE s.[value] IN({2}));";

    public static IQueryable<TEntity> StringSplit<TEntity, TValue>(this DbSet<TEntity> entity, Expression<Func<TEntity, TValue>> keySelector, IEnumerable<TValue> values) 
        where TEntity : class
    {         
        var columnName = (keySelector.Body as MemberExpression)?.Member?.Name;

        if (columnName == null) return entity;

        var queryString = string.Format(_stringSplitTemplate, entity.EntityType.GetTableName(), columnName, string.Join(',', values));

        return entity.FromSqlRaw(queryString);
    }
}

usage :

var result = context.Meetings.StringSplit(x=> x.DbColumnCommaSeparated, ArrayOfInputString).ToList();

this would generate the following SQL :

SELECT *
FROM table t1
WHERE EXISTS (
    SELECT 1
    FROM STRING_SPLIT(t1.column, ',') s
    WHERE 
        s.value IN(...)
);

ANSWER 1: SQL CLR Approach
STEP 1: Test SQL Schema

create table Meeting
(
    Id int identity(1,1) primary key,
    MeetingName nvarchar(max) null,
    DbColumnCommaSeparated nvarchar(max) not null
)
go
truncate table Meeting
insert into Meeting
values('one','1,2,3,4');
insert into Meeting
values('two','5,6,7,8');
insert into Meeting
values('three','1,2,7,8');
insert into Meeting
values('four','11,22,73,84');
insert into Meeting
values('five','14,25,76,87');

STEP 2: Write SQL CLR Function read more

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Data.Linq;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean FilterCSVFunction(string source, string item)
    {
        return new SqlBoolean(Array.Exists(source.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), i => i == item));
    }
}

STEP 3: Enable SQL CLR for database

EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;  
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'clr strict security', 0;
RECONFIGURE;

STEP 4: Actual Query

DECLARE @y nvarchar(max)
SET @y = '7'
SELECT * FROM Meeting
WHERE dbo.FilterCSVFunction(DbColumnCommaSeparated, @y) = 1

STEP 5: You can import the function in ef

ANSWER 2 Updated

DECLARE @ArrayOfInputString TABLE(y INT);
INSERT INTO @ArrayOfInputString(y) VALUES(7);
INSERT INTO @ArrayOfInputString(y) VALUES(8);

SELECT DISTINCT M1.* FROM Meeting M1 JOIN
(
    SELECT ID, Split.a.value('.', 'VARCHAR(100)') As Data FROM 
    (SELECT M2.ID, CAST ('<M>' + REPLACE(DbColumnCommaSeparated, ',', '</M><M>') + '</M>' AS XML) as DbXml FROM Meeting M2) A
    CROSS APPLY A.DbXml.nodes ('/M') AS Split(a)
) F ON M1.ID = F.ID
WHERE F.Data IN (SELECT y FROM @ArrayOfInputString)