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

DotNetKicks.com — Wed, 20 Feb 2008 02:23:33 GMT

You've been kicked (a good thing) - Trackback from DotNetKicks.com

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

burrowsuw — Wed, 20 Feb 2008 05:26:17 GMT

Hi Beth,

WhenI was teaching, I had a student who came to my office to tell me that I used the word "irregardless". She let me know that since I was a professor, I should know better and use the proper word, i.e., regardless.

I learned the original word from listening to my mom, who uses irregardless to this day.

Anyway, I thought I would be a pedant and let you know that the test in your message box that says "Your data was saved." is incorrect (sorry, I ma just getting back at that student). The word "data" is plural, so you should say "Your data were saved."

How about that for a useless and dumb comment :)

bill

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

Allan — Wed, 20 Feb 2008 06:34:10 GMT

Data leads a life of its own quite independent of datum, of which it was originally the plural. It occurs in two constructions: as a plural noun (like earnings), taking a plural verb and plural modifiers (as these, many, a few) but not cardinal numbers, and serving as a referent for plural pronouns (as they, them); and as an abstract mass noun (like information), taking a singular verb and singular modifiers (as this, much, little), and being referred to by a singular pronoun (it). Both constructions are standard. The plural construction is more common in print, evidently because the house style of several publishers mandates it.

In the case of messages from within this context (a master/detail form) the singular modifier is infered as we are saving a single record even though it may have many associated child records.

I couldn't leave that post unaswered, sorry!

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

Allan — Wed, 20 Feb 2008 06:36:30 GMT

Great post by the way. It answers quite a few questions I have had. On a slightly different path, is there a way we can determine the templates the generated code uses?

Thanks in advance.

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

karl1406 — Wed, 20 Feb 2008 14:05:53 GMT

Beth,

Love your work! Thanks for sharing with us.

Karl

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

Hernan — Wed, 20 Feb 2008 14:52:47 GMT

Hi Beth!

Your blog and videos are amazing and really usefull!

This is my first comment, or let me say my first question : Did you test this master-detail scenario using uniqueidentifiers as keys?

Is not binding the databinidingsource of the details table when I navigate the master table...

Thanks.

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

Josh Stodola — Wed, 20 Feb 2008 18:34:53 GMT

Beth,

Thanks for posting this. I definitely learned a few things from it. Keep up the great work!

LMAO @ bill's comment. I think if she put that sentence in the message box, more people would comment to say "You have a typo in your message box". And LMAO even harder at Allan's response to bill (at least the percentage of the response that I was able to comprehend).

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

Peter Kellner — Wed, 20 Feb 2008 18:48:22 GMT

Nice! good to have it spelled out so cleanly.

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

Beth Massi — Wed, 20 Feb 2008 20:10:16 GMT

Hi Allan,

You can't change the templates the designer uses. You'd have to write your own code generator.

-B

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

Beth Massi — Wed, 20 Feb 2008 20:14:58 GMT

Hi Hernan,

Yes this will work with uniqueidentifiers. Actually, it's even easier than DataSets in this case because you can set in the model the class's guid property to "Auto Generated Value" = True and Auto-Sync = OnInsert and then just specify the default on the database column to (newid()) and it will handle it all for you. I suspect something is wrong with the BindingSources. Make sure that the child's BindingSource's DataSource property is set to the parent's BindingSource.

HTH,

-B

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

Allann — Mon, 25 Feb 2008 07:13:54 GMT

Hi Beth,

I found out that edmx files use the SingleFilegenerator "Custom Tool" to generate the code behind files as do other "wizards", so it's just a matter of writing your own to output your own code behind. Is this what you meant when you said "write your own code generator"?

Thanks

Allan

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

Chris Rock — Mon, 25 Feb 2008 21:54:23 GMT

Beth,

I'm compiling a list of articles, tutorial and opinions about LINQ TO SQL. I hope you don't mind but I added a few of your links to the list.

Chris

Simple Validation with LINQ to SQL Classes

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

In the last few posts on LINQ to SQL I've showed how to set up an object model using the O/R designer

Simple Validation with LINQ to SQL Classes

Noticias externas — Tue, 26 Feb 2008 04:29:18 GMT

In the last few posts on LINQ to SQL I've showed how to set up an object model using the O/R designer

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

Beth Massi — Tue, 26 Feb 2008 06:17:37 GMT

Hi Alan,

Yep. Here's an example that may interest you: http://code.msdn.microsoft.com/sampleedmxcodegen

HTH,

-B

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

Beth Massi — Tue, 26 Feb 2008 06:18:45 GMT

Hi Chris,

I don't mind at all. You may also want to check out Code Gallery for LINQ to SQL samples: http://code.msdn.microsoft.com/Project/ProjectDirectory.aspx?TagName=LINQ%20to%20SQL

HTH,

-B

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

Chris Rock — Tue, 26 Feb 2008 22:43:55 GMT

Beth,

Thank you for that link!

Chris

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

James — Fri, 29 Feb 2008 23:26:16 GMT

Hi Beth

I saw your sub

Private Sub BindingNavigatorDeleteItem_Click() _

But if I want to display a error message to the user and retrieve the info, how I can do that?

Thanks you for your help

James

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

Rikki — Mon, 03 Mar 2008 01:05:40 GMT

Hi Beth,

I'm not sure this is the right place to ask this, but since I've searched high and low, I'll give it go anyway.

I'm trying to update a row in a datagrid, much like you describe above, which contains several dropdownlists. One of them has a foreignkey to my table 'tblCapacity' whit null values allowed. When I try to edit a row where a null value is currently in the capacityID attribute I get the message:''CapacityDropDownList' has a SelectedValue which is invalid because it does not exist in the list of items.

Parameter name: value'

I've tried (a mix from your code above and Scott Guthrie's in LINQ to SQL Part 9) to change the selectstatement of the linqdatasource I use for the dropdownlist like so:

Protected Sub CapacityLinqDataSource_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.LinqDataSourceSelectEventArgs) Handles CapacityLinqDataSource.Selecting

Dim campaignXL As New CampaignXLDataClassesDataContext

Dim iNullable As Nullable(Of Integer)

Dim emptyCapacity As tblCapacity() = _

{New tblCapacity With {.capacityCode = "<Select a capacity>", .capacityID = iNullable}}

Dim capacity = (From Empty In emptyCapacity).Union( _

From tblCapacity In campaignXL.tblCapacities _

Order By tblCapacity.capacityCode)

e.Result = capacity

End Sub

This doesn't work (when I edit the row it gives an error saying iNullable should have a value); when I assign '0' to iNullable it gives me the earlier error message (for a good reason because the null value is still not present in the linqdataset for the dropdownlist.

It seems to work if I make a dynamicdata project out of my datamodel, so somehow there is a solutions for this problem. Hope you can help my, thanks in advance,

Rikki

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

Grzegorz — Fri, 07 Mar 2008 13:31:29 GMT

Great text! Very helpfull.

Now I see DataSet is dead - the end of database concept. Welcome to business objects concept.

Thats a pitty that O/R designer cannot set the association properties and one has to do it in xml. I think maybe service pack 1 for VS 2008...

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

Fatih — Fri, 14 Mar 2008 00:37:01 GMT

Hi, been learning from you for a few weeks now, much respect.

I get an error with your program when i do something i will guide below, after which everything fails (need to restart program).

1: on a new orderdetail fill in only quantity and price, leave combobox as is.

2: Try to save. You'll get a warning.

3: Now highlight this orderdetail we worked on and delete..

4: Now save again and you get an error about relation violation. After this you cant work with the database anymore.

Can you Beth or anyone else tell how to fix this? I think this will happen with everyone.

Thanks great tutes xx

LINQ to SQL N-Tier Smart Client - Part 1 Building the Middle-Tier

Beth Massi - Sharing the goodness that is VB — Sat, 12 Apr 2008 19:27:13 GMT

In my previous posts on LINQ to SQL I showed how to build LINQ to SQL classes and set up the data binding

LINQ to SQL N-Tier Smart Client - Part 2 Building the Client

Beth Massi - Sharing the goodness that is VB — Mon, 14 Apr 2008 21:08:17 GMT

In my last post we built the service and data access layer for our LINQ to SQL N-Tier application. In

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

Simone — Fri, 18 Apr 2008 20:04:53 GMT

Hi Beth. Great articles!!!

I have a question, I read your articles about smart client with Linq. I have to implement a connected client with Linq. My problem is he following:

Suppose I mapped with Linq tables Orders, OrderDetails and OrderDetailLines. An order has many OrderDetails and each OrderDetail has many detailLines. I created a formOrder where I Have a datacontext and I retrieve info about an order and I show a grid with orderDetails. All is databound. If I doubleclick on a detail row I show another form: OrderDetailForm (I pass to the form the rowDataboundItem) where I show OrderDetail and a grid with OrderDetailLines. I want to implement an UNDO feature, so if I modify an OrderDetailLine and then I press "Cancel Button" the orderdetailLine object is not modified. The same is for the orderdetail. I tried to implement IEditableObject but I have problems with relations properties (they return empty). I can't get "GetOriginalEntityState" methods because I must have the ability of edit a child object, then press ok to update it, the re-edit and press "Cancel" (and I want last updated values, not db values).

I'ts a very frustrating problem from me...It's five days I try every kind of solutions...!

The problem is that with databinding values are automatically updated. I could clone objects but I have a large number of business objects and implement a clone method would mean too many days.

Any idea??

Thank's in advance and sorry for the english...!

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

Brian in England — Fri, 02 May 2008 11:46:47 GMT

Please can you put all this new programming in to new video, as an add on the ‘Use DataSets in an N-Tier Application’ as I find it hard to follow.

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

Gonzalo — Thu, 08 May 2008 03:56:51 GMT

Hi Beth,

Awesome topics. Thanks for being such a soothing breeze in the "Sea of Linq". Would you suggest a way to handle Default Values With LINQ to SQL. I would like to be able to use my default values previously set on my SQL database with Linq when I do an insert to a table for data entry.

Best regards,

Gonzalo

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

Burton Roberts — Thu, 15 May 2008 19:42:10 GMT

The OMS_log.ldf file seems to be missing on the download. I can't seem to attach OMS without it.

Thanks

Your work is great.

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

Burton Roberts — Fri, 16 May 2008 23:37:06 GMT

Okay, I built the OMS from scratch in SQLExpress. I just installed SQL 2008 Feb CTP, moved it, and upgraded to VS2008 SP1 (beta) and Framework 3.5 SP1 I'm not getting the OrderDetails collection represented within the "Order" Class in Data Sources window after I drag the Order table onto it. However I am getting "Orders" (not "Order") under Customer.

BTW the table is "Orders" in the database and magically becomes "Order" in the OMS.dbml. Why, and could this be an issue. I then changed the name to "Orders", rebuilding and still getting no representation of the OrderDetails collection.

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

George Gopie — Mon, 19 May 2008 21:11:14 GMT

Hi Beth,

I am building a small application with Master Detail forms. iave the DB design with necessary Primary Keys and FK's. I have combo boxes on some columns in the Detail forms. I am unablke to update or save new data from the DEtail forms.

Can you Please advise.

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

Beth Massi — Mon, 19 May 2008 23:55:54 GMT

Hi Burton,

When you attempt to attach the mdf file just remove the LDF reference and click OK. SQL-Server will rebuild it. You can also run the sample as it is packaged as a user instance.

I'm not sure of the SP1 beta behavior but in the released version as long as you have referential integrity set up on your tables and you drag both of them onto the LINQ to SQL O/R designer, it will infer the relationships. The designer will rename your classes to singular but the child collections on the class are plural properties. This is just the default, you can change it.

HTH,

-B

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

Beth Massi — Mon, 19 May 2008 23:57:45 GMT

Hi George,

Does the sample provided with this post work for you? Check the update statements and make sure you are calling EndEdit on the binding sources before attempting to save.

-B

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

Gonzalo Chiriboga — Tue, 27 May 2008 08:07:40 GMT

Hi Beth,

Thanks in advance! Would you suggest a way to handle Default Values With LINQ to SQL. I would like to be able to use my default values previously set on my SQL database with Linq when I do an insert to a table for data entry.

Apologies for insisting on this question.

Warm regards,

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

Beth Massi — Tue, 27 May 2008 22:36:52 GMT

Hi Gonzalo,

If you want the defaults to appear on the objects when you add them to the collections you can do this in a variety of ways. You can take a look at the OnCreated partial method to set values when the object is created. But since LINQ to SQL classes are just plain CLR classes you could also just create a non-default constructor and pass it any values you want:

Sub New(ByVal orderDate As Date)

Me.New()

'Set default values

Me._OrderDate = orderDate

Me._CustomerID = 1

End Sub

Then on your form you could handle the BindingSource.AddingNew event:

Private Sub OrderBindingSource_AddingNew(ByVal sender As Object, ByVal e As System.ComponentModel.AddingNewEventArgs) Handles OrderBindingSource.AddingNew

Dim o As New Order(Date.Today)

e.NewObject = o

End Sub

HTH,

-B

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

Gonzalo Chiriboga — Wed, 28 May 2008 20:27:10 GMT

Thanks a lot Beth! You are a VB angel.

TH,

Beth Massi - Sharing the goodness that is VB : One-To-Many (Master-Detail) Forms with LINQ to SQL

Dating — Sat, 31 May 2008 20:47:44 GMT

In previous posts this month I showed how to use LINQ to SQL classes with a couple different Combobox data binding scenarios. (You can read those articles here and here .) Today I'm going to show you how to create a one-to-many data entry form (and we'l

Beth Massi - Sharing the goodness that is VB : One-To-Many (Master-Detail) Forms with LINQ to SQL

Weddings — Thu, 05 Jun 2008 14:43:19 GMT

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

pgraves — Thu, 12 Jun 2008 00:26:58 GMT

Thanks Beth, Being quite new to Linq I have been grateful for your videos and other help in getting my master details forms working properly. However there is one scenario below in which I am still struggling to get my data to update efficiently.

I have a form in which the controls on the top half are bound to fields in a table (Vehicles) using LINQ, and the bottom half shows a datagrid with rows from a related table (Vehicle Status).

A few of the controls in the top half display data from a view which is bound to the Vehicles, and which only shows the most recent records from Vehicle Status - giving information about the current (most recent) status.

When the vehicle status changes I successfully insert a new row in the vehicle status table, using the sequence: bindingsource.endedit, BindingSource.Add, datacontext.submitchanges, and the new record is saved as the most recent item in the status table. The datagridview refreshes and shows the new data row at the top as expected.

However the controls in the upper portion of the form which are bound to the 'latest status' view do not refresh to show the new latest status data.

I am now calling BindingSource.ResetBindings(False) on the vehicles table and the status table as well as calling datacontext.Refresh(Data.Linq.RefreshMode.OverwriteCurrentValues, VehicleBindingSource), all to no avail. Even requerying the data from the database does not refresh the controls on the form. The only way I can refresh this data is by closing and re-loading the form - not the most elegant solution.

This is now a large application so posting code will be a major operation, however any ideas would be very much appreciated.

Regards,

Pete

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

Kim — Wed, 02 Jul 2008 14:58:58 GMT

Hi Beth

Your articles are Great !!!

Quick Question

How do delete an individual row from the orderdetails datagridview ?

Also how can I prevent the user from entering alpha data in a numeric field in the datagridview ?

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

Beth Massi — Mon, 21 Jul 2008 23:14:26 GMT

Hi Kim,

You call the OrderDetailBindingSource.Remove method to delete the current row. The DataGridView will also delete a row if you select the row header on the left and then press the delete key.

Check out this topic (and related resources at the bottom) for info on formatting data in the DataGridView: http://msdn.microsoft.com/en-us/library/hezscd0d.aspx

HTH,

-B

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

JMSteele — Sat, 02 Aug 2008 15:34:46 GMT

Beth, thanks for all your efforts. I'm a big fan.

I've hesitated bothering you with this recurring problem but it's vexed me no matter how many times I've followed this tutorial either here or in the HDI vid version.

Most of the time, though not all, the following IndexOutOfRangeException is rasied when I close the form:

"Index 0 does not have a value at System.Windows.Forms.CurrencyManager.get_Item(Int32 index) at

System.Windows.Forms.DataGridView.DataGridViewDataConnection.GetError(Int32rowIndex)."

I'm using VB 2008 Professional on a 64-bit unit, with a target platform of x86. Googling around has failed to help. Perhaps you can point me in the right direction? Please.

Best wishes,

Jim

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

JMSteele — Sat, 02 Aug 2008 16:17:14 GMT

Followup: I've managed to stop the error by adding this to the FromClosing event:

Me.OrderDetailsDataGridView.Refresh()

I still have no clue as what initially caused the aforementioned error, however. Oh, well. It's working now, anyway.

Take care,

Jim

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

JMSteele — Sun, 03 Aug 2008 08:13:15 GMT

Okay, now I'm being a nudge, but ...

Can someone please clear this up for me?: I was given to understand that the LinqToSql class dbml is for one to one relationships only. If one wants to work with one to many relationships, SqlMetal generated class files is the way to go, so the advice goes.

What am I not understanding properly? Orders to Order Details, as we have in this tutorial, is a one to many relationship, is it not?

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

Beth Massi — Mon, 04 Aug 2008 18:46:26 GMT

Hi JMSteele,

You can definately represent one-to-many relationships with LINQ to SQL. LINQ to SQL classes are representations of the tables in your database and so the MAPPING is one class = one table. These classes use the database relations to create the one-to-many associations.

However it is very common in non-trivial applications for the need to map your calsses to multiple underlying tables in the database and that is something that Entity Framework does (which will be released as part of Visual Studio SP1 this summer).

HTH,

-Beth

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

JMSteele — Tue, 05 Aug 2008 08:29:30 GMT

Much thanks for the clear response, Beth. Now it makes sense.

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

Tom — Sun, 10 Aug 2008 06:03:33 GMT

First sry for my bad English. You'r tutorials are very good and i learn so much so tnx a lot. I have just one question. How is possible "price" to be in "product" table and when you choose a product you get price to for this product . Then you just put a Quantity and you get total automatically :).

Tnx for any help and ty for all your tutorials they are best.

Bye

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

John — Fri, 29 Aug 2008 15:39:41 GMT

I have the exact same problem that Fatih has.

Any ideas how to solve it?

I spend a few hours but found no way to overcome this one!

I quote Fatih:

": on a new orderdetail fill in only quantity and price, leave combobox as is.

2: Try to save. You'll get a warning.

3: Now highlight this orderdetail we worked on and delete..

4: Now save again and you get an error about relation violation. After this you cant work with the database anymore."

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

Russ — Wed, 10 Sep 2008 02:46:59 GMT

I spent most of my day working on this issue - finding this article was a life saver. Thank you for your expertise.

So do you forsee Microsoft adding the ability to specify the ondelete behavior through the user interface in the future?

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

Benoit — Wed, 10 Sep 2008 20:26:18 GMT

To : pgraves or people who have the same problem

I got this same problem for a similar project.. the way i resolve it.. is, i dispose the Datacontext object and recreate an instance of it..

To : Beth

Very good work...! i like every screencast you made!

Thanks!

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

Emmanuel Nuyttens — Tue, 14 Oct 2008 11:20:54 GMT

Hi Beth,

I have a problem with de "Delete" of OrderDetails in the DeleteItem_Click. In my C# code (sorry, i'm not realy into VB.NET), when I click the "Delete" button in the navig. bar, I get the previous order when i check on the currentitem so :

Order order = (Order)this.orderBindingSource.Current;

doesn't give me the "deleted" order but the previous one. Then i checked you're VB.NET example and here i got the right (deleted one).

I hade to change my C# Code as next to make it work:

private void bindingNavigatorDeleteItem_Click(object sender, EventArgs e)

{

//The basic idea is that we need to tell the DataContext to delete the child objects anytime an Order is deleted.

//In this example, I chose to do this by calling DeleteOnSubmit before we call SubmitChanges.

//I added this code into the Click event handler for the Delete button on the ToolStrip of the form:

try

{

this.Cursor = Cursors.WaitCursor;

// Get the changed Objects

ChangeSet changeSet = _db.GetChangeSet();

if (changeSet.Deletes.Count > 0)

{

// Check the deleted ones

foreach (var deletedOrder in changeSet.Deletes)

{

// Check first if deleted object is of "Order" type

if (deletedOrder.GetType().Name == "Order")

{

Order currentOrder = (deletedOrder as Order);

if (currentOrder != null)

{

// Mark related OrderDetail records for deletion.

foreach (OrderDetail detail in currentOrder.OrderDetails)

{

_db.OrderDetails.DeleteOnSubmit(detail);

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

finally

{

this.Cursor = Cursors.Default;

}

Any clue ?

greetz,

Emmanuel Nuyttens.

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

Beth Massi — Sat, 18 Oct 2008 02:58:13 GMT

Hi Emmanuel ,

In your situation you may need full control over the order things are deleted. You can solve this very easily. In the form designer select the OrderBindingNavigator and for the DeleteItem property set that to "(none)". Now we can write the delete manually on the Order and the OrderDetails in the Click event handler. It's the same code as before, but at the end we need to delete the parent order:

Private Sub BindingNavigatorDeleteItem_Click() _

Handles BindingNavigatorDeleteItem.Click

If Me.OrderBindingSource.Position > -1 Then

Dim order As Order = CType(Me.OrderBindingSource.Current, Order)

'Ensure that children are deleted when the parent is deleted

For Each detail In order.OrderDetails

db.OrderDetails.DeleteOnSubmit(detail)

'******

Me.OrderBindingSource.Remove(order)

'******

End If

End Sub

HTH,

-B

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

Emmanuel Nuyttens — Wed, 22 Oct 2008 11:26:31 GMT

Hi Beth,

Allright, this works smoothly, thx for the quick reply !

Emmanuel.

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

peter sha — Wed, 22 Oct 2008 13:29:42 GMT

Hi Beth,

Thanks for such great post, it's really helped us to resolve relationship problems we're having in O/R designer.

Still, we're stuck at the following problem:

Suppose we have a two level dataGrid (we're using UltraGrid in our project), if while saving a newly added row at the second (child) level, an error occurs from the database (unique key constraint, for example,) and we delete the newly added row (that caused the trouble) and submit the changes again, it throws an error "Unabled to remove a relationship ..."

However, we can reinitialize the database to get rid off with the error but doing so will detached the Grid from datacontext and any other changes will also be lost.

It appears to be a bug in Change Tracker not been resolved in SP1 as well since, if we add and immediately delete a new row without calling submit changes in between, everything works fine.

Thanks

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

Beth Massi — Wed, 22 Oct 2008 18:59:29 GMT

Hi Peter,

It sounds like the relationship isn't being picked up between parent/child on the client side. Can you make sure your bindingsources are configured properly? Does it work if you use two DataGridViews with bindingsources as I'm doing in this example?

You may also want to ask your question to the LINQ to SQL forums: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=2035&SiteID=1

HTH,

-B

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

peter sha — Thu, 23 Oct 2008 14:42:37 GMT

Hi Beth,

Thanks for the reply. The problem has somewhat been caught.

Actually, we're using parent.child.add(NewRow) procedure since using InsertOnSubmit makes UltraGrid lost one of many newly added rows while it still exists in the data context causing database to fire any validation error infinitely. It seems the Add method creates a child entity only and does not attach it explicitly with data context till submit changes is called.

Similarly, we're using parent.child.remove(SelectedRow) (since DeleteOnSubmit works only with attached records), which works fine, but if some error occurs while submitting a new record and we remove that row and call submit changes, relationship error occured.

Now the solution: I've added extra InsertOnSubmit statement (that would attach added row with data context) along with parent.child.add and have replaced parent.child.remove with DeleteOnSubmit and it's been working fine.

Thanks for that link, it would definitely be helpful.

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

George — Sat, 13 Dec 2008 05:42:25 GMT

Hello Beth and thank you for your videos.

Unfortunately, being visually impaired, they are

a bit difficult to see.

I am interested in creating a database application.

My question to you is, do you know of any text that

gives step by step instructions on how to use this new

LINQ to add, edit, update, delete and search?

I'm interested in the add, edit, update and delete

being on the form with the search button being able

to open another form with a grid. There, the user

can select the record they want and have the first

form populated with the information for editing.

Thank you very very very very very much in advance.

--George

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

Beth Massi — Mon, 15 Dec 2008 21:19:39 GMT

Hi George,

There are a lot of good articles on this blog as well as ThinqLinq.com. I'd also read through the walkthroughs in the MSDN Documentation: http://msdn.microsoft.com/en-us/library/bb399349.aspx

HTH,

-B

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

Sunil K — Wed, 11 Mar 2009 09:41:56 GMT

I have a form containing two DataGridView Controls. DataGridView1 & DataGridView2.

Northwind Database used.

//Code

BindingSource OrdBindingSource = new BindingSource(NorthwindDataSet, "Orders");

//"OrderToOrderDetailsRelation" is Parent Child Relationship between Orders & OrderDetails Table filled in the Northwind DataSet.

BindingSource OrdDetailsBindingSource = new BindingSource(OrdBindingSource, "OrderToOrderDetailsRelation");

DataGridView1.DataSource = OrdBindingSource;

DataGridView2.DataSource=OrdDetailsBindingSource;

how do i sum Column Values of Expression Column (Total Value) of DataGridView2.

I want to reflect the sum into a textbox whenever I select different rown in DataGridView1.

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

Sunil K — Wed, 11 Mar 2009 09:52:41 GMT

in addition I am using Untyped DataSet.

I have learned from your VB.NET video on How Do I: Video Series.

the Code Provided with the video is:

Private Sub CategoriesBindingSource_CurrentChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles CategoriesBindingSource.CurrentChanged

Dim row As CategoryProductDataSet.CategoriesRow

row = CType(CType(Me.CategoriesBindingSource.Current, DataRowView).Row, CategoryProductDataSet.CategoriesRow)

Dim total = Aggregate Products In Me.CategoryProductDataSet.Products _

Where Products.CategoryID = row.CategoryID AndAlso _

Products.Discontinued = False _

Into Sum(Products.UnitPrice * Products.UnitsInStock)

Me.TextBox1.Text = Format(total, "c")

End Sub

I just Want to know, how do i implement this in my C# Code.

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

Beth Massi — Wed, 11 Mar 2009 21:44:33 GMT

Hi Sunil,

The Aggregate keyword is VB-specific and isn't supported in C#. Instead you have to call the .Sum extension method and pass it a lambda expression. This should help you get started:

http://msdn.microsoft.com/en-us/vcsharp/aa336747.aspx

HTH,

-B

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

Ben Gu — Wed, 02 Sep 2009 15:40:41 GMT

Hi Beth, I have a very tricky question for you. I have a master-detail datagridview and I also want to have some text box related to master dataset and detail dataset to show current value in the datagridview. However, when I run the program (VB.NET), it pops up a running error of "the column doesn't belong to the detail table." If I just keep the common fields textbox for both dataset, it's ok. Anything outside of the common fields but only with detail dataset, I will see the running error. I wonder if theoretically I just can't bind the textbox to the detail dataset. Thanks. By the way, if possible, this is my email: ben.gu@tpwd.state.tx.us.

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

Piyush Varma — Wed, 23 Sep 2009 02:17:23 GMT

How do I fetch a default value from the table and assign it to the blank field of a new record for insert in UI after I click Insert New please?

Alternatively, can I hard code a default value in the insert new row in the UI so that the user can either accept it or change it before inserting it in the database?

Thank you, Beth.