Update a collection in C#

rachidadukes

I'm trying to update some records in a collection, but I have issues updating it.
If I use this example:

from c in Customers
select
from p in c.Purchases
select new { c.Name, p.Price }

How can update the price?
thanks so much for your help.
Rachida

A_Tolmeijer

Hi Rachida,

I'm assuming you have a database containing the tables Customers and Purchases. If that's the case, make sure your tables have a primary key and there exists a foreign key relationship between the two. You can create sample tables with the SQL script below.

/****** Object:  Table [dbo].[Customers]    Script Date: 1-6-2012 12:24:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customers](
	[Id] [int] NOT NULL,
	[Name] [nvarchar](50) NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
	[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
/****** Object:  Table [dbo].[Purchases]    Script Date: 1-6-2012 12:24:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Purchases](
	[Id] [int] NOT NULL,
	[CustomerId] [int] NOT NULL,
	[Price] [float] NOT NULL,
 CONSTRAINT [PK_Purchases] PRIMARY KEY CLUSTERED 
(
	[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].[Purchases] ADD  CONSTRAINT [DF_Purchases_Price]  DEFAULT ((0)) FOR [Price]
GO
ALTER TABLE [dbo].[Purchases]  WITH CHECK ADD  CONSTRAINT [FK_Purchases_Customers] FOREIGN KEY([CustomerId])
REFERENCES [dbo].[Customers] ([Id])
GO
ALTER TABLE [dbo].[Purchases] CHECK CONSTRAINT [FK_Purchases_Customers]
GO

You will notice that LINQPad will put additional navigational properties in the schema explorer, like CustomerPurchases. If you change your code to the code below, you will get a list of items. Clicking ono each item, you can see the individual customer records, including purchase prices:

	from c in Customers
	select 
	from p in c.CustomerPurchases
	 select new { c.Name, p.Price }

Now, to update a price of a purchase, select the purchases you are interested in. When you have applied the update, make sure you'll execute SubmitChanges(), like in the following code:

// Execute as C# Statement(s)
var purchases = from c in Customers
				from p in Purchases
				where p.Customer == c 
					  && c.Id == 2
				select p;

foreach ( var p in purchases )
	p.Price = p.Price * 2;

( from c in Customers
  select c.CustomerPurchases ).Dump();

  SubmitChanges();

However, a much easier way is to navigate form the many side to the 1 side of the relationship. The following code will establiwsh exactly the same thing, but with far less ceremony:

// Execute as C# Statement(s)
var purchases = from p in Purchases
				where p.Customer.Id == 2
				select p;

foreach ( var p in purchases )
	p.Price = p.Price * 2;

( from c in Customers
  select c.CustomerPurchases ).Dump();

  SubmitChanges();

HTH,
Arno