CODEWITHSUNDEEP
c#winformsvisual-studio-2010linq
ghiboz
ghiboz

Reputation: 8003

build SqlQuery using linq and list

I'm new using linq and I have a question: I need to create a query to use later with SqlCommand that is like this:

string query = string.Format(@"
UPDATE dtLct SET
    bLctVer = 1
WHERE pLct IN (@value1, @value2, @value3)
", value1, value2, value3);

but I wish the values retrieve from a List<int> I'm able to compose a query ciclyng the values, but I wish use linq.. it's possible?

thanks

Upvotes: 0

Views: 48

Answers (1)

Christos
Christos

Reputation: 53958

Yes it is possible like below:

string values = String.Join(",",list.ToArray());

where list is the list of your integers.

Then your query should change to the following one:

string query = "UPDATE dtLct " +
               "SET bLctVer = 1 " +
               "WHERE pLct IN ("+values+")";

If you don't like this approach and you would like pure LINQ, then you could try the following one:

// Get the items that are to be updated from the database.
var items = (from b in db.bLctVer
            where list.Contains(b.pLct)
            select b);

// Iterate through the items and update tje value of bLctVer
foreach(var item in items)
    item.bLctVer=1;

// Submit the changes.
db.SubmitChanges();

Note

I have to note here that the first approach is more optimal, since you will have only one round trip to the database. Using the second approach, you make two round trips. In the first trip, you get the records that should be updated and in the second trip you update them.

Upvotes: 1

Related Questions