Hi There

I'm trying to get an entity collection with related info down from a web service with minimal load.

Using .Net Version 2, LLBLGenPro V 2.6 Build 12 October. Using the 2 Calasses Template, Adapter.

the simplest way with the least headache seems to be .Writexml ( then .Readxml in the client. )

The structure looks something like this

ShopOrders ShopCountry ( using ShopOrders.DelcountryID ) ShopCountry ( using ShopOrders.BuyercountryID ) OrderLines ( using ShopOrders.ID ) ShopProductVariations ( using ShopOrderLines.VariationID )

Now maybe it's because ShopCountry is linked twice to the same table , but when I write to XML, the XML is HUGE. 100 rows = about 7MB. Is this a bug or the way I'm writing the code?

The xml looks a bit like this. I wouldn't expect the <ShopOrders_> xml to exist at all.
It's like it's doing the relationship down from ShopOrders to Country and then back up to ShopOrders again on each of the 100 rows!

<ShopOrdersEntity ObjectID=\"71e9fddf-5aae-4a80-bb19-83236b183c7e\"> <BuyerCountry ObjectID=\"2b570415-89d5-43ca-bbf5-01009f9f8512\"> <Id>11</Id> <CountryName>AUSTRALIA</CountryName> <ShopOrders_> <ShopOrdersEntity Ref=\"71e9fddf-5aae-4a80-bb19-83236b183c7e\" /> <ShopOrdersEntity ObjectID=\"c7fea04f-c907-4a10-a511-0f59c40fd715\">

Here is the Code. Any ideas? Any help is much appreciated. There are no Entities Added twice in the LLBLGen Gui.

ExcludeIncludeFieldsList buyercountryfields = new ExcludeIncludeFieldsList(false);
ExcludeIncludeFieldsList deliverycountryfields = new ExcludeIncludeFieldsList(false);

buyercountryfields.Add(ShopCountriesFields.CountryName);
deliverycountryfields.Add(ShopCountriesFields.CountryName);


IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.ShopOrdersEntity);
prefetchPath.Add(ShopOrdersEntity.PrefetchPathShopOrderLines).SubPath.Add(ShopOrderLinesEntity.PrefetchPathShopProductVariations);
prefetchPath.Add(ShopOrdersEntity.PrefetchPathBuyerCountry, buyercountryfields);
prefetchPath.Add(ShopOrdersEntity.PrefetchPathDeliveryCountry, deliverycountryfields);

EntityCollection<ShopOrdersEntity> orders = new EntityCollection<ShopOrdersEntity>();
ISortExpression sorter = new SortExpression();
sorter.Add(ShopOrdersFields.Id | SortOperator.Ascending);

using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.CatalogNameToUse = catalog;
    adapter.ConnectionString = ConfigurationManager.ConnectionStrings[company].ConnectionString;
    adapter.FetchEntityCollection(orders, filter, 100, sorter, prefetchPath);
}
String retval = "";
orders.WriteXml(XmlFormatAspect.Compact25, out retval);
return retval;

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7725

Otis wrote:

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7725

sorry Version Info above. Using .Net Version 2, LLBLGenPro V 2.6 Build 12 October. Using the 2 Calasses Template, Adapter. SQL Server Version 2005.

Does ShopOrderEntity have relationship with itself?

Would you please give more info about the relationships and if the prefetched collections were indeed populated or whether there were empty collections before the xml was written.

Man you guys are fast!

ShopOrderEntity doesn't have a relationship with itself.

ShopOrders - ShopCountries (m:1) ( using ShopOrders.DelcountryID ) ShopOrders - ShopCountries (m:1) ( using ShopOrders.BuyercountryID ) ShopOrders - ShopOrderLines (1:n) ( using ShopOrders.ID ) ShopOrder - ShopProductVariations ( m:n) ( using ShopProductVariationsCollectionViaShopOrderLines )

What looks like it could be the problem is that there are 2 relationships from ShopCountries to ShopOrders

ShopCountry - ShopOrders (1:n) Field Mapped ShopOrders ShopCountry - ShopOrders (1:n) Field Mapped ShopOrders_

They are all standard relations - nothing has been manually added , and all the expected mapped fields are populated.
Problem is a whole lot of other data starting at the <ShopOrders_> node.

Seems fine in the actual collection, it's when I convert to xml that is the problem.

There's no real problem, the thing is this: the relations are bi-directional, so ShopOrder - ShopCountry but that's also ShopCountry - ShopOrder. You have that two times. The XML is produced recursively, so this is what happens: The first shopOrder is serialized, it has a relation with a ShopCountry entity via 'BuyerCountry' as you can see in the XML. the serializer is then going to serialize that entity's XML at that spot. So it proceeds with doing that and it reaches 'ShopOrders' one of the two collections in ShopCountry with ShopOrder entities. As it's not empty, (as setting shopOrder.BuyerCountry to shopCountry will also add shopOrder to shopCountry.ShopOrders (or ShopOrders, depending on which collection is mapped onto which relation), it will serialize that collection at that spot. The first ShopOrder entity we started with is in that collection as you can see: <ShopOrdersEntity Ref=\"71e9fddf-5aae-4a80-bb19-83236b183c7e\" />, and it proceeds with the next entity in that collection, and it's serialized at that spot, which has relations with other entities etc. etc.

This is the result of having objects with references to eachother in a single XML tree.

Now, about the huge data size: please check whether you have blob/image/ntext/text fields in one of the entities in the graph. Each entity is in the XML once, so that's not it. Try to fetch just 1 shoporder entity instead of 100 and see which entity is really big in the XML output.

Also a different thing could be this: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=12209