[RESOLVED] Use LINQ to entities to populate List View

[wild_bill]

Here's what I tried, but I get an error at run time on the AddRange line that states "Only parameterless constructors and initializers are supported in LINQ to Entities."

Private Sub PopulateServiceTypeCodes()

    ServiceTypeCodeListView.Items.Clear()

    Using context As New BLUEEXCHANGEEntities

        Dim query = From stc In context.ServiceTypeCodes
                    Join sbl In context.ServiceBenefitLookups
                    On sbl.ServiceTypeCodeId Equals stc.ServiceTypeCodeId
                    Select New ListViewItem(New String() {stc.ServiceTypeCodeId, stc.ServiceTypeCodeDescription})

        ServiceTypeCodeListView.Items.AddRange(query.ToArray)

    End Using

End Sub

[ForumAccount]

The idea of supporting only parameter-less constructor was one of the hard decisions we made as a product team. The main idea with this approach was we shouldn't open up new surface area in LINQ over Entities that is not supported by EDM. EDM in general doesn't allow you to construct in random objects (using the NEW constructor in eSQL). To make it consistent with the whole stack, we decided to implement it like wise. Limiting the constructions only to:

- Parameter less constructors

- Anonymous types (as it’s the only way to do multi-project in LINQ over Entities)



Thanks for the feedback, let me know if you still have concerns. I will be glad to hear.

-Sushil Chordia

<source>

What does this mean for you? It means you cannot create a ListViewItem like that. This might work for you, but I can't test it:

Private Sub PopulateServiceTypeCodes()

    ServiceTypeCodeListView.Items.Clear()

    Using context As New BLUEEXCHANGEEntities

        Dim query = (From stc In context.ServiceTypeCodes _
                     Join sbl In context.ServiceBenefitLookups _
                     On sbl.ServiceTypeCodeId Equals stc.ServiceTypeCodeId _
                     Select stc).AsEnumerable()
        '//if AsEnumerable() doesn't work then try either
        '//ToArray() or ToList()
        '//basically we are trying to release the hold of the EF on this query

        Dim items = From stc In query _
                    Select New ListViewItem(New String() _
                               {stc.ServiceTypeCodeId, stc.ServiceTypeCodeDescription})

        ServiceTypeCodeListView.Items.AddRange(items.ToArray())

    End Using

End Sub

I don't actually know if the syntax is correct, but I think you see what I mean.

[wild_bill]

Yeah the message is verbose, I got hung up where the exception occured. This has been frustrating the hell out of me any way, so I might be going back to the old DataSet + TSQL way.

[ForumAccount]

It's one of those "by design" things where no actual programmer ever understands why it's that way. I'd be curious to know if the AsEnumerable or ToArray or ToList calls worked though. I don't see why they wouldn't, I assume once you actually get the engine to run the query, you can do whatever you want with the results after. Speculation, however.

[jmcilhinney]

There are lots of different flavours of LINQ. There is a single LINQ syntax but the code that we write using that syntax then has to be parsed and used in the appropriate way by the specific LINQ provider. The LINQ to Entities provider does that by converting your LINQ code into T-SQL to execute on a SQL Server instance. As such, only features that can be converted to T-SQL code are supported. Your SQL Server database doesn't know what a ListViewItem is, so your LINQ to Entities query cannot use the ListViewItem class.

What you need to do is execute a LINQ to Entities query and bring the data back in a form that is supported, i.e. either one of your entity types or an anonymous type. You would call ToArray to force execution of that query on the database and create an in-memory instance of your data. You can then use a LINQ to Objects query to convert that data into ListViewItems. LINQ to Objects uses the same LINQ syntax as LINQ to Entities but it's a different underlying provider and it does know what a ListViewItem is.

[wild_bill]

Thanks for the explanation