Entity framework

Content:
1. Introduction
2. Tools
3. Create model
4. Data model definition
5. Add navigation property manually
6. Querying data
7. Querying data - joined tables
8. Query using LINQ syntax
9. Bound data to a visual control
10. Adding records
11. Deleting records
12. Saving changes made in DataGridView - EF6
13. Delete multiple selected rows DataGridView - EF6
14. Load, edit and save 2 tables in 1 DataGridView - EF6
15. Use combo box for enumerated values in DataGridView

---

Entity framework data model allows using LINQ . Data manipulation is little bit different from LINQ to SQL


Entity Framework 6 Tools for Visual Studio 2012 and 2013

From existing database:
- In Visual Studio, Solution Explorer click on the project and select Add/New item
- In Visual C#\Data section (in VS 2013) select ADO.NET Entity data model (name it e.g. BasicsModel) 
- Select "EF Designer from Database"
- Select or create database connection (name it e.g. BasicsEntities) 
=> BasicsModel.edmx will be created 

When Entiy data gests created the data model defintion gets stored .edmx file It has sections:
<edmx:StorageModels> - described how the data is stored in database
<edmx:ConceptualModels>- defined model used in clients code
<edmx:Mappings> - defines how data frome storage model maps to the Conceptual model. When you generate Entity framework model database, both Storage and Comceptual models are the same

The snippet of the .edmx file below shows how Persons table definition looks like.

XML
..
<EntityType Name="persons">
  <Key>
    <PropertyRef Name="id" />
  </Key>
  <Property Name="id" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
  <Property Name="lastName" Type="varchar" Nullable="false" MaxLength="20" />
</EntityType>
.. 

The C# part of the model is stored if file that matches .edmx file name. E.g. model.edmx, model.designer.cs
The designer file defines the entity model e.g.

C#
BasicsEntities m_context = new BasicsEntities();

which should be instantiated when your class gets created or needs data.

In case that you generate model from the DB and there is no foreign key between 2 tables that have a relationship, you can add the navigation property manually.

1. Right click on the designer, Add < Association

2. Create the association, e.g. between People and Departmens
3. Go into the Model Browser < Associations
4. Right click on the newly created association and select Properties
5. Change properties as necessary
6. Click on the [...] button in "Referential Constraint" field and define the constraint.

C#
BasicsEntities m_context = new BasicsEntities();
..
IQueryable<person> personsQuery = m_context.CreateObjectSet<person>();
string lastName = personsQuery.First().lastName;

C#
IQueryable<person> personsWithDepartmentsQuery = m_context.CreateObjectSet<person>().Include("department");
string deptName = personsWithDepartmentsQuery.First().department.name;

C#
IQueryable query = from p in m_context.persons
                   select p;

C#
using System.Data.Objects;  // System.Data.Entity.dll
...
IQueryable query = from p in m_context.persons select p;

listBox1.DisplayMember = "LastName";
listBox1.DataSource = ((ObjectQuery) query).Execute(MergeOption.AppendOnly);

C#
person person1 = new person { firstName = "Zbynek", lastName = "Cernin", department_id = 1 };
m_context.persons.AddObject(person1);
m_context.SaveChanges();

C#
m_context.persons.DeleteObject(m_context.persons.Last());
m_context.SaveChanges();

C#
// load data
IQueryable query = from pers in m_context.persons
                   select pers;

dataGridView1.DataSource = ((ObjectQuery)query).Execute(MergeOption.AppendOnly);
// or based on you data model
// dataGridView1.DataSource = ((System.Data.Entity.Infrastructure.DbQuery<Person>>)query).ToList();  

//Save changes after edit
dataGridView1.EndEdit();
m_context.SaveChanges();

C#
// EF 6
// code for a button event handler

// delete rows in database
foreach (var row in dataGridView1.SelectedRows)
{
    object o = ((DataGridViewRow)row).Cells[ID_INDEX].Value;
    int id = Convert.ToInt32(o); // (int)o throws InvalidCastException
    _dbContext.Persons.Remove(_dbContext.ElementExtern.Where(el => el.Id == id).First());
}
_dbContext.SaveChanges();

// delete rows in Grid
for (int i = dataGridView1.Rows.Count - 1; i >= 0; i--)
{
    if (dataGridView1.Rows[i].Selected)
    {
        dataGridView1.Rows.RemoveAt(i);  // dataGridView1.DataSource must be bound to BindingSource in order to allow RemoveAt() to run
    }
}

I did not find out how to delete multiple rows when user deletes the rows in dataGridView

DataGridView allows deleting rows only if its DataSource is bound to a BindingSource (can be solved)

When DataGridView.RowsRemoved event gets fired e.RowIndex is index of the row that was removed, but the row is not in the dataGardView.Rows anymore

When DataGridView.UserDeletedRow event gets fired then e.Row.Index is always -1, Cells[0] is null

In order to achieve this you need to create a helper class that will hold references to original entities loaded by entity framework and properties for the columns that will be shown in the DataGridView

C#
    public class QueryData
    {
        public Person Person;
        public Department Department;

        public long Id
        {
            get { return Person.Id; }
        }

        public string Name
        {
            get { return Person.Name; }
            set { Person.Name = value; }
        }

        public string DepartmentName
        {
            get { return Department.Name; }
            set { Department.Name = value; }
        }
    }

Then it is possible to read data and display it in the DataGridView

C#
  var query = from p in _dbContext.Persons
                 join d in _dbContext.Department on p.DepartmentId equals d.Id into d1
                 from d2 in d1.DefaultIfEmpty()
                 select new QueryData { Person = p, Department = d2 };
  
  BindingSource bs = new BindingSource(); // without BindingSource it is not possible to call: dataGridView1.Rows.RemoveAt(i);
  bs.DataSource = query.ToList();
  
  dataGridView1.DataSource = bs;
  dataGridView1.Refresh();
}

You need a class that represents internal data (e.g. value in DB) and text presented to the user

C#
public class Gender
{
    public int Code { get; set;}
    public string Name { get; set; }
}

Create list manually or read it from DB (e.g .from a table)

C#
List>Gender< genders = new List>Gender<();
genders.Add(new ElementType() { TypeCode = "0", Name = "Female" });
genders.Add(new ElementType() { TypeCode = "1", Name = "Male" });

Create combo box column type

C#
DataGridViewComboBoxColumn columnType = new DataGridViewComboBoxColumn();
columnType.DataPropertyName = "Gender";  // name of the public property in the class that is datasource for the DataGridView (QueryData in the example above) 
columnType.DataSource = genders;
columnType.ValueMember = "TypeCode";
columnType.DisplayMember = "Name";

Assign it to the DataGridView

C#
dataGridView1.Columns.Insert(2, columnType);

NOTES:

Implementing DataAccess EF with SQLite  (manually) http://damienbod.com/2013/11/18/using-sqlite-with-entity-framework-6-and-the-repository-pattern/

EF 6 with SQLite: http://erikej.blogspot.ch/2014/11/using-sqlite-with-entity-framework-6.html

EF 6 for SQLite Nuget package: https://www.nuget.org/packages/System.Data.SQLite.EF6/

Add Delete - DataBinding with DataGridView http://www.worldbestlearningcenter.com/index_files/csharp-entity-framework-datagridview-add-delete-edit-rows.htm

DataBinding WinForms (not direct EF) https://msdn.microsoft.com/en-us/data/jj682076.aspx


Entity Framework in WinForms (2011) http://www.codeproject.com/Articles/221931/Entity-Framework-in-WinForms



The Toolbox support is not included with the updated control (EntityDataSource), markup only: http://stackoverflow.com/questions/25836705/entitydatasource-and-entity-framework-6

My conclusion based on: https://social.msdn.microsoft.com/Forums/en-US/e07462b1-0043-4346-9b66-4c0dba4bede3/entity-framework-611-entitydatasource-toolbox-item-not-available?forum=adodotnetentityframework
 => EntityDataSource probably works in EF 5, but not EF 6