How to fetch data from 3 linked tables using dapper

Dave Bell

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 {
City
}
City {
}

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Naimish Makwana3y ago

Accepted 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

Dave Bell3y ago
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; }

}
+2

Naimish Makwana3y ago

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