Hi

I'm using LLBL 2.5 for my Data access layer to access SQL Server CE 3.5, and all is working great so far...

Now I'm coming across a problem with GetMulti, I have the following DataStructure:

Playlist -- TrackSet -< TrackSetItem >- Tracks

Where playlist is one to one to TrackSet, this has many trackSetitems and each one is a Tracks.

The problem is that in TrackSetItem we can have the same track twice for the same playlist, using an index and I want to get a TrackCollection from the tracks of a playlist, with repeated tracks of course.

I currently use the following code

      RelationCollection relations =
                new RelationCollection(PlaylistEntity.Relations.TrackSetEntityUsingTrackSetLid)
                    {
                        TrackSetEntity.Relations.TrackSetItemEntityUsingTrackSetLid,
                        TrackSetItemEntity.Relations.TrackEntityUsingTrackLid
                    };
        
            PredicateExpression filter = new PredicateExpression(PlaylistFields.Lid== 1);

            TrackCollection tracks = new TrackCollection();
            tracks.GetMulti(filter, relations);

This generates the SQL with the DISTINCT clause, therefore I'm missing the repeated Tracks on a Playlist.

It's there any way of getting around this easily... I've seen that using Dynamic list I have to option of allow Duplicates, but the ammount of code involved when using Dynamic list it's a stopper. I say this because for Dynamic list I'll have to define all the fields of the Track Entity, and then cast each one of this fields back from the DataTable to the Entity...

I see the dynamic list usefull when we need to get data from mixed tables, but in my case I need tracks and all the fields, I need a track collection.

Please let me know the best approach to this problem from your point of view.

Many Thanks Jaume

Hi Jaume,

By defination collections represent unique rows. However you have alternatives:

• DynamicLists
• TypedLists

An allosDuplicates parameter exists when you fetch DynamicLists/TypedLists. For more information read LLBLGen Pro Help - Using the generated code - Adapter - Using typedviews, typedlists and dynanmiclists.

Thanks for your reply Daelmo, unfortunatelly not as positive as I was hoping...

Yes I already know about DynamicLists and TypedList, but as said on my previous message I think the amount of work for using a DynamicList it's quite considerable and it is a pain having to use Datatable hardcoded fields and casting each one back to the Entity, wich was one of the reasons of adopting LLBL, to avoid using hardcoded strings in our code.

See below a sample of the same code using Dynamic List:

 RelationCollection relations =
                new RelationCollection(PlaylistEntity.Relations.TrackSetEntityUsingTrackSetLid)
                    {
                        TrackSetEntity.Relations.TrackSetItemEntityUsingTrackSetLid,
                        TrackSetItemEntity.Relations.TrackEntityUsingTrackLid
                    };
        
            PredicateExpression filter = new PredicateExpression(PlaylistFields.Id== 1);

            
            ResultsetFields fields = new ResultsetFields(6);
                fields.DefineField(TrackFields.Title, 0);
                fields.DefineField(TrackFields.ArtistName, 1);
                fields.DefineField(TrackFields.ReleaseName, 2);
                fields.DefineField(TrackFields.Filename, 3);
                fields.DefineField(TrackFields.Filesize, 4);
                fields.DefineField(TrackFields.DurationSecs, 5);
            
                DataTable dynamicList = new DataTable();
                TypedListDAO dao = new TypedListDAO();
        
               dao.GetMultiAsDataTable(fields, dynamicList, 0, null, filter, relations, true, null, null, 0, 0);
    
            TrackCollection tracks = new TrackCollection();
             foreach (DataRow row in dynamicList.Rows)
            {
                TrackEntity trackDal = new TrackEntity();
                trackDal.Title = row["Title"].ToString();
                trackDal.ArtistName = row["ArtistName"].ToString();
                trackDal.ReleaseName = row["ReleaseName"].ToString();
                trackDal.Filename = row["Filename"].ToString();
                trackDal.Filesize = (int?) row["Filesize"];
                trackDal.DurationSecs = (int?) row["DurationSecs"];
                tracks.Add(trackDal);
            }

This is the code using a Dynamic list, as you can see its not as simple as using Collection, and much longer than writting a SQL that populates the DataTable.

In case this is the only option, Is it there a way to assign directly all the fields of an Enitity to a ResultsetFields class? in example: ResultsetFields f = Tracks.Fields()

Secondly its a way to automatically assign a DataRow to an Entity?

Even If I define a TypedList I'll have to do field assignation one by one right?

I'll much appreciate your expertise on this.

Many Thanks

Hi,

I think it's got to do with:

That's because the GetMulti fills an entityCollection which in turn has the following property set to true: DoNotPerformAddIfPresent

As in the manual:

When set to true, an entity passed to Add() or Insert() will be tested if it's already present. If so, the index is returned and the object is not added again. If set to false (default: true) this check is not performed. Setting this property to true can slow down fetch logic. DataAccessAdapter's fetch logic sets this property to false during a multi-entity fetch.

It's discussed in: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=12252

Cheers, Gab

gabrielk wrote:

I think it's got to do with:

Quote:

That's because the GetMulti fills an entityCollection which in turn has the following property set to true: DoNotPerformAddIfPresent

I think DISTINCT is generated anyway, unless you have a relation/join specified, or you have a blob fieldin your entity.

In case this is the only option, Is it there a way to assign directly all the fields of an Enitity to a ResultsetFields class? in example: ResultsetFields f = Tracks.Fields()

You may try the following:

ResultsetFields fields = new ResultsetFields(myEntity.Fields.Count);
fields = (ResultsetFields)myEntity.Fields;

Secondly its a way to automatically assign a DataRow to an Entity? Even If I define a TypedList I'll have to do field assignation one by one right?

You can always fetch the DynamicList into a DataReader, and use projection to load the collection. Please check the manual for "using the generated code -> Adapter/SelfServicing -> Fetching DataReaders and projections"

Hi Gabriel

Thanks for this,

            TrackCollection tracks = new TrackCollection();
        [b] tracks.DoNotPerformAddIfPresent = false;[/b]
        

            RelationCollection relations = new RelationCollection
                                               {
                                                   PlaylistEntity.Relations.TrackSetEntityUsingTrackSetLid,
                                                   TrackSetEntity.Relations.TrackSetItemEntityUsingTrackSetLid,
                                                   TrackSetItemEntity.Relations.TrackEntityUsingTrackLid
                                               };

            PredicateExpression filter = new PredicateExpression(PlaylistFields.Guid == Guid);
            SortExpression sort = new SortExpression(new SortClause(TrackSetItemFields.TrackIndex, SortOperator.Ascending));
            tracks.GetMulti(filter, 0, sort, relations);

And the Sql generated it doesnt have the Distinct but inside the TracksCollection I only see one Row instead of the 10 that I get back running the SQL generated directly to the DB... strange...

Thanks.

Thanks Walaa

I was writing my previous message when you posted it .

I'll try your suggestion now...

Ok, just tested this and I'm getting an exception when doing the casting to ResultsetFields, so its a bad solution and the Projection system its far more complex than using dynamic list.

Unable to cast object of type 'SD.LLBLGen.Pro.ORMSupportClasses.EntityFields' to type 'Omnifone.MusicStation.PCClient.Dal.HelperClasses.ResultsetFields'.  

I think LLBL should incoporate the functionality of allowing repeated results in the collection under demand, a parameter in the GetMulti() function would be beautiful.

I've adopted the TypedList approach that will keep the code simplier...

            PlayListTracksTypedList playlistTracks = new PlayListTracksTypedList();
            
            SortExpression sort = new SortExpression(new SortClause(TrackSetItemFields.TrackIndex, SortOperator.Ascending));
            
            PredicateExpression filter = new PredicateExpression(PlaylistFields.Guid == Guid);
            if (!playlistTracks.Fill(0, sort, true, filter, null)) return null;

            
            foreach (PlayListTracksRow row in playlistTracks)
            {
                  //DO Mapping to a new Track entity Object
                  //Add to a Collection 
            }

But this means that each Track Entity will have to be refetched of the DB before updating it since I can't run update the tracks using TypedList right?

Regards

But this means that each Track Entity will have to be refetched of the DB before updating it since I can't run update the tracks using TypedList right?

You don't really need to refetch it. Just set the PK value, and IsNew = false; and Save.

I have a view on db site and I have added view as an entity to llblgen so i have retrieved data from view as a collection with getmulti method. I have read messages that are written before my message and i have set DoNotPerformAddIfPresent property to false. however in the generated sql, there is "distict" keyword.

The code that is written is as follows:

IRelationCollection relations = new RelationCollection(); relations.Add(RelationHelper.CreateCustomRelation(FatKonsimentoListeleVwFields.KonsId, DokIhrEkipmanListeleVwFields.KonsimentoId, "FatKonsimentoListeleVw", "DokIhrEkipmanListeleVw"));

FatKonsimentoListeleVwCollection fatKonsimentoList = new FatKonsimentoListeleVwCollection(); fatKonsimentoList.DoNotPerformAddIfPresent = false; fatKonsimentoList.GetMulti(konsimentoFiltre,0,null,relations);

Note: i produced custom relation to the view as i mentioned in code.

Please help me about avoiding "distinct" keyword in sql statement.

As said on our first reply in this thread, an fetching an EntityCollection assumes unique set of entities.

You'd better map the view to a TypedView rather than an Entity.