Solved

LINQ TO SQL Identity Issue

Posted on 2009-05-03
12
2,351 Views
Last Modified: 2013-11-11
Hi,

Can someone please explain why the code snippet below is not setting the Property_ID for the listing object. When I get to the SubmitChanges() line It throws a duplicate key exception

Normally, LinqToSQL handles the identities for me but if I add a watch at the point where SubmitChanges() is called, the Listing.Property_ID is set to 0 (I'm not sure if this is because it needs to do the insert to assign the key or if it's trying to set it to 0). In either case, how can I force it to use the next available key?

Thanks in advance
Private Sub CreateProperty()
		Dim DB As New GHSDB.GHSDataContext
		Dim Listing As New GHSDB.Property
		Listing.Address1 = Me.Address1.Text
		Listing.Address2 = Me.Address2.Text
		Listing.City = Me.City.Text
		Listing.County = Me.State.Text
		Listing.Country = Me.Country.SelectedValue
		Listing.Postcode = Me.Zipcode.Text
		Listing.Property_ID = DB.Properties.
 
		Listing.Rooms = Me.Rooms.SelectedValue
		Listing.Bedrooms = Me.Bedrooms.SelectedValue
 
		'For Each X As RepeaterItem In FeatureRepeater.Items
 
		'Next
 
		If _Photos IsNot Nothing Then
			Dim P As GHSDB.Image
			For Each Photo In _Photos
				P = New GHSDB.Image
				P.Description = Photo.Description
				P.OriginalName = Photo.OriginalFilename
				P.Title = Photo.Title
				P.Path = Photo.Filename
				P.Active = True
				P.Property_ID = Listing.Property_ID
				Listing.Images.Add(P)
			Next
		End If
		If _Videos IsNot Nothing Then
			Dim V As GHSDB.Image
			For Each Video In _Videos
				V = New GHSDB.Image
				V.Description = Video.Description
				V.OriginalName = Video.OriginalFilename
				V.Title = Video.Title
				V.Path = Video.Filename
				V.Active = False
				V.Property_ID = Listing.Property_ID
				Listing.Images.Add(V)
			Next
		End If
		DB.Properties.InsertOnSubmit(Listing)
		DB.SubmitChanges()
	End Sub

Open in new window

0
Comment
Question by:basiclife
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 2
12 Comments
 
LVL 21

Expert Comment

by:naspinski
ID: 24289941
I think it is because you are adding new videos *before* the Listing has an ID assigned. Try this:
Private Sub CreateProperty()
		Dim DB As New GHSDB.GHSDataContext
		Dim Listing As New GHSDB.Property
		Listing.Address1 = Me.Address1.Text
		Listing.Address2 = Me.Address2.Text
		Listing.City = Me.City.Text
		Listing.County = Me.State.Text
		Listing.Country = Me.Country.SelectedValue
		Listing.Postcode = Me.Zipcode.Text
		Listing.Property_ID = DB.Properties.
 
		Listing.Rooms = Me.Rooms.SelectedValue
		Listing.Bedrooms = Me.Bedrooms.SelectedValue
 
		'For Each X As RepeaterItem In FeatureRepeater.Items
 
		'Next
 
		If _Photos IsNot Nothing Then
			Dim P As GHSDB.Image
			For Each Photo In _Photos
				P = New GHSDB.Image
				P.Description = Photo.Description
				P.OriginalName = Photo.OriginalFilename
				P.Title = Photo.Title
				P.Path = Photo.Filename
				P.Active = True
				P.Property_ID = Listing.Property_ID
				Listing.Images.Add(P)
			Next
		End If
		DB.Properties.InsertOnSubmit(Listing)
		DB.SubmitChanges()
 
		If _Videos IsNot Nothing Then
			Dim V As GHSDB.Image
			For Each Video In _Videos
				V = New GHSDB.Image
				V.Description = Video.Description
				V.OriginalName = Video.OriginalFilename
				V.Title = Video.Title
				V.Path = Video.Filename
				V.Active = False
				V.Property_ID = Listing.Property_ID
				Listing.Images.Add(V)
			Next
		End If
		DB.SubmitChanges()
	End Sub

Open in new window

0
 
LVL 5

Author Comment

by:basiclife
ID: 24291978
Looks like that may have solved it - at least I'm getting a different error (FK Constraint for a field I omitted).

Let me double-check and assuming it doens't come back when that error is resovled, I'll award.

Many thanks
0
 
LVL 5

Author Comment

by:basiclife
ID: 24292074
Hmm it doesn't seem to have solved the problem - only shifted it. The issue is now with the last DB.SubmitChanges() line in the attached snippet - I'm getting a Primary Key Constraint issue again. Any other suggestions?

	Private Sub CreateProperty()
		Dim DB As New GHSDB.GHSDataContext
		Dim Listing As New GHSDB.Property
		Dim UserInfo As New GHSUserInfo(HttpContext.Current.User.Identity.Name)
 
		Listing.Address1 = Me.Address1.Text
		Listing.Address2 = Me.Address2.Text
		Listing.City = Me.City.Text
		Listing.County = Me.State.Text
		Listing.Country = Me.Country.SelectedValue
		Listing.Postcode = Me.Zipcode.Text
 
		Listing.Rooms = Me.Rooms.SelectedValue
		Listing.Bedrooms = Me.Bedrooms.SelectedValue
		Listing.Status = "PropertyStatusActive"
 
		Listing.User_ID = 2 ' UserInfo.GetUserID()
 
		'For Each X As RepeaterItem In FeatureRepeater.Items
 
		'Next
		DB.Properties.InsertOnSubmit(Listing)
		DB.SubmitChanges()
 
		If _Photos IsNot Nothing Then
			Dim P As GHSDB.Image
			For Each Photo In _Photos
				P = New GHSDB.Image
				P.Description = Photo.Description
				P.OriginalName = Photo.OriginalFilename
				P.Title = Photo.Title
				P.Path = Photo.Filename
				P.Active = True
				P.Property_ID = Listing.Property_ID
				DB.Images.InsertOnSubmit(P)
				DB.SubmitChanges()
			Next
		End If
 
...

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 21

Expert Comment

by:naspinski
ID: 24292632
is Property.Property_ID the Unique Key?

Also, what is the exact error you are seeing?
0
 
LVL 5

Author Comment

by:basiclife
ID: 24294019
I have 2 [relevant] Tables - Property and Images

Each has a Primary Key respectively - Property_ID and Images_ID

Image also has a foreign key which relates to property - Property_ID

So we have a one to many:


Property                           Image
Property_ID [PK] -----< Property_ID [FK]
                                         Image_ID [PK]
(Excuse th ASCII art)

So I want to create the Property record then attach multiple images to it.

I was originally hoping to do it in one go (to be atomic) but am now trying to do it in multiple stages (just to get it to work).

The property creation goes well but LINQ is now trying to create images with an image_ID of 0 (It's alrady created 1, now it errors)

Short Error Message: Violation of PRIMARY KEY constraint 'PK_Image'. Cannot insert duplicate key in object 'dbo.Image'. The statement has been terminated.

Stack in attached snippet

Thanks and hope that's clearer

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)
   at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)
   at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
   at System.Data.Linq.ChangeDirector.StandardChangeDirector.DynamicInsert(TrackedObject item)
   at System.Data.Linq.ChangeDirector.StandardChangeDirector.Insert(TrackedObject item)
   at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode)
   at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)
   at System.Data.Linq.DataContext.SubmitChanges()
   at user_AddProperty.CreateProperty() in D:\[Obfuscated]\user\AddProperty.aspx.vb:line 216
   at user_AddProperty.Wizard_FinishButtonClick(Object sender, WizardNavigationEventArgs e) in D:\[Obfuscated]\user\AddProperty.aspx.vb:line 60
   at System.Web.UI.WebControls.Wizard.OnFinishButtonClick(WizardNavigationEventArgs e)
   at System.Web.UI.WebControls.Wizard.OnBubbleEvent(Object source, EventArgs e)
   at System.Web.UI.WebControls.Wizard.WizardChildTable.OnBubbleEvent(Object source, EventArgs args)
   at System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args)
   at System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e)
   at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
   at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
   at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
   at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Open in new window

0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 24295088
Hi basiclife;

Linq to SQL will take care of filling in the PK and FK if you have set up the database correctly. The following code snippet will add one Property table entry with multiple Image table entries all at one time. The primary keys of bothe tables have it set to Identity to true. Note that only one InsertOnSubmit and SubmitChanges should be needed.

Fernando
Private Sub CreateProperty()
    Dim DB As New GHSDB.GHSDataContext
    Dim Listing As New GHSDB.Property
    Dim UserInfo As New GHSUserInfo(HttpContext.Current.User.Identity.Name)
    
    Listing.Address1 = Me.Address1.Text
    Listing.Address2 = Me.Address2.Text
    Listing.City = Me.City.Text
    Listing.County = Me.State.Text
    Listing.Country = Me.Country.SelectedValue
    Listing.Postcode = Me.Zipcode.Text
    
    Listing.Rooms = Me.Rooms.SelectedValue
    Listing.Bedrooms = Me.Bedrooms.SelectedValue
    Listing.Status = "PropertyStatusActive"
    
    Listing.User_ID = 2 ' UserInfo.GetUserID()
    
    'For Each X As RepeaterItem In FeatureRepeater.Items
    
    'Next
    
    If _Photos IsNot Nothing Then
      Dim P As GHSDB.Image
      For Each Photo In _Photos
        P = New GHSDB.Image
        P.Description = Photo.Description
        P.OriginalName = Photo.OriginalFilename
        P.Title = Photo.Title
        P.Path = Photo.Filename
        P.Active = True
        P.Property = Listing
      Next
    End If
    
    DB.Images.InsertOnSubmit(Listing)
    DB.SubmitChanges()
    
End Sub

Open in new window

0
 
LVL 5

Author Comment

by:basiclife
ID: 24295534
Hi Fernando, thanks for the feedback.

This was how I originally envisioned it but obfviously my syntax on the images in my first snippet was incorrect.

The line

DB.Images.InsertOnSubmit(Listing)

is erroring as we're trying to insert a property into the images table but if I change it to read

DB.Properties.InsertOnSubmit(Listing)

and run the code, I get the following error on the submitchanges() line:

"Cannot add an entity with a key that is already in use."

In cast eh DB structure is an issue, please see relevant points from the tables in the attached snippets



CREATE TABLE [dbo].[Property](
	[Property_ID] [int] IDENTITY(1,1) NOT NULL,
	[User_ID] [int] NOT NULL,
	[Status] [varchar](50) NOT NULL,
	[Address1] [varchar](100) NOT NULL,
	[Address2] [varchar](100) NULL,
	...[Lots of other stuff]
	[ParkingSpaces] [int] NULL,
 CONSTRAINT [PK_Property] PRIMARY KEY CLUSTERED 
(
	[Property_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
 
 
 
CREATE TABLE [dbo].[Image](
	[Image_ID] [int] NOT NULL,
	[Property_ID] [int] NOT NULL,
	[OriginalName] [varchar](255) NULL,
	[Title] [varchar](100) NOT NULL,
	[Description] [varchar](1000) NULL,
	[Path] [varchar](1000) NOT NULL,
	[Active] [bit] NOT NULL,
 CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED 
(
	[Image_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
ALTER TABLE [dbo].[Image]  WITH CHECK ADD  CONSTRAINT [FK_Image_Property] FOREIGN KEY([Property_ID])
REFERENCES [dbo].[Property] ([Property_ID])
GO
 
ALTER TABLE [dbo].[Image] CHECK CONSTRAINT [FK_Image_Property]
GO

Open in new window

0
 
LVL 5

Author Comment

by:basiclife
ID: 24295538
Please excuse the numerous typos - long long day :s
0
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 24297202
Hi basiclife;

The issue I see is in your Image table. The primary key is not an Identity and therefore requires you to supply it for each entry. If you were to make the Image_ID from this:
[Image_ID] [int] NOT NULL,
to this:
[Image_ID] [int] IDENTITY(1,1) NOT NULL,

Then Linq to SQL will handle the assignment of the key value for you and you should not see a dup key error.

Fernando
0
 
LVL 5

Author Comment

by:basiclife
ID: 24297343
I can't believe I missed that - that's so irritating!

Thanks very much for your help :)
0
 
LVL 5

Author Closing Comment

by:basiclife
ID: 31577322
Perfect answer - thanks for all your help
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 24297392
Not a problem, glad I was able to help.  ;=)
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question