Message: Unable to update the EntitySet – because it has a DefiningQuery and no element exist

Reason: 1) This error often occur due to the absence of primary key in the respective table, or 2)the identity column is not set to true and hence there is no option to set the auto-increment value 3) due to unupdated model file after adding the primary key to the table.

Solutions:
1) This error often occur due to the absence of primary key in the respective table where you are trying to perform an insert operation:

Check your database to see if the table has the primary key is set. If not, add the primary key by “opening the Design view >> right click and set as primary key >>  click save” or by executing the following query.
ALTER TABLE <TABLE_NAME>
ADD CONSTRAINT <CONSTRAINT_NAME> PRIMARY KEY(<COLUMN_NAME>)

Set primary key

Set primary key

2) The identity column is not set to true and hence there is no option to set the auto-increment value:

If the table contains the primary key already, go to the design view and check the column properties of the primary key. If Identity specification is set as No as shown in figure below, change it to Yes and then make sure the auto-increment option below that row is set and the value for the same is 1.

Column Properties

Column Properties

3) Due to unupdated model file after adding the primary key to the table

The third step must be followed if any change is made to the table using above steps, so that your change will be reflected in your Model.

Now, Go to the Visual Studio >> Open your solution with the project having the issue >> Open the Entity Framework Model Designer(.edmx) >> Right click the designer>> Select the model corresponding to the table to click update model from database. Now save and run the solution.

Update Model From Database

Update Model From Database

Hope this helps. Happy Learning!!!

Advertisements

Update in Entity Framework

Hi friends,

In the previous post, we saw about insert, delete and displaying records using Entity Framework. This week let us see how to update a record in Entity framework.

In order to perform update we need to, 1) Fetch the object using a LINQ, 2) Assign the values to be updated, 3) Save the changes to persist the values in db.


using (SampleDatabaseEntities context = new SampleDatabaseEntities())
{
int i = 1;
Dictionary recordDict = new Dictionary();
var CountryTable = from offices in context.Offices
join countries in context.Countries on offices.CountryId equals countries.Id
select new
{
CountryName = countries.Name,
Location = offices.Name
};
foreach (var ent in CountryTable.ToList())
{
Console.WriteLine(i + ")\t" + ent.CountryName + "-----" + ent.Location);
recordDict[i] = ent.CountryName + "-" + ent.Location;
i++;
}
Console.WriteLine("Enter the element to be updated from the choices 1 to {0}", i);
int c = Int32.Parse(Console.ReadLine());
if (c < i)
{
string country = recordDict[c].Split('-').ElementAt(0);
string location = recordDict[c].Split('-').ElementAt(1);
Office obj = (from ent in context.Offices
join con in context.Countries on ent.CountryId equals con.Id
where con.Name == country && ent.Name == location
select ent).FirstOrDefault(); //Fetch the record using the framework's class object
Console.WriteLine("Enter the new name to be updated instead of {0}", obj.Name);
obj.Name = Console.ReadLine(); //Update the record by assigning the value to the desired property
context.SaveChanges(); //Save the changes in the database
}
else
{
Console.WriteLine("Invalid Choice");
}

Whenever we perform updation inside the loops/while writing batch updation logics, it is recommended to use “context.ObjectStateManager.ChangeObjectState(obj, EntityState.Modified);” to perform updates.

int i=1;
foreach(var obj in ListElements)
{
obj.CountryID = i;
context.ObjectStateManager.ChangeObjectState(obj, EntityState.Modified); //requires namespace using System.Data.Objects; and using System.Data;
context.SaveChanges();
i++;
}

Hope it helps. Happy Learning!

Beginners Tutorial on Entity Framework : A Console Application

Let us start with the Introduction to Entity Framework. Entity Framework is one of the ADO.NET approaches, which is used for communication between the application and the database. It is a Open Source project.

Entity framework is widely used with Microsoft .NET applications, that provides database connectivity using a model class (whose object is often referred as context) that forms the abstraction of database objects as classes and properties. There are two approaches, viz Creating model from database and creating database from models. Advantage of entity framework is, it reduces the dependency of database admin and makes the most of the code to handle database operations using the Model Designer. Entity framework often make use of LINQ to query the database objects. Care should be taken while using LINQ which may affect the performance, in case of the use of improper LINQs (Language INtegrated Query) .

Now let us see how to create a console application using Entity Framework.

Steps to create the Model from Database:

  1. Create a new console application.
  2. Right click the project and click “add new item” >> Select “Data” from the left side of the wizard >> Select “ADO.NET Entity Model” >> Name your Entity Model >> click “Add”.
  3. A “Entity Data Model Wizard” will appear.
  • Select “Generate from Database” >> Click “Next”
  • Create the connection to the database. Click “Next”.
  • Select “Tables” >> Click “Finish”

Note: The steps are illustrated in below. Follow the presentation for more clarification.

Step 1:  Create a new Console Application

Step 2: Add a item “ADO.NET Entity Model to the Project”

Step 3: Run the “Entity Data Model Wizard”

After running the wizard go to Program.cs in your console application.
Follow the steps to create, delete and display the database records, as demonstrated below
1 )Add the required .dlls to the header

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.Objects;

using System.Data;

2) The code to insert record into db

In the Main method of program.cs add the following code.

using (SampleDatabaseEntities context = new SampleDatabaseEntities())

{

Console.WriteLine(“Enter a Country Name”);

string country = Console.ReadLine(); //read the country name to be stored

Country con = new Country(); //Create an object to the Country Entity

con.Name = country;

context.AddToCountries(con); //Equivalent to inset query

context.SaveChanges();

}

3) The Code to display the records from the database

using (SampleDatabaseEntities context = new SampleDatabaseEntities())

{

int i = 1;

var CountryTable = from offices in context.Offices

join countries in context.Countries on offices.CountryId equals countries.Id

select new

{

CountryName = countries.Name,

Location = offices.Name

};

foreach (var ent in CountryTable.ToList())

{

Console.WriteLine(i + “\t” + ent.CountryName + “—–” + ent.Location);

i++;

}

}

4) The Code to delete a record from database

using (SampleDatabaseEntities context = new SampleDatabaseEntities())

{

int i=1;

var locations = from loc in context.Offices

select loc.Name;

Console.WriteLine(“Enter the Location Name to be deleted from the list”);

foreach (string loc in locations)

{

Console.WriteLine(i+”)”+loc);

i++;

}

string locationDel = Console.ReadLine().ToUpper();

Office record = (from loc in context.Offices

where loc.Name == locationDel.ToUpper()

select loc).FirstOrDefault();   //Fetch the object to be deleted

context.DeleteObject(record); // the object to be deleted

context.SaveChanges();

}

Happy Learning!