Is SubmitChanges() not Working in LinQ ?

Some times this type of problem comes especially when you are working with N-tier application. I got this problem when I was trying to made a change in database and I wrote

(Here Domain is a class and table in database and permission is a field of this table)

public static void UpdateXmlByDomainID(int domainID, Settings permissions)

        {

MiniSitesDataContext DataContext = MSD.GetDataContext();

 

XElement xml = Util.GetXmlInXElement(permissions);

 

            Domain updatedDomain = GetDomainByID(domainID);

           

updatedDomain.Permissionsxml = xml;          

            DataContext.SubmitChanges(); 

 

        

        }

Where GetDomainByID(domainID) is a function to fetch Domain but by this method I was unable to change then I a made a change.

Hurrah! It’s working

public static void UpdateXmlByDomainID(int domainID, Settings permissions)

        {

            MiniSitesDataContext DataContext = MSD.GetDataContext();

                        

XElement xml = Util.GetXmlInXElement(permissions);

 

            Domain updatedDomain = new Domain();

           

updatedDomain = DataContext.Domains.Single(d => d.DomainID == domainID);

           

updatedDomain.Permissionsxml = xml;          

            DataContext.SubmitChanges();          

                    

        }

Actually problem is this, LinQ put DataContext on track so this type of problems arising. In first one DataContext was not identifying that this domain is related to it but in second one we are fetching object from datacontext so when we submit changes it make change in database.

Note: This example is not related to only xml functions. Applicable any where. If your problem is still unsolved you are free to ask, may be I can help you 🙂 

Problem in linQ Updates – Object-tracking and Caching

As I told I am working on linQ and daily I get some interesting problems. I feel LinQ has some issues and today I made an application to research. I am surprised because I found a major issue which I am going to discuss.

The problem is related to database update. After fetching an object from database if you are assigning it some other value and then you submit your changes or not but in application it will show you have changed the value. If you submit those changes then it will reflect in database. Irrespective of database changes, it shows in application that it’s changed.
Even I fetched data again using different object but it shows changed value in spite of the fact that I didn’t submit changes.

I want to show an example of this,

In database I have a table by name ‘Contact’

namespace WebApplication1

{

    public partial class _Default : System.Web.UI.Page

    {

        DataClasses3DataContext db = new DataClasses3DataContext();

        Contact c, d;

        protected void Page_Load(object sender, EventArgs e)

        {   

            // feching data for contactid 7

            c = db.Contacts.Single(a => a.ContactID == 7);           

 

            TextBox1.Text = c.ContactID.ToString();

            TextBox2.Text = c.Name;

            TextBox3.Text = c.Organization; 

           

        }

    }

}

 

Result  

 

 

 

 

 

Again I made some change in code and write a button event also

 

namespace WebApplication1

{

    public partial class _Default : System.Web.UI.Page

    {

        DataClasses3DataContext db = new DataClasses3DataContext();

        Contact c, d;

        protected void Page_Load(object sender, EventArgs e)

        {   

            // feching data for contactid 7

            c = db.Contacts.Single(a => a.ContactID == 7);

 

            c.Name = “changed”; // assigning the value

 

            TextBox1.Text = c.ContactID.ToString();

            TextBox2.Text = c.Name; // now it have new value “changed”

            TextBox3.Text = c.Organization;           

 

 

            c = db.Contacts.Single(a => a.ContactID == 7);         

          

 

            TextBox1.Text = c.ContactID.ToString();

            TextBox2.Text = c.Name; // now it shoulda have value from database which is larry

            TextBox3.Text = c.Organization;

          

           

        }

 

        protected void Button1_Click(object sender, EventArgs e)

        {

 

            d = db.Contacts.SingleOrDefault(a => a.ContactID == 7);

                     

            TextBox6.Text = d.ContactID.ToString();

            TextBox7.Text = d.Name; // now it should have value from database which is larry

            TextBox8.Text = d.Organization;       

 

        }

 

 

 

    }

}

 And the result is

 

 

Remember I didn’t use any where SubmitChanges().

So now what should we do??

Answer is very simple don’t assign value to object member, if you haven’t made any change.
If it’s conditional then first check, and if change is needed then assign.

Actually the reason of the problem is tracking and caching of objects, so there is one more option available for that and you can disable object tracking by a single line of code. But it should be written just after creating DataContext.

DataClasses3DataContext db = new DataClasses3DataContext();

db.ObjectTrackingEnabled = false;

and code should be written before LinQ-query.

Sql to LinQ : Stored procedure

Two month ago our client had decided to change the middle tier of the project which was implemented using Sql Helper to Linq. I and my team started work on LinQ. We had only very initial level knowledge of LinQ but the best part was this that we have a good knowledge of ORMs(Object Relational Mapping). Initially we were very much exited with LinQ because it looks very simple in implementation. Very small and effective query makes life easy.

But after some time we start facing different kind of problems. I want to share some of them here.

Stored Procedure: whenever we talk about linq implementation the biggest problem is sprocs. In the process of creating .dbml file we have to drag and drop the tables on designer. Designer (Sql to LinQ) generates code for sproc also. The problem starts here, the function which is auto generated by designer always return int type by default. So if you want to map it to any other object or collection then you are bound to change result type in Designer.cs file like this.

Generated:

 

[Function(Name=“dbo.GetActivitesByPage”)]

           

public int GetActivitesByPage([Parameter(Name=“CurrentPage”, DbType=“Int”)] System.Nullable<int> currentPage, [Parameter(Name=“PageSize”, DbType=“Int”)] System.Nullable<int> pageSize, [Parameter(DbType=“NVarChar(4000)”)] string sql, [Parameter(DbType=“NVarChar(25)”)] string programType)

           

{

                 

IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), currentPage, pageSize, sql, programType);

                 

return ((int)(result.ReturnValue));

           

}

 

Changed:

 

[Function(Name=“dbo.GetActivitesByPage”)]

[ResultType(typeof(Program))]

[ResultType(typeof(int))]

 

public IMultipleResults GetActivitesByPage([Parameter(Name=“CurrentPage”, DbType=“Int”)] System.Nullable<int> currentPage, [Parameter(Name=“PageSize”, DbType=“Int”)] System.Nullable<int> pageSize, [Parameter(DbType=“NVarChar(4000)”)] string sql, [Parameter(DbType=“NVarChar(25)”)] string programType)

           

{

 

IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), currentPage, pageSize, sql, programType);

 

return (IMultipleResults) result.ReturnValue; 

 

}

 

But the problem will not end with this. The main problem is this if you want to make any change in schema, for that you have to do again whole process. Because any change on designer automatically is reflected to designer.cs file so it is automatically updated and the change you made vanish.

To avoid this, one solution might be to use two separate Sql to LinQ file one for tables and one for sprocs with same namespace.