Aleksander Dudek Aleksander Dudek - 3 months ago 17
MySQL Question

ASP.NET - Query'ing table with string ID and 2 keys

I'm currently stuck on a piece of code that returns exception:

CONTROLLER:

public ActionResult Edit (string userId)
{
AspNetUserRoles personRole;
if (userId == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}

///error happens here during execution of Find() on table
personRole = tbls.AspNetUserRoles.Find(userId);


if (personRole == null)
{
return HttpNotFound();
}
return View(personRole);
}


VIEW (through which I pass string ID):

@model IEnumerable<WebApplication1.Models.MyViewModel>



<p>
@Html.ActionLink("Create New", "Create")
</p>
<table class="table">
<tr>

<th>
@Html.DisplayNameFor(model => model.Id)
</th>

<th>
@Html.DisplayNameFor(model => model.Name)
</th>

<th>
@Html.DisplayNameFor(model => model.RoleId)
</th>
<th></th>
</tr>

@foreach (var item in Model) {
<tr>

<td>
@Html.DisplayFor(modelItem => item.Id)
</td>

<td>
@Html.DisplayFor(modelItem => item.Name)
</td>
<td>
@Html.DisplayFor(modelItem => item.RoleId)
</td>
<td>
@*I added null, so for the sake of routing*@
@Html.ActionLink("Edit", "Edit", "Home", new { id = item.Id }, null) |



@Html.ActionLink("Details", "Details", new { id = item.Id }) |
@Html.ActionLink("Delete", "Delete", new { id = item.Id })
</td>
</tr>
}

</table>


MODELS:

public class MyViewModel
{

public string Id { get; set; }
public string Name { get; set; }
public string RoleId { get; set; }
}
public partial class AspNetUserRoles
{
[Key]
[Column(Order = 0)]
public string UserId { get; set; }

[Key]
[Column(Order = 1)]
public string RoleId { get; set; }

public virtual AspNetUsers AspNetUsers { get; set; }
}
}

public partial class AspNetUsers
{
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
public AspNetUsers()
{
AspNetUserRoles = new HashSet<AspNetUserRoles>();
}

public string Id { get; set; }

[StringLength(256)]
public string Email { get; set; }

public bool EmailConfirmed { get; set; }

public string PasswordHash { get; set; }

public string SecurityStamp { get; set; }

public string PhoneNumber { get; set; }

public bool PhoneNumberConfirmed { get; set; }

public bool TwoFactorEnabled { get; set; }

public DateTime? LockoutEndDateUtc { get; set; }

public bool LockoutEnabled { get; set; }

public int AccessFailedCount { get; set; }

[Required]
[StringLength(256)]
public string UserName { get; set; }

[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<AspNetUserRoles> AspNetUserRoles { get; set; }
}
}


I wonder what could be wrong here. I can loop through joined AspNetUsers and AspNetUserRoles on the string ID and it gives all records (typed against MyViewModel). Then in the View I want to be able to get to the particular record typed against AspNetUserRoles and be able to edit it, though it's irrelevant now since I can't get the right record while querying the database.

My thoughts were that it's either the problem with routing using @Html.ActionLink("Edit", "Edit", "Home", new { id = item.Id }, null) (checked what URI looks like and it's correct meaning controller/action/parameter is ok, also during debbuging string Id wasn't null) or rather problem with Find() method while it takes string argument or the fact that I'm querying table which has 2 keys (though it's just an idea nothing solid).

Any lead could be usefull.

Thanks!

Answer

Find method finds entities using primary keys. As you have composite primary key on AspNetUserRoles model you have to provide both keys to find entity:

personRole = tbls.AspNetUserRoles.Find(userId, roleId);

roleId is the id of role you want to find. If you have only one role associated with user, then you can query and get it as:

personRole = tbls.AspNetUserRoles.Single(m => m.UserId == userId);

But keep in mind that if user has more than one role above code will throw exception.

Read more about finding entities here.