How to fetch data from 3 linked tables using dapper

Question

I have 3 objects and used EF to create tables as below. 1 to many between Country and State. 1 to many between State and City

Country {
List<State>
}
State {
<List> City
}
City {
}

How to I query using dapper so that I get all 3 in response

Naimish Makwana · Answer

Hi Dave, Your class structure is like below: public class Country { public int Id { get; set; } public string Name { get; set; } public List States { get; set; } } public class State { public int Id { get; set; } public int CountryId { get; set; } public string Name { get; set; } public List Cities { get; set; } } public class City { public int Id { get; set; } public int StateId { get; set; } public string Name { get; set; } } Thanks Naimish Makwana

Naimish Makwana · Answer

Hello Dave, Example SQL query that retrieves data for all countries, their states, and cities. SELECT c.*, s.*, city.* FROM Country c JOIN State s ON c.Id = s.CountryId JOIN City city ON s.Id = city.StateId Map the results to your object model using Dapper: var countries = connection.Query ( query, (c, s, city) => { c.States = c.States ?? new List (); s.Cities = s.Cities ?? new List (); var existingState = c.States.FirstOrDefault(x => x.Id == s.Id); if (existingState == null) { s.Cities.Add(city); c.States.Add(s); } else { existingState.Cities.Add(city); } return c; }, splitOn: "Id" ).Distinct().ToList(); Thanks Naimish Makwana

Dave Bell · Answer

Thanks Naimish. How to I define the Country Class? With below I do not get Cities list in response. I wish to have it under State. public class Country { public int Id { get; set; } public string Country_Name { get; set; } public List States { get; set; } }