How to update data using DbLinq?

[Yi]

Hi, I have some trouble updating data using DbLinq and MySQL. My
updates looks like this:

Profile p = DataContext.Profiles.First( r => r.username == username);
p.LastUpdated = DateTime.Now;
DataContext.SubmitChanges();

My application didn't fail and didn't give any error message, but the
data in the table was not updated. I used the same update solution in
my other Linq to SQL project and it works fine.

Did I do anything wrong? Should I do something different in my code?
Should I look at my dbml/linq cs file? I found some bugs in the Visual
Metal tool so I wonder if I need to change anything to make the update
work.

Thanks,
Yi

[Yi]

Another thing I tried is using Attach method. When I use the Attach
method with one parameter, I got the error "duplicated key", when I
use the Attach with two parameters, I got a DBLinq error "method not
implemented".

I would appreciate any ideas and suggestions!

[Pascal Craponne]

This won't help, but the update should work.
When a request returns an entity, it is already attached, so you don't need to attach it anymore.

Can you tell if a SQL command is sent to MySQL? Can you take a look at the changeset (GetChangeSet) after the changes and before the submitchanges?

--
Pascal.

jabber/gtalk: pas...@jabber.fr
msn: pas...@craponne.org

[Yi]

I took a look at changeset and you are right, no changes were tracked,
all three change types are valued 0. From the debugger I can see the
object returned from the query is correct and also i changed the value
in the object correctly, why the changes were not tracked? Please
help!

[Yi]

I compared the CS file generated by Visual Metal with my Linq CS files
for SQL Server, and noticed the property set function is different.
When I use VS to trace into the function, I can see the value is
properly set, and the function this.SendPropertyChanged("BirthDay") is
also called. The VS2008 however didn't "see" the two lines for
OnBirthDayChanging() and OnBirthDayChanged(). It won't let me set a
breakpoint on them, when stepping through, it simply skips these two
function calls. Any ideas?

[Column(Storage="_BirthDay", Name="BirthDay", DbType="int
unsigned", CanBeNull=true)]
[DebuggerNonUserCode()]
public uint BirthDay
{
get
{
return this._BirthDay;
}
set
{
if (_BirthDay != value)
{
this.OnBirthDayChanging(value);
this._BirthDay = value;
this.SendPropertyChanged("BirthDay");
this.OnBirthDayChanged();

}
}
}

On Aug 10, 11:59 am, Yi <yic...@gmail.com> wrote:
> I took a look at changeset and you are right, no changes were tracked,
> all three change types are valued 0. From the debugger I can see the
> object returned from the query is correct and also i changed the value
> in the object correctly, why the changes were not tracked? Please
> help!
>
> On Aug 5, 3:06 pm, "Pascal Craponne" <pic...@gmail.com> wrote:
>

> > This won't help, but theupdateshould work.

> > When a request returns an entity, it is already attached, so you don't need
> > to attach it anymore.
>
> > Can you tell if a SQL command is sent to MySQL? Can you take a look at the
> > changeset (GetChangeSet) after the changes and before the submitchanges?
>
> > On Sat, Aug 2, 2008 at 21:07, Yi <yic...@gmail.com> wrote:
>
> > > Another thing I tried is using Attach method. When I use the Attach
> > > method with one parameter, I got the error "duplicated key", when I
> > > use the Attach with two parameters, I got a DBLinq error "method not
> > > implemented".
>
> > > I would appreciate any ideas and suggestions!
>
> > > On Aug 2, 12:04 pm, Yi <yic...@gmail.com> wrote:
> > > > Hi, I have some trouble updating data using DbLinq and MySQL.  My
> > > > updates looks like this:
>
> > > > Profile p = DataContext.Profiles.First( r => r.username == username);
> > > > p.LastUpdated = DateTime.Now;
> > > > DataContext.SubmitChanges();
>
> > > > My application didn't fail and didn't give any error message, but the

> > > > data in the table was not updated. I used the sameupdatesolution in

[Pascal Craponne]

Can you post a zip of your .cs file containing the object definition?

[ibu...@gmail.com]

I too am having this exact same issue.
Did the OP solve the issue?

[ibu...@gmail.com]

I noticed after a bit of testing that my case is slightly different.
When I do a GetChangeSet() it does report that it detects that
modifications have been made,
SubmitChanges however has no effect in commiting them (modified is 1
before and after).
Would using PostgreSQL have any effect on this?

> > > > I took a look atchangesetand you are right, no changes were tracked,

> > > > all three change types are valued 0. From the debugger I can see the
> > > > object returned from the query is correct and also i changed the value
> > > > in the object correctly, why the changes were not tracked? Please
> > > > help!
>
> > > > On Aug 5, 3:06 pm, "Pascal Craponne" <pic...@gmail.com> wrote:
>
> > > > > This won't help, but theupdateshould work.
> > > > > When a request returns an entity, it is already attached, so you don't
> > > need
> > > > > to attach it anymore.
>
> > > > > Can you tell if a SQL command is sent to MySQL? Can you take a look at
> > > the

> > > > >changeset(GetChangeSet) after the changes and before the

[Yi]

What is the OP?

I am still blocked by this issue, but it seems it is not widespread
since this is a very basic LINQ scenario. I figure if there is
something wrong in my/our system configurations. I had .net framework
3.5 sp1 beta installed. I am trying to un-install the beta and install
the final release and see if that resolve the problem.

[Pablo Iñigo Blasco]

Which version of dblinq are you using guys?

There was a bug in MemberModificationHandler. This class is subscribed to the PropertyChanging and PropertyChanged events of each entity.

There were two problems. In first place Propertychanging was being used for registering changes (instead of propertyChanged) that in my opinion is conceptually wrong.

Besides the autogenerated datacontext usually doesn't fill the PropertyChangingEventArgs.PropertyName but a "emptyChangingEventArgs" is sent. Look at this autogenerated code with sql-metal:


    [Table(Name = "dbo.Employees")]
    public partial class Employee : INotifyPropertyChanging, INotifyPropertyChanged
    {

        private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);
...

now look at the property changing:

 protected virtual void SendPropertyChanging()
        {
            if ((this.PropertyChanging != null))
            {
                this.PropertyChanging(this, emptyChangingEventArgs);
            }
        }
...

In the other hand propertyChanged is well managed by the autogenerated code:

  protected virtual void SendPropertyChanged(String propertyName)
        {
            if ((this.PropertyChanged != null))
            {
                this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
            }
        }

Then the following changes have been applied and committed:

Index: MemberModificationHandler.cs
===================================================================
--- MemberModificationHandler.cs    (revision 854)
+++ MemberModificationHandler.cs    (working copy)
@@ -160,7 +160,8 @@
                 {
                     ((INotifyPropertyChanging)entity).PropertyChanging += (OnPropertyChangingEvent);
                 }
-                else if (entity is INotifyPropertyChanged)
+
+                if (entity is INotifyPropertyChanged)
                 {
                     ((INotifyPropertyChanged)entity).PropertyChanged += (OnPropertyChangedEvent);
                 }
@@ -188,7 +189,7 @@
         /// <param name="e"></param>
         private void OnPropertyChangingEvent(object sender, PropertyChangingEventArgs e)
         {
-            SetPropertyChanged(sender, e.PropertyName);
+            //SetPropertyChanged(sender, e.PropertyName);
         }
 
         /// <summary>
@@ -248,7 +249,11 @@
         {
             lock (modifiedProperties)
             {
-                modifiedProperties[entity][propertyName] = GetProperty(entity, propertyName);
+                PropertyInfo pi=GetProperty(entity, propertyName);
+                if(pi==null)
+                    throw new ArgumentException("Incorrect property changed");
+
+                modifiedProperties[entity][propertyName] = pi;
             }
         }
 

Guys, if you update to the current revision the error should not exist.

Regards.

[ibu...@gmail.com]

This does not seem to have had an effect on my issue (I was using
latest SVN revision when I posted yesterday as well)
In my case the ChangeSet *does* list a modification to be submitted.
SubmitChanges does not seem to be having an effect though.
If it helps, the _tableMap in SubmitChanges() has a Count of 0 when I
call it.

- ibutsu

[Pablo Iñigo Blasco]

Can you send the autogenerated file where is located your DataContext and entities classes?
Do your EntityRef/Set properties have an invocation to PropertyChanged method?

[ibu...@gmail.com]

The properties do have the invocations, debug and disassembly are
implying they are non-functional.

Here is my DataContext:
namespace ShadowMUD.Database
{
public partial class ShadowDb : DataContext
{
public ShadowDb(string connStr)
: base(new NpgsqlConnection(connStr), new PgsqlVendor())
{
OnCreated();
}

public ShadowDb(IDbConnection connection) :
base(connection, new PgsqlVendor())
{
OnCreated();
}

public Table<Character> CharacterTable
{
get { return GetTable<Character>(); }
}

public Table<Room> RoomTable
{
get { return GetTable<Room>(); }
}

public Table<World> WorldTable
{
get { return GetTable<World>(); }
}

public Table<Text> TextTable
{
get { return GetTable<Text>(); }
}

partial void OnCreated();
}
}

Here is a portion of my mapped entity (truncated a bit as it is rather
large):

namespace ShadowMUD.MudObjects
{
[Table(Name = "public.character_table")]
public partial class Character : INotifyPropertyChanged
{

// ....

private bool _IsNewCharacter;

// ....

[Column(Storage = "_IsNewCharacter", Name = "IsNewCharacter",
DbType = "boolean", CanBeNull = false)]
[DebuggerNonUserCode()]
public bool IsNewCharacter
{
get
{
return this._IsNewCharacter;
}
set
{
if (_IsNewCharacter != value)
{
this.OnIsNewCharacterChanging(value);
this._IsNewCharacter = value;
this.SendPropertyChanged("IsNewCharacter");
this.OnIsNewCharacterChanged();
}
}
}

#region INotifyPropertyChanged Members

public event PropertyChangedEventHandler PropertyChanged;

#endregion

// ....

partial void OnIsNewCharacterChanging(System.Boolean
instance);
partial void OnIsNewCharacterChanged();

// ....

protected virtual void SendPropertyChanged(string
propertyName)
{
if (PropertyChanged != null)
{
PropertyChanged(this, new
PropertyChangedEventArgs(propertyName));
}
}
}
}

[ibu...@gmail.com]

Also, I'm not sure if it makes any difference, I retrieve my entities
using ExecuteQuery<>
as opposed to using the language extensions for queries.

- ibutsu

[Pablo Iñigo Blasco]

I am not sure. I am going to check if is the problem that you said.

It would be also useful if you could reproduce such error with our Northwind database, using a similar table with similar columns.  Then we would include it to the unitary testing stack.

Regards your datacontext, everything seems to be right.

[ibu...@gmail.com]

I'll see what I can do regarding the Northwind testing.

My system config, btw:
Visual Studio 2008 SP1
Microsoft .Net 3.5 SP1

[ibu...@gmail.com]

I reproduced it with a very simple app using the pgsql Northwind sql
file as well as
the entity mapping included in the latest SVN (slightly modified to
use an existing user).
I also used the code from my app that does the ExecuteQuery<>()
modified to use the Northwind db.

The sample grabs the first returned Customer (should be the entry for
Airbus) and
changes the Country to "Burmuda" (from France).
Here is the code:

using System.Linq;
using nwind;

namespace ConsoleApplication1
{
class Program
{
private static Northwind northwind;

private static readonly string selectQueryFormat = "SELECT *
FROM \"Customers\";";

public static bool LoadCharacter(string name, out Customer
character)
{
string query = string.Format(selectQueryFormat,
name.ToLower());

var characters =
northwind.ExecuteQuery<nwind.Customer>(query);

if (characters.Count() == 0)
{
character = null;
return false;
}

character = characters.First();

return true;
}

static void Main(string[] args)
{
northwind = new Northwind(new
Npgsql.NpgsqlConnection(("Server=127.0.0.1;Port=5432;User
Id=shadowmud;Password=testpass;Database=Northwind;")));

Customer cust;

LoadCharacter("", out cust);

cust.Country = "Burmuda";

northwind.SubmitChanges();
}
}
}

[Pablo Iñigo Blasco]

As you said the problem was that Customer table wasn't registered in the datacontext.
ExecuteQuery method doesn't registered the table, so if it was the first access to such entityType and after you executed submitchanges it didn't work properly.

I executed the following sample (the yours but simplified) . Submitchanges didn't work properly and the database wasn't updated.
Nonetheless there was a difference regards your case, ChangeSet returned the update properly.

            var db = CreateDB();
            string query = "SELECT * FROM \"Customers\";";

            var characters = db.ExecuteQuery<Customer>(query);
            var character = characters.First();

            string beforecountry = character.Country;
            character.Country = "Burmuda";

            Assert.Greater(db.GetChangeSet().Updates.Count, 0);
            db.SubmitChanges();

            var character2 = db.Customers.First(c=>c.CustomerID==character.CustomerID);
            Assert.AreEqual(character2.Country, "Burmuda");

            character2.Country = beforecountry;
            db.SubmitChanges();


Applying the following patch such problem disappeared. You can download the current revision for testing it.
Regards.

Index: C:/Documents and Settings/Administrator/Desktop/Mono/CurrentRevision/DbLinq/Data/Linq/DataContext.cs
===================================================================
--- C:/Documents and Settings/Administrator/Desktop/Mono/CurrentRevision/DbLinq/Data/Linq/DataContext.cs    (revision 857)
+++ C:/Documents and Settings/Administrator/Desktop/Mono/CurrentRevision/DbLinq/Data/Linq/DataContext.cs    (working copy)

                 if (entitySetValue == null)
@@ -590,8 +590,9 @@
         /// <summary>
         /// Execute raw SQL query and return object
         /// </summary>
-        public IEnumerable<TResult> ExecuteQuery<TResult>(string query, params object[] parameters) where TResult : new()
+        public IEnumerable<TResult> ExecuteQuery<TResult>(string query, params object[] parameters) where TResult : class, new()
         {
+            GetTable<TResult>();
             foreach (TResult result in ExecuteQuery(typeof(TResult), query, parameters))
                 yield return result;
         }

Index: C:/Documents and Settings/Administrator/Desktop/Mono/CurrentRevision/Tests/Test_NUnit/ExecuteQuery_Test.cs
===================================================================
--- C:/Documents and Settings/Administrator/Desktop/Mono/CurrentRevision/Tests/Test_NUnit/ExecuteQuery_Test.cs    (revision 857)
+++ C:/Documents and Settings/Administrator/Desktop/Mono/CurrentRevision/Tests/Test_NUnit/ExecuteQuery_Test.cs    (working copy)
@@ -15,11 +15,11 @@
 #if MYSQL
     namespace Test_NUnit_MySql
 #elif ORACLE
-    #if ODP
+#if ODP
         namespace Test_NUnit_OracleODP
-    #else
+#else
         namespace Test_NUnit_Oracle
-    #endif
+#endif
 #elif POSTGRES
 namespace Test_NUnit_PostgreSql
 #elif SQLITE
@@ -29,8 +29,8 @@
 #elif MSSQL
 #if MONO_STRICT
 namespace Test_NUnit_MsSql_Strict
-#else
-    namespace Test_NUnit_MsSql
+#else
+    namespace Test_NUnit_MsSql
 #endif
 #else
 #error unknown target
@@ -62,5 +62,27 @@
                 Assert.AreEqual(categories1[index].Description, categories2[index].Description);
             }
         }
+
+        [Test]
+        public void X2_CheckChanges()
+        {
+            var db = CreateDB();
+            string query = "SELECT * FROM \"Customers\";";
+
+            var characters = db.ExecuteQuery<Customer>(query);
+            var character = characters.First();
+
+            string beforecountry = character.Country;
+            character.Country = "Burmuda";
+
+            Assert.Greater(db.GetChangeSet().Updates.Count, 0);
+            db.SubmitChanges();
+
+            var character2 = db.Customers.First(c=>c.CustomerID==character.CustomerID);
+            Assert.AreEqual(character2.Country, "Burmuda");
+
+            character2.Country = beforecountry;
+            db.SubmitChanges();
+        }
     }
 }

[ibu...@gmail.com]

Excellent, the changes seem to have done it, thanks for all your help.
Nice work of DBLinq as well, it's a great library :)

- ibutsu