My drop down list data are being passed to the controller using ajax helper in the view and in the controller, the partial view is returned using those data. When I run the application to see the partial view, it throws an error:
An exception of type 'System.Data.Entity.Core.EntityCommandExecutionException' occurred in EntityFramework.SqlServer.dll but was not handled in user code
However, when I debug it slowly, partial view is correctly shown in the page. I assume that since the partial view is correctly shown as I intended in debug mode, I probably need to delay the response for ajax? Is this the right approach? Any ideas?
View:
@using (Ajax.BeginForm("GetEmployee", "Employee", new AjaxOptions
{
HttpMethod = "POST",
UpdateTargetId = "showResult"
}))
{
@Html.AntiForgeryToken()
<div class="form-horizontal">
<div class="form-group">
<label class="control-label col-md-2" for="CountryId">Country</label>
<div class="col-md-10">
@Html.DropDownList("CountryId", null, "-- Select Country --", new { @class = "form-control", @onchange = "FillCity()" })
</div>
</div>
<div class="form-group">
<label class="control-label col-md-2" for="City">City</label>
<div class="col-md-10">
@Html.DropDownListFor(m => m.City, new SelectList(Enumerable.Empty<SelectListItem>(), "CityId", "CityName"), "-- Select City --", new { @class = "form-control" })
</div>
</div>
<div class="form-group">
<div class="col-md-offset-2 col-md-10">
<input type="submit" value="Search" class="btn btn-default" />
</div>
</div>
</div>
}
<div id="showResult"></div>
@section Scripts {
@Scripts.Render("~/bundles/jqueryval")
<script src="~/Scripts/jquery.unobtrusive-ajax.min.js"></script>
<script>
function FillCity() {
var countryId = $('#CountryId').val();
$.ajax({
url: '/Posting/FillCity',
type: "GET",
dataType: "JSON",
data: { country: countryId },
success: function (cities) {
$("#City").html(""); // clear before appending new list
$("#City").append($('<option>-- Select City --</option>'))
$.each(cities, function (i, city) {
$("#City").append($('<option></option>').val(city.CityId).html(city.CityName));
});
}
});
}
</script>
}
Controller:
[HttpPost]
public ActionResult GetEmployee(int CountryId, int city)
{
var model = db.Employees
.Where(x => x.Country.CountryId == CountryId && x.City.CityId == city);
return PartialView("PartialEmployee", model);
}
PartialView:
@model IEnumerable<PokeGoTradeModel.Models.Employee>
<table class="table">
<tr>
<th>Country</th>
<th>City</th>
</tr>
@foreach (var item in Model)
{
<tr>
<td>@Html.DisplayFor(modelItem => item.Country.CountryName)</td>
<td>@Html.DisplayFor(modelItem => item.City.CityName)</td>
</tr>
}
</table>
Here is my inner exception stack trace:
at System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command)
at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)
at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c)
at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
I figured it out. It was all because of lazy-loading. So my employee model has navigation properties of Country and City. In my Actionresult GetEmployee method, I needed to add more lines that includes navigation properties for employee model. So for example,
var model = db.employees.Include(x=>x.Country)
.Include(x=>x.City)
.Where(x=>x.Country.CountryId=countryId && x.City.CityId=cityId);
Hope it can help others if you have similar issues when using ajax for partial view. If you used virtual property in your model, for example:
public virtual Country Country { get; set; }
make sure you use Include in your query.