4

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.

fiberOptics
  • 6,955
  • 25
  • 70
  • 105

2 Answers2

1

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;
McGarnagle
  • 101,349
  • 31
  • 229
  • 260
  • Thanks, but that's not what I want to do. I can't do that in over 50 columns. – fiberOptics Jun 25 '12 at 07:09
  • 1
    @CustomException ah, gotcha ... do you want to select on 50 columns, or update 50 columns ... or both? – McGarnagle Jun 25 '12 at 07:16
  • @pratapk No. I just don't want to create queries for `each` column. – fiberOptics Jun 25 '12 at 07:22
  • @CustomException so you basically want to create a predicate programmatically, correct? Something similar to this? http://stackoverflow.com/questions/3431617/creating-dynamic-predicates-passing-in-property-to-a-function-as-parameter – McGarnagle Jun 25 '12 at 07:37
0

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

Community
  • 1
  • 1
Jason Larke
  • 5,289
  • 25
  • 28
  • The function is like this http://stackoverflow.com/questions/848415/linq-dynamic-where-clause the only difference is that, I want to use "UPDATE", if it is not possible, then it's a big problem to me. – fiberOptics Jun 25 '12 at 07:38
  • 1
    As far as I can tell there is no dynamic LINQ support for running an update, as there's no actual LINQ extension to handle this normally. – Jason Larke Jun 25 '12 at 07:50