Let's see on Northwind Customer <-> Order relation and DLink entity classes generated by SQLMetal.
Helper code for printing Order id:
static void WriteOrders (IEnumerable<Order> orders){
foreach (Order order in orders)
Console.Write ("#{0} ", order.OrderID);
Console.WriteLine ();
}
First code example:
static void Main (string [] args) {Northwind db = new Northwind ("Data Source=WIZARD;Initial Catalog=Northwind;Integrated Security=True"); Customer customerALFKI = db.Customers.Single (c => c.CustomerID == "ALFKI"); Customer customerANATR = db.Customers.Single (c => c.CustomerID == "ANATR"); Console.WriteLine ("Initial state :"); WriteOrders (customerALFKI.Orders); // #10643 #10692 #10702 #10835 #10952 #11011 WriteOrders (customerANATR.Orders); // #10308 #10625 #10759 #10926 Console.WriteLine (); // Change Customer Of Order #10308 customerANATR.Orders [0].Customer = customerALFKI; // Now we can suppose that Order #10308 is moved from customerANATR.Orders to customerALFKI.Orders Console.WriteLine ("When moved :"); WriteOrders (customerALFKI.Orders); // #10643 #10692 #10702 #10835 #10952 #11011
// but where is #10308 WriteOrders (customerANATR.Orders); // #10625 #10759 #10926
// we can see that #10308 is removed Console.ReadKey (); } So it seems that Order #10308 is lost. But if we save changes to DB, the Customer of the Order #10308 is changed to `ALFKI`. The hypothesis is that when my code references to customerANATR.Orders [0], this EntitySet is loaded, but customerALFKI.Orders is not loaded.
Actually, if I say
customerALFKI.Orders.Load ();
customerANATR.Orders.Load ();
customerANATR.Orders [0].Customer = customerALFKI;
everything works fine.
Second example:
just change
customerANATR.Orders [0].Customer = customerALFKI;
to
db.Orders.Single (c => c.OrderID == 10308).Customer = customerALFKI;
now outputs before and after moving Order #10308 from Customer `ANATR` to `ALFKI` are equal (But result of saving to DB remains correct, of course). Evidently Customer.Orders collections are not loaded.
Where can I read about cases when EntitySet is loaded (besides explicit call of .Load()) Also is there any recommended pattern for preloading of related entities Explicit calls of both EntitySet's .Load() methods before every moving entity from one EntitySet to another seems to be not a good idea.
DLink: Need more docs about loading of EntitySets.
wwwillem
EntitySet does currently exist in one of two states, loaded or unloaded. An unloaded entityset is connected back to the database, so queries relative to it will fire against the database and not load the entityset. When the entityset is loaded, queries using it occur locally. How do you load an entityset EntitySet.Load(), using .Including() in a query or an indexing operation.
We have received a variety of feedback on this behavior. The change originally was meant to alleviate confusion, but seems to have only added to it. I suspect this duality will be removed before the next
ARTZ
But set method for .Customer property of Order entity (generated by SQLMetal) does perform some actions for handling Order movement from one collection to another.
So I see a problem is in "dualism" of EntitySet. Before EntitySet is loaded it is just query and when I write something like
foreach (Order in aCustomer.Orders) {...}
data is requested from DB.
But after I call aCustomer.Orders.Load() method, the same instruction
foreach (Order in aCustomer.Orders) {...}
requests in-memory data.
It seems from my example above that sometimes an implicit .Load () call occures. So the first question is: when does this "implicit" call occur (i mean conditions, not a time)
So I ask for a common pattern for loading related data. Of course I do know obvious model "load everything first" but I am looking for appropriate pattern that uses EntitySet's lazy-loading ability. First I thought that changing related data (before related collection is not loaded entirely) is very easy, but asymmetric behavior of source and destination EntitySets (example1: source EntitySet looks like loaded whereas destination EntitySet is queried from DB despite of changes made) wrecks my naive hopes.
From the other hand I don't think that explicit call of .Load () method before any related Entity moving is a good idea. Or am I wrong
PCIGUY
Section 3.1 of the DLinq overview document discusses deferred execution. As I read it, essentially each time you iterate over the results, you get the record from the database again. If you want work with a <table> with LINQ and not re-fetch it, use the .ToArray or .ToList to pre-fetch the results. Do your manipulations and then post the changes back with DLINQ.
Jim Wooley
http://devauthority.com/blogs/jwooley/default.aspx
Gerry Baerman
Konstantin Meleshenko MSFT
In my example above source and destination EntitySets have been iterated already (inside WriteOrders method) before I try to make any changes. So if I understand you correctly further requests shouldn't lead to database queries. But it seems that final calls of WriteOrder method request DB again (I suppose that EntitySet.Add and .Remove methods called in Order.set_Customer work rightly).
Daisy34001
DLINQ allows you to prefetch the query results and place them into an in-memory cache using the .ToList or .ToArray methods. Additionally, you can fetch the child records with the .Including method. Using these will cause your entire heirarchy to load into memory.
It appears the main issue here has to do with the opposition of OO and Relational mindsets. From an OO standpoint, changing the ID property of the child's parent does not automatically move remove it from a given object and transfer it to another one. You need to manually handle the movement of the child out of one parent's tree and into another one.
What you are trying to accomplish is possible in a relational model as the connection between objects is made by joins. It may be possible to do what you want through the new Join methods in the May CTP. I haven't had a chance to work through them much yet.
Jim Wooley
http://devauthority.com/blogs/jwooley/default.aspx