Using System.Linq.Dynamic in VB.Net

Dynamic Linq

Was trying to using dynamic filter as I would do with datasets. Sometimes you may want to build the query at runtime. Steps are:

  1. Use NuGet to install System.Linq.Dynamic
  2. At top of class Imports System.Linq.Dynamic
  3. Use code as in example below:
Dim queryString1 = "Title = @0"
FilmsList = FilmsList.AsQueryable.Where(queryString1, "Casablanca").ToList

Dim queryString2 = "Title.Contains(@0)"
FilmsList = FilmsList.AsQueryable.Where(queryString2, "a").ToList

Dim queryString3 = "Title = ""Casablanca"""
FilmsList = FilmsList.AsQueryable.Where(queryString3).ToList
FilmDataGrid.ItemsSource = FilmsList

Error 1: Character literal must contain exactly one character

Solution 1: Use double quotes when constructing predicate in code e.g. Dim myFilter = String.Concat( “Title = “””,myTitle,””””)

End.

Advertisements

LINQ and Entity Framework with DbContext – Convert Anonymous type to Strongly Typed – Bind to ListBox

[ Personally I have a sample of this in EFSample1.sln ]
To convert anonymous type to strongly typed, you can add a class that does what you want, then Select New … with {.ID = …}
This allows you to say get data from more than one table and still have a strongly typed output. Will not be updateable.

To fill a ListBox use

Imports System.Linq ‘if namespace not already imported

See http://wp.me/p17IS4-9S

Dim result = …GetEmployees
ListBox.DataSource = result.ToList

To save on setting the DisplayMember use .ToString in the class

Public Class EmployeeList
Property ID As Integer
Property FullName As String

Overrides Function ToString() As String
Return FullName
End Function

End Class

Public Class EmployeeEF

Private context As New EFWinFormsDBContext.TestEFIEntities

Function GetEmployees() As System.Linq.IQueryable(Of EmployeeList)

Dim result = From emp As Employee In context.Employees Join con As Contact In context.Contacts On emp.EmployeeID Equals con.ID Order By con.FullName
Select New EmployeeList With {.ID = emp.EmployeeID, .FullName = con.FullName}

Return result

End Function

End Class

LINQ errors

Error:

  • ‘First’ is not a member of ‘System.Data.EnumerableRowCollection(Of System.Data.DataRow)
  • “Definition of method ‘GroupBy’ is not accessible in this context”
  • ‘ToList’ is not a member of ‘System.Linq.Iqueryable(Of T)’

Solution:
Imports System.Linq
Also ensure that the DataSet is up to date. Use Run Custom Tool on the DataSet to ensure this.
Then ensure there is a reference to “System.Data.DataSetExtensions”.

See MSDN “How to Create a LINQ Project”
http://msdn.microsoft.com/en-us/library/bb546156.aspx

See MSDN 101 LINQ Samples
http://msdn.microsoft.com/en-us/vbasic/bb688088

LINQ and Entity Framework binding to ComboBox and DataGridViewComboBox

Private context As BC.SWT.EntityModel.SOPEntities

Private Sub LoadLookupData()

Try

    Dim EntityConnectionString = Configuration.ConfigurationManager.ConnectionStrings(“SOPEntities”).ConnectionString
    context = New BC.SWT.EntityModel.SOPEntities(EntityConnectionString)

    Dim CustomerQuery As ObjectQuery(Of Customer) = context.Customers
    ‘CustomerIDComboBox.DataSource = CustomerQuery.Execute(MergeOption.AppendOnly) ‘works but includes all columns
    Dim results = From a In CustomerQuery Select a.CustomerID, a.Customer1 Order By Customer1
    CustomerIDComboBox.DataSource = results
    CustomerIDComboBox.DisplayMember = “Customer1”

    CustomerIDDataGridViewComboBoxColumn.DataSource = results.ToList
    CustomerIDDataGridViewComboBoxColumn.DisplayMember = “Customer1”
    CustomerIDDataGridViewComboBoxColumn.ValueMember = “CustomerID”
    CustomerIDDataGridViewComboBoxColumn.DataPropertyName = “CustomerID”

Catch ex As Exception
    MessageBox.Show(ex.Message, Me.Text)
End Try

End Sub

LINQ samples

To use LINQ need

Imports System.Linq
May need reference to System.Core and System.Data
‘LINQ
Dim MRU As IEnumerable(Of PadMain) = From pm In mruList OrderBy pm.PadMaterial

LINQ to Entity Framework

Private
context As BC.SWT.EntityModel.SOPEntities

Dim EntityConnectionString = Configuration.ConfigurationManager.ConnectionStrings(“SOPEntities”).ConnectionString
context = New BC.SWT.EntityModel.SOPEntities(EntityConnectionString)

Dim
CustomerQuery AsObjectQuery(OfCustomer) = context.Customers
Dim results = From a In CustomerQuery Select a.CustomerID, a.Customer1 OrderBy Customer1

Entity Framework – Connection Strings

Technorati Tags:

See:

http://msdn.microsoft.com/en-us/library/vstudio/cc716756(v=vs.100).aspx

Entity Framework connection string for Entity Model

If in a different project in the same solution and a folder in that project then.


res://Project.ProjectName/Folder.ModelName.csdl|
res://Project.ProjectName/Folder.ModelName.ssdl|
res://Project.ProjectName/Folder.ModelName.msl;

If you struggle then the following will work, but may not be as efficient because the application has to search through available references. This is all that is needed though.


res://*/

 

  <connectionStrings>
    <add name="MovieModel"
         connectionString="metadata=res://OneWay.EntityModel/Models.MovieModel.csdl|
                                    res://OneWay.EntityModel/Models.MovieModel.ssdl|
                                    res://OneWay.EntityModel/Models.MovieModel.msl;
                                    provider=System.Data.SqlClient;
                                    provider connection string=
                                      "data source=(local);
                                      initial catalog=DBName;
                                      integrated security=True;
                                      multipleactiveresultsets=True;
                                      App=EntityFramework;""
         providerName="System.Data.EntityClient" />

    <add name="OneWayEntitiesCF"
         connectionString="Data Source=(local);Initial Catalog=OneWay;Integrated Security=True"
         providerName="System.Data.SqlClient" />
  </connectionStrings>

Error 1: System.IO.FileNotFoundException: Unable to resolve assembly ‘OneWay.EntityMode’.

Solution is to correct res://[this bit]/..

Error 2: System.Data.Entity.Core.MetadataException: Unable to load the specified metadata resource.

Solution is to correct res://../[ThisBit]

Possibly you require FolderName.ModelName.

 

Other notes

If do not have Initial Catalog then will attach it to program and detach itself from SQL. Agh ! May also need User Instance = False for this.

Connection string in App.config.

Name and Model name need to match.

In edmx change Entity Set Names to have an ‘s’ on the end for each entity

Good luck !