C# - Search in List with array in WHERE clause

Question

C#

Ok, I have something like that:

var message = db.Table<Message>().Where(cond => cond.ID == messageID).Single();
message.Status = status;

But I'd like to change my messageID from int to int[], so I will select all data where cond.ID is in my int[] messageID. I need something like .Where(cond => cond.ID IN messageID). I don't want to do that in a way like:

foreach(var ID in messageID)
{
   message = db.Table<Message>.Where(cond => cond.ID == ID).Single();
   message.Status = status;
   db.Update(message);
}

I use SQLite, any help with update multiple rows at once will be very appreciated.

Rob · Accepted Answer · 2015-08-20 21:10:30Z

3

This will do it:

var messages = db.Table<Message>().Where(msg => messageIDs.Contains(msg.ID));

answered Aug 20, 2015 at 21:10

Rob♦

27.5k16 gold badges89 silver badges103 bronze badges

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

2 Comments

Vlad Over a year ago

Actually, I'm updating ObservableCollection. Is there a way to update all items with single line of code? ;)

Rob Over a year ago

@VladislavKasianenko You could potentially do (at the end) .ToList().ForEach(msg => msg.Status = status); - but I personally find that a bit ugly (creating a list just to update a property) and usually just use a foreach.

James R. · Accepted Answer · 2015-08-20 21:10:46Z

2

I think this should work.

var message = db.Table<Message>().Where(cond => messageIDs.Any(id => cond.ID == id).Single();
message.Status = status;

answered Aug 20, 2015 at 21:10

James R.

8508 silver badges17 bronze badges

Comments

Stefan · Accepted Answer · 2015-08-20 21:24:00Z

2

Something like this:

var messageIDs = new int[]; // assume it's filled
var result = db.Table<Message>().Where(cond => messageIDs.Contains(cond.ID));

foreach(var message in result)
    message.Status = status;

db.SaveChanges(); //or something similar.

It will return all the messages which ID's are present in the test array.

Be advised: Contains can translate to OR in SQL. It might perform badly. Especially when applied to string.

Meanwhile you can set the status for each message using a foreach loop. The ORM you are using might have something like db.SaveChanges which should send a single update query to the database.

edited Aug 20, 2015 at 21:24

answered Aug 20, 2015 at 21:10

Stefan

17.7k11 gold badges65 silver badges86 bronze badges

Comments

BufferUnderRun · Accepted Answer · 2015-08-20 21:11:52Z

0

use the .contains() method

var message = db.Table<Message>().Where(cond => messageID.contains(cond.ID)).Single();

answered Aug 20, 2015 at 21:11

BufferUnderRun

1611 silver badge14 bronze badges

Collectives™ on Stack Overflow

C# - Search in List with array in WHERE clause

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related