I'm finding it difficult to return a list of users who have in common the same city (IdCity) as the user logged into the system. Instead of returning the various users it creates redundancy and returns the same user logged in. Btw I changed those models properties because it was written in Portuguese...
User Model
public partial class TblUser
{
[Key]
public int UserId { get; set; }
public string Name { get; set; }
[NotMapped]
public string City { get; set; }
public int IdCity { get; set; }
public virtual TblCity TblCity { get; set; }
}
City Model
public partial class TblCity
{
[Key]
public int IdCity { get; set; }
public string City { get; set; }
}
Procedure
ALTER PROC [dbo].[SP_Citizen]
@IdCity int
AS
BEGIN
Select Name,t1.City from TblUser t1
join TblCity t2 on t2.IdCity = t1.IdCity
where t1.IdCity= @IdCity
END
Action
public ActionResult List()
{
List<TblUser> _users = new List<TblUser>();
using (SqlConnection con = new SqlConnection(Conexao.ConnectionString))
{
con.Open();
string sql = "SP_Citizen";
using (SqlCommand cmd = new SqlCommand(sql, con))
{
var logged = User.Identity.Name;
int userId = db.TblUser.Where(x => x.Name== logged).FirstOrDefault().IdCity;
cmd.CommandType = CommandType.StoredProcedure;
TblUser citizen= new TblUser();
cmd.Parameters.AddWithValue("@IdCity", userId);
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr != null)
{
while (dr.Read())
{
citizen.Name = dr["Name"].ToString();
citizen.City= dr["City"].ToString();
_users .Add(citizen);
}
}
}
}
}
return View(_usuarios);
}
how to solve this?
