I am reading from my database performing an inner join clause in c#. Whenever it hits the line x.Id = (int)reader["Id"]; it says "System.IndexOutOfRangeException: Id". I know that error displays when it does not exist in the db. But on ssms when I run the cmd

select ItemName
from MainStore
inner join SecondStore
on MainStore.Id = SecondStore.Id

which returns

ItemName

Candy

Marshmallow

In c# I do,

        List<MainStore> storeList = new();

        SqlConnection connection = new();

        using (connection = new SqlConnection(_connectionString))
        using (SqlCommand command = new SqlCommand("select ItemName from MainStore inner join SecondStore on MainStore.Id = SecondStore.Id", connection))
        {
            connection.Open();

            using SqlDataReader reader = command.ExecuteReader();


            while (reader.Read())
            {
                var x = new MainStore();

                x.ItemName = reader["ItemName"].ToString();
                x.Id = (int)reader["Id"];
                x.SecondStore.Id = (int)reader["Id"];

                storeList.Add(x);
            }

        }

both lines x.Id and x.SecondStore.Id give me the same error. Why is that?

public class MainStore
{
    public int Id { get; set; }
    public string ItemName { get; set; }
    public SecondStore SecondStore { get; set; }
}

public class SecondStore
{
    public int SecondStoreId { get; set; }
    public int Id { get; set; }
}

CodePudding user response：

Change your query

"select ItemName, MainStore.Id as MainStoreId, SecondStore.Id as SecondStoreId from MainStore inner join SecondStore on MainStore.Id = SecondStore.Id", connection))

But I think you have a bug in your query relations. Pls post the MainStore and the SecondStore classes.

and code

 x.ItemName = reader["ItemName"].ToString();
 x.Id = (int)reader["MainStoreId"];

SecondStore.Id is the same as MainStoreId. You will have to create then SecondStore object too, but I can't see why do you need it