CODEWITHSUNDEEP
c#asp.netentity-frameworkentity-framework-6
Omtechguy
Omtechguy

Reputation: 3651

Entity Framework dataset mapping

All of my DAL functions are using dbContext.Database.SqlQuery to map stored procedure results in business logic objects.

My application became more complicated and I'm looking for a modern, "up to date" way to handle the following situations. I know that I can achieve this using the low-level ADO.NET component like SqlDataReader and map the result manually, but I am sure there is the best way to do so using Entity Framework 6.

To the question: with this command dbContext.Database.SqlQuery<MyClass>, I can not handle:

  1. The stored procedure that returns 2 result sets

  2. Mapping the result set to a complex datatype

Example:

public class Order
{
    public Customer customer { get; set; }
    public Items[] items { get; set; }
}

Again, I know that I can map it manually or with AutoMapper, but I'm looking for an "up to date" approach based on Entity Framework 6.

Upvotes: 3

Views: 1068

Answers (1)

Francesco B.
Francesco B.

Reputation: 3097

Yes, there's a way using Translate.

Adapted from the official documentation:

    var cmd = dbContext.Database.Connection.CreateCommand();
    cmd.CommandText = "[dbo].[GetAllCustomersAndOrders]";


    dbContext.Database.Connection.Open();
    // Run the sproc 
    var reader = cmd.ExecuteReader();

    var Customers= ((IObjectContextAdapter)dbContext)
        .ObjectContext
        .Translate<Customer>(reader, "Customers", MergeOption.AppendOnly);   

    reader.NextResult();
    var Orders = ((IObjectContextAdapter)db)
        .ObjectContext
        .Translate<Order>(reader, "Orders", MergeOption.AppendOnly);

As far as the problem of mapping

few columns from the result to a 2nd level complex type? for example: SELECT FirstName, LastName, OrderId FROM Orders I want to map it to: public class Order { public Customer customer { get; set; } public int OrderId { get; set; } }

The best would be to use a CustomerId inside your Order table, referencing a Customer table, instead of FirstName/LastName. It would be a good refactoring, normalizing the database. Otherwise you will not have a real mapping between your objects and your database, since your Order object will have a Customer property that doesn't exist in your database. In that case, you will have to create a class, e.g. NormalizedOrder

public class NormalizedOrder { 
   int OrderId { get; set; }; 
   Customer OrderCustomer { get; set; }; 
}

And then, after the code above where you retrieve all Orders, do something like

 var NormalizedOrders = Orders.Select new Order(){OrderId = e.OrderId, OrderCustomer = new Customer(){FirstName=>e.FirstName,LastName=>e.LastName}};

Upvotes: 2

Related Questions