I have searched a multitude of answers and tried a plethora of ways to code this. Currently I'm getting a
System.Data.Entity.Core.EntityCommandExecutionException: 'The data reader has more than one field. Multiple fields are not valid for EDM primitive or enumeration types.'
in my partial view.
I want to learn this, so I spent the last 40 hours researching and trying different ways. I finally crying out for help.
I simply want to have a drop down box containing all this:
[Key] UserId - all user Id's taken from Identity Model
value = Users.LastName + ', ' + Users.FirstName + ' - Council: ' + CAST(Groups.CouncilNumber AS VARCHAR) + ', ' + UnitTypes.Name + ' ' + CAST(Groups.UnitNumber AS VARCHAR)
So I can assign a UserId to the customer record. The needs to be displayed so the user has enough information to select the correct UserId.
Linqer gave me this to use instead of my SQL. Even after replacing db.AspnetUsers with ApplicationUser I couldn't seem to get it working.
var query =
from Users in db.AspNetUsers
join Groups in db.Groups on new { GroupId = Convert.ToInt32(Users.GroupId) } equals new { GroupId = Groups.GroupId }
join UnitTypes in db.UnitTypes on Groups.UnitTypeId equals UnitTypes.UnitTypeId
orderby
Users.LastName,
Users.FirstName
select new {
Users.Id,
FullDescription = (Users.LastName + ", " + Users.FirstName + " - Council: " + Convert.ToString(Groups.CouncilNumber) + ", " + UnitTypes.Name + " " + Convert.ToString(Groups.UnitNumber))
};
foreach (var r in query)
Add(new AspNetUser(
r.Id, r.FullDescription))
and Here is my setup:
public class CustomerViewModel : DbContext{
public CustomerViewModel(): base("name=CustomerViewModel"){ }
[Key]
public int CustomerId { get; set; }
[Required(ErrorMessage = "Please enter your first name.")]
[StringLength(50)]
[Display(Name = "First Name")]
public string FirstName { get; set; }
[Required(ErrorMessage = "Please enter your last name.")]
[StringLength(100)]
[Display(Name = "Last Name")]
public string LastName { get; set; }
[MaxLength(128), ForeignKey("ApplicationUser")]
public string UserId { get; set; }
public SelectList UserSelectList { get; set; }
public virtual ApplicationUser ApplicationUser { get; set; }
}
}
namespace WOA.Models {
public class ApplicationUser : IdentityUser {
[Required(ErrorMessage = "Please enter your first name.")]
[StringLength(50)]
[Display(Name = "First Name")]
public string FirstName { get; set; }
[Required(ErrorMessage = "Please enter your last name.")]
[StringLength(100)]
[Display(Name = "Last Name")]
public string LastName { get; set; }
public int? GroupId { get; set; }
public int CustomerId { get; set; }
[ForeignKey("GroupId")]
public virtual Group Group { get; set; }
public async Task<ClaimsIdentity> GenerateUserIdentityAsync(UserManager<ApplicationUser> manager)
{
// Note the authenticationType must match the one defined in CookieAuthenticationOptions.AuthenticationType
var userIdentity = await manager.CreateIdentityAsync(this, DefaultAuthenticationTypes.ApplicationCookie);
// Add custom user claims here
return userIdentity;
}
}
}
namespace WOA.Models {
public class Group {
public int GroupId { get; set; }
public int CouncilNumber { get; set; }
public int UnitTypeId { get; set; }
public string UnitNumber { get; set; }
[ForeignKey("UnitTypeId")]
public virtual UnitType UnitType { get; set; }
}
}
namespace WOA.Models{
public class UnitType{
public int UnitTypeId { get; set; }
[Required] public string Name { get; set; }
}
}
namespace WOA.Controllers {
public class CustomersController : Controller {
private CustomerViewModel MapToViewModel(Models.Customer customer) {
CustomerViewModel customerVm = new CustomerViewModel() {
CustomerId = customer.CustomerId,
GroupId = customer.GroupId
};
return customerVm;
}
public ActionResult Edit(int customerId){
CustomerViewModel customerViewModel = MapToViewModel(customer);
customerViewModel.UserSelectList = GetUserGroupList(DbContext);
if (Request.IsAjaxRequest())
{
return PartialView("_CustomerEditPartial", customerViewModel);
}
return PartialView("_CustomerEditPartial", customerViewModel);
}
public SelectList GetUserGroupList(DbContext _dbContext) {
var sql = @"SELECT Users.Id as Value
, Users.LastName + ', ' + Users.FirstName + ' - Council: ' + CAST(Groups.CouncilNumber AS VARCHAR) + ', ' + UnitTypes.Name + ' ' + CAST(Groups.UnitNumber AS VARCHAR) AS Text
FROM dbo.AspNetUsers AS Users
JOIN dbo.Groups ON Users.GroupId = dbo.Groups.GroupId
JOIN dbo.UnitTypes ON dbo.Groups.UnitTypeId = dbo.UnitTypes.UnitTypeId
Order by Users.LastName, Users.FirstName ";
var kk = _dbContext.Database.SqlQuery<string>(sql);
SelectList mySl = new SelectList(kk, "Value", "Text");
return mySl;
}
}
}
@model WOA.ViewModels.CustomerViewModel
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div class="form-horizontal">
<h4>CustomerViewModel</h4>
<hr />
@Html.ValidationSummary(true, "", new { @class = "text-danger" })
@Html.HiddenFor(model => model.CustomerId)
<div class="form-group">
@Html.LabelFor(model => model.UserId, "UserId", htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.DropDownListFor(model => model.UserId, ViewData.Model.UserSelectList, "Select One", new { @class = "form-control" })
@*@Html.DropDownListFor(model => model.UserId, new SelectList(Model.UserSelectList, "Value", "Text"), htmlAttributes: new { @class = "form-control", id = "UserId" })*@
@Html.ValidationMessageFor(model => model.UserId, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.FirstName, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.FirstName, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.FirstName, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.LastName, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.LastName, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.LastName, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
<div class="col-md-offset-2 col-md-10">
<input type="submit" value="Save" class="btn btn-default" />
</div>
</div>
</div>
}
<div>
@Html.ActionLink("Back to List", "Index")
</div>
The exception message is obvious: SQL data reader (SqlQuery method) from EF returns more than single value (i.e. Users.Id and concatenated string from multiple columns).
This query returns Value & Text column aliases in result set while return type of SqlQuery supposed to be single string:
var sql = @"SELECT Users.Id as Value,
Users.LastName + ', ' + Users.FirstName + ' - Council: ' + CAST(Groups.CouncilNumber AS VARCHAR) + ', ' + UnitTypes.Name + ' ' + CAST(Groups.UnitNumber AS VARCHAR) AS Text
FROM dbo.AspNetUsers AS Users
JOIN dbo.Groups ON Users.GroupId = dbo.Groups.GroupId
JOIN dbo.UnitTypes ON dbo.Groups.UnitTypeId = dbo.UnitTypes.UnitTypeId
Order by Users.LastName, Users.FirstName ";
To resolve the issue, try creating extra class with 2 properties corresponding both text and value properties like SelectListItem has:
public class QueryResults
{
public string Value { get; set; } // change to int if Users.Id has integer value
public string Text { get; set; }
}
Then, bind query results into designated viewmodel and create List<SelectListItem> afterwards:
var kk = _dbContext.Database.SqlQuery<QueryResults>(sql).ToList();
SelectList mySl = new SelectList(kk, "Value", "Text");
A similar issue can be found here.