How to add data of one table to another table using Id and display it using .net api

Question

I have 2 tables in my db. One is country table and other is state table.

Country table has Id, name ,states[]. State table has Id, stateName, countryId.

I need to fetch a country details based on Id using .net core.

Whenever I use get operation for country using country Id - I should get the Id, name, and all state details with that country Id in an array format.

Ex. I am fetching country with Id = 1 and it has 2 states then, output should be,

Id: 1,
Name: abc,
States: [
{
Id:1,
stateName: st1,
countryId: 1
},
{
Id:2,
stateName: st2,
countryId:1
}]

Do I need to use join? Or something else. What code I need to write in DAL to get output like this? Please help!! I am new to .Net

Answer 1

You have two ways to fetch data.1. EF and 2.adoNet

1.EF

You can use EF

First you must add Classes (two classes) ,a class is country and other class is status ,because country has a lot of status you must use "ICollection"

Classes

  public class Country
    {
        public int Id { set; get; }
        public string name { set; get; }
        public virtual ICollection<states> states { set; get; }

    }
    public class states
    {
        public int Id { set; get; }
        public string stateName { set; get; }
        public int countryId { set; get; }
        public virtual Country country { set; get; }

    }

This is Code in DbContext(must add classes)

public DbSet<Country> Country { get; set; }
public DbSet<states> states { get; set; }

This is Code to insert Data(Country,Status) that Countryid after savechange It takes a value

  var Country = new Country();
            Country.name = "test2";
            Country.states = new Collection<states>();
           
            var state = new states();
            state.stateName = "st1";
            Country.states.Add(state);
            state = new states();
            state.stateName = "st2";
           
            Country.states.Add(state);
            context.Country.Add(Country);
            context.SaveChanges();

This is Code to fetch Data(Country,Status) that use Include for fetch states with Country

var d = context.Country.Include(d => d.states).FirstOrDefault(d=>d.Id==2);

2.with Ado.net

DataBaseCommon _GetData = new DataBaseCommon();
var _list = _GetData.GetData(ConntionStringSource);
 var _result = _GetData.getCountry(_list);

  public class DataBaseCommon
    {
        public DataTable GetData(string connectionString)
        {
            DataTable dtPerson = new DataTable();

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                var _SqlCommand = "SELECT  b.*,a.stateName,a.countryId\r\n  FROM [TestDB7].[dbo].[Country] b\r\n  inner join [TestDB7].[dbo].[states] a on a.countryId=b.Id";
                SqlCommand objSqlCommand = new SqlCommand(_SqlCommand, con);
                SqlDataAdapter objSqlDataAdapter = new SqlDataAdapter(objSqlCommand);
                try
                {
                    objSqlDataAdapter.Fill(dtPerson);

                }
                catch (Exception ex)
                {
                    con.Close();
                }
            }

            return dtPerson;

        }

        public  List<Country> getCountry( DataTable table)
        {


            var _listCountry =
               from p in table.AsEnumerable()
               group p by p.Field<int>("Id") into g
               select new Country
               {
                   Id = g.Key,
                   name = g.Select(i => i.Field<string>("Name")).FirstOrDefault(),
                   states = table.AsEnumerable()
                           .Where(row => g.Any(p => g.Key == row.Field<int>("CountryId")))
                           .Select(d => new states
                           {
                               stateName = d.Field<string>("stateName"),
                               countryId = d.Field<int>("countryId")

                           }).ToList()
               };




            return _listCountry.ToList();

        }


    }