Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Updates in Linq

Posted on 2009-02-20
7
Medium Priority
?
617 Views
Last Modified: 2013-11-11
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

0
Comment
Question by:bhtownend
  • 5
7 Comments
 
LVL 21

Accepted Solution

by:
naspinski earned 1500 total points
ID: 23698222
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
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 23698555
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
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 23705936
Hi bhtownend;

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

Thank you.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 64

Expert Comment

by:Fernando Soto
ID: 23706224
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
 

Author Comment

by:bhtownend
ID: 23706330
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
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 23707264
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
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 23707981
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

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question