Filter SQL Based on C# List Instead of a Filter Table

Filter sql based on C# List instead of a filter table

Personally I can see two tricks you can pursue. And one "blast from the past" more.

Route #1. Use GTT: GLOBAL TEMPORARY TABLE

GTTs were introduced in FB 2.1 (and u use it) and can be per-connection or per-transaction. You would want the per-transaction one. This difference is about the data(rows), the schema(structure and indexes, the meta-data) is persistent. See ON COMMIT DELETE ROWS option in the GTT documentation.

https://www.firebirdsql.org/refdocs/langrefupd21-ddl-table.html
http://firebirdsql.su/doku.php?id=create_global_temporary_table and www.translate.ru
Firebird global temporary table (GTT), touch other tables?

and so on.

In that way, you open the transaction, you fill the GTT with the data from your list (copying those 1500 value-pairs of data from your workstation to the server), you run your query JOINing over that GTT, and then you COMMIT your transaction and the table content is auto-dropped.

If you can run many almost-similar queries in the session, then it might make sense to make that GTT per-connection instead and to modify the data as you need, rather than re-fill it for every next query in every next transaction, but it is a more complex approach. Cleanse-early on every COMMIT is what i'd prefer as default approach until argued why per-connection would be better in this specific case. Just not to keep that garbage on the server between queries.

Route #2. Use string search - reversed LIKE matching.

In its basic form this method works for searching for some huge and arbitrary list of integer numbers. Your case is a bit more complex, you match against PAIRS of numbers, not single ones.

The simple idea is like that, let's assume we want to fetch rows where ID column can be 1, 4, 12, 24.
Straightforward approach would be either making 4 queries for every value, or making WHERE ID = 1 or ID = 4 or ... or using WHERE id IN (1,4,12,24). Internally, IN would be unrolled into that very = or = or = and then most probably executed as four queries. Not very efficient for long lists.

So instead - for really long lists to match - we may form a special string. And match it as a text. This makes matching itself much less efficient, and prohibits using any indexing, the server runs a NATURAL SCAN over a whole table - but it makes a one-pass scan. When the matching-list is really large, the one-pass all-table scan gets more efficient than thousands of by-index fetches.
BUT - only when the list-to-table ratio is really large, depends on your specific data.

We make the text enlisting all our target values, interspersed by AND wrapped into a delimiter: "~1~4~12~24~". Now we make the same delimiter-number-delimiter string of our ID column and see whether such a substring can be found.

The usual use of LIKE/CONTAINING is to match a column against data like below: SELECT * from the_table WHERE column_name CONTAINING value_param
We reverse it, SELECT * from the_table WHERE value_param CONTAINING column_name-based-expression

  SELECT * from the_table WHERE '~1~4~12~24~' CONTAINING '~' || ID || '~'

This assumes ID would get auto-casted from integer to string. IF not you would have to do it manually: .... CONTAINING '~' || CAST( ID as VARCHAR(100) ) || '~'

Your case is a bit more complex, you need to match two numbers, Department and Number, so you would have to use TWO DIFFERENT delimiters, if you follow this way. Something like

SELECT * FROM employee e WHERE
  '~1@10~1@11~2@20~3@7~3@66~' CONTAINING
  '~' || e.Department || '@' || e.Number || '~'

Gotcha: you say your target list is 1500 elements. The target line would be... long.
How exactly long???

VARCHAR in Firebird is limited with 32KB AFAIR, and longer texts should be made as text BLOBs, with reduced functionality. Does LIKE work against BLOBs in FB2.1? I don't remember, check release-notes. Also check if your library would even allow you to specify the parameter type as a BLOB not string.
Now, what is your CONNECTION CHARSET? If it would be something like Windows-1250 or Windows-1251 - then one character is one byte, and you can fit 32K characters into 32KBytes. But if the CONNECTION CHARSET your application sets is UTF-8 - then each letter takes 4 bytes and your maximum VARCHARable string gets reduced to 8K letters.

You may try to avoid using parameter for this long string and to inline the target string constant into the SQL statement. But then you may hit the limit of maximum SQL statement length instead.

See Also: MON$CHARACTER_SET_ID in c:\Program Files\Firebird\Firebird_2_1\doc\README.monitoring_tables.txt and then SYSTEM TABLES section in the FB docs how to map IDs to charset textual names.

Route #3 Poor man's GTT. Enter pseudo-tables.

This trick could be used sometimes in older IB/FB versions before GTTs were introduced.

Pro: you do not need to change your persistent SCHEMA.

Con: without changing SCHEME - you can not create indices and can not use indexed joining. And yet again, you can hit the length limit of single SQL statement.

Really, don't think this would be applicable to your case, just to make the answer complete I think this trick should be mentioned too.

select * from employee e, (
  SELECT 1 as Department, 10 as Number FROM RDB$DATABASE
  UNION ALL SELECT 1, 11 FROM RDB$DATABASE
  UNION ALL SELECT 2, 20 FROM RDB$DATABASE
  UNION ALL SELECT 3, 7 FROM RDB$DATABASE
  UNION ALL SELECT 3, 66 FROM RDB$DATABASE
) t, 
where e.Department = t.Department 
  and e.Number = t.Number

Crude and ugly, but sometimes this pseudo-table might help. When? mostly it helps to make batch INSERT-from-SELECT, where indexing is not needed :-D It is rarely applicable to SELECTs - but just know the trick.

Filtering SQL Table results based upon C# collection

Using a large IN list is not neccessarily bad, but you may get into problems if you have thousand of elements in the list since the numnber of parameters may cause problems.

But basically the other approach would be to join on some (temporary) table. Using that strategy would however require you to insert the data into the table, so unless you can reuse the query many times the work involved with inserting the filter data would largely exceed the work in returning the results.

The best advice here as anywhere else would be to look at the query analyzer for results though.

An example of the second approach using a temporary table is shown below. Here you may get the benefit of using indexes in the JOIN, but this should be contrasted to the cost of the inserts for each filter value and the complexity in the code utilizing the filter function. (A simple IN clause is autogenerated by LINQ, while the temp table approach would require quite some plumming in the code.)

   CREATE TABLE vals (v nvarchar(255), inserted DATETIME DEFAULT(getdate()))

   SELECT * FROM vals 
     WHERE v IN ('a', 'aa', 'aaa')

   DECLARE @filters TABLE (v varchar(256))

   INSERT INTO @filters (v) VALUES ('a')
   INSERT INTO @filters (v) VALUES ('aa')
   INSERT INTO @filters (v) VALUES ('aaa')

   SELECT * FROM vals INNER JOIN @filters f
     ON f.v = vals.v

Filter query by multiple parameters from the list of objects

I had this issue earlier and here how I fixed it
You have two options in this case

1- Filter based on the ids

var res1 = dbcontext.CustomerPurchases
    .Where(p => filters.Select(c=>c.PurchaseId).Contains(p.PurchaseId))
    .Where(p => filters.Select(c => c.CustomerId).Contains(p.CustomerId));

2- Use contains

var resq = await dbcontext.CustomerPurchases
    .Where(p=> filters.Contains(new Filter { CustomerId = p.CustomerId,PurchaseId = p.PurchaseId }))
    .ToListAsync();

but if you run this one you won't get any result unless you implement IEquatable

So your Filter class will look like this

public class Filter : IEquatable<Filter>
{
    public int CustomerId;
    public int PurchaseId;

    public bool Equals(Filter? other)
    {
        return this.PurchaseId == other.PurchaseId &&
            this.CustomerId == other.CustomerId;
    }
}

Here is the full code of both ways

var options = new DbContextOptionsBuilder<ApplicationDBContext>()
                  .UseInMemoryDatabase("test")
                  .Options;
var dbcontext = new ApplicationDBContext(options);
await dbcontext.CustomerPurchases.AddAsync(new CustomerPurchase { CustomerId = 1,PurchaseId = 1,Cost = 10 });
await dbcontext.CustomerPurchases.AddAsync(new CustomerPurchase { CustomerId = 1, PurchaseId = 2, Cost = 10 });
await dbcontext.CustomerPurchases.AddAsync(new CustomerPurchase { CustomerId = 1, PurchaseId = 3, Cost = 10 });
await dbcontext.CustomerPurchases.AddAsync(new CustomerPurchase { CustomerId = 2, PurchaseId = 2, Cost = 10 });
await dbcontext.SaveChangesAsync();

var filters = new List<Filter>();
filters.Add(new Filter { CustomerId = 1, PurchaseId = 2 });
filters.Add(new Filter { CustomerId = 2, PurchaseId = 2 });

var resq = await dbcontext.CustomerPurchases
    .Where(p=> filters.Contains(new Filter { CustomerId = p.CustomerId,PurchaseId = p.PurchaseId }))
    .ToListAsync();
foreach (var item in resq)
{
    Console.WriteLine($" CustomerId : {item.CustomerId} , PurchaseId : {item.PurchaseId} Cost : {item.Cost}");
}

var res1 = dbcontext.CustomerPurchases
    .Where(p => filters.Select(c=>c.PurchaseId).Contains(p.PurchaseId))
    .Where(p => filters.Select(c => c.CustomerId).Contains(p.CustomerId));

var res = await res1.ToListAsync();
Console.WriteLine("===========================================================");
foreach (var item in res)
{
    Console.WriteLine($" CustomerId : {item.CustomerId} , PurchaseId : {item.PurchaseId} Cost : {item.Cost}");
}

and the running code
Sample Image

Update
After changed to SQL server I still got the error so option 2 is not an option when running on SQL server

But I found another solution where I am able to build the where clause based on the list of filters that I have
I found this PredicateBuilder
and here is the code using predicate builder

var whereclause = PredicateBuilder.False<CustomerPurchase>();
foreach (var filterrow in filters)
{

    whereclause = whereclause.Or(c => c.CustomerId == filterrow.CustomerId && c.PurchaseId == filterrow.PurchaseId);
    
}
var resqq = dbcontext.CustomerPurchases.Where(whereclause);

var resq = await resqq.ToListAsync();
foreach (var item in resq)
{
    Console.WriteLine($" CustomerId : {item.CustomerId} , PurchaseId : {item.PurchaseId} Cost : {item.Cost}");
}

That will build the query which will be translated by sql to the following statement

DECLARE @__filterrow_CustomerId_0 int = 1;
DECLARE @__filterrow_PurchaseId_1 int = 2;
DECLARE @__filterrow_CustomerId_2 int = 2;
DECLARE @__filterrow_PurchaseId_3 int = 2;

SELECT [c].[PurchaseId], [c].[CustomerId]
FROM [dbo].[CustomerPurchase] AS [c]
WHERE (([c].[CustomerId] = @__filterrow_CustomerId_0) AND ([c].[PurchaseId] = @__filterrow_PurchaseId_1)) 
    OR 
    (([c].[CustomerId] = @__filterrow_CustomerId_2) AND ([c].[PurchaseId] = @__filterrow_PurchaseId_3))

Here is the full class of PredicateBuyilder

public static class PredicateBuilder
{
    public static Expression<Func<T, bool>> True<T>() { return f => true; }
    public static Expression<Func<T, bool>> False<T>() { return f => false; }

    public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1,
                                                        Expression<Func<T, bool>> expr2)
    {
        var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
        return Expression.Lambda<Func<T, bool>>
              (Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);
    }

    public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr1,
                                                         Expression<Func<T, bool>> expr2)
    {
        var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
        return Expression.Lambda<Func<T, bool>>
              (Expression.AndAlso(expr1.Body, invokedExpr), expr1.Parameters);
    }
}

I hope that answer your question !!

SQL query filtering by list of parameters

Assuming SQL Server 2008 or newer, in SQL Server, create a table type once:

CREATE TYPE dbo.ColumnBValues AS TABLE
(
  ColumnB INT
);

Then a stored procedure that takes such a type as input:

CREATE PROCEDURE dbo.whatever
  @ColumnBValues dbo.ColumnBValues READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT A.* FROM dbo.TableA AS A
    INNER JOIN @ColumnBValues AS c
    ON A.ColumnB = c.ColumnB;
END
GO

Now in C#, create a DataTable and pass that as a parameter to the stored procedure:

DataTable cbv = new DataTable();
cbv.Columns.Add(new DataColumn("ColumnB"));

// in a loop from a collection, presumably:
cbv.Rows.Add(someThing.someValue);

using (connectionObject)
{
    SqlCommand cmd        = new SqlCommand("dbo.whatever", connectionObject);
    cmd.CommandType       = CommandType.StoredProcedure;
    SqlParameter cbvParam = cmd.Parameters.AddWithValue("@ColumnBValues", cbv);
    cbvParam.SqlDbType    = SqlDbType.Structured;
    //cmd.Execute...;
}

(You might want to make the type a lot more generic, I named it specifically to make it clear what it is doing.)

Lightswitch Sql Filter list based on contents of second list

I think there may be a simpler way to achieve what you want. Add a boolean field called Selected to the Home Players collection. When a Home Player is selected to be in the Match Players collection, set that boolean to True. Make sure the Home Players collection has a filter on Selected = False.

You might need to refresh the screen manually. And if you're saving data, you'll also need to think about what happens to the Home Players collection - possibly make sure somewhere in the save pipeline that Selected is always false when saved. But all that sounds a lot easier than filtering a collection based on another one which I'm not even sure is possible without some serious contortions.

Filter and Match Column on a List of Substrings

I got it to work; it no longer times out and doesn't block any other services.

I did this by building an SQL query and running it directly against the database.

var actions = new HashSet<string>
{
    "Account Creation",
    "Attempt to create duplicate account"
};

using (var databaseContext = new DatabaseContext())
{
    var query = "SELECT * FROM [dbo].[Actions] WHERE [UserAction] LIKE ";
    var queryStringActions = actions
        .Select(action => $"'{action}%'");

    query += $"{string.Join(" OR [UserAction] LIKE ", queryStringActions)}";

    var logs = databaseContext.Database.SqlQuery<Entities.Action>(query).AsQueryable();
}

The resulting SQL query is as follows

SELECT * FROM [dbo].[Actions]
WHERE [UserAction] LIKE 'Account Creation%'
OR [UserAction] LIKE 'Attempt to create duplicate account%'

Filter one table based on collection of values and one-to-many relation

Based on Alan Burstein answer I created (for extended version)

select N.PersonId from (values ('j'), ('m'), ('i')) AS Input(Value)
join Name as N on CHARINDEX(Input.Value, N.Name) > 0
group by N.PersonId
having count(distinct Input.Value) = 3

which does not use concatenation. Compared on 125k Names and 125k Persons

This:
 SQL Server Execution Times:
   CPU time = 672 ms,  elapsed time = 700 ms.

Alan's: (with replaces splitting input to table by "values" and counting of elements by "3")
 SQL Server Execution Times:
   CPU time = 2547 ms,  elapsed time = 2560 ms.

But still 700ms is probably a lot

Filter SQL Based on C# List Instead of a Filter Table