0

The title says most of what I wish to do. I have two tables "orders" and "Customers". the orders table contains the customerID of every customer who has placed an order and the customers table contains every customerID. I need to select and display the customers who have not placed an order. I know

I need to display the rows where the customerID from the customers table does not match the customerID from orders table.

I am not sure how to do this however and have not come across a solution that I can understand so any help would be much appreciated. This is what I have tried doing.

private void btnQ9_Click(object sender, RoutedEventArgs e)
{
    DataClasses1DataContext dc = new DataClasses1DataContext();
    var query = from c in dc.Customers
                join o in dc.Orders on c.CustomerID equals o.CustomerID
                group o by new { o.CustomerID, c.CompanyName } into grp
                where(grp.Key.CustomerID.Count() ==0)
                select new
                {
                    CompanyName = grp.Key.CompanyName,
                    Order = grp.Key.CustomerID.Count()
                };
    DataQ9.ItemsSource = query.ToList();
}
abatishchev
  • 98,240
  • 88
  • 296
  • 433
Kevin
  • 2,258
  • 1
  • 32
  • 40
  • 2
    this is the same type of query that you're wanting.. http://stackoverflow.com/questions/9171063/convert-sql-to-linq-left-join-with-null – JamieD77 May 03 '16 at 18:26
  • 1
    also if you have your foreign keys set properly you should be able to do `from c in dc.Customers where c.Orders.Count == 0 select c` – JamieD77 May 03 '16 at 18:37
  • I didn't see that first link, thanks for posting it, UpVoted so it will help somebody else out in future. It is a linq assignment, we were given the database to work with and were to solve the problems given through linq. hopefully the second comment will help someone, maybe myself in the future – Kevin May 03 '16 at 18:41

1 Answers1

3

It seems like you just want

from c in dc.Customers
where !dc.Orders
         .Select(o => o.CustomerID)
         .Contains(c.CustomerID)

or

from c in dc.Customers
where !dc.Orders
         .Any(o => o.CustomerID == c.CustomerID)

They might generate different SQL so I would try both and see which one performs better (if the difference is even noticeable).

D Stanley
  • 149,601
  • 11
  • 178
  • 240