LINQ to SQL INSERT Failing

Question

I am experiencing a very frustrating issue when trying to insert a new record using LINQ to SQL. If I step through this code sometimes it inserts the new record but most of the time it doesn't. When it fails I seeing the following error.

Cannot insert the value NULL into column 'Name', table 'EquipmentManufacturer'; column does not allow nulls. INSERT fails. The statement has been terminated.

This error is complaining about the 'Name' field being null but that should not be the case. When I debug and step through this collection["Name"] has the value I entered on the form.

Here is the table create statement.

CREATE TABLE [EquipmentManufacturer] (
  [EquipmentManufacturerID] [int] IDENTITY(1,1) NOT NULL,
  [Name] [nvarchar](50) NOT NULL,

 CONSTRAINT [PK_EquipmentManufacturer] PRIMARY KEY CLUSTERED 
 (
    [EquipmentManufacturerID] ASC
 ) ON [PRIMARY]
) ON [PRIMARY]

Here is the ASP.NET MVC Controller and Create Action where I am trying to add a new record.

public partial class EquipmentManufacturerController : Controller
{
  private IRepository<EquipmentManufacturer> reposManu;

  // POST: /EquipmentManufacturer/Create
  [AcceptVerbs(HttpVerbs.Post)]
  public virtual ActionResult Create(FormCollection collection)
  {
    EquipmentManufacturer entity = reposManu.New();
    try
    {
      //HACK: Something screwy is going on here the entity oject doesn't always get updated correctly
      //UpdateModel(entity);

      entity.Name = collection["Name"];
      reposManu.Insert(entity);
      reposManu.SubmitChanges();

      return RedirectToAction("Details", new { id = entity.EquipmentManufacturerID });
    }
    catch (RulesException ex)
    {
      ex.AddModelStateErrors(ModelState, "EquipmentManufacturer");
      return ModelState.IsValid ? RedirectToAction("Create")
        : (ActionResult)View();
    }
  }
}

Here is the Create.aspx view.

<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">

    <h2>Create</h2>

    <%= Html.ValidationSummary("Create was unsuccessful. Please correct the errors and try again.") %>

    <% using (Html.BeginForm()) {%>

        <fieldset>
            <legend>Fields</legend>
            <p>
                <label for="Name">Name:</label>
                <%= Html.TextBox("Name") %>
                <%= Html.ValidationMessage("Name") %>
            </p>
            <p>
                <input type="submit" value="Create" />
            </p>
        </fieldset>

    <% } %>
    <%= Html.ClientSideValidation<EquipmentManufacturer>() %>

    <div>
        <%=Html.ActionLink("Back to List", "Index") %>
    </div>

</asp:Content>

Here is the Repository implementation I am using.

public class Repository<T> : IRepository<T> where T : class
{
  public IDataContext DC { get; set; }

  public Repository(IDataContext dataContext)
  {
      DC = dataContext;
  }

  /// <summary>
  /// Return all instances of type T.
  /// </summary>
  /// <returns></returns>
  public IEnumerable<T> All()
  {
      return GetTable;
  }

  /// <summary>
  /// Return all instances of type T that match the expression exp.
  /// </summary>
  /// <param name="exp"></param>
  /// <returns></returns>
  public IQueryable<T> Find(Expression<Func<T, bool>> exp)
  {
      return GetTable.Where<T>(exp);
  }

  /// <summary>See IRepository</summary>
  /// <param name="exp"></param>
  /// <returns></returns>
  public T Single(Expression<Func<T, bool>> exp)
  {
      return GetTable.SingleOrDefault(exp);
  }

  /// <summary>See IRepository</summary>
  /// <param name="exp"></param>
  /// <returns></returns>
  public T First(Expression<Func<T, bool>> exp)
  {
      return GetTable.First(exp);
  }

  /// <summary>See IRepository</summary>
  /// <param name="entity"></param>
  public virtual void Delete(T entity)
  {
      DC.Context.GetTable<T>().DeleteOnSubmit(entity);
  }

  /// <summary>
  /// Create a new instance of type T.
  /// </summary>
  /// <returns></returns>
  public virtual T New()
  {
      T entity = Activator.CreateInstance<T>();
      GetTable.InsertOnSubmit(entity);
      return entity;
  }

  /// <summary>
  /// Adds an insance T.
  /// </summary>
  /// <returns></returns>
  public virtual void Insert(T entity)
  {
      GetTable.InsertOnSubmit(entity);
  }

  /// <summary>
  /// Update entity.
  /// </summary>
  /// <returns></returns>
  public virtual void Update(T entity)
  {
      DC.Context.Refresh(System.Data.Linq.RefreshMode.KeepCurrentValues, entity);
  }

  /// <summary>See IRepository</summary>
  public void SubmitChanges()
  {
      DC.SubmitChanges();
  }

  private string PrimaryKeyName
  {
      get { return TableMetadata.RowType.IdentityMembers[0].Name; }
  }

  private System.Data.Linq.Table<T> GetTable
  {
      get { return DC.Context.GetTable<T>(); }
  }

  private System.Data.Linq.Mapping.MetaTable TableMetadata
  {
      get { return DC.Context.Mapping.GetTable(typeof(T)); }
  }

  private System.Data.Linq.Mapping.MetaType ClassMetadata
  {
      get { return DC.Context.Mapping.GetMetaType(typeof(T)); }
  }
}

Answer 1

In my experience this sort of problem generally comes from errors in the mapping configuration.

Some suggestions for finding an answer:

• Use SQL Profiler to trace the INSERT statements, which might give you more clues.

• Double-check the mapping for the EquipmentManufacturer class, Name might not be mapped correctly. Include that information in your question as it provides a better picture of the problem.

Answer 2

I see that you are not using the 'auto-bind' (or whatever you call it, where you accept the entity you want bound as the action method parameter) but are instead (A) newing the object and (B) setting its "Name" property from the formCollection manually. Perhaps I do not see where the "EquipmentManufacturerID" is being bound?

Is the NULL failure on this EquipmentManufacturerID property?

Answer 3

Is it because you are calling .InsertOnSubmit(entity) twice?

You call it once in New()

public virtual T New()
{
    T entity = Activator.CreateInstance<T>();
    GetTable.InsertOnSubmit(entity);
    return entity;
}

And then again in .Insert()

public virtual void Insert(T entity)
{
    GetTable.InsertOnSubmit(entity);
}

Personally I would remove the GetTable.InsertOnSubmit(entity) from the New() method.

Reason being that I think it would be better for the user of the repository to specifically insert the entity rather than it automatically get set for insertion every single time they create a new one.

HTHs,
Charles