Issue with Updating Changes in LINQ

Question

I'm having an issue with updating the database. The app shows the updated value, but the database does not. No errors returned. My table has a PK. Using DotConnect for Oracle, but the LINQ syntax is the same.

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            DataContext DB = 
              new DataContext("User Id=...Password=...;Server=...;");

            DB.Log = Console.Out;
            Console.WriteLine();

            IEnumerable<Foodorder> fo = from f in DB.Foodorders
                       where f.Orderid == 10210
                       select f;
            foreach (Foodorder food in fo)
            {
              Console.WriteLine(food.Orderid + " " + food.Externalref 
                + "\r\n " + food.Orderremarks);
            }

            Console.Read();

            //Try a new connection (eliminate caching)    
            DB.Dispose();

            DataContext DB2 = new DataContext("User Id=...Password=...;Server=...;");

            Foodorder fo2 = DFunc.GetFoodOrder(10198);
            fo2.Orderremarks = "This should save now.";
            fo2.Orderqty = 9999;
            DB.SubmitChanges();

            //Retrieves a single order:  DFunc.GetFoodOrder(pk)
            Console.WriteLine(DFunc.GetFoodOrder(10198).Orderremarks);
            Console.Read();
        }
    }
}

The console reads the correct updated values, but the DB Does Not Update.

The data function, DFunc.GetFoodOrder(Oid) and data context functions are below. The are both in another assembly as the LINQ DAL:

    #region Data Context
    private static CommoDTContext cdtDataContext = new CommoDTContext(connectionSTringHere);
    /// <summary>
    /// This property gets the DevArt Oracle DotConnect data context, 
    /// providing LINQ to Oracle, and direct ORM capabilities.
    /// </summary>
    public static CommoDTContext DB
    {
        get
        {
            return cdtDataContext;
        }
        set
        {
            cdtDataContext = value;
        }
    }
    #endregion Data Context


    /// <summary>
    /// Get food order by specifying the order ID.
    /// </summary>
    /// <param name="orderId"></param>
    /// <returns></returns>
    public static Foodorder GetFoodOrder(decimal orderId)
    {   //left out validation/try-catch for brevity.
        if (orderId == 0) return null;
        var food =
            from fo in DB.Foodorders
            where fo.Orderid == orderId
            select fo;
        if (food.FirstOrDefault() == null)
            return null;
        else
            return food.FirstOrDefault();
    }

Answer 1

You don't show how DFunc.GetFoodOrder(10198) is implemented. However, it doesn't seem to have access to the data context. You also submit changes against DB after disposing. I think you meant to submit against DB2.

In order for LINQ to do updates, the record has to be "attached" to the data context. If you query via the DataContext, the record will be attached and LINQ will track changes. Try...

FoodOrder fo2 = DB2.Foodorders.Single(x => x.Orderid == 10198);
fo2.Orderremarks = ...

DB2.SubmitChanges();

Answer 2

Here's how I modified my program based on Rob's answer.

class Program
{
    static void Main(string[] args)
    {
        string outFmt = "{0} {1} of {2}\r\n {3}";
        CommoDT.Context.CommoDTContext DB =
            new CommoDT.Context.CommoDTContext(PutOracleConnectionStringHere);
        DB.Log = Console.Out;
        Console.WriteLine();

        Foodorder fo2 = DB.Foodorders.Single(x => x.Orderid == 10198);
        fo2.Orderremarks = "These are the first comments.";
        fo2.Orderqty = 1000;
        DB.SubmitChanges();
        Console.WriteLine(outFmt, 
            fo2.Orderid.ToString(), fo2.Orderqty.ToString(), fo2.Externalref, fo2.Orderremarks);
        Console.Read();

        Foodorder fo3 = DFunc.GetFoodOrder(ref DB, 10198);
        fo3.Orderremarks += " And these are the second comments for the order.";
        fo3.Orderqty = 2000;
        DB.SubmitChanges();
        Console.WriteLine(outFmt,
            fo3.Orderid.ToString(), fo3.Orderqty.ToString(), fo3.Externalref, fo3.Orderremarks);
        Console.Read();

        DB.Dispose();
    }
}

        /// <summary>
        /// Get food order by specifying the order ID.
        /// </summary>
        /// <param name="orderId"></param>
        /// <returns>Food</returns>
        public static Foodorder GetFoodOrder(ref CommoDT.Context.CommoDTContext DB, decimal orderId)
        {
            if (orderId == 0) return null;
            var food =
                from fo in DB.Foodorders
                where fo.Orderid == orderId
                select fo;
            if (food.FirstOrDefault() == null)
                return null;
            else
                return food.FirstOrDefault();
        }