Update a collection in C#

edited May 2012
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

Comments

  • edited June 2012
    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
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!