How do I provide a list as my parameter in sqlite-net

Question

I am building a Xamarin Forms application and using Sqlite-net. I want to run a delete query that deletes all records that have a field in a list so something like below:

//usersToDelete is a list of Objects each representing a user.
List<int> idsToDelete = new List<int>();
foreach (var user in usersToDelete)
{
    idsToDelete.Add(user.Id);
}
string dbQuery = "DELETE FROM Users WHERE Id IN (?)";
var deleteCount = await LocalDatabaseConnection.ExecuteAsync(dbQuery, idsToDelete);

This does not work for me. It fails with the error Cannot store type: System.Collections.Generic.List1[System.Int32]. Does this mean I have build the string in code in palce of "(?)"? Or is there some way to provide this as a parameter.

I think you need to build the string yourself. See stackoverflow.com/questions/4788724/… — Jason
– Jason, Commented Dec 23, 2021 at 21:53

Serge · Accepted Answer · 2021-12-23 22:41:42Z

1

try this

var utd= usersToDelete.Select(i=> i.Id.ToString()).ToArray();
string ids=string.Join(",",utd);
string dbQuery = $"DELETE FROM Users WHERE Id IN ({ids})";

edited Dec 23, 2021 at 22:41

answered Dec 23, 2021 at 22:35

Serge

44.4k4 gold badges26 silver badges53 bronze badges

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

1 Comment

Kikanye Over a year ago

seems like there's no way to do this just using a parameter, but this works. Thanks

thomasforth · Accepted Answer · 2024-02-14 20:09:12Z

1

I don't know of a way to pass a List or an Array as a parameter into a written SQL query in SQLite-net except using the method in the answer that Serge has given.

This should also work,

//usersToDelete is a list of Objects each representing a user.
List<int> idsToDelete = new List<int>();
foreach (var user in usersToDelete)
{
    idsToDelete.Add(user.Id);
}

var deleteCount = await LocalDatabaseConnection.Table<Users>().DeleteAsync(x => x.idsToDelete.Contains(x.Id));

And makes better use of the capabilities of SQLite-net.

edited Feb 14, 2024 at 20:09

answered Feb 14, 2024 at 18:20

thomasforth

6757 silver badges14 bronze badges

Collectives™ on Stack Overflow

How do I provide a list as my parameter in sqlite-net

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related