MVC and Entity Framework - How to select column values from multiple tables/navigation property

Question

I'm new in using MVC and LINQ to Entity. I don't know how to select column values from multiple tables with navigation property defined for each table.

Below is the structure of the tables:

Table: MasterTable
Fields: MasterID (PK), CaseNumber, Status, [OtherFields....]
Navigation Properties: DetailsTable, Clients

Table: DetailsTable
Fields: DetailsID (PK), MasterID (FK), InsuranceId (FK), DetailsStatus
Navigation Properties: MasterTable, Insurance

Table: Customers
Fields: CustID (PK), CustFName, CustFName, Address_id,Phone_id, MasterID (FK)
Navigation Properties: Order, Addresses, Phones

Table: Insurance
Fields: InsuranceId (PK), InsuranceName, Address1, Address2, ZipCode, State
Navigation Properties: Customers, DetailsTable, Addresses

Table: Addresses
Fields: Address_id (PK), Address1, Address2, ZipCode, State
Navigation Properties: State

Table: State
Fields: State_id (PK), StateAbbrev
Navigation Properties: Country

Table: Phones
Fields: Phone_id (PK), Home,Work,Mobile

How would I be able to pull the details data:
- CaseNumber, Status from the MasterTable
- InsuranceName, Address1, Address2, ZipCode, State from the Insurance table (based on the InsuranceId from the DetailsTable)
- DetailsStatus from the DetailsTable - CustFName, CustFName,Address1, Address2, ZipCode, State from the Customers table (*based on the Customers.MasterID = MasterTable.MasterID *)

Thanks.

Do you have models, and a DBContext set up for all your tables? — Ben Tidman
– Ben Tidman, Commented Jan 7, 2013 at 0:20

Ladislav Mrnka · Accepted Answer · 2013-01-07 09:23:27Z

1

That is just projection which can look like:

var query = from master in dbContext.MasterTables
            select {
                master.CaseNumber,
                master.Status,
                Details = master.DetailTables.Select(detail => new {
                    detail.DetailStatus,
                    detail.Insurance.InsuranceName, 
                    detail.Insurance.Address1,
                    detail.Insurance.Address2,
                    detail.Insurance.ZipCode,
                    detail.Insurance.State 
                },
                Customers = master.Clients.Select(customer => new {
                    customer.CustFName,
                    customer.CustLName,
                    customer.Address.Address1,
                    customer.Address.Address2,
                    customer.Address.ZipCode,
                    customer.Address.State
                }
            };

I'm not sure if it is exactly what you want but it should give you and idea how to make such queries. This query uses anonymous types for projection but you can also use your custom view models instead.

You can also make just this:

var query = dbContext.MasterTables
                     .Include(m => m.Clients)
                     .Include(m => m.DetailsTables.Select(d => d.Insurance));

and it will simply load master data with all they related clients, details and insurances. After that you can do projection in your code or use your entities directly in the view.

and it will

answered Jan 7, 2013 at 9:23

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1953372 Over a year ago

Hi Ladislav, thanks for responding to my question. I will give this a try and will let you know of the result.

AminM Over a year ago

@LadislavMrnka how can i select multiple value of DetailsTables?? example i just need 3 field of DetailsTables

Collectives™ on Stack Overflow

MVC and Entity Framework - How to select column values from multiple tables/navigation property

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related