Entity Framework Core: how to use result set of stored procedure and map it to object and object of object by maintaining async

.net-core c# entity-framework entity-framework-core sql

Question

I'm working on Entity Framework Core.

My task is to execute a stored procedure and populate the DTO based on the result of that stored procedure. I'm having difficult to map the properties of DTO class with stored procedure result especially when I've to map it object (another class type) property.

Consider the following DTO classes

public class Contact
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string EmailAddress { get; set; }
    public Company Company { get; set; }
}

public class Company
{
    public int Id { get; set; }
    public string Name { get; set; }
}

My SQL query for stored procedure is:

SELECT
    Id                  = contact.id,
    Name                = LTRIM(RTRIM(contact.name)),
    EmailAddress        = LTRIM(RTRIM(contact.email)),
    -- company information
    CustomerId          = contact.cust_id
    CustomerName        = customer.name
FROM
    contact 
INNER JOIN
    customer ON contact.cust_id = customer.cust_id

I'm using NuGet package StoredProcedureEFCore to perform execution of stored procedure.

Here is the code to call the stored procedure:

Task<Contact> contactDetails = null;

_context.LoadStoredProc("usp_GetContactDetails")
        .Exec(r =>
        {
            contactDetails = r.FirstOrDefaultAsync<Contact>();
        });

// TODO: need to populate Company object

This code work for Id, Name, EmailAddress but not for Company property as I've to go company object and populate that object properties.

Based on my research on StoredProcedureEFCore, we can do this using ToDictionaryAsync or ToLookupAsync but I'm still not able to do that.

Plus, one more thing I've to do that as asynchronously.

1
1
10/14/2018 11:00:15 AM

Popular Answer

First of all, I don't think it is possible to map relations in StoredProcedureEFCore using ToDictionaryAsync or ToLookupAsync.

But you can create your own mapping function like:

 public static async Task<Contact> MapContact(DbDataReader dataReader){
    if(await dataReader.ReadAsync() &&  dataReader.HasRows){
        Contact contact = new Contact();
        contact.Id =  dataReader.GetInt32( dataReader.GetOrdinal("Id"));
        contact.Name = dataReader.GetString(dataReader.GetOrdinal("Name"));
        contact.EmailAddress = dataReader.GetString(dataReader.GetOrdinal("EmailAddress"));
        Company company = new Company();
        company.Id = dataReader.GetInt32(dataReader.GetOrdinal("CompanyId"));
        company.Name = dataReader.GetString(dataReader.GetOrdinal("CompanyName"));
        contact.Company = company;
        return contact;
    }
    return null;
}

And path it as a parameter to your Exec call

Task<Contact> contactDetails = null;

_context.LoadStoredProc("usp_GetContactDetails")
    .Exec(r =>
    {
        contactDetails = MapContact(r)
    });
1
10/14/2018 1:09:59 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