VB.Net Entity Framework

Question

How to join two entities and retrieve columns from both entities as a new model list and use for each on new collection in VB.Net Entity Framework 6. any example will be helpful to learn

Answer 1

Hello,

• This code is Entity Framework 6, database type doesn't matter so long as there is a data provider so it will work with SQL-Server, Oracle, SQLite etc.
• All code is code first with existing database rather than using .edmx yet that should not matter.
• In the code samples below, I only show read operations, for add/edit/remove these code samples will work with a live DbContext or detached DbContext where how you present data will dictate how to work with the projections used.

Full source
https://github.com/karenpayneoregon/EntityFrameworkSolutionStructureWindowsForms_VisualBasic

The following code, Customer is the main entity with contact type, contact and country.

Imports NorthWind.Data.Contexts
Namespace NorthWindOperations
    Public Class Operations
        Public Function CustomerJoined() As List(Of CustomerEntity)

            Using context = New NorthWindContext()
                Return (
                        From customer In context.Customers
                        Join contactType In context.ContactTypes On customer.ContactTypeIdentifier _
                            Equals contactType.ContactTypeIdentifier
                        Join contact In context.Contacts On customer.ContactIdentifier _
                            Equals contact.ContactIdentifier
                        Join country In context.Countries On customer.CountryIdentfier _
                            Equals country.Id
                        Select New CustomerEntity With
                            {
                                .CustomerIdentifier = customer.CustomerIdentifier,
                                .CompanyName = customer.CompanyName,
                                .ContactIdentifier = customer.ContactIdentifier,
                                .FirstName = contact.FirstName,
                                .LastName = contact.LastName,
                                .ContactTypeIdentifier = contactType.ContactTypeIdentifier,
                                .ContactTitle = contactType.ContactTitle,
                                .Address = customer.Street,
                                .City = customer.City,
                                .PostalCode = customer.PostalCode,
                                .CountryIdentifier = customer.CountryIdentfier,
                                .CountyName = country.CountryName}
                    ).ToList()

            End Using
        End Function
    End Class
End Namespace

Class for above return type

Note for each table I include the primary key so if needed updates can be done to the database tables.

Namespace NorthWindOperations
    Public Class CustomerEntity
        Public Property CustomerIdentifier As Integer
        Public Property CompanyName As String
        Public Property ContactTitle As String
        Public Property ContactIdentifier As Integer?
        Public Property ContactTypeIdentifier As Integer
        Public Property FirstName As String
        Public Property LastName As String
        Public Property PostalCode As String
        Public Property CountryIdentifier As Integer?
        Public Property CountyName As String
        Public Property Address As String
        Public Property City As String

        Public Overrides Function ToString() As String
            Return $"{CustomerIdentifier} {CompanyName}"
        End Function
    End Class
End NameSpace

Off topic

If Entity Framework Core was being used see the following GitHub repository.