Updates in Linq

Hi,
I am new to Linq (this is my second day!) and am having a problem.
I have a csv file which I am querying and a sql database I am also querying. I am then creating a third query joining the two and want to update the results of the third query back to the underlying SQL data. The queries work correctly and bring back the results that I want, but when I try to update the data although no errors are thrown nothing in the underlying database changes. I have included the code, sql create and csv contents.
Thanks in advance
'CSV File Contents
    '1, name, address
    '2, another one, 1 high st
    '3, Something else, 123 acacia av
    '____________
    'SQL Table
    '    CREATE TABLE [dbo].[Property](
    '	[ID] [int] IDENTITY(1,1) NOT NULL,
    '	[Address] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
    '	[name] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
    '	[dateAdded] [datetime] NULL
    ') ON [PRIMARY]
  
Private Sub LinqTest()
 
        Dim csvQuery = From line In File.ReadAllLines("c:\temp\props.txt") _
                         Let x = line.Split(","c) Select [id] = (x(0))
 
        Dim results = csvQuery.ToList() 'gets 3 items
        Dim dc As New DataClasses1DataContext
        Dim sqlQuery = From p In dc.Properties Select p.ID, p.name, p.dateAdded
 
        Dim query = From c In csvQuery _
                 Join p In sqlQuery On c Equals p.ID _
                  Where p.ID > 1 And p.dateAdded Is Nothing _
                    Select p.dateAdded
 
        Dim result = query.ToList() 'gets 1 item
 
        For Each p In query
            p = Now 'this updates here but underlying data is unchanged
        Next
        dc.SubmitChanges() 'this doesn't update 
    End Sub

Open in new window

bhtownendAsked:
Who is Participating?
 
naspinskiCommented:
here:

Dim query = From c In csvQuery _
                 Join p In sqlQuery On c Equals p.ID _
                  Where p.ID > 1 And p.dateAdded Is Nothing _
                    Select p.dateAdded

You are selecting just the dateAdded, not a DB object.   You need to select the whole object, change the date added, then submitchanges:


Dim query = From c In csvQuery _
                 Join p In sqlQuery On c Equals p.ID _
                  Where p.ID > 1 And p.dateAdded Is Nothing _
                    Select p



Dim query = From c In csvQuery _
                 Join p In sqlQuery On c Equals p.ID _
                  Where p.ID > 1 And p.dateAdded Is Nothing _
                    Select p
 
For Each p In query
  p.dateAdded = Now 
Next
dc.SubmitChanges()

Open in new window

0
 
Fernando SotoRetiredCommented:
Hi bhtownend;

Two issues that I see:

1. Whenever you want ADO .Net to generate update and delete statements you need to assign a
    Primary Key to the table otherwise no updates or deletes will happen,

2. When working with Linq to SQL you can not mix a local sequence with a remote sequence. A local
    sequence is one that is in your local memory, an example would be csvQuery and a remote
    sequence would be a sequence from another machine or one from a server such as SQL server.
    The reason is that Linq will build a query not using the local sequence. Your query using the
    Join clause will look like this:

    SELECT [t0].[ID], [t0].[Address], [t0].[name], [t0].[dateAdded]
    FROM [dbo].[Property] AS [t0]

In this case I took the local sequence csvQuery and used the contains and feed it the SQL server sequence and the result is this as a SQL command sent to the SQL server:

SELECT [t0].[ID], [t0].[Address], [t0].[name], [t0].[dateAdded]
FROM [dbo].[Property] AS [t0]
WHERE ((CONVERT(NVarChar,[t0].[ID])) IN (@p0, @p1, @p2)) AND ([t0].[ID] > @p3) AND ([t0].[dateAdded] IS NULL)

The Contains builds a list of items and Linq adds the items into the query and sends the whole thing to SQL server.

Fernando

Private Sub LinqTest()
 
    Dim csvQuery = From line In File.ReadAllLines("c:\temp\props.txt") _
                   Let x = line.Split(","c) Select [id] = (x(0))
    Dim results = csvQuery.ToList() 'gets 3 items
 
    Dim dc As New DataClasses1DataContext
    dc.Log = Console.Out
 
    Dim sqlQuery = From p In dc.Property _
                   Where results.Contains(p.ID.ToString()) And _
                         p.ID > 1 And p.dateAdded Is Nothing _
                   Select p
 
    For Each p In sqlQuery
        p.dateAdded = Now 'this updates here but underlying data is unchanged
    Next
    dc.SubmitChanges() 'this doesn't update 
 
End Sub

Open in new window

0
 
Fernando SotoRetiredCommented:
Hi bhtownend;

Can you please post the code you finally ended up with.

Thank you.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Fernando SotoRetiredCommented:
Hi bhtownend;

This is my reasons for questioning the selected solution to the question.

I will assume that this is the query you used from the accepted answer from naspinski.

Dim csvQuery = From line In File.ReadAllLines("c:\temp\props.txt") _
               Let x = line.Split(","c) Select [id] = (x(0))

Dim results = csvQuery.ToList() 'gets 3 items
Dim dc As New DataClasses1DataContext
dc.Log = Console.Out
Dim sqlQuery = From p In dc.Properties Select p.ID, p.name, p.dateAdded

' naspinski posted solution
Dim query = From c In csvQuery _
            Join p In sqlQuery On c Equals p.ID _
            Where p.ID > 1 And p.dateAdded Is Nothing _
            Select p

' queryResult is here to enumerate the query because the For Each loop below gives the error that p.dateAdded id Read Only.
Dim queryResult() = query.ToArray

' The following four lines are removed because of the compile time error, p.dateAdded is Read Only
'For Each p In query
'    p.dateAdded = Now
'Next
'dc.SubmitChanges()

When the above code is executed in the IDE, the IDE Output window will have the SQL query built by Linq to SQL. This is the result of the line of code, dc.Log = Console.Out.

SELECT [t0].[ID], [t0].[name], [t0].[dateAdded]
FROM [dbo].[Property] AS [t0]

The Linq to SQL that is defined by the variable sqlQuery gets executed when the Linq to Object, defined by variable query, is enumerated over by the statement following the query and produces the SQL query above. The Linq to Object will return a collection of Anonymous type which are read only and not tracked by the data context and therefore can not be used to update the database.


============================================================================

The solution I have suggested:

Dim csvQuery = From line In File.ReadAllLines("c:\temp\props.txt") _
               Let x = line.Split(","c) Select [id] = (x(0))
Dim results = csvQuery.ToList()

Dim dc As New DataClasses1DataContext
dc.Log = Console.Out

Dim sqlQuery = From p In dc.Properties _
               Where results.Contains(p.ID.ToString()) And _
                     p.ID > 1 And p.dateAdded Is Nothing _
               Select p

For Each p In sqlQuery
    p.dateAdded = Now
Next
dc.SubmitChanges()

When the above Linq to SQL is enumerated over it will create the SQL statement below and sent to the SQL Server.

SELECT [t0].[ID], [t0].[Address], [t0].[name], [t0].[dateAdded]
FROM [dbo].[Property] AS [t0]
WHERE ((CONVERT(NVarChar,[t0].[ID])) IN (@p0, @p1, @p2)) AND ([t0].[ID] > @p3) AND ([t0].[dateAdded] IS NULL)

It sends the values of the fields in csvQuery as parameters in the SQL statement to the SQL server. The return type of the sqlQuery is of type Property and this type the data context will keep track of and any changes made to its value will be updated to the DB table on the execution of, dc.SubmitChanges().

Also as I stated in my original post the table needs a Primary Key.

Your reply will be deeply appreciated.
Fernando

0
 
bhtownendAuthor Commented:
Hi I have changed the table so that there is a primary key. The code is now as below and works perfectly. I have also changed the table name in this example to Propers as I felt using the table name of Property was confusing.
Regards
Ben
Private Sub LinqTest()
 
        Dim csvQuery = From line In File.ReadAllLines("c:\temp\props.txt") _
                       Let x = line.Split(","c) Select [idCSV] = (x(0))
 
        Dim dc As New DataClasses1DataContext()
 
        Dim sqlQuery = From p In dc.Propers _
                       Where p.dateAdded Is Nothing _
                       Select p
 
        Dim query = From c In csvQuery _
                 Join p In sqlQuery On c Equals p.ID _
                                      Select p
 
        For Each p In query
            p.dateAdded = DateAdd(DateInterval.Year, 1, Now)
 
        Next
        dc.SubmitChanges()
 
    End Sub
    'CSV File Contents
    '1, name, address
    '2, another one, 1 high st
    '3, Something else, 123 acacia av
    '____________
    'SQL Table
    '   CREATE TABLE [dbo].[Proper](
    '	[ID] [int] IDENTITY(1,1) NOT NULL,
    '	[Address] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
    '	[name] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
    '	[dateAdded] [datetime] NULL,
    ' CONSTRAINT [PK_Property] PRIMARY KEY CLUSTERED 
    '(
    '	[ID] ASC
    ')WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ') ON [PRIMARY]

Open in new window

0
 
Fernando SotoRetiredCommented:
Hi Ben;

Well I am glad you took my suggestion on setting a primary key on the table otherwise it will NOT update the table because of what I stated in my first post.

On this part of your solution:

Dim query = From c In csvQuery _
                   Join p In sqlQuery On c Equals p.ID _
                   Select p

I do not know how you are able to update the database table because the result that will be placed into the variable "query" is of Anonymous type and not of the table "Proper" type which means that the DataContext is not tracking the results of that query and when you do a dc.SubmitChanges() there will be nothing in the DataContext to update. I will test this solution one more time with the code you just supplied and will post my results.

Thank you;
Fernando Soto
0
 
Fernando SotoRetiredCommented:
Hi Ben;

Well I tested it and in fact it does work as you stated and the reason why is that the SQL query is executed and then the main query uses the local sequence returned from that SQL query the local version of csvQuery.

Fernando
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.