Restrict LINQ Subquery when databound to a grid

Question

I want to limit the number of child elements I get back. In this example Order.CustomerID "VINET" has 3 Order Details. I only want to see the record that has a unit price of 14. I do NOT want to see the Order Details where the unit price equals 9.8 or 43.8

In the end I want to do this in a dynamic where query or with a predicate, but the simple example should show my problem. I tried this a couple of different ways including the two i have shown below. I realize the problem is in the fact that LINQ is automatically running its own queries when I expand, but does anyone have a good solution?

private void btnJoinProblem_Click(object sender, EventArgs e)
{
NorthwindDataContext db = new NorthwindDataContext();
var tempQ2 = (from od in db.Order_Details   
join o in db.Orders on od.OrderID equals o.OrderID
where od.UnitPrice == 14
select o).Distinct();
}

Also brings back too many subrecords at the Order Detail level

NorthwindDataContext db = new NorthwindDataContext();
var tempQ = from o in db.Orders 
            where o.Order_Details.Any(od => od.UnitPrice == 14) 
            select o;

var bindingSource = new BindingSource();
bindingSource.DataSource = tempQ;
ultraGrid1.DataSource = bindingSource;

Answer 1

Try binding to a BindingSource and using AsDataView() on your query and see if that works, like the following:

var bindingSource = new BindingSource();
bindingSource.DataSource = tempQ.AsDataView();

Of course, don't forget to set the grid's data source to the BindingSource.

A quick note: AsDataView() cannot be used if there's a join. IIRC, the following should work though:

var tempQ = from o in db.Orders
            where o.Order_Details.Any(od => od.UnitPrice == 14)
            select o;

Note that you probably want to use the child relation o.Order_Details, not db.Order_Details, (assuming it's set up correctly in your dataset).

Answer 2

Well, the first thing I'd do is "select" the columns that you want into an anonymous type (or a named type) - this means you are getting rectangular data, and you don't have to worry as much about lazy loading. Secondly, you need a list for the grid.

Try something like:

using(NorthwindDataContext db = new NorthwindDataContext()) {
    var query= from od in db.Order_Details   
               join o in db.Orders on od.OrderID equals o.OrderID
               where od.UnitPrice == 14
               select new {o.OrderId, o.Customer.CustomerName,
                          od.UnitPrice}; // etc

    ultraGrid1.DataSource = query.Distinct().ToList();
}