Convert SQL update query to c# LINQ code

Question

I want to convert this SQL query to C# code:

UPDATE Layouts 
SET LayoutDetails.modify('delete /ColumnLayout/LayoutColumns/Column[@PropertyId = sql:variable("@propId")]') 
WHERE LayoutId = 236

This query deletes a node in a XML column in a SQL Server table. I want to perform a similar operation in C# using LINQ.

Which ORM you're using? I think the `UPDATE` statement is ORM-based, LINQ to SQL/LINQ to Entities mainly used for `SELECT` statements. — Tetsuya Yamamoto, Oct 25 '17 at 04:40
@IpsitGaur : Usually for updating any record , will get the relevant record with the layoutid , modify relevant data field and savechanges. But here i have to play with XML column so absolutely no idea . If nothing works, i have to go with ADO.net — Sparrow, Oct 25 '17 at 04:53
@Prasu Just perform a simple SELECT query first, e.g. (`db.Layouts.Where(x => x.ColumnName == value`), assign all updated values to the result set and use `db.SaveChanges()` method (`db` is your `DbSet` name). — Tetsuya Yamamoto, Oct 25 '17 at 04:57
@TetsuyaYamamoto Yeah that is how we usually update a record using EF, but here i want to delete a node which contains propertyid as 43. Whole node ned to be removed from XML column data. — Sparrow, Oct 25 '17 at 06:03

score 1 · Answer 1 · answered Oct 25 '17 at 04:43

1

LINQ does not have anything like this available, because LINQ doesn't interact with a database in that way. I'm not sure what you are using to connect to your database, but there should be something in there that would allow you to do this.

As an example, Entity Framework has SaveChanges(), which will push any changes made to objects as an UPDATE sql command to the database you're connected to. Other types of ORMs should have something similar.

answered Oct 25 '17 at 04:43

Chris Thompson

490
5
17

Yeah i know about Savechanges method in entityframework, but i wanted to know about updating an XML column in LINQ query and savechanges to DB. – Sparrow Oct 25 '17 at 04:55
@Prasu There's something missing here, then - you will need something other than LINQ to do this. LINQ will let you manipulate collections, but it doesn't have anything to do with pushing changes to anything. – Chris Thompson Oct 25 '17 at 20:49

score 1 · Answer 2 · edited Oct 25 '17 at 07:51

1

If it is possible to read that XML column, then you can update that column and then use SaveChanges() method. like

var details = (from lay in context.Layouts where lay.id = 236 select lay).firstOrdefalut();

details.ColumnLayout.LayoutColumns.PropertyId = yourId;

saveChange();

Check it will be helpful

edited Oct 25 '17 at 07:51

barakcaf

1,294
2
16
27

answered Oct 25 '17 at 05:37

Haady Baabs

44
6

Yeah that is how we usually update a record using EF, but here i want to delete a node which contains propertyid as 43. Whole node ned to be removed from XML column data. – Sparrow Oct 25 '17 at 06:02

Convert SQL update query to c# LINQ code

2 Answers2