Binding datagridview to a multiple table linq-to-sql query

Question

I'm binding the results of a linq-to-sql query to a datagridview. This works fine if I'm just selecting from a single database table. However, if it's a join query where I'm selecting fields from both tables, then because the select is going into an anonymous type, the results are readonly. Hence my datagridview is also readonly. I want the fields in the DGV to be editable.

If I create a non-anonymous type, and use that in the linq query, then the DGV is editable, but calling the SubmitChanges() method doesn't work. I know I could manually fill in the results before calling SubmitChanges(), but it feels like there should be a better way of doing this. Especially given that it must be a fairly common task.

What's the recommended way of doing this?

Thankyou for any help,
Dan.

Answer 1

What I've done is create a display class, taking the linq to sql table objects as arguments to the constructor and then just wrapping the properties I wanted to display. For example, the following class where I wanted to allow edits to the street and city, but also display the application number and status:

public class AddressDisplay
{
    private Retailer _retailer;
    private BusinessAddress _address;

    public AddressDisplay(Retailer retailer, BusinessAddress address)
    {
        _retailer = retailer;
        _address = address;
    }

    public string ApplicationNumber
    {
        get { return _retailer.ApplicationNumber; }
    }
    public string Status
    {
        get { return _retailer.Status; }
    } 
    public string Street
    {
        get { return _address.Street1; }
        set { _address.Street1 = value; }
    }
    public string City
    {
        get { return _address.City; }
        set { _address.City = value; }
    }
}

and then return instances of AddressDisplay to bind to the DataGridView:

var addresses = from a in _context.BusinessAddresses
                join r in _context.Retailers on a.ApplicationNumber equals r.ApplicationNumber
                where a.City == city
                select new AddressDisplay(r, a);

HTH