none
Loop through query for custom EF classes RRS feed

  • Question

  • Hello,

    I'm experiencing some problems on looping through a query and filling a list with results of this Query.
    I'll try to explain with an example :-).
    In the code I have 3 options I tried out, but they all give the same result ...
    So I'm looking for a correct way to handle this.
    IMHO I think it all has to do with pointers. When you add an object to a collection in VB, you add a reference to that object. So everything you change, through that pointer, affects the object ...
    (This code is written directly in the forum, so some mal-types can be possible ...)

    Database Table:
      Employee
      Employee_PID
      Employee_Name
      Employee_Nr
    ==> Create an entity class for this one, named Employee
    I create a partial class for this Employee:
    Public Class Employee
      Public Property BirthDate As Date
    End Class
    ==> Assuming I don't have the Birthdate of this employee in this database, but in another.

    I create in this class also a function to retrieve all Employees, and fill the data:
    Public Class Employee
      Public Property BirthDate As Date

      Public Shared Function Load() As Objectmodel.Collection(Of Employee)
        Dim oRetVal as New Objectmodel.Collection(Of Employee)
        Dim oDB As New Entitytywitty
        Dim oQuery = (From currEmployee In oDB.Employee
                      Join currBDTable In oDB.BirthdaysTable
                      on currEmployee.Employee_Nr = currBDTable.Employee_Nr
                       Select currEmployee, currBDTable
        For Each oObj in oQuery
               'WHAT SHOULD I DO HERE????
               'Option 1:
               oRetValue.Add(oObj.currEmployee)
               oRetValue.Item(oRetValue.Count - 1).BirthDate = currBDTable.BirthDate
               ' ==> Doesn't work (values get mixed up, BirthDate from one Employee, mixed with another Employee.)

               'Option 2:
               Dim oEmployee As Employee = oObj.currEmployee
               oEmployee.BirthDate = currBDTable.BirthDate
               oRetValue.Add(oEmployee)
               ' ==> Doesn't work (values get mixed up, BirthDate from one Employee, mixed with another Employee.)

               'Option 3:
               oObj.currEmployee.BirthDate = currBDTable.BirthDate
               oRetValue.Add(oObj.currEmployee)
               ' ==> Doesn't work (values get mixed up, BirthDate from one Employee, mixed with another Employee.) 
        Next
        Return oRetVal
     End Function
    End Class

    Greetz,

    Tim

    Friday, July 27, 2012 9:43 AM

Answers

  • Hey Alexander,

    I found a solution!
    There is a method "Memberwiseclone" that I can use ...
    I do the following now in my loop:

                    Dim oDMFA As DMFAAangifteVolgnrs_273 = oObj.DMFAAangifteVolgnrs.MemberwiseClone()

                    oDMFA.IntNrWg = oObj.IntNrWg_060

                    oDMFA.Omgeving = oObj.Omgeving_271

                    oRetValue.Add(oDMFA)

    Thanks for your help,

    Greetz,

    Tim

    • Marked as answer by Alexander Sun Monday, August 6, 2012 4:53 AM
    Friday, August 3, 2012 12:07 PM

All replies

  • Nobody? :-(
    Am I the only one trying to do this? It looks like a "simpel"-usage of EF? Or am I understanding something wrong?
    Isn't this the way I should use "custom" properties for an EF-class? Or fill these custom properties?

    Greetz,
    Tim

    Sunday, July 29, 2012 1:07 PM
  • Hi Tim,

    Thank you for your post.

    Please note this: since you have not the where clause in your LINQ query, the result will return all the currEmployee and currBDTable which have the same Employee_Nr. Thus, if you iterate the results, all the Employee will be added into the collection.

    Since you declared the method “Load” as a static method, it is not a instance method. Consequently, if you want to set the BirthDate property of each Employee, you should search the all the Employee entities with the Employee_PID of the oObj.currEmployee then assign it the value. Alternatively, I think you can set a one to one relationship for oDB.Employee and oDB.BirthdaysTable, which you will not be required to set the BirthDate property manually: http://weblogs.asp.net/manavi/archive/2011/05/01/associations-in-ef-4-1-code-first-part-5-one-to-one-foreign-key-associations.aspx

    Have a nice day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    Monday, July 30, 2012 6:47 AM
  • Hey Alexander,

    Thank you for your reply.
    I'll check out the website you provided to try and solve it with the foreign keys.
    Unfortunately the situation is a bit more complicated than the example I gave here. (Therefore I also didn't have a WHERE clause).
    Searching each Employee, using the Employee_PID is not an option since I would have thousands of single-line queries.

    Have a nice day,
    Tim

    Wednesday, August 1, 2012 8:31 AM
  • Hey Alexander,

    I checked out the article, but unfortunately it doesn't solve my problem.
    I'll post the original query statement here, to explain:
    Dim oQuery = (From DMFAAangifteVolgnrs In oDB.DMFAAangifteVolgnrs_273

                        Join WgNummersDoc In oDB.WgNummersDoc_271

                        On DMFAAangifteVolgnrs.Volgnr_273 Equals WgNummersDoc.Aangifte_271 And

                        WgNummersDoc.Periode_271 Equals DMFAAangifteVolgnrs.Jaar_273 & DMFAAangifteVolgnrs.Kwartaal_273

                        Join Werkgever In oDB.Werkgever_060

                        On Werkgever.IntNrWg_060 Equals WgNummersDoc.IntNrWg_271

                        Where DMFAAangifteVolgnrs.OntvangstDatum_273 >= dSinceDate

                        Select Werkgever.ExtNrWg_060, Werkgever.NrDosBeh_060, DMFAAangifteVolgnrs, WgNummersDoc.Omgeving_271, Werkgever.IntNrWg_060

                        Order By NrDosBeh_060, ExtNrWg_060, DMFAAangifteVolgnrs.OntvangstDatum_273).ToList

    The database is designed by an external company, so I am not able to create constraints for foreign keys, and most relations between tables are very strange ...
    In the above query, I create a relationship between the tables DMFAAangifteVolgnrs_273, WgNummersDoc_271 and Werkgever_060.
    When I loop through the query, I want to put all information in a collection (or list or whatever) of DMFAAangifteVolgnrs_273.
    But this class contains two extra properties: Omgeving_271 and IntNrWg_060 I can only get from those other two tables ...
    So I tried a lot things in my loop, and -as an example- this was my last try:

    Dim oRetValue As New ObjectModel.Collection(Of DMFAAangifteVolgnrs_273)
    For
    Each oObj In oQuery
                    oRetValue.Add(oObj.DMFAAangifteVolgnrs)
                    oRetValue.Item(oRetValue.Count - 1).Omgeving = oObj.Omgeving_271
                    oRetValue.Item(oRetValue.Count - 1).IntNrWg = oObj.IntNrWg_060
                Next

    But all options I tried, adapt previous objects in my collection, so the result isn't correct ...
    Is there a way to Select columns directly into my object? (So I don't need to do a For Each loop anymore ...)

    Greetz,
    Tim

    Wednesday, August 1, 2012 9:01 AM
  • Hi Tim,

    The query results you want do not contain Omgeving_271 and IntNrWg_060 properties. Is that right?

    If I misunderstood anything, please feel free to let me know.

    Have a nice day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, August 2, 2012 6:38 AM
  • Hey Alexander,

    Sorry for that. Here is the correct query:

                Dim oQuery = (From DMFAAangifteVolgnrs In oDB.DMFAAangifteVolgnrs_273

                        Join WgNummersDoc In oDB.WgNummersDoc_271

                        On DMFAAangifteVolgnrs.Volgnr_273 Equals WgNummersDoc.Aangifte_271 And

                        WgNummersDoc.Periode_271 Equals DMFAAangifteVolgnrs.Jaar_273 & DMFAAangifteVolgnrs.Kwartaal_273

                        Join Werkgever In oDB.Werkgever_060

                        On Werkgever.IntNrWg_060 Equals WgNummersDoc.IntNrWg_271

                        Where DMFAAangifteVolgnrs.OntvangstDatum_273 >= dSinceDate

                        Select Werkgever.IntNrWg_060, DMFAAangifteVolgnrs, WgNummersDoc.Omgeving_271).ToList

    Greetz,
    Tim


    Thursday, August 2, 2012 12:14 PM
  • I assume I can use Associations in EF (without touching the database, since it's not mine) to create the join between the tables automatically.
    For the Werkgever_060 table, it's easy since it's only one column to join.
    For the DMFAAangifteVolgnrs_273 it's harder, since I need to be able to concatenate two columns to compare with one.


    You have any idea if this is possible?

    Greetz,
    Tim

    Thursday, August 2, 2012 1:22 PM
  • Hi Tim,

    Personally, I think it is possible, so I created a small sample to test it. It works perfectly on my sample. I think this attempt is worth trying for you.

    Best Regards,


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    Friday, August 3, 2012 7:58 AM
  • Hey Alexander,

    Sounds perfect. Where can I find the sample?
    In the meanwhile I tried to simulate the problem in another project, but there it seems to work. I'm not using EF (but just a class) so maybe it has something to do with EF.

    Thanks,
    Tim

    Friday, August 3, 2012 11:01 AM
  • This is the example I created.
    When I run this, I get two times the following in Console:

    Initial:

    1 - Piet Huysentruyt - NEW-IIIIIIIIII

    2 - Jozef De Vuyster - NEW-IIIIIIIIII

    3 - Steve Tijpels - NEW-UUUUUUUUUU

    4 - Svetlana Bolsjakova - NEW-UUUUUUUUUU

    5 - Mia Dewulf - NEW-AAAAAAAAAA

    6 - Jos Den os - NEW-AAAAAAAAAA

    7 - Fons De Spons - NEW-EEEEEEEEEE

    8 - Eddy Wally - NEW-EEEEEEEEEE

    Result:

    1 - Piet Huysentruyt - NEW-IIIIIIIIII

    2 - Jozef De Vuyster - NEW-IIIIIIIIII

    3 - Steve Tijpels - NEW-UUUUUUUUUU

    4 - Svetlana Bolsjakova - NEW-UUUUUUUUUU

    5 - Mia Dewulf - NEW-AAAAAAAAAA

    6 - Jos Den os - NEW-AAAAAAAAAA

    7 - Fons De Spons - NEW-EEEEEEEEEE

    8 - Eddy Wally - NEW-EEEEEEEEEE


    When I run this, using database tables (and using a partial class to add the "extra" property "SomeExtraInfo" not contained in the DB) I get something like this: (Just an example!!)

    Initial:

    1 - Piet Huysentruyt - NEW-IIIIIIIIII

    2 - Jozef De Vuyster - NEW-IIIIIIIIII

    3 - Steve Tijpels - NEW-UUUUUUUUUU

    4 - Svetlana Bolsjakova - NEW-UUUUUUUUUU

    5 - Mia Dewulf - NEW-AAAAAAAAAA

    6 - Jos Den os - NEW-AAAAAAAAAA

    7 - Fons De Spons - NEW-EEEEEEEEEE

    8 - Eddy Wally - NEW-EEEEEEEEEE

    Result:

    1 - Piet Huysentruyt - NEW-EEEEEEEEEE

    2 - Jozef De Vuyster - NEW-EEEEEEEEEE

    3 - Steve Tijpels - NEW-EEEEEEEEEE

    4 - Svetlana Bolsjakova - NEW-EEEEEEEEEE

    5 - Mia Dewulf - NEW-IIIIIIIIII

    6 - Jos Den os - NEW-EEEEEEEEEE

    7 - Fons De Spons - NEW-EEEEEEEEEE

    8 - Eddy Wally - NEW-EEEEEEEEEE


    Class MainWindow

        Public Class Person

            Public Property ID As Integer

            Public Property Firstname As String

            Public Property Lastname As String

            Public Property HealthID As Integer

            Public Property SomeExtraInfo As String

            Public Sub New(iID As Integer, sFirstname As String, sLastName As String, iHealth As Integer)

                Me.ID = iID

                Me.Firstname = sFirstname

                Me.Lastname = sLastName

                Me.HealthID = iHealth

            End Sub

        End Class

        Public Class Health

            Public Property ID As Integer

            Public Property HealthIdenticator As String

            Public Sub New(iID As Integer, sHealthId As String)

                Me.ID = iID

                Me.HealthIdenticator = sHealthId

            End Sub

        End Class

        Private Sub MainWindow_Loaded(sender As Object, e As System.Windows.RoutedEventArgs) Handles Me.Loaded

            Try

                Dim oHealths As New ObjectModel.Collection(Of Health)

                Dim oHealth10 As New Health(10, "EEEEEEEEEE")

                Dim oHealth20 As New Health(20, "AAAAAAAAAA")

                Dim oHealth30 As New Health(30, "UUUUUUUUUU")

                Dim oHealth40 As New Health(40, "IIIIIIIIII")

                oHealths.Add(oHealth10)

                oHealths.Add(oHealth20)

                oHealths.Add(oHealth30)

                oHealths.Add(oHealth40)

                Dim oPersons As New ObjectModel.Collection(Of Person)

                oPersons.Add(New Person(1, "Piet", "Huysentruyt", oHealth40.ID))

                oPersons.Add(New Person(2, "Jozef", "De Vuyster", oHealth40.ID))

                oPersons.Add(New Person(3, "Steve", "Tijpels", oHealth30.ID))

                oPersons.Add(New Person(4, "Svetlana", "Bolsjakova", oHealth30.ID))

                oPersons.Add(New Person(5, "Mia", "Dewulf", oHealth20.ID))

                oPersons.Add(New Person(6, "Jos", "Den os", oHealth20.ID))

                oPersons.Add(New Person(7, "Fons", "De Spons", oHealth10.ID))

                oPersons.Add(New Person(8, "Eddy", "Wally", oHealth10.ID))

                Dim oRetValue As New ObjectModel.Collection(Of Person)

                Dim query = (From allPersons In oPersons

                             Join allHealths In oHealths

                             On allPersons.HealthID Equals allHealths.ID

                            Select allPersons, allHealths.HealthIdenticator).ToList

                Debug.Print("Initial:")

                For Each tmpPerson In query

                    'Debug.Print("/ " & tmpPerson.allPersons.ID & " - " & tmpPerson.allPersons.Firstname & " " & tmpPerson.allPersons.Lastname & " - " & tmpPerson.HealthIdenticator)

                    tmpPerson.allPersons.SomeExtraInfo = "NEW-" & tmpPerson.HealthIdenticator

                    Debug.Print(tmpPerson.allPersons.ID & " - " & tmpPerson.allPersons.Firstname & " " & tmpPerson.allPersons.Lastname & " - " & tmpPerson.allPersons.SomeExtraInfo)

                    oRetValue.Add(tmpPerson.allPersons)

                Next

                Debug.Print("Result:")

                For Each somePerson In oRetValue

                    Debug.Print(somePerson.ID & " - " & somePerson.Firstname & " " & somePerson.Lastname & " - " & somePerson.SomeExtraInfo)

                Next

                Debug.Print("Done")

            Catch ex As Exception

                MessageBox.Show(ex.Message)

            End Try

        End Sub

    End Class

    Friday, August 3, 2012 11:41 AM
  • Hey Alexander,

    I found a solution!
    There is a method "Memberwiseclone" that I can use ...
    I do the following now in my loop:

                    Dim oDMFA As DMFAAangifteVolgnrs_273 = oObj.DMFAAangifteVolgnrs.MemberwiseClone()

                    oDMFA.IntNrWg = oObj.IntNrWg_060

                    oDMFA.Omgeving = oObj.Omgeving_271

                    oRetValue.Add(oDMFA)

    Thanks for your help,

    Greetz,

    Tim

    • Marked as answer by Alexander Sun Monday, August 6, 2012 4:53 AM
    Friday, August 3, 2012 12:07 PM