Assume we have a Menu class that has SubMenus (which is the same type of Menu therefore can have SubMenus and Items too) and Items (which has a different type) and we stored them in two tables(One for Menus and one for Items). Is there any way to load complete graph of the Menu class (all of its SubMenus and Items) using LINQ to SQL?
A:
If you define the relationships properly in SQL, LINQToSQL should give you back all of the menus/items with a single query on menus. However, this won't give it back to use as a graph, but as an enumeration of the menus (with related menus and items). You would need to iterate through this enumeration and construct the graph on your own.
On the other hand, you might be able to do something like this:
var roots = db.Menus.Where( m => !m.SubMenus.Count == 0 );
Then, roots would be a collection of the base menus. This might be sufficient if you don't intend to perform any graph analysis algorithms on it, but only intend to traverse it from top to bottom to construct the GUI components.
tvanfosson
2009-04-11 13:23:39
I want to serialize the complete graph to a XML file using DataContractSerializer, so I think i need to load all objects. Can I use LoadOptions somehow to eager load them?
Mohammadreza
2009-04-11 13:45:46
If you want to serialize the graph structure instead of just the elements, I think you will need to construct the actual graph. In the enumeration you'll have duplicate elements in the parent/child properties for any shared menus. Check your DBML - the relations may already be eager loaded.
tvanfosson
2009-04-11 13:57:04
I want to serialize the elements.
Mohammadreza
2009-04-11 14:13:07
Try using partial classes to mark your entities as Serializable and see if that results in a usable format. If not, you may need to implement ISerializable as well to serialize the entity the way you want.
tvanfosson
2009-04-11 14:26:02
My problem is not with the serialization method. How can I LOAD the data from file with a single query to pass it to the WriteObject method.
Mohammadreza
2009-04-11 15:19:47
From a file or from the database. LINQToSQL only works with a MS SQL Server database. Loading the data from the database should just be a matter of executing the query I referenced in my answer assuming you have your Menus and Items tables mapped using the DBML designer.
tvanfosson
2009-04-11 15:24:44
Maybe you got me wrong. Yes, I have used LINQToSQL ORM designer to create objects. The Menu class could have nested menus which will increase the levels of the graph. I am looking for a way to load all the levels from SQL instead of writing a recursive method to load them.
Mohammadreza
2009-04-11 16:27:46
If you create the proper foreign key relationships on your table -- they can be self-referential -- then the designer will add collections to your Menu entity that will contain the related menus. I believe that these are eagerly loaded by default, but you can check in the designer...
tvanfosson
2009-04-11 16:40:51
...if the relationships don't already exist, you can add them then either update your DBML by hand (search MSDN for adding associations to DBML) or remove/readd the tables in the designer to pick up the new relationships.
tvanfosson
2009-04-11 16:41:51
I don't think, however, that the entire relationship hierarchy can be loaded at once (probably only two levels). But you should be able to construct the graph in memory once you've done the initial query (not the one I mention in my answer, but simply iterating through Menus)...
tvanfosson
2009-04-11 16:45:01
...since you will have an object for each menu and all of the direct connections between them.
tvanfosson
2009-04-11 16:45:40
You'd need to be careful that there aren't any cycles (perhaps by using a data structure that doesn't allow them to be added) when you construct the graph.
tvanfosson
2009-04-11 16:50:39
Then I think I have to write the recursive method anyways. Thanks.
Mohammadreza
2009-04-11 17:11:14