Custom Classes, Associations, effective data strategy RRS feed

  • Question

  • Interestingly I originally thought this was a wcf serialization issue,(posted on the wcf forum) but in the end it comes down to linq and sql and probaly my here it goes..


    I have a bunch of custom entity classes which I've mapped via Linq Table/Column Attributes

    I have set up Asscociations like so... (much code removed for illustration) note the use of IsReference to preserve the references. (This may now be a redundent issue as its not a serialization issue)


    PARENT CLASS >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

    [Table(Name = "COUNTRY")]
        [DataContract(Name = "country", IsReference = true, Namespace = "....")]
        public class Country
            private int id;
            [Column(Name = "COUNTRY_ID", IsPrimaryKey=true, AutoSync = AutoSync.OnInsert, IsDbGenerated=true)]
            public int Id
                get { return id; }
                set { id = value; }

            private EntitySet<Entities.Location> locations;
            [Association(Storage = "locations", OtherKey = "CountryID")]
            public EntitySet<Entities.Location> Locations
                get { return locations; }
                set { locations.Assign(value); }

    CHILD CLASS>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

     [Table(Name = "LOCATION")]
        [DataContract(Name = "Location", IsReference = true, Namespace = "....")]
        public class Location

            private int iD;

            public string RegionName;
            [Column(Name = "LOCATION_ID", IsPrimaryKey=true, IsDbGenerated=true, AutoSync=AutoSync.OnInsert)]
            public int ID
                get { return iD; }
                set { iD = value; }

            private EntityRef<Country> country;
            [Association(Storage = "country", ThisKey="CountryID",  OtherKey = "Id")]
            public Country Country
                get { return country.Entity; }
                set { country.Entity = value; }



    The behaviour  is that the dataload only retieves the top level countries and anyother objects that have an entityref relationship (one to one)

    I ran a profiler on the sql box and even though Quick watch shows me that before serialization time the whole of my obect graph is in memory, - (or so I thought, it turns out that quick watch fires off a sql statement when I expand my object graph - well fooled!)


    So as part of the serialization process a sql select happens again for every item in my object graph, as I'm working remotly from the sql box this was taking an age, and is about as unoptimized as it could be - it takes just under 10 minutes to get a response at the moment. (its even doing a round trip for every item!!!!)


    Can someone tell me if this is expected behaviour? or even better how I can stop these ridiculous selects and roundtrips?


    What i really wanted was for the whole dataset to be returned in one roundtrip (or most a few) and then the Mapping attributes to make sense of the data and map into my object graph. (which is what I thought was happening)


    I'm obvisoulsy rather misguided somewhere!





    Tuesday, October 21, 2008 2:26 PM

All replies

  • Have a look at DataLoadOptions.  That lets you tell LINQ to SQL to load child tables in the same SQL query as the parent.  But, it only works for one level - e.g. it works for getting Orders with Customers, but if you want to get Order Lines as well, it will revert to inefficient behaviour.




    Tuesday, October 21, 2008 10:13 PM