v2.6 final October 9th 2009, Adapter template, SQL Server 2008, Win7 x64, .NET 3.5

I want to create an object with a collection that also has a collection for each item. The data is created from other tables in the database.

ScoreCard->ScordCardBase->ScoreCardText

Each level has other single valued properties.

I can create, using Linq the first 2 levels, but the last doesn't work. At least not they way I have tackled it. Here is my code so far:

    public class ScoreCardTests
    {
        [Test]
       //Rollback]
        public void CanCreateScoreCardEntityAndScordCardBaseEntitiesPlusTextEntities()
        {
            using (var adapter = new DataAccessAdapter(ConfigurationManager.ConnectionStrings["local"].ToString()))
            {
                var metaData = new LinqMetaData(adapter);
                const string countryCode = "BE";
                var scoreCard = new ScoreCardEntity
                                    {
                                        CountryCode = countryCode,
                                        CutOffDate = DateTime.Parse("02-02-2010"),
                                        ResearchDate = DateTime.Parse("02-02-2010")
                                    };

                const string languageCode = "en";
                var regionCode = String.Empty;
                var baseEntities = from questions in metaData.QnBase
                                   where questions.CountryCode == scoreCard.CountryCode && (questions.RegionCode == "_countrywide" || questions.RegionCode == regionCode)
                                   select new ScoreCardBaseEntity
                                              {
                                                  ScoreCardID = scoreCard.ScoreCardID,
                                                  QnCode = questions.QnCode,
                                                  CountryCode = scoreCard.CountryCode,
                                                  OriginalQnHeadingCode = questions.HeadingCode,
                                                  OriginalQnRegionCode = questions.RegionCode,
                                              };
                foreach (var entity in baseEntities)
                {
                    var textEntities = from text in metaData.QnText
                                       join questions in metaData.QnBase on text.QnCode equals questions.QnCode
                                       where
                                           questions.CountryCode == scoreCard.CountryCode && (questions.RegionCode == "_countrywide" || questions.RegionCode == regionCode) &&
                                           text.LanguageCode  == languageCode && text.QnCode == entity.QnCode
                                       select
                                           new ScoreCardTextEntity
                                               {
                                                   ScoreCardID = scoreCard.ScoreCardID,
                                                   QnCode = text.QnCode,
                                                   OriginalQn = text.QnTitle,
                                                   OriginalQnGuideNote = text.QnGuideNote.Substring(0, 3000),
                                                   LanguageCode = text.LanguageCode
                                               };
                    entity.ScoreCardText.DoNotPerformAddIfPresent = false;
                    entity.ScoreCardText.AddRange(textEntities);
                }
                scoreCard.ScoreCardBase.DoNotPerformAddIfPresent = false;
                scoreCard.ScoreCardBase.AddRange(baseEntities);
                adapter.SaveEntity(scoreCard);
            }
        }
    }

So all the inserts are done for the Scorecard and the ScoreCardBase entities, but although the foreach loop to create and add the ScoreCardText entities completes, the adapter.SaveEntity command doesn't create the associated inserts for them.

As an aside, but it might be a clue, entity.QnCode in the foreach loop linq gets a warning from Resharper "Access to modified closure". This might be a red herring though as I can see that the entity is still an IQueryable at that time.

I would appreciate some help on whether this is a valid approach or not. Maybe the 3rd level is read-only in Adapter?

Hi neilx,

neilx wrote:

So all the inserts are done for the Scorecard and the ScoreCardBase entities, but although the foreach loop to create and add the ScoreCardText entities completes, the adapter.SaveEntity command doesn't create the associated inserts for them.

• Please debug and see if the correct entities are added to the entity.ScoreCardText (before and after the loop).
• Is there any generated sql for ScoreCardText inserts?
• Runtime library version? (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7718)

neilx wrote:

As an aside, but it might be a clue, entity.QnCode in the foreach loop linq gets a warning from Resharper "Access to modified closure". This might be a red herring though as I can see that the entity is still an IQueryable at that time.

It has to do with scoping rules of closures. Frans wrote an article about that: http://weblogs.asp.net/fbouma/archive/2009/06/25/linq-beware-of-the-access-to-modified-closure-demon.aspx

Try this instead:

 foreach (var entity in baseEntities)
                {
                    var code = entity.QnCode;

                    var textEntities = from text in metaData.QnText
                      ...
                            text.LanguageCode == languageCode && text.QnCode == code
                      ...

neilx wrote:

I would appreciate some help on whether this is a valid approach or not. Maybe the 3rd level is read-only in Adapter?

Your approach seems ok. You also could do this: 1. Fetch the entire graph (http://www.llblgening.com/archive/2009/10/prefetchpaths-in-depth/) 2. Clone the graph (http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7568) 3. Traverse the graph to make your changes 4. Save

However, what you are doing (at first look) seems to do the job as well.

I have finally saved the textEntities collection, but there seems to be a lot of things to do to make it work. It still crashes all my test runners though (UPDATE: I turned off tracing and now they don't crash), so I am forced to use a console app

    public class ScoreCardTests
    {
        //[Test]
        //Rollback]
        //[Description("long running test shouldn't be run unless on purpose. 25seconds")]
        public void CanCreateScoreCardEntityAndScordCardBaseEntitiesPlusTextEntities()
        {
            using (var adapter = new DataAccessAdapter(ConfigurationManager.ConnectionStrings["local"].ToString()))
            {
                var metaData = new LinqMetaData(adapter);
                const string countryCode = "BE";
                var scoreCard = new ScoreCardEntity
                                    {
                                        CountryCode = countryCode,
                                        CutOffDate = DateTime.Parse("02-02-2010"),
                                        ResearchDate = DateTime.Parse("02-02-2010")
                                    };

                const string languageCode = "en";
                var regionCode = String.Empty;
                var baseEntities = (from questions in metaData.QnBase
                                    where
                                        questions.CountryCode == scoreCard.CountryCode &&
                                        (questions.RegionCode == "_countrywide" || questions.RegionCode == regionCode)
                                    select new ScoreCardBaseEntity
                                               {
                                                   ScoreCardID = scoreCard.ScoreCardID,
                                                   QnCode = questions.QnCode,
                                                   CountryCode = scoreCard.CountryCode,
                                                   OriginalQnHeadingCode = questions.HeadingCode,
                                                   OriginalQnRegionCode = questions.RegionCode,
                                               });
                scoreCard.ScoreCardBase.DoNotPerformAddIfPresent = false;
                scoreCard.ScoreCardBase.AddRange(baseEntities);
                adapter.SaveEntity(scoreCard, true); // needs to be true as I use scoreCard later
// now do the full textEntities collection
                var queryable = from text in metaData.QnText
                                join questions in metaData.QnBase on text.QnCode equals questions.QnCode
                                where
                                    questions.CountryCode == scoreCard.CountryCode &&
                                    (questions.RegionCode == "_countrywide" || questions.RegionCode == regionCode) &&
                                    text.LanguageCode == languageCode
                                select
                                    new ScoreCardTextEntity
                                        {
                                            ScoreCardID = scoreCard.ScoreCardID,
                                            QnCode = text.QnCode,
                                            OriginalQn = text.QnTitle,
                                            OriginalQnGuideNote = text.QnGuideNote.Substring(0, 3000),
                                            LanguageCode = text.LanguageCode
                                        };
// I need to create a new collection then AddReage to it rather than simply saving queryable.
                var textEntities = new EntityCollection<ScoreCardTextEntity>();
                textEntities.AddRange(queryable);
                adapter.SaveEntityCollection(textEntities);
            }
        }
    }

This all seems quite a long way round just to create an object with collection of collections. (Actually, I haven't here as the final collection is created and saved independently). I was hoping the SaveEntity(scorecard) in my original post could do everything without having to read all the data into memory first as it does with the scoreCard.baseEntity. All comments and improvemts welcome

Please try the following:

                var textEntities = from text in metaData.QnText
                                 join questions in metaData.QnBase on text.QnCode equals questions.QnCode
                                 where
                                     questions.CountryCode == scoreCard.CountryCode &&
                                     (questions.RegionCode == "_countrywide" || questions.RegionCode == regionCode) &&
                                     text.LanguageCode == languageCode
                                 select
                                     new ScoreCardTextEntity
                                     {
                                         ScoreCardID = scoreCard.ScoreCardID,
                                         QnCode = text.QnCode,
                                         OriginalQn = text.QnTitle,
                                         OriginalQnGuideNote = text.QnGuideNote.Substring(0, 3000),
                                         LanguageCode = text.LanguageCode
                                     };

                EntityCollection<ScoreCardTextEntity> collection= 
    ((ILLBLGenProQuery)textEntities).Execute<EntityCollection<ScoreCardTextEntity>>();

                adapter.SaveEntityCollection(collection); 

(EDIT) Trying to answer your first post:

adapter.SaveEntity(scoreCard);

So all the inserts are done for the Scorecard and the ScoreCardBase entities, but although the foreach loop to create and add the ScoreCardText entities completes, the adapter.SaveEntity command doesn't create the associated inserts for them.

If you want to save related entities as well, you should supply a recurse parameter to the SaveEntity() method.

adapter.SaveEntity(scoreCard, false, true);

My last edit to my post crossed with your reply. I'll try it now. Thanks.

Using this works thanks

EntityCollection<ScoreCardTextEntity> collection= 
    ((ILLBLGenProQuery)textEntities).Execute<EntityCollection<ScoreCardTextEntity>>();

I think I was in effect doing the same thing by doing an AddRange to a new object. Your code the right way though, although I will need to read the docs again to understand it.

On my first post issue with the foreach loop, the recursive false,true arguments you propose didn't change the problem that the textEntities are correctly assigned inside the loop but then aren't in the object after the loop.

It would have been nice to see why as it looks better to save just one entity with its filled graph, but I guess the second approach of saving it in two bites will suffice for now.

Thanks to all.

As a final thought on all this, I guess that an object relational mapper is the wrong tool to use when creating values in related tables that are calculated from other tables rather than input as values by a user. I say this as a mapper expects to suck data in memory, manipulate it, then save it back. This creation task is more easily done directly in the database and eliminates the need to transfer large amounts of data to the application.

Ours is probably an unusual requirement in that we need to store the snapshot values of data so we can see what what sent to our client in 6 months time when they return the filled in document we create for them. In the meantime, the live data changes.

The T-SQL to create the object we need is trivial. It just inserts into three tables taking data from 2 other tables and 3 passed in arguments. We currently use a sproc that took around 5 minutes to write and test. I will probably not use the whole of the above's 2-3 days work as it is much slower and more complex to maintain than the T-SQL.

Having said that, once the correct rows are created in the tables , normal use of LLBLGenPro will significantly speed up and simplify the task of producing the document. We produce around 1000 of these documents per year.