MVC C# Combine data models in View

Question

User1391845369 posted

I have 3 data Models that were auto created by PetaPoco.

 public partial class DbTable_PatientCareReportMasterFailureList : NemsisV3_MetaData.Record<DbTable_PatientCareReportMasterFailureList>  
    {
		[Column] public long Id { get; set; }
		[Column] public Guid RequestHandle { get; set; }
		[Column("dAgency.01")] public string dAgency_01 { get; set; }
		[Column("eRecord.01")] public string eRecord_01 { get; set; }
		[Column] public int StatusCode { get; set; }
	}

public partial class DbTable_SubmitDataLog : NemsisV3_MetaData.Record<DbTable_SubmitDataLog>  
    {
		[Column] public long Id { get; set; }
		[Column] public Guid RequestUserId { get; set; }
		[Column] public Guid RequestOrganizationId { get; set; }
		[Column] public DateTime RequestTimestamp { get; set; }
		[Column] public int RequestDataSchema { get; set; }
		[Column] public string RequestSchemaVersion { get; set; }
		[Column] public string RequestXmlPayload { get; set; }
		[Column] public string RequestAdditionalInfo { get; set; }
		[Column] public int ResponseStatusCode { get; set; }
		[Column] public Guid ResponseRequestHandle { get; set; }
		[Column] public string ResponseXml { get; set; }
	}

public partial class DbTable_PatientCareReportMasterList : NemsisV3_MetaData.Record<DbTable_PatientCareReportMasterList>  
    {
		[Column] public Guid DocumentId { get; set; }
		[Column("dAgency.01")] public string dAgency_01 { get; set; }
		[Column("eRecord.01")] public string eRecord_01 { get; set; }
		[Column] public int StatusCode { get; set; }
	}

How do I combine the three data models in my controller with one Query result?

This is my query:

 .Append("SELECT")
            .Append("sdl.[Id],pcrmf.[RequestHandle],pcrmf.[dAgency.01],pcrmf.[eRecord.01],pcrmf.[StatusCode],")
            .Append("sdl.[RequestTimestamp]")
            .Append("FROM[database].[dbo].[PatientCareReportMasterFailureList] pcrmf")
            .Append("Join [database].[dbo].[SubmitDataLog] sdl on sdl.[ResponseRequestHandle] = pcrmf.[RequestHandle]")
            .Append("Left Join [database].[dbo].[PatientCareReportMasterList] pcrml on pcrml.[eRecord.01] = pcrmf.[eRecord.01]")
            .Append("where pcrmf.[eRecord.01] =   @0", pcr);

I need to return pcrmf.[RequestHandle],pcrmf.[dAgency.01],pcrmf.[eRecord.01],pcrmf.[StatusCode]  from table DbTable_PatientCareReportMasterList

and sdl.[Id], sdl.[RequestTimestamp] from Table DbTable_SubmitDataLog 

I tested my query and it returns what I need.  But I can not get everything to return in my view. 

How would I do this in my controller?  I can get one set of data but not there other. 

Answer 1

User61956409 posted

Hi steven0608,

ViewModel can be used to shape multiple entities from one or more models into a single object. which would help pass and display data from more than one models to your View page.

The following blog explained how to manage data with ViewModel in ASP.NET MVC application, please refer to it.

https://weblogs.asp.net/dotnetstories/viewmodels

With Regards,

Fei Han

Answer 2

User1391845369 posted

I understand the viewModel and how to combine my data but how do I join the two tables with the same object collection for the same record?  I am not using  Entity Framework.  

Answer 3

User475983607 posted

The ViewModel pattern for related records is a member property that is a collection.

    public class MainRecord
    {
        public int Id { get; set; }
        public string SomeColumn { get; set; }
        public List<RelatedRecord> RelatedRecord { get; set; }
    }

    public class RelatedRecord
    {
        public int Id { get; set; }
        public string SomeColumn { get; set; }
    }

Answer 4

User1391845369 posted

So currently in my controller I make a query call to this Database Model table.  

Controller Code:

var query = Sql.Builder  
            .Append("SELECT")
            .Append("sdl.[Id],pcrmf.[RequestHandle],pcrmf.[dAgency.01],pcrmf.[eRecord.01],pcrmf.[StatusCode],")
            .Append("sdl.[RequestTimestamp]")
            .Append("FROM[database].[dbo].[PatientCareReportMasterFailureList] pcrmf")
            .Append("Join [database].[dbo].[SubmitDataLog] sdl on sdl.[ResponseRequestHandle] = pcrmf.[RequestHandle]")
            .Append("Left Join [database].[dbo].[PatientCareReportMasterList] pcrml on pcrml.[eRecord.01] = pcrmf.[eRecord.01]")
            .Append("where pcrmf.[eRecord.01] =   @0", pcr);
            

            try
            {
                using (var db = new database())
                {
                    var results = db.Query<DbTable_PatientCareReportMasterFailureList>(query) ;
                    var pcrfailedList = results.Select(failedList1 => new SubmissionHistoryInfoModel
                    {
                        Id = failedList1.Id,
                        StatusCode = failedList1.StatusCode,
                        RequestHandle = failedList1.RequestHandle,
                        eRecord_01 = failedList1.eRecord_01,
                        dAgency_01 = failedList1.dAgency_01,
                    }).ToList();


                    //results2.Select(r => r.RequestTimestamp).FirstOrDefault();
                    

                    return pcrfailedList;
                                                                          
                     
                }
            }
            catch (Exception ex)
            {
                Helpers.PostSupport(Program.SubmissionSite, $"Failed to get PCR history reports: ", ex.ToLoggingString());
                throw;           }
        }

[TableName("PatientCareReportMasterFailureList")]
	[PrimaryKey("Id")]
	[ExplicitColumns]
    public partial class DbTable_PatientCareReportMasterFailureList : NemsisV3_MetaData.Record<DbTable_PatientCareReportMasterFailureList>  
    {
		[Column] public long Id { get; set; }
		[Column] public Guid RequestHandle { get; set; }
		[Column("dAgency.01")] public string dAgency_01 { get; set; }
		[Column("eRecord.01")] public string eRecord_01 { get; set; }
		[Column] public int StatusCode { get; set; }
	}

I am able to return everything from my query into pcrfailedList except for 

sdl.[RequestTimestamp]

That column is located in a different table model called DbTable_SubmitDataLog

In my controller how can I combine the two data tables into one object?   

  public class SubmissionHistoryInfoModel
{
public long Id { get; set; }
public int StatusCode { get; set; }
public Guid RequestHandle { get; set; }
public string eRecord_01 { get; set; }
public string dAgency_01 { get; set; }
public DateTime SubmissionTime { get; set; }
}

 

Answer 5

User475983607 posted

steven0608

That column is located in a different table model called DbTable_SubmitDataLog

Keep in mind that forum members can only see what you share.  The shared code shows sdl.[RequestTimestamp] comes from [NEMSISv3_MetaData].[dbo].[SubmitDataLog] sdl. 

Have you tried running the script using Management Studio to verify the script returns the RequestTimestamp?  We cannot verify this information on the forum.

steven0608

In my controller how can I combine the two data tables into one object?   

A JOIN combines two tables.  However, there needs to be a foreign key relationship.  You have not provided the table schema or sample data.  There's not much forum members can do other than guess. 

Answer 6

User1391845369 posted

Thank you, I thought I removed all that before I pasted it. 

Yes I have all my data, I have it in 2 different objects but need to combine the objects.