LINQ Update Statement

Imar · April 17th, 2011, 04:44 AM

Hi Phil,

Yes, that should work without a problem. Something like this should work:

Code:

 
myEntity.PropertyOfTypeGuid = null;

In order for this to work, the PropertyOfTypeGuid column needs to be marked as nullable in the database before you add its table to the EF diagram.

Hope this helps,

Imar

BTW: while related to updates, I think this question would have been better off in a new thread. Makes it easier for everyone to find relevant stuff.

Phil Critchley · April 17th, 2011, 02:11 PM

Hi Imar

I did originally think that it was that simple but when I tried setting the value as 'null' as per your example Visual Studio code behind page throws an exception saying it is not allowed. The SQL Server database table for that particular field does allow NULL values.

What I ended up doing is

Code:

reservationStatus.ReservationID = (GUID.Empty)

From what I've been able to find out about the GUID.Empty option from Google searches this should provide me with the same functionality as changing the GUID to NULL, which like I say is not possible through Visual Studio.

Imar · April 17th, 2011, 02:29 PM

I just tried it in my own code and it works fine. Did you set the column as nullable before you added the table to the diagram? Otherwise, the EF diagram is not aware of this. You can check the property in the EF designer and look at the properties grid. What do you see for the nullable behavior?

Guid.Empty is sort of null, but not really. It looks like 000000-000000-000000-000000-00000 in the database.

Cheers,

Imar

Imar · April 17th, 2011, 02:54 PM

Oh, BTW, if you're using VB you need to set it to Nothing instead:

reservationStatus.ReservationID = Nothing

Hope this helps,

Imar

Phil Critchley · April 17th, 2011, 03:04 PM

Hi Imar

Yeah the 'Nothing' function worked as you suggested. The majority of web sources I'd found suggested the 'Null' function I didn't even think that the NULL option was only available for C# I just took it for granted that it would work with VB.

Thank you once again for the suggestion.

Phil

Phil Critchley · April 30th, 2011, 07:42 AM

Hi

Does anyone know how to assign the returned values of a LINQ to Entities Select query to individual variables?

Code:

Using myLiteratureCatalogue As New Model1.Literature_Cataloguing_SystemEntities
            Dim getReservationStatus = From publication In myLiteratureCatalogue.Publications
                                       Where publication.PublicationID = (PublicationID)
                                       Select publication.ReservationID, publication.StatusID

ReservationID = 
StatusID = 
        End Using

Imar · April 30th, 2011, 07:49 AM

Hi Phil,

You can loop over the result set and acces the properties if you have more than one item. If you know your query returns a single result, you can use Single() or SingleOrDefault at the end to create a single item:

Code:

Dim getReservationStatus = (From publication In myLiteratureCatalogue.Publications
                                       Where publication.PublicationID = (PublicationID)
                                       Select publication.ReservationID, publication.StatusID).Single()

SingleOrDefault returns Nothing when no item can be found, while Single() will throw an exception if the list is empty. That can be a good thing to track bugs if you think your query must always return a value.

Cheers,

Imar

Phil Critchley · April 30th, 2011, 08:04 AM

Hi Imar

Thank you for the quick response. My code should always return two values the ReservationID (GUID) and the StatusID(INT32) for the specific PublicationID (INT32) provided from a stored variable. When the code executes and brings back the results I want it to store the returns in additional stored variables so they can be accessed by other sub procedures within the same class

Code:

Using myLiteratureCatalogue As New Model1.Literature_Cataloguing_SystemEntities
            Dim getReservationStatus = From publication In myLiteratureCatalogue.Publications
                                       Where publication.PublicationID = (PublicationID)
                                       Select publication.ReservationID, publication.StatusID

I did try it with SQL but for some reason SQL Select query is not bringing back any results

Code:

Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("LiteratureCataloguingConnectionString").ConnectionString)

            Const SQL As String = "SELECT ReservationID, StatusID FROM Publication WHERE PublicationID = @PublicationID"
            Dim myCommand As New SqlCommand(SQL, myConnection)
            myCommand.Parameters.AddWithValue("@PublicationID", PublicationID)

            myConnection.Open()
            Dim myReader As SqlDataReader = myCommand.ExecuteReader

            If myReader.Read Then
                ReservationID = myReader.GetGuid(0)
                StatusID = myReader.GetInt32(1)
            End If

            myReader.Close()
            myConnection.Close()
        End Using

Phil Critchley · April 30th, 2011, 09:25 AM

Does anyone have an example of how you loop through a LINQ to Entities resultset to store each result as a different variable?

Imar · April 30th, 2011, 10:00 AM

Hi Phil,

I think you need to define what you mean with " to store each result as a different variable".

Consider this:

Code:

 
Dim getReservationStatus = From publication In myLiteratureCatalogue.Publications
                                       Where publication.PublicationID = (PublicationID)
                                       Select publication.ReservationID, publication.StatusID

What is getReservationStatus? It retuns an IEnumerable of some anonymous type with properties called ReservationID and StatusID.

Whether getReservationStatus returns one or more items depends on the query. If it retuns a single instance (with two properties) Single() should work. You should then be able to access getReservationStatus.ReservationID and getReservationStatus.StatusID

If, however, the query returns multiple objects (each with the two properties), you need a For Each loop over getReservationStatus and within the loop access the two properties.

Does this make sense?

Imar