CODEWITHSUNDEEP
asp.netentity-framework-6
bob56
bob56

Reputation: 29

Entity Framework - How to return a strongly typed object containing multiple Entities?

I have three tables in SQL Server: Company, Financial and FinancialHistory.

Relationships are:

Company -> Financial (1-1)
Financial -> FinancialHistory (1-Many)

Having generated an Entity Model, I want to return one larger "Entity" that contains ALL the columns from Company and Financial and a select few from FinancialHistory so that I can maintain a sort across multiple GridViews at the front end. Basically I want to avoid returning a dynamic List type which is where i'm at currently, I want a more strongly typed return type.

Is there any feature in Entity Framework 6 that allows me to do this via the Model diagram or do I have to create my own class and use it separately to the Model? An example of the class and instructions would help.

NOTE: I flatten out the FinancialHistory data and create dynamic columns in a DataTable before assigning to GridViews.

Originally this was a Stored Procedure that used PIVOT and generated dynamic columns but I wanted to move it over to EF and use LINQ.

Upvotes: 1

Views: 224

Answers (1)

Richard
Richard

Reputation: 109015

When querying you can project the results into any type. While that type won't be updatable (you need to directly modify entity types) it works very well when querying.

var res = await (from cpy in myDbContent.Companies
                // get two newest financial results
                let fin = cpy.financials
                let finResRecent = fin.History.OrderByDescending(h => h.FinancialYear)
                let finResLast = finResRecent.FirstOrDefault()
                let finResPrev = finResRecent.Skip(1).FirstOrDefault()
                select new {
                  Company = cpy,
                  Financials = fin,
                  LastResults = finResLast,
                  PreviousResults = finresPrev
                }
               ).ToListAsync();

(Inside the query many operators – like Single – can't be used, but FirstOrDefault can)

Upvotes: 1

Related Questions