Mapping data from 2 tables to a single entity

c# dapper sql

Question

I have 2 tables which have one to one relationship.The tables are as follows:

CREATE TABLE [dbo].[Provider](
[ProviderId] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[FirstName] [varchar](40) NOT NULL,
[LastName] [varchar](40) NOT NULL,
[SSN] [varchar](15) NOT NULL,
[NPI] [varchar](15) NOT NULL,
[ProviderStatus] [bit] NOT NULL)

CREATE TABLE [dbo].[ProviderDetails](
[ProviderDetailsID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Certification] [varchar](40) NOT NULL,
[Specialization] [varchar](40) NOT NULL,
[TaxonomyCode] [varchar](40) NOT NULL,
[ContactNumber] [varchar](15) NOT NULL,
[ContactEmail] [varchar](40) NOT NULL,
[ProviderId]  [int] FOREIGN KEY REFERENCES Provider(ProviderId) NOT NULL)

I have created 2 entities in C# (Mind you, I am not using EF)

 public class Provider
{
    public int ProviderID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string SSN { get; set; }
    public string NPI { get; set; }
    public ProviderDetails ProviderDetails { get; set; }
}

public class ProviderDetails
{
    public int ProviderDetailsId { get; set; }
    public string Certification { get; set; }
    public string Specialization { get; set; }
    public string TaxonomyCode { get; set; }
    public string ContactNumber { get; set; }
    public string ContactEmail { get; set; }
    public int ProviderId { get; set; }
}

I, earlier had a single entity, Provider. So, I was able to get the list of providers to display with the following code.

    public List<Provider> GetListofProviders()
    {
        List<Provider> Providers = new List<Provider>();
        using (_dbConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["DemoApplicationConnection"].ConnectionString))
        {
            try
            {
                Providers = (List<Provider>)(_dbConnection.Query<Provider>("spGetAllProviders", commandType: CommandType.StoredProcedure));
            }
            catch (Exception)
            {
                throw;
            }
        } 
        return Providers;
    }

To get data from the database, I am using the following query:

ALTER PROC [dbo].[spGetAllProviders]
AS
SELECT * FROM Provider 
INNER JOIN ProviderDetails 
ON Provider.ProviderId = ProviderDetails .ProviderId
WHERE Provider.ProviderStatus = 1

Since I have split the entities, could someone please guide me on how I would map the data from the database to the 2 entities?

Popular Answer

Now this is not the right answer.But I think this would be the way to go about.Although its not working for me.So,if someone could tell me what I am doing wrong,would be really helpful.

public List<Provider> GetListofProviders()
{
   List<Provider> Providers = new List<Provider>();
   using (_dbConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["DemoApplicationConnection"].ConnectionString))
   {
       try
       {
          Providers = _dbConnection.Query<Provider,ProviderDetails,Provider>("spGetAllProviders",null,splitOn: "ProviderId", commandType: CommandType.StoredProcedure).ToList();
       }
       catch (Exception)
       {
         throw;
       }
   } 
   return Providers;
}

Also the stored procedure is

ALTER PROC [dbo].[spGetAllProviders]
AS
SELECT Provider.*,
       ProviderDetails.ProviderId,
       ProviderDetails.ProviderDetailsID,
       ProviderDetails.Certification,
       ProviderDetails.Specialization,
       ProviderDetails.TaxonomyCode,
       ProviderDetails.ContactNumber,
       ProviderDetails.ContactEmail 
FROM Provider 
INNER JOIN ProviderDetails 
ON Provider.ProviderId = ProviderDetails .ProviderId
WHERE Provider.ProviderStatus = 1

I am getting the following error.

When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why