CODEWITHSUNDEEP
c#entity-framework
Samuel Pereira
Samuel Pereira

Reputation: 277

EF stored procedure mapping to complex ViewModel

Is it possible to have a ViewModel not mapped to database and have the result mapped to a complex ViewModel?

I need to execute a stored procedure that would return only relevant data to my view, and executing it as a LINQ query with the actual mapped models has a lack of performance.

Let's say my classes are something like:

public class Class1Vm
{
    /* some properties */
    public Class2Vm Child1 {get;set;}
}

public class Class2Vm
{
    /* some properties */
    public Class3Vm Child2 {get;set;}
}

/* and so on */

And the stored procedure is something like this

SELECT   c1.Property1,
         c1.Property2,
         c2.Property1 AS [Child1.Property1],
         c2.Property2 AS [Child1.Property2],
         c3.Property1 AS [Child1.Child2.Property1],
         c3.Property2 AS [Child1.Child2.Property2],
FROM     Class1 c1
         JOIN Class2 c2 ON c2.Id = c1.Id
         JOIN Class3 c3 ON c3.Id = c2.Id

All properties of Class1 are automatically mapped successfully by executing:

var query = db.Database.SqlQuery<Class1Vm>("EXEC [MyStoredProcedure]").ToList();

I tried to put "[Child1.Property1]" as an attempt to EF map to my viewmodel automatically, but it doesn't.

Thanks for help.

This code is only an example of what I need to happen when execute the stored procedure, my viewmodel are far more complex than this.

Upvotes: 1

Views: 825

Answers (1)

KMeda
KMeda

Reputation: 473

AFAIK there is no means to map a stored procedure output to nested complex types without creating a flat table structure and then re-mapping it in code.

A word of caution against going to the flat table structure if you have outer joins is that there will be a lot of redundant rows and data transfer between the application and SQL Server.

In such cases, you can use Multiple Result Sets feature of Entity Framework. There is no EDMX Designer support but it can certainly be achieved by hand editing the EDMX file. You can find a description here

If you have more than 2 result sets to read, you'd need to cache the result object from the GetNextResult call and use that to call GetNextResult again.

Upvotes: 2

Related Questions