Asp.Net MVC3, Update query in Linq

Question

I'd like to know how to run this query in Linq way.

UPDATE orders SET shipDate = '6/15/2012' WHERE orderId IN ('123123','4986948','23947439')

My Codes,

[HttpGet]
public void test()
{
    EFOrdersRepository ordersRepository = new EFOrdersRepository();
    var query = ordersRepository.Orders;

    // How to run this query in LINQ
    // Query : UPDATE orders SET shipDate = '6/15/2012' WHERE orderId IN ('123123','4986948','23947439')
}

EFOrdersRepository.cs

public class EFOrdersRepository
{
    private EFMysqlContext context = new EFMysqlContext();

    public IQueryable<Order> Orders
    {
        get { return context.orders; }
    }
}

EFMysqlContext.cs

class EFMysqlContext : DbContext
{
     public DbSet<Order> orders { get; set; }
}

Answer 1

Actually it's pretty easy check the following code

EFOrdersRepository db = new EFOrdersRepository();
int[] ids= new string[] { "123123", "4986948", "23947439"};
//this linq give's the orders with the numbers
List<Order> orders = db.Order().ToList()
                        .Where( x => ids.Contains(x.orderId.Contains));

foreach(var order in orders)
{
    order.ShipDate = '06/15/2012';
    db.Entry(usuario).State = EntityState.Modified;
}

  db.SaveChanges();

Answer 2

Something like this should work (warning Pseudo code ahead!!)

EDIT I like using the Jorge's method of retrieving the orders better (using contains), but leaving this here as another alternative. The statements below the code sample still hold true however.

[HttpGet]
    public void test()
    {
        EFOrdersRepository ordersRepository = new EFOrdersRepository();
        var query = ordersRepository.Orders.Where(x=>x.orderId == '123123' || 
x.orderId == '4986948' || x.orderId = '23947439').ToList();
            foreach(var order in query){
               var localOrder = order; 
               order.ShipDate = '06/15/2012';
            }
            ordersRepository.SaveChanges();
        }

Basically, LINQ does not do 'bulk updates' well. You either have to fetch and loop through your orders or write a stored procedure that can take an array of ids and bulk update them that way. If you are only doing a few at a time, the above will work ok. If you have tons of orders that need to be updated, the ORM probably will not be the best choice. I look forward to see if anyone else has a better approach.

Disclaimer: the var localOrder = order line is to ensure that there are no modified closure issues. Also, ReSharper and other tools may have a less verbose way of writing the above.

Answer 3

In ORMs, You have to fetch the record first make the change to the record then save it back. To do that, I will add an UpdateOrder method to my Repositary like this

public bool UpdateOrder(Order order)
{
   int result=false;
   int n=0;
   context.Orders.Attach(order);
   context.Entry(order).State=EntityState.Modified;
   try
   {
      n=context.SaveChanges();
      result=true;
   }
   catch (DbUpdateConcurrencyException ex)
   {
      ex.Entries.Single().Reload();
      n= context.SaveChanges();        
      result= true;
   }
  catch (Exception ex2) 
  {
    //log error or propogate to parent 
  }
  return result;
}

And i will call it from my Action method like this

int orderId=123232;
var orders=ordersRepository.Orders.Where(x=> x.orderId.Contains(orderId)).ToList();
if(orders!=null)
{
   foreach(var order in orders)
   {
     order.ShipDate=DateTime.Parse('12/12/2012);
     var result= ordersRepository.UpdateOrder();     
   }
}

In this Approach, if you have to update many number of records, you are executing thatn many number of update statement to the database. In this purticular case, i would like to execute the Raw SQL statement with only one query using the Database.SqlQuery method

string yourQry="UPDATE orders SET shipDate = '6/15/2012' 
                     WHERE orderId IN ('123123','4986948','23947439')";
var reslt=context.Database.SqlQuery<int>(yourQry);

Answer 4

Note: You need to call SaveChanges from your DBContext at the end

Short answer:

var f = new[] { 123123, 4986948, 23947439 };
var matchingOrders = orders.Where(x => f.Contains(x.ID)).ToList();
matchingOrders.ForEach(x => x.ShipDate = newDate);

Complete test:

    // new date value
    var newDate = new DateTime(2012, 6, 15);
    // id's
    var f = new[] { 123123, 4986948, 23947439 };
    // simpulating the orders from the db
    var orders = Builder<Order2>.CreateListOfSize(10).Build().ToList();
    orders.Add(new Order2 { ID = 123123 });
    orders.Add(new Order2 { ID = 4986948 });
    orders.Add(new Order2 { ID = 23947439 });

    // selecting only the matching orders
    var matchingOrders = orders.Where(x => f.Contains(x.ID)).ToList();

    matchingOrders.ForEach(x => Console.WriteLine("ID: " +  x.ID + " Date: " + x.ShipDate.ToShortDateString()));
    // setting the new value to all the results
    matchingOrders.ForEach(x => x.ShipDate = newDate);
    matchingOrders.ForEach(x => Console.WriteLine("ID: " + x.ID + " Date: " + x.ShipDate.ToShortDateString()));

Output:

ID: 123123 Date: 1/1/0001
ID: 4986948 Date: 1/1/0001
ID: 23947439 Date: 1/1/0001
ID: 123123 Date: 6/15/2012
ID: 4986948 Date: 6/15/2012
ID: 23947439 Date: 6/15/2012