How to create editable data in WPF DataGrid using EF?

Question

I'm using EF6 Code First and WPF DataGrid. Say, I have two columns, T1 and T2, with one-to-one relationship:

-- Create tables.
-- This is the parent table.
create table dbo.T1
(
    id int primary key,
    addr varchar(100) not null
);
-- This is the child table.
create table dbo.T2
(
    id int primary key,
    name varchar(100) not null
);

-- Set primary "id" column of T2 as foreign key.  
alter table dbo.T2
    add constraint T1_pk
    foreign key (id) references dbo.T1(id);

-- Insert some sample values
insert into dbo.T1 values (1, 'addr1'), (2, 'addr2'), (3, 'addr3');
insert into dbo.T2 values (1, 'name1'), (2, 'name2');

I want 1) to show all columns in DataGrid from T2 child table and some columns from parent T1 table, as the following SQL expresses, and 2) make this data editable:

select T1.id, T1.addr, T2.name
from dbo.T1 left join dbo.T2 on T1.id = T2.id;

I solved the first part of my problem with the following:

var query = from r1 in db.T1 
            join r2 in db.T2 on r1.id equals r2.id into gj
            from x in gj.DefaultIfEmpty()
            select new
            {
                r1.id,
                r1.addr,
                name = (x == null ? string.Empty : x.name)
            };
query.Load();
dg.ItemsSource = null;
dg.ItemsSource = query.ToList();

But this makes data read-only. So, how I can achieve the "editability" of data? The data, returned by query, should somehow be related to underlying entities, but can't figure it out. Please, help me to solve this problem! Thanks beforehand!

Answer 1

Please try to pack query result into special join class like this

var query = from r1 in db.T1
            join r2 in db.T2 on r1.ID equals r2.ID into gj
            from x in gj.DefaultIfEmpty()
            select new {T1 = r1, T2 = x};

 dg.ItemsSource = new ObservableCollection<JoinT1T2Class>(query.ToList().Select(n=>new JoinT1T2Class(n.T1,n.T2)));
//Here is editable DataSource; 


public class JoinT1T2Class
{
    private T1 _T1;
    private T2 _T2;

    public JoinT1T2Class(T1 t1, T2 t2)
    {
        this._T1 = t1;
        this._T2 = t2;
    }

    public int ID { get { return _T1.ID; } }

    public string Addr { get { return _T1!=null?_T1.Addr:string.Empty; } set { if (_T1!=null)_T1.Addr = value; } }
    public string Name { get { return _T2!=null?_T2.Name:string.Empty; } set { if(_T2!=null)_T2.Name = value; } }
}

where T1 and T2 are your Entity classes.

public class T1
{
    [Key]
    [Column("id")]
    public int ID { get; set; }
    [Column("addr")]
    public string Addr { get; set; }
}

public class T2
{
    [Key]
    [Column("id")]
    public int ID { get; set; }
     [Column("name")]
    public string Name {get;set;}
}

To be able to store your changes into DataBase you have save the reference on your DataContext in private variable and dispose it on window closed;

To save any changes you have to call db.SaveChanges();

Use CollectionChanged event handler if you need to process addition or removing rows

ObservableCollection<JoinT1T2Class> dataSource = new ObservableCollection<JoinT1T2Class>(query.ToList().Select(n=>new JoinT1T2Class(n.T1,n.T2)));
dataSource.CollectionChanged += dataSource_CollectionChanged;

void dataSource_CollectionChanged(object sender, System.Collections.Specialized.NotifyCollectionChangedEventArgs e)
    {
        switch (e.Action)
        {
            case System.Collections.Specialized.NotifyCollectionChangedAction.Add:                   
                 // to access added items use  e.NewItems
                break;
            case System.Collections.Specialized.NotifyCollectionChangedAction.Remove:                    
                 // to access deleted items use e.OldItems
                break;
        }
    }