CODEWITHSUNDEEP
c#linqpredicate
fiberOptics
fiberOptics

Reputation: 7165

How to update column data using sql query in linq?

I have this query in sql and it works fine: 

update userinfo set Interest = 0.98 where userid = 313

And I want to do it in linq, so I prepared the following: 

public class TableDataDTO
{
    public string Columnname { get; set; }
    public string Value { get; set; }
    public Type DataType { get; set; }
}

Implementation: 

TableDataDTO tableData = new TableDataDTO();
tableData.Columnname = "Interest";
tableData.Value = "0.98";

using (dbase instance = new dbase())
{
    string predicate = string.Format("it.UserID=={0} set it.{1}={2}" ,
                313, tableData.Columnname, tableData.Value);

    var uinfo = instance.userinfoes.Where(predicate).FirstOrDefault();

    if (uinfo != null)
    {
        instance.SaveChanges();
        return true;
    }
}

But it gives me this error: 

The query syntax is not valid. Near keyword 'SET'

I will be dealing with different columns, so I need to use linq predicates to minimize the code. I don't like using any plugins to make this. Hope someone could help.

Edit

I think what I mean is "How to update data in using Dynamic linq"

Edit2

So this is the real scenario. Users/Client can update their information, e.g. First name, Last name, Address, City.. etc.. not at once but capable of updating the info one by one.
So what does it mean? Ok I can create a method that can update the First Name, next is the Last Name, Address and so one.. But if I do this, it will consume a lot of code. If only if there is a code in linq that can do what SQL does in updating data, then I would just need a code that gets the column name and set its value. Hope I'd explain it well.

Edit3

I have changed the question from How to update data in linq using predicates? to How to update column data using sql query in linq? for I misunderstood the real meaning of predicate.

Upvotes: 4

Views: 4786

Answers (2)

Jason Larke
Jason Larke

Reputation: 5609

You can structure LINQ similar to how you'd structure SQL. Through a combination of Where and ForEach you should be able to update all the rows you need. I.e:

    instance.userinfoes.Where(it => it.UserId == 313).ToList()
                       .ForEach(
                           it => it.Interest = 0.98M
                        );

There's not really any way to write SQL-like queries as text and pass them to regular LINQ as far as I know.

See this question for more solutions: Update all objects in a collection using LINQ

Upvotes: 0

McGarnagle
McGarnagle

Reputation: 102793

Your predicate should just be the where part of the query (a predicate just returns true or false). Try this:

instance.userinfoes.Where(user => user.userid == 313).First().Interest = 0.98;

Upvotes: 1

Related Questions