I'm trying to save status messages with Entity Framework, but have the problem that every time i try to save a tweet from a user that already exists in the database, i get an exception!
{"A duplicate value cannot be inserted into a unique index. [ Table name = users,Constraint name = PK_dbo.users ]"}
I totally understand why i get this error, but have no idea how to prevent it!
To save the status messages i do the following
Version 1:
db.status.Add(status);
db.SaveChanges();
Every status message contains the complete user information as a sub-entity. I use the Model(status.cs & user.cs) of this piece of code: https://github.com/swhitley/TwitterStreamClient
With the information from this Question: Save detached entity in Entity Framework 6 I came up with this idea:
Version 2:
user usr = db.user.Where<user>(u => u.id == status.user.id).SingleOrDefault();
if (usr == null)
db.user.Add(status.user);
else
{
db.user.Attach(status.user);
db.Entry(status.user).State = EntityState.Modified;
}
db.status.Add(status);
db.SaveChanges();
But this just lead to the next error message:
Attaching an entity of type 'TwitterStreamClient.user' failed because another entity of the same type already has the same primary key value. This can happen when using the 'Attach' method or setting the state of an entity to 'Unchanged' or 'Modified' if any entities in the graph have conflicting key values. This may be because some entities are new and have not yet received database-generated key values. In this case use the 'Add' method or the 'Added' entity state to track the graph and then set the state of non-new entities to 'Unchanged' or 'Modified' as appropriate.
And as requested here are my status and user classes:
user.cs:
using System;
using System.Globalization;
using System.Runtime.Serialization;
using System.ComponentModel.DataAnnotations;
namespace TwitterStreamClient
{
[DataContract]
public class user
{
//<user>
//<id>1401881</id>
// <name>Doug Williams</name>
// <screen_name>dougw</screen_name>
// <location>San Francisco, CA</location>
// <description>Twitter API Support. Internet, greed, users, dougw and opportunities are my passions.</description>
// <profile_image_url>http://s3.amazonaws.com/twitter_production/profile_images/59648642/avatar_normal.png</profile_image_url>
// <url>http://www.igudo.com</url>
// <lang>en</lang>
// <protected>false</protected>
// <followers_count>1027</followers_count>
// <profile_background_color>9ae4e8</profile_background_color>
// <profile_text_color>000000</profile_text_color>
// <profile_link_color>0000ff</profile_link_color>
// <profile_sidebar_fill_color>e0ff92</profile_sidebar_fill_color>
// <profile_sidebar_border_color>87bc44</profile_sidebar_border_color>
// <friends_count>293</friends_count>
// <created_at>Sun Mar 18 06:42:26 +0000 2007</created_at>
// <favourites_count>0</favourites_count>
// <utc_offset>-18000</utc_offset>
// <time_zone>Eastern Time (US & Canada)</time_zone>
// <profile_background_image_url>http://s3.amazonaws.com/twitter_production/profile_background_images/2752608/twitter_bg_grass.jpg</profile_background_image_url>
// <profile_background_tile>false</profile_background_tile>
// <statuses_count>3390</statuses_count>
// <notifications>false</notifications>
// <following>false</following>
// <verified>true</verified>
// <contributors_enabled>false</verified>
//</user>
[DataMember]
[Key]
public string id { get; set; }
[DataMember]
public string name { get; set; }
[DataMember]
public string screen_name { get; set; }
[DataMember]
public string location { get; set; }
[DataMember]
public string description { get; set; }
[DataMember]
public string profile_image_url { get; set; }
[DataMember]
public string url { get; set; }
[DataMember]
public string lang { get; set; }
[DataMember]
public string @protected { get; set; }
[DataMember]
public string followers_count { get; set; }
[DataMember]
public string profile_background_color { get; set; }
[DataMember]
public string profile_text_color { get; set; }
[DataMember]
public string profile_link_color { get; set; }
[DataMember]
public string profile_sidebar_fill_color { get; set; }
[DataMember]
public string profile_sidebar_border_color { get; set; }
[DataMember]
public string friends_count { get; set; }
//save date only as string for now as DateTimeOffset is not supported
//public DateTimeOffset created_at_dt { get; set; }
[DataMember]
public string created_at
{
get ; //{ return created_at_dt.ToString("ddd MMM dd HH:mm:ss zzz yyyy"); }
set; //{ created_at_dt = DateTimeOffset.ParseExact(value, "ddd MMM dd HH:mm:ss zzz yyyy", CultureInfo.InvariantCulture); }
}
[DataMember]
public string favourites_count { get; set; }
[DataMember]
public string utc_offset { get; set; }
[DataMember]
public string time_zone { get; set; }
[DataMember]
public string profile_background_image_url { get; set; }
[DataMember]
public string profile_background_tile { get; set; }
[DataMember]
public string statuses_count { get; set; }
[DataMember]
public string notifications { get; set; }
[DataMember]
public string following { get; set; }
[DataMember]
public string verified { get; set; }
[DataMember]
public string contributors_enabled { get; set; }
}
}
status.cs
using System;
using System.Runtime.Serialization;
using System.Globalization;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace TwitterStreamClient
{
//<status>
//<created_at>Tue Apr 07 22:52:51 +0000 2009</created_at>
//<id>1472669360</id>
//<text>At least I can get your humor through tweets. RT @abdur: I don't mean this in a bad way, but genetically speaking your a cul-de-sac.</text>
//<source><a href="http://www.tweetdeck.com/">TweetDeck</a></source>
//<truncated>false</truncated>
//<in_reply_to_status_id></in_reply_to_status_id>
//<in_reply_to_user_id></in_reply_to_user_id>
//<favorited>false</favorited>
//<in_reply_to_screen_name></in_reply_to_screen_name>
//<geo/>
//<contributors/>
//</status>
[DataContract]
public class status
{
[DataMember]
[Key]
public string id { get; set; }
//save date only as string for now as DateTimeOffset is not supported
//public DateTimeOffset created_at_dt { get; set; }
[DataMember]
public string created_at
{
get; //{ return created_at_dt.ToString("ddd MMM dd HH:mm:ss zzz yyyy"); }
set; //{ created_at_dt = DateTimeOffset.ParseExact(value, "ddd MMM dd HH:mm:ss zzz yyyy", CultureInfo.InvariantCulture); }
}
[DataMember]
public string text { get; set; }
[DataMember]
public string source { get; set; }
[DataMember]
public string truncated { get; set; }
[DataMember]
public string in_reply_to_status_id { get; set; }
[DataMember]
public string in_reply_to_user_id { get; set; }
[DataMember]
public string favorited { get; set; }
[DataMember]
public string in_reply_to_screen_name { get; set; }
[DataMember]
public user user { get; set; }
[DataMember]
public geo geo { get; set; }
[DataMember]
public string contributors { get; set; }
}
[DataContract]
public class geo
{
[Key]
public int geoId { get; set; }
[DataMember]
public string type { get; set; }
[DataMember]
public string[] coordinates { get; set; }
}
}
DataStore.cs
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
namespace TwitterStreamClient
{
class DataStore
{
static private TweetContext Db = null;
private static TweetContext getContext() {
if(Db == null)
{
Db = new TweetContext();
}
return Db;
}
public static bool Add(status status)
{
TweetContext db = getContext();
{
db.status.Add(status);
db.SaveChanges();
return true;
}
}
}
}
TweetContext.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Entity;
namespace TwitterStreamClient
{
class TweetContext : DbContext
{
public DbSet<status> status { get; set; }
public DbSet<user> user { get; set; }
static TweetContext()
{
// Database initialize
Database.SetInitializer<TweetContext>(new DbInitializer());
using (TweetContext db = new TweetContext())
db.Database.Initialize(false);
}
class DbInitializer : DropCreateDatabaseAlways<TweetContext>
{
}
}
}
I found a solution!
In Version 2 from above I just needed to change the SingleOrDefault() to Any<user>()!
if(!db.user.Where<user>(u => u.id == status.user.id).Any<user>())
db.user.Add(status.user);
else
{
//db.user.Attach(status.user);
db.Entry(status.user).State = EntityState.Modified;
}
db.status.Add(status);
db.SaveChanges();
I guess the problem with getting the user with SingleOrDefault() tracks the user with this ID therefore the Attach() throws an Exception because it already tracks a user with this ID. But when I use Any<user>() it just checks for existance of a user with this key and it does not track it! So Attaching the user is possible then.