Limit foreign-table retrieval

Limit foreign-table retrieval

• sbadams

  Hi Matt!

  ok, but that would make my orders-collection within customers obsolete - at least in a practical sense. I would say that if I'd already have the relationship projected, I should be able to selectively retrieve information without having to build another collection of orders which I then cannot access through the cust.Orders collection...

  
  
  

• JudyinFL

  hi,

  let me post my code here. I've commented it for myself so I understand what DLinQ does. BTW: I am not able to use evironment-variables in the path to the northwind DB).

  DataContext db = new DataContext(

  @"c:\Programme\LINQ Preview\Data\northwnd.mdf");

  // Get a typed table to run queries

  Table<Customer> Customers = db.GetTable<Customer>();

  Table<Order> orders = db.GetTable<Order>();

  // Attach the log showing generated SQL to console

  // This is only for debugging / understanding the working of DLinq

  db.Log = Console.Out;

  Int32 orderCount = 3;

  var _custs = (

  from c in Customers

  //from o in c.Orders //causes customers to duplicate!

  //where c.Orders.Any()

  where c.Orders.Count < orderCount //value of orderCount is evaluated here!!

  from o in c.Orders

  where o.CustomerID == c.CustomerID

  && o.OrderDate.Year == 1996

  //&& (c.Orders.OrderDate.Year >= 1996 && c.Orders.OrderDate.Year <= 1997)

  select c).Distinct();

  //select new { c.CustomerID, Company = c.CompanyName, c.City }; //retuns a new/anonymous object, but now misses sub-objects of original CUSTOMER object

  orderCount = 5; //this change is not taken into account in the above query!

  var custs = _custs.ToArray();

  Console.WriteLine("Found {0} Customers", custs.Count());

  if (custs.Count() > 91)

  throw new ApplicationException("SQL Statement incorrect! Customers occur more than once in the resulting list!");

  Console.ReadLine();

  /*

  Console.WriteLine("Name of FIRST Customer: {0}", custs.First().CustomerID); //invokes SQL SELECT on CUSTOMERS!!

  Console.ReadLine();

  * */

  Console.WriteLine("Stepping through Customers...");

  List<String> customerIDs = new List<String>();

  foreach(var cust in custs) { //invokes SQL SELECT on CUSTOMERS

  if (customerIDs.Contains(cust.CustomerID))

  throw new ApplicationException("SQL Statement incorrect! Customer " + cust.CustomerID + " occurs more than once in the resulting list!");

  customerIDs.Add(cust.CustomerID);

  Console.WriteLine("\r\n\r\n======================================================");

  Console.WriteLine("{2} ({0}), City={1}", cust.CustomerID, cust.City, cust.Company);

  //Console.WriteLine(" .. TOTAL Order-Count: {0}", cust.Orders.Count); //invokes SQL SELECT on ORDERS

  /*

  var custOrders = from o in cust.Orders

  where o.OrderDate.Year == 1996 || o.OrderDate.Year == 1997

  select o;

  Console.WriteLine(" .. defined custOrders");

  Console.WriteLine(" .. FILTERED Order-Count: {0}", custOrders.Count());

  * */

  Order[] orderList = cust.Orders.Where(o => o.OrderDate.Year == 1996).ToArray();

  Order[] _orderList = cust.Orders.ToArray();

  Console.WriteLine(" .. Order-Count: Child:{0}; Filtered:{1}", _orderList.Count(), orderList.Count());

  foreach (var order in cust.Orders) {

  Console.WriteLine(" .. Order {0} from {1:d}", order.OrderID, order.OrderDate);

  }

  }

  Console.WriteLine("\r\n\r\nAll done.");

  //var custsArray = _custs.ToArray(); //custs Query executed AGAIN!

  
  
  

• nCognito

  var custs = from c in db.Customers where c.Orders.Any(o => o.OrderDate.Year == 1996) select c;

  
  

• Galford

  The customer's Orders collection will always contain all the orders. Since the orders collection is not a query (it is an actual collection) trying to query it will force it to populate. You can, however, retrieve just the orders you want by going back to the database directly.

  Customer cust = ...

  var orders =
  from c in db.Customers where c == cust
  from o in c.Orders where o.OrderDate.Year == 1997
  select o;

  
  
  

• Marco.Casamento

  I believe Tilli is referring to the issue at http://forums.microsoft.com/MSDN/showpost.aspx postid=287911&siteid=1.

  Tiki Wan

  
  

• Cathyjack

  Note that Tilifried is retrieving customers (and their orders), whereas you are retrieving orders.
  
  

• Bekas

  Tilfried,

  I'm just tried this on our current DLINQ bits against the Northwind database and the following worked and only returned those orders requested from the database.

  var q = db.Orders.Where(o => o.OrderDate.Value.Year == 1996 && o.OrderDate.Value.Month == 7 && o.OrderDate.Value.Day == 4);

  So, it may be a bug in the last preview. What query text are you seeing executed on the database (use db.GetQueryText(q);)

  
  
  
• Limit foreign-table retrieval