When Joining Tables, If I pass Object I get result but null when passing ViewModel-CodePudding

at the moment I have 2 models, 1 called Customer and 1 called ContactDetails as shown below

    // Customer Model
    public class CustomerDto
    {
        public int ID { get; set; }
        public string? Name { get; set; }
    }

    // Customer Contact Details
    public class ContactdetailsDto
    {
        public int ID { get; set; }
        public string? Name { get; set; }
    }

And A ViewModel as shown below

    public class CustomersViewModel
    {
        public CustomerDto? CustomerDto { get; set; }
        public List<CustomerContactDetailsDto>? CustomerContactDetailsDto { get; set; }
    }

and I am trying to query the database to get every related customer => ContactDetails but I have a problem.

If I use the below query

List<CustomersViewModel> customerData = await Task.FromResult(dapper.GetAll<CustomersViewModel >($"SELECT * FROM [dbo].[Customer] LEFT JOIN [Customer].[ContactDetails] ON [dbo].[Customer].[CompanyID] = [Customer].[ContactDetails].[CompanyID] WHERE [dbo].[Customer].[CompanyID] = '{companyID}'", null, commandType: CommandType.Text));

the result is null for each Model

but If I change the ViewModel to Object I get a result: Name => Value, I am not sure what's wrong here!

I even tried to Select only the name from each table instead of select all but it didn't work

I have tried this query on the SQL studio and I did work, so I guess the problem here is the view model itself (some data is not matching I guess)

Hope this description is enough for you to help.

Thanks

CodePudding user response：

It Turned out to be impossible to bind this query to the view model because the query return the result as combined result so binding them to either CustomerDto or ContactDetailsDto is impossible.

to fix this issue I had to return result list from the first table (customer) and then query the second table (contact details) and use viewModel.Add(new ViewModel(){ // bind the models here}.