6 Replies - 2133 Views - Last Post: 18 July 2011 - 01:15 AM Rate Topic: -----

#1 The Architect 2.0  Icon User is offline

  • D.I.C Regular

Reputation: 37
  • View blog
  • Posts: 351
  • Joined: 22-May 08

Linq-to-SQL and SQLCE - wth...

Posted 17 July 2011 - 01:41 AM

                
 using (Local_Database fzDB = new Local_Database(connectionString))
            {
dynamic allSourcePosts = API.Get(GET_POSTS);
                foreach (dynamic post in allSourcePosts)
                {
                    Post newPost = new Post();

                    newPost.Service = "Service";
                    newPost.ID = post.post_id;

                    newPost.ApplicationLinkBack = null;
                    newPost.ApplicationName = post.attribution;

                    newPost.Message = post.message;

                    dynamic poster = API.Get(post.actor_id);
                    newPost.PosterID = poster.id;
                    newPost.PosterName = poster.name;
  
                    dynamic receiver = API.Get(post.source_id);  
                    newPost.ReceiverID = receiver.id;
                    newPost.ReceiverName = receiver.name;

                    string createdTime = post.created_time;
                    string updatedTime = post.updated_time;

                    newPost.CreatedTime = ConvertUnixTime(Convert.ToDouble(createdTime));
                    newPost.UpdatedTime = ConvertUnixTime(Convert.ToDouble(updatedTime)); 

                    fzDB.Post.Insertonsubmit(newPost);
                    fzDB.SubmitChanges();
                }



errors:

A first chance exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll
A first chance exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.Linq.dll

everything is of type 'nvarchar'; length:100 ; is nullable(just for testing purposes) except for 'CreatedTime' and 'UpdatedTime' which are of type DateTime. Service and ID are the primary keys.


anyone have a clue whats wrong? I'll provide more data if you guys need it.

EDIT: so i made it catch a generic exception and the e.message was "
A duplicate value cannot be inserted into a unique index. [ Table name = Post,Constraint name = PK_Post_000000000000046 ]"...what the hell does that mean? I don't have any data in the table at the start and the program craps out as soon as it hits submit() for the first time.

EDIT: so it must simply mean i'm passing the same value twice to a primary key column. I don't get why that is a problem though, if I specified two primary key columns, shouldn't it only require the combination of the two columns to be unique?

This post has been edited by The Architect 2.0: 17 July 2011 - 02:26 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Linq-to-SQL and SQLCE - wth...

#2 Sergio Tapia  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1253
  • View blog
  • Posts: 4,168
  • Joined: 27-January 10

Re: Linq-to-SQL and SQLCE - wth...

Posted 17 July 2011 - 07:49 AM

Since you're creating a new Post object (and your Post object has been generated by your ORM), L2SQL will handle assigning it a primary key.

Try removing this line:

newPost.ID = post.post_id;

Was This Post Helpful? 0
  • +
  • -

#3 The Architect 2.0  Icon User is offline

  • D.I.C Regular

Reputation: 37
  • View blog
  • Posts: 351
  • Joined: 22-May 08

Re: Linq-to-SQL and SQLCE - wth...

Posted 17 July 2011 - 11:08 AM

hm, but i kinda NEED to explicitly give it that value. it is VERY important for referencing data from the Service.

i also didn't set that primary key auto-incrementer when i made the database
Was This Post Helpful? 0
  • +
  • -

#4 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4490
  • View blog
  • Posts: 7,822
  • Joined: 08-June 10

Re: Linq-to-SQL and SQLCE - wth...

Posted 17 July 2011 - 12:17 PM

Ok, if you didn't make it auto-increment, then it won't add it for you. But PKs are always unique, and that error says you're trying to insert a duplicate value.

If you need that value able to be duplicates, then it shouldn't be your PK. Actually, I suggest always using an autonumber PK. If you have another significant field, you can index it, but an autonumber makes sure you never have to worry about it.

This post has been edited by Curtis Rutland: 17 July 2011 - 12:19 PM

Was This Post Helpful? 1
  • +
  • -

#5 Momerath  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1010
  • View blog
  • Posts: 2,444
  • Joined: 04-October 09

Re: Linq-to-SQL and SQLCE - wth...

Posted 17 July 2011 - 12:37 PM

View PostThe Architect 2.0, on 17 July 2011 - 01:41 AM, said:

EDIT: so it must simply mean i'm passing the same value twice to a primary key column. I don't get why that is a problem though, if I specified two primary key columns, shouldn't it only require the combination of the two columns to be unique?

Depends on what you did. If you said "this is a primary key, and that is a primary key" then no, each value must be unique. If you said "this combined with that is the primary key" then yes, the combination must be unique.
Was This Post Helpful? 0
  • +
  • -

#6 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4490
  • View blog
  • Posts: 7,822
  • Joined: 08-June 10

Re: Linq-to-SQL and SQLCE - wth...

Posted 17 July 2011 - 12:40 PM

Also, it's just not smart design. Give it a truly unique PK. Have the value you're talking about just be a normal column. Index it if you want, but I guarantee you'll regret making a composite key column later. It just adds needless complexity.
Was This Post Helpful? 0
  • +
  • -

#7 The Architect 2.0  Icon User is offline

  • D.I.C Regular

Reputation: 37
  • View blog
  • Posts: 351
  • Joined: 22-May 08

Re: Linq-to-SQL and SQLCE - wth...

Posted 18 July 2011 - 01:15 AM

View PostMomerath, on 17 July 2011 - 01:37 PM, said:

View PostThe Architect 2.0, on 17 July 2011 - 01:41 AM, said:

EDIT: so it must simply mean i'm passing the same value twice to a primary key column. I don't get why that is a problem though, if I specified two primary key columns, shouldn't it only require the combination of the two columns to be unique?

Depends on what you did. If you said "this is a primary key, and that is a primary key" then no, each value must be unique. If you said "this combined with that is the primary key" then yes, the combination must be unique.


ah, that might just (very likely) be the problem, good sir. How would I resolve this?

i'm using SQL CE 3.5 in visual studio 2010. My query language is LINQ-TO-SQL, meaning that I've ran the database through sqlmetal.exe and am referencing a .dbml file.

I'm not so sure what else you need to know.


View PostCurtis Rutland, on 17 July 2011 - 01:40 PM, said:

Also, it's just not smart design. Give it a truly unique PK. Have the value you're talking about just be a normal column. Index it if you want, but I guarantee you'll regret making a composite key column later. It just adds needless complexity.


one column = the name of the web service, the other column = the unique identifier of the object that the described web service.

I'm not sure whats so bad about that exactly...in fact, it SPECIFICALLY stops the problem of a post being added twice into the database(not a problem for querying, but for database bloat from mismanaged transfers/merges/etc.).

EDIT:

var test = from post in DB.Post
           where post.PosterID == post.ReceiverID 
                 && !DB.Tag.Any(t => t.Service == post.Service 
                                && t.PostID == post.ID
                               ) 
           select post



not totally related...but its a linq question for any wandering eyes.

TAG
Service PK
PostID PK
TaggedID PK

POST
Service PK
ID PK
PosterID
ReceiverID
...

my query should give me all the posts in POST that DON'T have any entries in TAG right? not having an IN operator makes my head hurt :/

This post has been edited by The Architect 2.0: 18 July 2011 - 02:55 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1