Binding Entity Framework objects to a Datagridview C#

Question

I have been trying to bind an Entity Framework object to a DataGridView but I keep hitting dead ends and I can't seem to find my answer anywhere.

I can bind the whole of a table (entity) to a gridview and it will allow me to make changes and save those changes back to the DB like this:

    WS_Model.WS_Entities context;

    private void simpleButton1_Click(object sender, EventArgs e)
    {
        context = new WS_Entities();

        var query = from c in context.Users select c;

        var users = query.ToList();

        gridControl1.DataSource = users;
    }

    private void simpleButton2_Click(object sender, EventArgs e)
    {
        context.SaveChanges();
    }

but I dont want to see all of the columns from the table in my DB in my datagridview so I tried doing it this way...

WS_Entities context = new WS_Entities();

    private void simpleButton1_Click(object sender, EventArgs e)
    {
        var query = from c in context.Users
                    where c.UserName == "James"
                    select new { c.UserName, c.Password, c.Description };

        var results = query.ToList();

        gridControl1.DataSource = results;
    }

    private void simpleButton2_Click(object sender, EventArgs e)
    {
        context.SaveChanges();
    }

but now I cant edit any data in my DataGridView.

I can't see the wood for the trees here - please would someone mind pointing our the error of my ways or telling me what the best practises are for binding EF with Winforms as I'm getting brain drain.

I can see it's to do with the section:

select new { c.UserName, c.Password, c.Description }

But I dont know why.

Answer 1

The problem with the line:

select new { c.UserName, c.Password, c.Description }

Is that it is creating an anonymous type, and anonymous types are immutable - that is read only. This is why your changes do not get reflected in either the new type or in the original EF object.

Now, as for ways of not showing all the columns of the object you are binding to, I've given three options below.

Hide unwanted columns

The most straight forward approach is to set the visible property to false for the columns you do not want to show.

dataGridView1.Columns[0].Visible = false;

Where the value in the Columns collection indexer can either be an integer specifying the column location or a string for the column's name.

Custom object in EF for this databinding

You could also handle this at the EF layer - creating a custom object for your binding which EF maps from the database without the columns you don't want. I haven't used EF 4.0 at all really but I understand that it has this capability now.

Custom DTO projected from EF object and then mapped back

The third option (and these are going from good to bad in my opinion of them but I thought I'd tell you a few approaches!) is to query to a concrete type and then map back to the EF object. Something like:

private class DataBindingProjection
{
    public string UserName { get; set; };
    public string Password { get; set; };
    public string Description { get; set; };
}

private void simpleButton1_Click(object sender, EventArgs e)
{
    context = new WS_Entities();
    var query = from c in context.Users
                where c.UserName == "James"
                select new DataBindingProjection { UserName = c.UserName, Password = c.Password, Description = c.Description };
    var users = query.ToList();
    gridControl1.DataSource = users;
}

private void simpleButton2_Click(object sender, EventArgs e) 
{
    // and here you have some code to map the properties back from the 
    // projection objects to your datacontext

    context.SaveChanges();
}

In certain situations that could be a workable solution too...