Linq Update query with Mulitple Tables

Question

I need to convert the following Query to Linq :-

UPDATE INOUT
SET TIME_FLD1 = A.SCODE,
    TIME_FLD2 = B.STATUS
FROM SHIFTSCHEDULE A,
     SHIFT B,
     INOUT C
WHERE A.COMPANY = B.COMPANY
  AND A.SCODE = B.SCODE
  AND A.CODE = C.CODE
  AND A.SHIFT_DATE = C.DATE1

Normally I do Update like this :-

    var data = ctx.INOUT.Where(m=>m.COMPANY == COMPANY).Select(m=>m).FirstOrDefault();
    ctx.INOUT.Attach(rec);    
    ctx.Entry(data).State = EntityState.Modified;
    ctx.SaveChanges();

Can anybody explain what the Sql query exactly does? And how to do it in Linq?

score 0 · Accepted Answer · answered Sep 15 '15 at 05:45

I suppose this is what you're looking for:

        //Select your data
        var data = from A in contextDB.SHIFTSCHEDULE
                    from B in contextDB.SHIFT
                    from C in contextDB.INOUT
                    where   A.COMPANY == B.COMPANY
                            && A.SCODE == B.SCODE 
                            && A.CODE == C.CODE 
                            && A.SHIFT_DATE == C.DATE1
                    select new { A, B, C};

        //interate througt collection and update
        foreach (var row in data)
        {
            row.C.TIME_FLD1 = row.A.SCODE;
            row.C.TIME_FLD2= row.B.STATUS;
        }
        //save changes
        contextDB.SaveChanges();

Linq to SQL can't update many records at the same time. That's pitty, but it's true so you should get your collection, and then update entities one by one.

Is there any need to write this one :- `ctx.INOUT.Attach(row); ctx.Entry(row).State = EntityState.Modified;`? — Anup, Sep 15 '15 at 05:52
@Anup since you get your entity from `contextBD` it's already attcahed and you don't need to manipulate `State` manualy, you need it if you attach entity to DB. A bit more info you can find [here](http://stackoverflow.com/questions/7106211/entity-framework-why-explicitly-set-entity-state-to-modified) — teo van kot, Sep 15 '15 at 06:22

score -1 · Answer 2 · answered Sep 15 '15 at 05:25

-1

The SQL query is updating from multiple tables, and its LINQ version should be like this:

var data = (from A in contextDB.SHIFTSCHEDULE
        from B in contextDB.SHIFT
         from C in contextDB.INOUT
        where A.COMPANY==B.COMPANY
        && A.SCODE==B.SCODE && A.CODE==C.CODE && A.SHIFT_DATE==C.DATE1
        select new {A,B,C}).FirstOrDefault();
if(data != null)
{
  data.TIME_FLD1= data.SCODE,
  data.TIME_FLD2= data.STATUS
 contextDB.SubmitChanges();
 }

answered Sep 15 '15 at 05:25

Shilpa Soni

2,034
4
27
38

`data` don't have `TIME_FLD1` property if we follow your example – teo van kot Sep 15 '15 at 05:37
Also `contextDB.SubmitChanges();` is not working in my code. Do you mean `contextDB.SaveChangs()`? – Anup Sep 15 '15 at 05:38
1

@Anup This query is in LINQ to SQL that's why I used SubmitChanges, EntityFramework uses SaveChanges instead of SubmitChanges. If you're expecting your answer in EF then please add Entity Framework tag in your question. – Shilpa Soni Sep 15 '15 at 05:45

Linq Update query with Mulitple Tables

2 Answers2