Delete a record entity framework

Question

I am receiving an exception:

Unable to update the EntitySet 'Session' because it has a DefiningQuery and no element exists in the element to support the current operation.

while trying to delete a row from a database table. The exception appears when calling SaveChanges() for the row which exists. Below is my code:

public static Func<DC21GPDEntities, string, IQueryable<Session>> compiledDeleteQuery =
    CompiledQuery.Compile((DC21GPDEntities ctx, string userId) =>
        (from rows in ctx.Sessions
         where rows.User_ID == userId
         select rows));

[HttpPost]
public ActionResult Index(string searchItem )
{
   try
   {
      string userId =searchItem.Trim();
      string successMessage 
                = "The session for User ID: " + userId + " has been cleared in Fascor.";

      dc21gpdContext.CommandTimeout = 180;

      Models.Session session = Queries.compiledDeleteQuery(dc21gpdContext, userId).FirstOrDefault();

      if (session == null)
          successMessage = "Session for User ID: " + userId + " does noe exist";
      else
      {
          dc21gpdContext.DeleteObject(session);
          dc21gpdContext.SaveChanges();
      }

      ViewData["SuccessMessage"] = successMessage;
      return View();
  }
  catch (Exception ex)
  {
     ViewData["SuccessMessage"] = "Failed to clear session";
     return View();
  }
}

@Klaus Byskov Hoffmann: unfortunately, it does not have a primary key — 14578446

Ladislav Mrnka Ladislav Mrnka · Accepted Answer · 2012-01-18T11:24:13

If your Session table doesn't have primary key it is mapped as DefiningQuery which makes it read only. You cannot delete, insert or update records mapped to DefinedQuery unless you create stored procedures defining these operations and map them in the model.

Even after that it doesn't have to be enough. Entities are deleted by their key. You can define key in your entity model but the key must uniquely identify the record. By default EF will use all non-nullable, non-binary columns as a key. If this set of columns don't identify records uniquely you will have more problems with using the Session entity (for example delete operation will delete more than one record and SaveChanges will fail) - in such case the only option is adding a unique column to the table and use it as a key. Once you add PK to the table all these issue will be solved.

Delete a record entity framework

2 Answers