none
Convert strong typed from linq ObjectQuery to ObservableCollection RRS feed

  • Question

  • In my DAL I want to have a linq query return a subset of columns and bind directly to a view in WPF. I keep running into the InvalidCastException when trying to go from ObjectQuery to ObservableCollection.

    Below is a simple example using a basic Customer and Order table. I've created a myCustomer class to be a subset of the Customer table columns. In the linq code I've made sure to format the results as a myCustomer type. But I cannot figure out a way to return this so it can be bound to in WPF.

     Public Function GetCustomerList2() As System.Collections.ObjectModel.ObservableCollection(Of myCustomer)
    
            Dim results = From c In _db.Customers
                          Join o In _db.Orders
                          On c.CustomerID Equals o.CustomerID
            Group c By c.CustomerID, c.LastName, c.FirstName, c.Address, c.City, c.State, c.ZIP, c.Modified Into Group
            Order By LastName, FirstName
            Select New myCustomer(CustomerID, LastName, FirstName)
    
            Return results
    
        End Function
    
    Public Class myCustomer
        Public Property CustomerID() As Global.System.Int32
        Public Property LastName() As Global.System.String
        Public Property FirstName() As Global.System.String
        Public Sub New(ByVal cid As Int32, ByVal ln As String, ByVal fn As String)
            CustomerID = cid
            Me.LastName = ln
            Me.FirstName = fn
        End Sub
    End Class
    
    

     

     

    Friday, September 16, 2011 5:11 PM

Answers

  • Hi pretzelb;

    Re-formatted your query so that the ObservableCollection will correctly initialize. In your query you Join two tables and group by multiple fields but only use three fields on the result and all of Customers. If you tell me what you need to do I will see if I can re-write the query.

    Public Function GetCustomerList2() As System.Collections.ObjectModel.ObservableCollection(Of myCustomer)
    
        Dim results = From c In _db.Customers
                      Join o In _db.Orders
                      On c.CustomerID Equals o.CustomerID
                      Group c By Key = New With { .CustomerID = c.CustomerID, .LastName = c.LastName, .FirstName = c.FirstName, c.Address, .City = c.City, .State = c.State, .Zip =c.ZIP, .Modified = c.Modified} Into Group
                      Order By Key.LastName, Key.FirstName
                      Select New myCustomer With
                      {
                      	.CustomerID = Key.CustomerID, 
                      	.LastName = Key.LastName, 
                      	.FirstName = Key.FirstName
                      }
    
    	Dim oc As ObservableCollection(Of myCustomer) = New ObservableCollection(Of myCustomer)(results)
    	
        Return oc
    
    End Function
    
    ' Do NOT place a constructor in this call otherwise the Linq query will have an error. 
    Public Class myCustomer
        Public CustomerID() As Global.System.Int32
        Public LastName() As Global.System.String
        Public FirstName() As Global.System.String
    End Class
    


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by pretzelb Tuesday, September 20, 2011 8:21 PM
    Friday, September 16, 2011 11:33 PM

All replies

  • Hi pretzelb;

    The query returns a IQueryable(Of myCustomer) and to convert that to an ObservableCollection you can pass the query results to the constructor of the ObservableCollection as shown in the code snippet.

    Dim customerObservable As ObservableCollection(Of myCustomer) = _
    	 New ObservableCollection(Of myCustomer)(results)
    	 
    Return customerObservable
    
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Friday, September 16, 2011 6:37 PM
  • I think I did try that but just to be sure I tried it again and got the "Only parameterless constructors and initializers are supported in LINQ to Entities" error. Looking around I think this link explains the issue (http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/49a23b65-cdf8-4f58-b4a0-4effbe10c1ae). I also found another related post here (http://mosesofegypt.net/post/2008/08/31/LINQ-to-Entities-Workarounds-on-what-is-not-supported.aspx). Still not sure if either will have a solution.

    What confuses me is that if you just pull from a model directly you can wrap this in a custom class that inherits an ObservableCollection of <t> and it all works. For example, this works fine (Customer is the entity as defined by the model and maps to a table). I see how the EF defined Customer and I'm not sure why it's any different than how I defined the custom class myCustomer, yet the query results on the EF generated class convert fine to ObservableCollection.

    Public Class CustomerCollection
        Inherits ObservableCollection(Of Customer)
        Private db As OMSEntities
        Sub New(ByVal cust As IEnumerable(Of Customer), ByVal context As OMSEntities)
            MyBase.New(cust)
            db = context
        End Sub
    End Class
    
    Public Function GetCustomerList2b() As CustomerCollection
        Dim results = From c In _db.Customers
            Select c
        Return New CustomerCollection(results, _db)
    End Function
    
    


     

     

    Friday, September 16, 2011 8:47 PM
  • Combining the suggestion of Fernandoo and the links I found the code below works but seems very clunky. I'd like to know if there is a better way or have someone else confirm that this is indeed what you need to do when dealing with linq to entities and trying to bind to a wpf client.

        Public Function GetCustomerList2() As System.Collections.ObjectModel.ObservableCollection(Of myCustomer)
            Dim results = From c In _db.Customers
                          Join o In _db.Orders
                          On c.CustomerID Equals o.CustomerID
                        Group c By c.CustomerID, c.LastName, c.FirstName, c.Address, c.City, c.State, c.ZIP, c.Modified Into Group
                        Order By LastName, FirstName
                        Select id = CustomerID, ln = LastName, fn = FirstName
            Dim mycusts As IEnumerable(Of myCustomer) = From myc In results.AsEnumerable()
                Select New myCustomer(
                    myc.id,
                    myc.ln,
                    myc.fn
                    )
            Return New ObservableCollection(Of myCustomer)(mycusts)
        End Function
    

     

    Friday, September 16, 2011 9:38 PM
  • Hi pretzelb;

    Re-formatted your query so that the ObservableCollection will correctly initialize. In your query you Join two tables and group by multiple fields but only use three fields on the result and all of Customers. If you tell me what you need to do I will see if I can re-write the query.

    Public Function GetCustomerList2() As System.Collections.ObjectModel.ObservableCollection(Of myCustomer)
    
        Dim results = From c In _db.Customers
                      Join o In _db.Orders
                      On c.CustomerID Equals o.CustomerID
                      Group c By Key = New With { .CustomerID = c.CustomerID, .LastName = c.LastName, .FirstName = c.FirstName, c.Address, .City = c.City, .State = c.State, .Zip =c.ZIP, .Modified = c.Modified} Into Group
                      Order By Key.LastName, Key.FirstName
                      Select New myCustomer With
                      {
                      	.CustomerID = Key.CustomerID, 
                      	.LastName = Key.LastName, 
                      	.FirstName = Key.FirstName
                      }
    
    	Dim oc As ObservableCollection(Of myCustomer) = New ObservableCollection(Of myCustomer)(results)
    	
        Return oc
    
    End Function
    
    ' Do NOT place a constructor in this call otherwise the Linq query will have an error. 
    Public Class myCustomer
        Public CustomerID() As Global.System.Int32
        Public LastName() As Global.System.String
        Public FirstName() As Global.System.String
    End Class
    


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by pretzelb Tuesday, September 20, 2011 8:21 PM
    Friday, September 16, 2011 11:33 PM
  • The idea behind the query was to create a unique list of customers who had an order. That is why the join was there to the order entity and why the group by clause was included. The other objective I wanted to test was working with an entity model that was based on a database you couldn't change to see how you could shape the results into custom classes that made more logical sense.

    Maybe you are correct in that the problem with my first example was the inclusion of a constructor. I made one minor change (I had to specify "property" for the myCustomer field definitions) to your latest example and it worked. I would like to know why using a constructor throws an error.

     

    Monday, September 19, 2011 8:24 PM
  • Hi pretzelb,

    >>Do NOT place a constructor in this call otherwise the Linq query will have an error.

    I think this link is help: http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/49a23b65-cdf8-4f58-b4a0-4effbe10c1ae/

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, September 20, 2011 8:52 AM
    Moderator