Entity Framework dataset mapping

asp.net c# entity-framework entity-framework-6

Question

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.

1
3
3/4/2018 9:38:23 AM

Accepted Answer

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}};
2
3/4/2018 2:30:00 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow