LINQ to SQL and One-To-Many Relationships

Techy News Blog » LINQ to SQL and One-To-Many Relationships

Techy News Blog » LINQ to SQL and One-To-Many Relationships — Wed, 03 Oct 2007 02:51:58 GMT

PingBack from http://www.artofbam.com/wordpress/?p=4710

re: LINQ to SQL and One-To-Many Relationships

Eulinky — Sun, 07 Oct 2007 15:13:44 GMT

Thanks Beth for that great post. Hope the "DeleteOnNull" property can be set from the Designer in the final version...

re: LINQ to SQL and One-To-Many Relationships

Jason Crosby — Tue, 09 Oct 2007 01:30:59 GMT

This is great stuff but I'm still getting stuck. Anyone else getting the error:

Overload resolution failed because no accessible 'New' accepts this number of arguments

After modifying the .dbml file?

The error is in this line, Private db As New DropShipClassesDataContext.

This error remains even when I remove the added DeleteOnNull="true" text from the .dbml file.

Prior to editing the .dbml file the project compiles and everything on the form works fine (of course I can't delete rows from the order grid).

I've tried this with multiple projects and datasources so I would think someone else must be running into this as well.

Beths code and database is the only one I can get working with out the error.

TIA to anyone who can offer some insight into this.

re: LINQ to SQL and One-To-Many Relationships

Beth Massi — Tue, 09 Oct 2007 18:58:27 GMT

Hi Jason,

In Beta 2 when you create the instance of the DataContext, pass it the connection string from your My.Settings, something like:

Dim db As New DropShipClassesDataContext(My.Settings.MyConnectionString)

HTH,

-B

re: LINQ to SQL and One-To-Many Relationships

Jason Crosby — Wed, 10 Oct 2007 01:02:38 GMT

That's it Beth, thanks again.

I don't know if the comments area is the right place for these questions, just let me know if there is a more appropriate place.

When pressing the delete button I can’t find a way to stop the delete from happening. I’d like to prompt the user with If MsgBox("Are you sure you want to delete?", MsgBoxStyle.YesNo, "Delete?") = MsgBoxResult.No Then Exit Sub

Inside the click event of the delete button. The delete happens even if the user clicks no. It seems as soon as the Sub ends the delete happens no matter where you exit/end the Sub.

re: LINQ to SQL and One-To-Many Relationships

Beth Massi — Wed, 10 Oct 2007 03:22:41 GMT

Hi Jason,

You can clear out the delete button's default behavior by selecting the BindingNavigator toolstrip and clearing out the DeleteItem behavior. Then you can create your own handler by double-clicking the delete button. When you want to actually delete the item, just call the BindingSource's RemoveCurrent method.

You can also check out the VB Forums here: http://forums.microsoft.com/msdn/default.aspx?forumgroupid=10&siteid=1

Cheers,

-B

re: LINQ to SQL and One-To-Many Relationships

Julie Lerman — Sat, 03 Nov 2007 23:13:55 GMT

Thanks. I'll be using this in my linq session at DevConnections! :-)

re: LINQ to SQL and One-To-Many Relationships

Adam — Tue, 15 Jan 2008 14:07:46 GMT

Hey Beth.

Hows the weather in San Fransisco?

Great artical.

You can also use DeleteOnSubmit() as an alternative. Either reference back to the original DataContext or if it isn't available, use Attatch() to attatch the item to a new context.

e.g.

DbDataContext _db;

protected override void OnInit(EventArgs e) {

_db = new DbDataContext();

...

base.OnInit(e);

}

protected override void Dispose() {

_db.Dispose();

base.Dispose();

}

void Delete(object sender, EventArgs args) {

...

_db.Products.DeleteOnSubmit(product);

}

Creating Lookup Lists with LINQ to SQL

Noticias externas — Thu, 07 Feb 2008 20:36:33 GMT

In yesterday's post I showed you how to bind LINQ to SQL classes to a Combobox in order to filter

One-To-Many (Master-Detail) Forms with LINQ to SQL

Beth Massi - Sharing the goodness that is VB — Wed, 20 Feb 2008 02:11:17 GMT

In my previous posts here and here I showed how to use LINQ to SQL classes with a couple different Combobox

Creating Lookup Lists with LINQ to SQL

Beth Massi - Sharing the goodness that is VB — Tue, 26 Feb 2008 04:16:19 GMT

In yesterday's post I showed you how to bind LINQ to SQL classes to a Combobox in order to filter records

re: LINQ to SQL and One-To-Many Relationships

MatthewY — Fri, 26 Sep 2008 04:43:46 GMT

Awesome, Had the same issue and came accross this posting. Fixed. Shame that its not in the designer :-(

LINQ to SQL: DeleteOnNull

Dave Brace — Thu, 02 Oct 2008 00:01:20 GMT

My first project at Catalyst just wrapped up this past week, and it involved creating an order fulfillment

re: LINQ to SQL and One-To-Many Relationships

Cheryl — Mon, 09 Feb 2009 18:41:12 GMT

I have just started using LINQ and everything worked fine until I wanted to put in the combo box.

I am getting a string to double conversion error. Any ideas on how I correct this. My table uses an "int"

Thanks in advance for any help

Dim teamList = From Bowler In Db.Bowlers _

Where Bowler.TeamNumber <> "" _

Order By Bowler.TeamNumber _

Select Bowler.TeamNumber

For Each TeamNumber In teamList

Me.ToolStripComboBox1.Items.Add(TeamNumber)

re: LINQ to SQL and One-To-Many Relationships

Beth Massi — Mon, 09 Feb 2009 21:20:35 GMT

Hi Cheryl,

If TeamNumber is an integer you cannot compare its value to a empty string. If it's a nullable field then try "Where Bowler.TeamNumber IsNot Nothing" otherwise you'll need to compare it to an integer value.

You also may want to try the LINQ to SQL forums: http://social.msdn.microsoft.com/forums/en-US/linqtosql/threads

HTH,

-B

re: LINQ to SQL and One-To-Many Relationships

Cheryl — Mon, 09 Feb 2009 21:38:43 GMT

Thank you for answering. I was able to get work by using the code below. I works well now. I really enjoy your videos. They are clear and concise. I have learned a lot. Thank you.

Dim teamList = (From Bowler In Db.Bowlers _

Where Bowler.TeamNumber <> 0 _

Order By Bowler.TeamNumber _

Select Bowler.TeamNumber).Distinct()

For Each TeamNumber In teamList

Me.ToolStripComboBox1.Items.Add(TeamNumber)

re: LINQ to SQL and One-To-Many Relationships

Beth Massi — Mon, 09 Feb 2009 21:58:46 GMT

Hi Cheryl,

Distinct is a Keyword in VB so you could also write it this way to be more clear:

Dim teamList = From Bowler In Db.Bowlers _

Where Bowler.TeamNumber <> 0 _

Order By Bowler.TeamNumber _

Select Bowler.TeamNumber Distinct

HTH,

-B

re: LINQ to SQL and One-To-Many Relationships

Bassam — Wed, 27 May 2009 16:35:04 GMT

Hello Beth,

this works for entities that came from db , but if i added new row to the details in the grid then delete that row before i submit changes , i still got the same error

i tried everything i could but still , no solution, any help is desparately needed and greatly appreciated

thank you

Bassam

re: LINQ to SQL and One-To-Many Relationships

Beth Massi — Tue, 02 Jun 2009 23:23:01 GMT

Hi Bassam,

I'm not seeing this behavior with the sample I attached to the post. I tried adding a new order in the detail grid, moving off the row (to commit it to the datasource) and then highlighting the row again and pressing the delete key. When I click save, I do not get an error. Can you verify these steps with the sample: http://blogs.msdn.com/bethmassi/attachment/5246895.ashx

Thanks,

-B

re: LINQ to SQL and One-To-Many Relationships

Gunnar — Thu, 11 Jun 2009 03:21:43 GMT

Thanks for good information. Just got "An attempt was made to remove a relationship between..." error fixed in my library code. :)

re: LINQ to SQL and One-To-Many Relationships

argg — Sat, 20 Jun 2009 01:08:06 GMT

Thanks! You've saved me much hearache.

re: LINQ to SQL and One-To-Many Relationships

Andrei — Tue, 28 Jul 2009 09:49:00 GMT

Thanks.

It works.

It's a shame its not in the designer.

re: LINQ to SQL and One-To-Many Relationships

Paul Hodgson — Thu, 19 Nov 2009 21:01:37 GMT

I think there's a typo in this:-

For instance, if we bind the LINQ to SQL classes to two grids, delete just the child Order row, and attempt to call SubmitChanges(), we get the following error "An attempt was made to remove a relationship between a Customer and a Order. However, one of the relationship's foreign keys (Order.CustomerID) cannot be set to null."

Surely, adn according to the screenshot, should this actually read "...delete just the parent Customer row..."?

Which is a shame as I am getting the offending error message when I attempt to delete a child and the fixes mentioned here don't help.

re: LINQ to SQL and One-To-Many Relationships

Beth Massi — Thu, 19 Nov 2009 21:15:17 GMT

Hi Paul,

No, the article is correct. I'm showing what happens if you try to delete the child row that has a non-null FK reference to its parent. Make sure in your situation that you're applying the DeleteOnNull attribute to the right association in the XML. Otherwise maybe there is another association involved? You may want to check out th LINQ to SQL forums as well:

http://social.msdn.microsoft.com/Forums/en-US/linqtosql/threads

HTH,

-Beth

re: LINQ to SQL and One-To-Many Relationships

Paul Hodgson — Thu, 19 Nov 2009 21:26:13 GMT

Hi Beth

Awesome quick response and yes you are correct.

Although I followed your example and I still got an error message I failed to notice that the error message had indeed changed. Basically validation on the class was thowing exception.

Cheers and my apologies regards my assumptions.

re: LINQ to SQL and One-To-Many Relationships

Beth Massi — Thu, 19 Nov 2009 21:34:10 GMT

Hi Paul,

No worries, glad you figured it out! And thanks for reading my blog... ;-)

Cheers,

-B