LINQ to SQL submitchanges not doing anything

Question

My issue is the following: i'm trying to build a function to which i could pass a list of items, which would then go to the db with each of those items and update them. I believe the issue is within the way datacontexts are being used but i cannot figure out this issue.

Here is my function that builds the list of items that were changed:

 protected void btnSave_Click(object sender, EventArgs e)
{
    List<AFF_CMS_FMA> fmasToSave = new List<AFF_CMS_FMA>();
    AFF_CMS_FMA newFmaItem = new AFF_CMS_FMA();

    foreach (AFF_CMS_FMA fmaItem in FmaLib.fetchAllActiveAssetsInFMA())
    {
        if (fmaItem.SortOrder != Convert.ToInt32(Request.Form["fmaItem_" + fmaItem.ID + "_SortOrder"]))
        {
            newFmaItem = fmaItem;
            newFmaItem.Name = SecurityLib.SqlSafeString(Request.Form["fmaItem_" + fmaItem.ID + "_Name"]);
            newFmaItem.AssetID = Convert.ToInt32(Request.Form["fmaItem_" + fmaItem.ID + "_AssetID"]);
            newFmaItem.SortOrder = Convert.ToInt32(Request.Form["fmaItem_" + fmaItem.ID + "_SortOrder"]);
            newFmaItem.ImagePathEn = SecurityLib.SqlSafeString(Request.Form["fmaItem_" + fmaItem.ID + "_ImagePathEn"]);
            newFmaItem.ImagePathCh = SecurityLib.SqlSafeString(Request.Form["fmaItem_" + fmaItem.ID + "_ImagePathCh"]);
            newFmaItem.StartDate = DateTime.Parse(SecurityLib.SqlSafeString(Request.Form["fmaItem_" + fmaItem.ID + "_StartDate"]));
            newFmaItem.EndDate = DateTime.Parse(SecurityLib.SqlSafeString(Request.Form["fmaItem_" + fmaItem.ID + "_EndDate"]));
            newFmaItem.ClickToUrl = SecurityLib.SqlSafeString(Request.Form["fmaItem_" + fmaItem.ID + "_ClickToUrl"]);
            fmasToSave.Add(newFmaItem);

        }
    }
    FmaLib.saveEditedFmas(fmasToSave);
}

here is the function that the foreach loops calls to get all the items that are in the db:

 public static List<AFF_CMS_FMA> fetchAllActiveAssetsInFMA()
    {
        List<AFF_CMS_FMA> results = null;

        using (fmaDataContext db = new fmaDataContext())
        {
            using (TransactionScope ts = new TransactionScope())
            {
                try
                {
                    if (HttpContext.Current.Cache["fmaActiveList"] == null)
                    {
                        db.LoadOptions = loadAll;
                        results = clsCompiledQuery.getAllActiveFmas(db).ToList();
                        HttpContext.Current.Cache["fmaActiveList"] = results;
                    }
                    else
                        results = (List<AFF_CMS_FMA>)HttpContext.Current.Cache["fmaActiveList"];

                    ts.Complete();
                }
                catch (Exception ex)
                { Transaction.Current.Rollback(); }
            }
            return results;
        }
    }

here are the queries being used:

 protected static class clsCompiledQuery
    {
        public static Func<DataContext, IOrderedQueryable<AFF_CMS_FMA>>
        getAllActiveFmas = CompiledQuery.Compile((DataContext db)
        => from fma in db.GetTable<AFF_CMS_FMA>()
           where fma.IsArchived == false
           orderby fma.SortOrder ascending
           select fma);


        public static Func<DataContext, int,IQueryable<AFF_CMS_FMA>>
        getFmaById = CompiledQuery.Compile((DataContext db, int ID)
        => from fma in db.GetTable<AFF_CMS_FMA>()
           where fma.ID == ID
           select fma);

    }

and finally this were im trying to get the save to happen to the db but no exeptions are throwns, yet the db does not change

 public static bool saveEditedFmas(List<AFF_CMS_FMA> fmaToSaveList)
    {
        using (fmaDataContext db = new fmaDataContext())
        {
            using (TransactionScope ts = new TransactionScope())
            {
                try
                {
                    foreach (AFF_CMS_FMA fmaItemToSave in fmaToSaveList)
                    {
                        AFF_CMS_FMA fmaItemToUpdate = clsCompiledQuery.getFmaById(db, fmaItemToSave.ID).ToList()[0];
                        fmaItemToUpdate = fmaItemToSave;

                        db.SubmitChanges();
                    }

                    return true;
                }
                catch (Exception ex)
                {
                    Transaction.Current.Rollback();
                    return false;
                }
            }
        }
    }

I have checked and the table does contain a primary key in the designer. If i do the save from the btnSave_click function by passing a datacontext to the fetchAllActiveAssetsInFMA() then doing submitchanges on that context it works .. but im trying to abstract that from there.

thanks all in advance

Answer 1

Your not calling ts.Complete in function saveEditedFmas.

Also I would recommend calling db.SubmitChanges(); outside of the for loop. And why do you have a transaction in function fetchAllActiveAssetsInFMA? It's only fetching data right? And I'm not quite sure whats happening inside the for loop in save function, looks strange.

I think you should map the properties from fmaItemToSave to fmaItemToUpdate

foreach (var fmaItemToSave in fmaToSaveList)
{
   var fmaItemToUpdate = clsCompiledQuery.getFmaById(db, fmaItemToSave.ID).First();
   fmaItemToUpdate.Name    = fmaItemToSave.Name;
   fmaItemToUpdate.AssetID = fmaItemToSave.AssetID;
   //And the rest of the properties           
}
db.SubmitChanges();