Forum Libraries, Components, and Plugins
We are excited to announce that the Xamarin Forums are moving to the new Microsoft Q&A experience. Q&A is the home for technical questions and answers at across all products at Microsoft now including Xamarin!

We encourage you to head over to Microsoft Q&A for .NET for posting new questions and get involved today.

[Announcement] Cascade operations in SQLite-Net Extensions

GuillermoGutierrezGuillermoGutierrez ESMember ✭✭✭

Hello everybody,

I've been quite busy lately and I couldn't improve too much SQLite-Net Extensions in the past few months. Hopefully I've been able to dedicate some time to it and I'm glad to announce the implementation of cascade operations in SQLite-Net Extensions!

By default cascade operations are not enabled for safety's sake. But it can be configured easily to fetch, insert or delete any relationship without any headache.

I've been improving the documentation lately so hopefully it would be self-explanatory, but here are some examples (extracted from the Integration Tests sample project):

public class Customer {
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    public string Name { get; set; }

    [OneToMany(CascadeOperations = CascadeOperation.All)]
    public Order[] Orders { get; set; }
}

[Table("Orders")] // 'Order' is a reserved keyword
public class Order {
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    public float Amount { get; set; }
    public DateTime Date { get; set; }

    [ForeignKey(typeof(Customer))]
    public int CustomerId { get; set; }

    [ManyToOne(CascadeOperations = CascadeOperation.CascadeRead)]
    public Customer Customer { get; set; }
}

As you can see, there's a new property in the relationship attributes named CascadeOperations. This property will allow us to fine-grain configure the cascade operations easily. In this case, we have a Customer that has many Orders. In this case we've enabled all cascade operations (insert, delete and read) for the Orders property of the customer, but we've only enabled cascade read operations for the Customer property of the order. What does this means? Well, we can now insert a Customer in the database and all the related orders will be inserted also. If we delete the customer, all the orders will be deleted on cascade, and fetching a customer will load the orders recursively too. Let's see some code:

var customer = new Customer
{ 
    Name = "John Smith",
    Orders = new []
    {
        new Order { Amount = 25.7f, Date = new DateTime(2014, 5, 15, 11, 30, 15) },
        new Order { Amount = 15.2f, Date = new DateTime(2014, 3, 7, 13, 59, 1) },
        new Order { Amount = 0.5f, Date = new DateTime(2014, 4, 5, 7, 3, 0) },
        new Order { Amount = 106.6f, Date = new DateTime(2014, 7, 20, 21, 20, 24) },
        new Order { Amount = 98f, Date = new DateTime(2014, 02, 1, 22, 31, 7) }
    }
};

conn.InsertWithChildren(customer, recursive: true);

And that's it. All the orders are automatically inserted in the database and all the relationships are set as expected.

Let's move to a more complex scenario. Let's suppose that we're implementing a Twitter client. We will have a many-to-many relationship between the users and their followers:

public class TwitterUser {
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    public string Name { get; set; }

    [ManyToMany(typeof(FollowerLeaderRelationshipTable), "LeaderId", "Followers",
        CascadeOperations = CascadeOperation.CascadeRead | CascadeOperation.CascadeInsert)]
    public List<TwitterUser> FollowingUsers { get; set; }

    // ReadOnly is required because we're not specifying the followers manually, but want to obtain them from database
    [ManyToMany(typeof(FollowerLeaderRelationshipTable), "FollowerId", "FollowingUsers",
        CascadeOperations = CascadeOperation.CascadeRead, ReadOnly = true)]
    public List<TwitterUser> Followers { get; set; }
}

// Intermediate class, not used directly anywhere in the code, only in ManyToMany attributes and table creation
public class FollowerLeaderRelationshipTable {
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    public int LeaderId { get; set; }
    public int FollowerId { get; set; }
}

In this scenario we have the relationship FollowingUsers that represent the users that this user is following. And we have the relationship Followers that represents the users that are following the current user; this relationship is the inverse of the previous one. As we're not going to insert the Followers property manually, we're going to mark it as read-only by setting the ReadOnly flag to true. On the other hand, we want to load this property on cascade read operations, so let´s add the CascadeRead operation.

We don't want to delete users on cascade, so we'll only set CascadeOperations to CascadeRead and CascadeInsert on FollowingUsers property, so any user that doesn't exist in the database could be inserted recursively, and of course, read recursively.

Let's see some code to see how this works:

        var john = new TwitterUser { Name = "John" };
        var thomas = new TwitterUser { Name = "Thomas" };
        var will = new TwitterUser { Name = "Will" };
        var claire = new TwitterUser { Name = "Claire" };
        var jaime = new TwitterUser { Name = "Jaime" };
        var mark = new TwitterUser { Name = "Mark" };
        var martha = new TwitterUser { Name = "Martha" };
        var anthony = new TwitterUser { Name = "anthony" };
        var peter = new TwitterUser { Name = "Peter" };

        john.FollowingUsers = new List<TwitterUser>{ peter, thomas };
        thomas.FollowingUsers = new List<TwitterUser>{ john };
        will.FollowingUsers = new List<TwitterUser>{ claire };
        claire.FollowingUsers = new List<TwitterUser>{ will };
        jaime.FollowingUsers = new List<TwitterUser>{ peter, thomas, mark };
        mark.FollowingUsers = new List<TwitterUser>();
        martha.FollowingUsers = new List<TwitterUser>{ anthony };
        anthony.FollowingUsers = new List<TwitterUser>{ peter };
        peter.FollowingUsers = new List<TwitterUser>{ martha };

        var allUsers = new []{ john, thomas, will, claire, jaime, mark, martha, anthony, peter };

        // Inserts all the objects in the database recursively
        //conn.InsertAllWithChildren(allUsers, recursive: true);

        // In fact we only need to insert Jaime and Claire, the other users are contained in these trees
        // and will be inserted recursively
        conn.InsertAllWithChildren(new []{ jaime, claire }, recursive: true);

        // Fetch the user 'Thomas' and all the related entities recursively
        var obtainedThomas = conn.GetWithChildren<TwitterUser>(thomas.Id, recursive: true);

In this sample, obtainedThomas will contain the complete entity tree starting from 'Thomas' and loading all the users that are related to this user, recursively.

SQLite-Net Extensions will ensure that relationship loops and inverse relationships are solved correctly and will ensure that two entities of the same type with the same primary key are in fact the same object, so it will preserve integral reference inside the returned objects. The same applies for inserted objects; SQLite-Net Extensions will ensure that every object in the entity tree is inserted once and only once, and will solve loops correctly.

If you want to see more samples, feel free to check out and run the Integration Tests project, in the same Git repository.

I know some of you had problems regarding the installation. I'm already working on publishing the packages to NuGet, that will hopefully solve all the issues related to methods not found or attributes not being subclass of Attribute class.

As always, any feedback is welcome and highly appreciated.

Kind regards,
Guillermo

PS: Sorry for the long post

Posts

  • GuillermoGutierrezGuillermoGutierrez ESMember ✭✭✭

    I just deployed the SQLite-Net Extensions NuGet package.

    Kind regards,
    Guillermo

  • GuillermoGutierrezGuillermoGutierrez ESMember ✭✭✭

    MvvmCross version of the library is now available as a NuGet package.

    Enjoy!

  • thedigitalseanthedigitalsean USUniversity ✭✭
    edited July 2014

    Many thanks Guillermo! I just attempted to switch my project over from using the manually added binary DLLs I downloaded from the website with the nuget PCL package. I am getting the following error:

    Attempting to resolve dependency 'Newtonsoft.Json (≥ 6.0.3)'.
    Attempting to resolve dependency 'SQLite.Net-PCL (≥ 2.3.0)'.
    Installing 'Newtonsoft.Json 6.0.3'.
    Successfully installed 'Newtonsoft.Json 6.0.3'.
    Installing 'SQLiteNetExtensions 1.1.1'.
    Successfully installed 'SQLiteNetExtensions 1.1.1'.
    Adding 'Newtonsoft.Json 6.0.3' to MyProject.Core.
    Uninstalling 'Newtonsoft.Json 6.0.3'.
    Successfully uninstalled 'Newtonsoft.Json 6.0.3'.
    Install failed. Rolling back...
    Could not install package 'Newtonsoft.Json 6.0.3'. You are trying to install this package into a project that targets 'portable-net45+wp80+MonoAndroid10+MonoTouch10', but the package does not contain any assembly references or content files that are compatible with that framework. For more information, contact the package author.

    It seems there is some sort of incompatability with Newtonsoft.Json and my targets (.Net Framework 4.5 and higher, Windows Phone 8, Xamarin.Android, and Xamarin.iOS.) But I believe this is a pretty standard target set. Suggestions?

  • GuillermoGutierrezGuillermoGutierrez ESMember ✭✭✭

    Hmmm, Json.Net is used only for TextBlob attributes. That makes me wonder if it would make sense to be in a different NuGet package to remove that dependency.

    Anyway, I usually work with Profile78 or Profile158 and had no issues with Json.Net. What profile are you using?

  • thedigitalseanthedigitalsean USUniversity ✭✭

    Thanks for the response. I got it to work but have no idea why.

    I've just started with Xamarin so I'm still finding my way around Visual Studio 2013. I couldn't find where to set the profile in Visual Studio so I closed Visual Studio, opened Xamarin Studio and easily found where to change from Profile49 to Profile78. I then closed XStudio and re-opened VStudio and tried again to add the Nuget packages but still got the same error with installing NewtonSoft Json.NET.

    Out of curiosity I decided to try adding the Nuget packages in XStudio instead, and everything installed fine. I closed Xamarin Studio and went back to Visual Studio and everything compiles and runs fine...

    So anyway changing to Profile78 in combination with installing from Xamarin Studio seemed to do the trick.

  • MattButlerMattButler USUniversity ✭✭

    Guillermo,

    Nice work with Extensions! Enjoying using it. I'm having an issue wonder if you can point me in the right direction. Related to ManyToMany. I'll just show you my code so it might benefit someone else.

        [Table("Project")]
        public class Project : Attribute
        {
            [PrimaryKey, Column("ProjectId")]
            public string ProjectId { get; set; }
    
            [Column("Desc")]
            public string Desc { get; set; }
    
            #region Many To Many
    
            [ManyToMany(typeof(ProjectActivityDependencies))]
            public List<Activity> Activities { get; set; }
    
            [ManyToMany(typeof(ProjectBillTypeDependencies))]
            public List<BillType> BillTypes { get; set; }
    
            [ManyToMany(typeof(ProjectPayCodeDependencies))]
            public List<PayCode> PayCodes { get; set; }
    
            #endregion Many To Many
        }
    
    
        [Table("ProjectActivityDependencies")]
        public class ProjectActivityDependencies
        {
            [ForeignKey(typeof(Project))]
            public string ProjectId { get; set; }
    
            [ForeignKey(typeof(Activity))]
            public string ActivityId { get; set; }
        }
    
        [Table("Activity")]
        public class Activity : Attribute
        {
            [PrimaryKey, Column("ActivityId")]
            public string ActivityId { get; set; }
    
            [Column("Desc")]
            public string Desc { get; set; }
    
            [ManyToMany(typeof(ProjectActivityDependencies))]
            public List<Project> Projects { get; set; }
    
    
        }
    

    I was explicitly inserting the ProjectActivityDependencies relationship previously and it worked nicely, but we changed our API and I wanted to give InsertWithChildren and go. The other ManyToMany relationships shown below are setup exactly the same way.

    I've attached an image that shows the InsertWithChildren statement that shows my Project object filled with a child object and these children objects already exist in the db. Couple things I've thought of:

    • Maybe the Matrix table can only have int PKs?
    • I need to add some CascadeOperations = CascadeOperation.CascadeRead | CascadeOperation.CascadeInsert to my table definitions.
    • I dunno...

    The insert is happening without an exception, but nothing is showing up in the child relationships and when I readwithchildren. They all appear as null.

    Any help MUCH appreciated!

  • MattButlerMattButler USUniversity ✭✭

    Update, tried this, No joy...

        [Table("Project")]
        public class Project : Attribute
        {
            [PrimaryKey, Column("ProjectId")]
            public string ProjectId { get; set; }
    
            [Column("Desc")]
            public string Desc { get; set; }
    
            #region Foreign Keys
    
            [ManyToMany(typeof(ProjectActivityDependencies), CascadeOperations = CascadeOperation.CascadeInsert | CascadeOperation.CascadeRead)]
            public List<Activity> Activities { get; set; }
    
            [ManyToMany(typeof(ProjectBillTypeDependencies), CascadeOperations = CascadeOperation.CascadeInsert | CascadeOperation.CascadeRead)]
            public List<BillType> BillTypes { get; set; }
    
            [ManyToMany(typeof(ProjectPayCodeDependencies), CascadeOperations = CascadeOperation.CascadeInsert | CascadeOperation.CascadeRead)]
            public List<PayCode> PayCodes { get; set; }
    
            #endregion Foreign Keys
    
        }
    
            public bool InsertProjectWithChildren(Project szProject)
            {
                try
                {
                    try
                    {
                        JXDB.InsertWithChildren(szProject, recursive: true);
                        return true;
                    }
                    catch (Exception ex)
                    {
                        return false;
                    }
    
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
    
  • GuillermoGutierrezGuillermoGutierrez ESMember ✭✭✭

    Hey @ATXMatt‌ ,

    I think that it's not strictly needed, but I'd recommend you to add an AutoIncrement primary key to ProjectActivityDependencies class.

    Another thing that may (or may not) be related. Why are is Project class inheriting from System.Attribute?

    The code seems to be right, depending on how you're inserting and reading the objects, probably this may be more handy:

    Try setting the CascadeOperations of the main class to All:

    [ManyToMany(typeof(ProjectActivityDependencies), CascadeOperations = CascadeOperation.All)]
    public List<Activity> Activities { get; set; }
    

    And the inverse relationship to read only to avoid issues with the insert (as long as you are not editing this one directly):

    [ManyToMany(typeof(ProjectActivityDependencies) 
            CascadeOperations = CascadeOperation.CascadeRead, ReadOnly = true)]
    public List<Project> Projects { get; set; }
    

    If this doesn't work please post a sample that I can try on my computer.

    Kind regards.

  • MattButlerMattButler USUniversity ✭✭
    edited October 2014

    Hey Guillermo,

    Yea, no functionar, unfortunately. Sorry, for the delay in response, iOS8 carnage with our previous app. Now fixed, back to new .Forms app. :)

    1. Put the auto increment PK on the ProjectActivityDependency

      [Table("ProjectActivityDependencies")]
      public class ProjectActivityDependencies
      {
          [AutoIncrement, PrimaryKey]
          public int Id { get; set; }
      
          [ForeignKey(typeof(Project))]
          public string ProjectId { get; set; }
      
          [ForeignKey(typeof(Activity))]
          public string ActivityId { get; set; }
      }
      
    2. System.Attribute was a left over from Entity Framework definition of these classed, I've removed all that.

    3. Set the CascadeOperations = CascadeOperation.All in the main class

      [Table("Project")]
      public class Project 
      {
          [PrimaryKey, Column("ProjectId")]
          public string ProjectId { get; set; }
      
          [Column("Desc")]
          public string Desc { get; set; }
      
          #region Foreign Keys
      
          [ManyToMany(typeof(ProjectActivityDependencies), CascadeOperations = CascadeOperation.All)]
          public List<Activity> Activities { get; set; }
      
          [ManyToMany(typeof(ProjectBillTypeDependencies), CascadeOperations = CascadeOperation.All)]
          public List<BillType> BillTypes { get; set; }
      
          [ManyToMany(typeof(ProjectPayCodeDependencies), CascadeOperations = CascadeOperation.All)]
          public List<PayCode> PayCodes { get; set; }
      
          #endregion Foreign Keys
      }
      
    4. Set the CascadeOperations = CascadeOperation.CascadeRead on the child class

       public class Activity
                      {
                          [PrimaryKey, Column("ActivityId")]
                          public string ActivityId { get; set; }
      
                          [Column("Desc")]
                          public string Desc { get; set; }
      
                          [ManyToMany(typeof(ProjectActivityDependencies), CascadeOperations = CascadeOperation.CascadeRead, ReadOnly = true)]
                          public List<Project> Projects { get; set; }
                      }
      

    One thing I've noticed is that the child classes, that I'm filing with data before this operation, are having their data deleted when I do the InsertWithChildren(Project, recursive: true);

    Hrm, posting a sample going to be tough because this is getting data from a WCF end point the data that is being inserted is inside a sync structure based class and it gets data from the SQLiteDB, so kinda tough. Up for a GoToMeeting? I'd be happy to post a video of our session. If you are up for that, let me know. [email protected] If not, let me scratch my head a bit about how to isolate a sample.

    Here is the code that does the insert. I have both the individual and list version of the code.

           public override bool UpdateSQLiteData(List<Project> szUpdateList)
            {
                try
                {
                    List<Project> InsertList = new List<Project>();
                    foreach (ProjectDependencies pd in UpdateList)
                    {
                        Project pj = new Project { ProjectId = pd.project_id, Desc = pd.project_name };
                        List<Activity> ListActivity = new List<Activity>();
                        foreach (string act in pd.codes_tasks)
                        {
                            ListActivity.Add(SQLiteController.Instance.GetActivityByName(act));
                        }
                        pj.Activities = ListActivity;
                        List<PayCode> ListPayCodes = new List<PayCode>();
                        foreach (string pay in pd.codes_pay_types)
                        {
                            ListPayCodes.Add(SQLiteController.Instance.GetPayCodeByName(pay));
                        }
                        pj.PayCodes = ListPayCodes;
                        List<BillType> ListBillType = new List<BillType>();
                        foreach (string pay in pd.codes_bill_types)
                        {
                            ListBillType.Add(SQLiteController.Instance.GetBillTypeByName(pay));
                        }
                        pj.BillTypes = ListBillType;
                        //InsertList.Add(pj);
                        SQLiteController.Instance.InsertProjectWithChildren(pj);
                    }
                    //SQLiteController.Instance.InsertProjectsWithChildren(InsertList);
                    return true;
                }
                catch (Exception)
                {
                    return false;
                }
            }
    
  • MattButlerMattButler USUniversity ✭✭

    Hold the line, my issue might be a data issue, will be back shortly...

  • MattButlerMattButler USUniversity ✭✭

    Well, when you have 265 records coming back from the WCF end point and the PK and Description field of the data have the same values, sometimes you can get confused about which is which. So, yes, I had a data issue. However, I ran the insert with the a couple different ways just to test.

    First, I removed the AutoIncrement PK from the ManyToMany matrix table.

        [Table("ProjectActivityDependencies")]
        public class ProjectActivityDependencies
        {
            //[AutoIncrement, PrimaryKey]
            //public int Id { get; set; }
    
            [ForeignKey(typeof(Project))]
            public string ProjectId { get; set; }
    
            [ForeignKey(typeof(Activity))]
            public string ActivityId { get; set; }
        }
    

    If I tried to insert each of my projects with children using the InsertOrReplaceWithChildren(szProject, recursive: true); I would get an exception, but if I built up a list and used InsertOrReplaceAllWithChildren(szProjectList); it was like BUTTA Baby... BUTTA :)

    Ok, so I can't delete my previous posts, hope this helps some folks. Thanks for being responsive @GuillermoGutierrez and for building SQLite.NET.Extensions!

  • GuillermoGutierrezGuillermoGutierrez ESMember ✭✭✭

    @ATXMatt‌ I'm glad that you solved it :)

    Kind regards!

  • AlejandroRuiz.7150AlejandroRuiz.7150 USMember
    edited October 2014

    Hi @GuillermoGutierrez‌ i have a question is possible to make double Foreign Key to the same table ?
    schema example

    --Table1--
    Id
    Name

    --Table2--
    Id
    Name
    IdTable11
    IdTable12

  • GuillermoGutierrezGuillermoGutierrez ESMember ✭✭✭
    edited October 2014

    @AlejandroRuiz.7150‌ yes, it's possible, but automatic discovery of foreign keys and inverse relarionships are not supported in that case. You have to specify the foreign key property and the inverse property names in the relarionship attribute.

    For example:

    [ManyToOne("TeacherId", "Students")]
    public Teacher Teacher { get; set; }
    
    [ManyToOne("TutorId", "Students")]
    public Teacher Teacher { get; set; }
    

    Hope it helps.

  • [Table("Events")] public class Event { [PrimaryKey,AutoIncrement] public int Id_Primary_Key { get; set; }

    public string Name { get; set; }
    
    public Guid id { get; set; }
    
    [OneToMany(CascadeOperations = CascadeOperation.All)]
    public List<Track> Tracks { get; set; }
    

    }
    [Table("Tracks")] public class Track { [PrimaryKey, AutoIncrement] public int Id_Primary_Key { get; set; }

    public Guid id { get; set; }
    
    public int TrackNo { get; set; }
    
    [ForeignKey(typeof(Event))]
    public int eventId{get; set; }
    
    [ManyToOne(CascadeOperations = CascadeOperation.CascadeRead)]
    public Event eventObj {get;set;}
    

    }
    Hi, i am new in this mobile application development. I was reading your blog and learn how to insert relational data into SQLite database. Event and Track has one to many and vice versa relationship. I wrote this code for inserting the instance of the data like this:

    var sqliteFilename = "EventTrack.db3"; string libraryPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal); var path = Path.Combine(libraryPath, sqliteFilename); var conn = new SQLiteConnection(new SQLitePlatformAndroid(), path, false, null);

    conn.CreateTable(); conn.CreateTable();

    var event1 = new Event{id = Guid.NewGuid(), Name = "kkk"};

    var track1 = new Track {id = Guid.NewGuid(),TrackNo = 2};

    conn.Insert(event1); conn.Insert(track1);

    event1.Tracks = new List{track1};

    conn.UpdateWithChildren(event1);

    All these are working fine. But i dont know how to check from database that data has been inserted successfully or not. I am using visual studio 2013 for android development. I am trying to debug using my android device but not exactly understood what is happening to database. how can i check table is updating or not? any help would be appreciated

  • GuillermoGutierrezGuillermoGutierrez ESMember ✭✭✭
    edited October 2014

    @MohammadMuazzemHussain You can browse your SQLite file 'EventTrack.db3' in your android device or emulator with any SQLite viewer (I use the firefox plugin and works flawlessly). Or you can browse the database programmatically.

  • GuillermoGutierrezGuillermoGutierrez ESMember ✭✭✭

    Just a quick update. SQLite-Net Extensions now provides asynchronous operations extending SQLiteAsyncConnection from SQLite.Net.Async PCL project. Add this nuget package to your project to access Async versions of all SQLite-Net Extensions methods.

    Synchronous example:

    conn.InsertWithChildren(customer);
    

    Asynchronous equivalent:

    await conn.InsertWithChildrenAsync(customer);
    

    More information in the project page and the integration tests project.

  • NatxoNatxoNatxoNatxo ESMember
    edited February 2015

    Hi @GuillermoGutierrez , All,

    I am having problems with [OneToOne] relationship.

            public class Room
            {
                [PrimaryKey]
                [AutoIncrement]
                public int Id { get; set; }
    
                [JsonProperty("description")]
                [OneToOne]
                public RoomDescription RoomDescription { get; set; }
            }
    
            public class RoomDescription
            {
                [PrimaryKey]
                [AutoIncrement]
                public int Id { get; set; }
    
                [ForeignKey(typeof(Room))]
                public int RoomId { get; set; }
    
                [JsonProperty("de")]
                [OneToMany(CascadeOperations = CascadeOperation.CascadeInsert)]
                public List<RoomDescFull> RoomDescLanguageGerman { get; set; }
    
                [JsonProperty("en")]
                [OneToMany(CascadeOperations = CascadeOperation.CascadeInsert)]
                public List<RoomDescFull> RoomDescLanguageEnglish { get; set; }
    
            }
    
            public class RoomDescFull
            {
                [PrimaryKey]
                [AutoIncrement]
                public int Id { get; set; }
                [JsonProperty("title")]
                public string Title { get; set; }
                [JsonProperty("description")]
                public string Description { get; set; }
    
                [ForeignKey(typeof(RoomDescription))]
                public int RoomDescriptionId { get; set; }
    
                [ManyToOne(CascadeOperations = CascadeOperation.All)]
                public RoomDescription RoomDescription{ get; set; }
            }
    

    the rooms in 'roomList' have good defined the children, but the rooms in 'lr' have RoomDescription as null.
    I have tried also with InsertAllWithChildren.

        Conn.InsertOrReplaceAllWithChildren (roomList,true);
        List<Room> lr = Conn.GetAllWithChildren<Room> (null, true);
    

    Thankyou in advance, gracias.

  • GuillermoGutierrezGuillermoGutierrez ESMember ✭✭✭

    @Natxo You haven't defined cascade operations for the RoomDescription property of the Room entity, so you're only loading the first 'layer' of children. If your entity model doesn't get much more complicated you can try adding CascadeOperation.All to RoomDescription, RoomDescLanguageGerman and RoomDescLanguageGerman. Otherwise you'll have to manually insert and load their children without recursive operations.

  • NatxoNatxoNatxoNatxo ESMember

    @GuillermoGutierrez Thanks for your quick response.
    I ended up removing the CascadeOperation by following this example, after trying many combinations to get it to work. https://bitbucket.org/twincoders/sqlite-net-extensions/src/2ef301a49ac4327480323d2a37e6cde9908eefba/IntegrationTests.Touch/Tests/OneToOneTests.cs?at=master

    Changing all relations to CascadeOperations.All works fine.
    Thanks a lot !

  • SagarPanwalaSagarPanwala USMember ✭✭✭

    How can I cascade delete data from all table? I see above links but didn't succeed. Only one table data is deleted.

  • GuillermoGutierrezGuillermoGutierrez ESMember ✭✭✭

    @SagarPanwala with the information that you provide I can only 'guess' what's wrong. I guess that either you didn't define the CascadeOperation property to allow cascade deletion or you didn't set recursive parameter of the delete call to true.

  • SagarPanwalaSagarPanwala USMember ✭✭✭
    edited April 2015

    @GuillermoGutierrez here is my code,

    public class FlyEntity
    {
    [PrimaryKey]
    public int EntityId { get; set; }
    public string EntityName { get; set; }
    public int SortOrder { get; set; }

      [OneToMany(CascadeOperations = CascadeOperation.All)] 
      public List<OpenTicketData> TicketData{ get; set; }
    }
    

    public class OpenTicketData
    {
    [PrimaryKey]
    public int Id { get; set; }

      [ForeignKey(typeof(FlyEntity))]
      public int EntityId { get; set; }
    
      public DateTime Date { get; set; }
    
      public DateTime LastOrderDate { get; set; }
    
      public string TicketNumber { get; set; }
    
      public decimal RemainingAmount { get; set; }
    

    }

    Below is the sql

    Entity entity = conn.GetWithChildren(entityId);
    // Entity entity = conn.Get(entityId);
    conn.Delete(entity.EntityId,true);

  • GuillermoGutierrezGuillermoGutierrez ESMember ✭✭✭

    Try this:

    conn.Delete(entity, true);
    

    Take into account that SQLite-Net Extensions won't go into database to search for relationships, they have to be already loaded in memory.

  • sujith_gowdasujith_gowda USMember

    Hello Guillermo

    I am having problems with UpdateChildrenAsync where the secondary table is not getting updated with any of the changes.

    Here's my structure:

        public class User
        {
            [PrimaryKey, AutoIncrement]
            public int Id { get; set; }
        public string FirstName {get; set;}
        public string LastName {get; set;}
    
            [OneToMany(CascadeOperations = CascadeOperation.All)]
            public List<Address> Addresses { get; set; }
    
        } 
    
        public class Address
        {
            [PrimaryKey, AutoIncrement]
            public int Id { get; set; }
        public string Address1 {get; set;}
        public string Address2 {get; set;}
    
            [ForeignKey(typeof(Person))]
             public int? UserID { get; set; }
    
             [ManyToOne(CascadeOperations = CascadeOperation.CascadeRead)]
             public User user { get; set; }
    
        } 
    

    InsertWithChildrenAsync(user).ConfigureAwait(false); -> Works fine where both user and Address tables are populated. But while updating the tables using UpdateWithChildrenAsync(user).ConfigureAwait(false); - is NOT working.

    Any idea why? Are there any code snippet/project I could refer to?

    Thanks

  • GuillermoGutierrezGuillermoGutierrez ESMember ✭✭✭

    @sujith_gowda there's no recursive version of UpdateWithChildren method. You can either call InsertOrUpdateWithChildren to replace all objects or you can keep track of the modified entities by yourself and call the update method manually.

  • jbravobrjbravobr BRMember ✭✭

    Hi @GuillermoGutierrez !!

    I'm have problems with the use of CascadeOperations,

    /Users/Rodrigo/Projects/Mais/Mais/Models/Enquete.cs(4,4): Error CS0117: SQLiteNetExtensions.Attributes.OneToOneAttribute' does not contain a definition forCascadeOperation' (CS0117) (Mais)

    Here is a class

    `
    using SQLiteNetExtensions.Attributes;
    using SQLite.Net.Attributes;
    using System.Collections.Generic;

    namespace Mais
    {
    [Table("Pergunta")]
    public class Pergunta
    {
    [PrimaryKey,AutoIncrement]
    public int Id { get; set; }

        public string TextoPergunta { get; set; }
    
        [OneToMany(CascadeOperation = CascadeOperation.CascadeInsert | CascadeOperation.CascadeRead)]
        public List<Resposta> Respostas { get; set; }
    }
    

    }`

    What's wrong ???

    Thanks in advanced.

  • GuillermoGutierrezGuillermoGutierrez ESMember ✭✭✭

    @jbravobr CascadeOperation is the name of the enum. To set the property you have to use CascadeOperations. So change this:

    [OneToMany(CascadeOperation = CascadeOperation.CascadeInsert | CascadeOperation.CascadeRead)]
    

    to this:

    [OneToMany(CascadeOperations = CascadeOperation.CascadeInsert | CascadeOperation.CascadeRead)]
    

    Un saludo!

  • jbravobrjbravobr BRMember ✭✭

    @GuillermoGutierrez thanks!!!!

  • mstrandmstrand SEMember

    Hi,

    Testing out some of your examples but cant get the following to work:

    conn.Delete(customer, recursive: true);

    Just throws an error stating that "The best overload for 'Delete' does not have a parameter named 'recursive'"

    What am I missing?

    Followed the instructions at https://bitbucket.org/twincoders/sqlite-net-extensions using the SQLiteNetExtensions NuGet package in a Win Phone 8.1 RT project.

    //Thanks

  • GuillermoGutierrezGuillermoGutierrez ESMember ✭✭✭

    @MartinStrand Did you add using SQLiteNetExtensions.Extensions; to the file? If you did, maybe you have more than one referenced SQLite.Net library. I think that Windows Phone projects don't use the SQLite.Net PCL NuGet package; if that's your case, you may need to copy the SQLite-Net Extensions sources to your project to force SQLite-Net Extensions to link against your SQLite.Net library.

  • jvinhitjvinhit USMember

    Last night , i have create example app xamarin form with sqlite-net extension but it's error :wink:

  • mstrandmstrand SEMember

    @GuillermoGutierrez I was using a SQLiteAsyncConnection, changed it to a SQLiteConnection so now its working, Thanks!

  • daizdaiz USMember
    edited May 2016

    Hi. I am having a problem with creating table. Hope you can help me.

    public class Program
    {
        [PrimaryKey, AutoIncrement]
        public int ProgramId { get; set; }
        public string Name { get; set; }
        [OneToMany()]
        public List<Training> Trainings { get; set; }
    }
    
    public class Training
    {
        [PrimaryKey, AutoIncrement]
        public int TrainingId { get; set; }
    
        [ForeignKey(typeof(Program))]
        public int ProgramId { get; set; }
    
        [ManyToOne("ProgramId")]
        public Program Program { get; set; }
    
    }
    

    When i am trying to create a table with
    _db.CreateTable<Program>();
    it throws Exception with message that "Program know nothing about Training".

    What am I doing wrong?
    Thanks

  • hagnrhagnr USMember ✭✭

    @GuillermoGutierrez

    I lose the foreign key when I update children in object with foreign key

    OBJECTS

    `public class Object1
    {
    [PrimaryKey, AutoIncrement]
    public int id { get; set; }

    [OneToMany(CascadeOperations = CascadeOperation.All)]
    public List ListObject2 { get; set; }

    [OneToMany(CascadeOperations = CascadeOperation.All)]
    public List<Object3> ListObject3 { get; set; }
    

    }`

    `public class Object2
    {
    [PrimaryKey, AutoIncrement]
    public int id { get; set; }

    [ForeignKey(typeof(Object1))]
    public int object1_id { get; set; }
    [ManyToOne(CascadeOperations = CascadeOperation.All)]
    public Object1 Object1 { get; set; }
    

    [OneToMany(CascadeOperations = CascadeOperation.All)]
    public List ListObject3 { get; set; }
    }`

    `public class Object3
    {
    [PrimaryKey, AutoIncrement]
    public int id { get; set }
    public string name {get; set;}

    [ForeignKey(typeof(Object2))]
    public int object2_id { get; set; }
    [ManyToOne(CascadeOperations = CascadeOperation.All)]
    public Object2 Object2 { get; set; }

    [ForeignKey(typeof(Object1))]
    public int object1_id { get; set; }
    [ManyToOne(CascadeOperations = CascadeOperation.All)]
    public Object1 Object1 { get; set; }
    

    }`

    this update works:
    Object1.ListObject2 = Object2List;
    connection.UpdateWithChildren(Object1);
    then I do
    Object2.ListObject3 = Object3List;
    connection.UpdateWithChildren(Object2);
    When i update object2 with children, Object2.object1_id is 0, I lose the foreign key with Object1.

    Any idea? Whats is my problem? What 's the error?

  • HelloWorldThereHelloWorldThere USMember ✭✭
  • begolozbegoloz Member

    Hey @GuillermoGutierrez I'm having the same problem than @MartinStrand

    conn.Delete(customer, recursive: true) throws an error stating that "The best overload for 'Delete' does not have a parameter named 'recursive', but my methods do require using a SQLiteAsyncConnection.

    I installed sql-net-pcl, SQLiteExtensions and SQLiteExtensions.Async, but whenever I try adding :

    using SQLiteNetExtensionsAsync;
    using SQLiteNetExtensions.Extensions;

    tells me the directives are unnecessary.

    Only the reference to SQLite is being used and also SQLiteNetExtensions.Attributes but in another class where my tables are declared with the foreign keys and the cascade operations, and seems to be compiling with no error.

    what could be wrong in my case? Im building a forms application for both android and ios.

  • yurylankovskiyyurylankovskiy Member ✭✭

    Hey!

    I'm having an issue retrieving many-to-many relationships. I have the following structure,

    public class Story {
        [PrimaryKey, AutoIncrement]
        public int Id { get; set; }
        public string Name { get; set; }
        // foreground
        [ManyToMany(typeof(StoryForeground), "StoryId", "Stories", CascadeOperations = CascadeOperation.All)]
        public List<ForegroundStored> Foregrounds { get; set; }
    }
    
    public class StoryForeground
    {
        public int ForegroundStoredId { get; set; }
        public int StoryId { get; set; }
    }
    
    public class ForegroundStored
    {
        [PrimaryKey, AutoIncrement]
        public int Id { get; set; }
    
        [ManyToMany(typeof(StoryForeground), "ForegroundStoredId", "Foregrounds", CascadeOperations = CascadeOperation.CascadeRead, ReadOnly = true)]
        public List<Story> Stories { get; set; }
    }
    

    To store, I use the SQLiteExtensions,

    var conn = await GetDatabaseConnection<Story>().ConfigureAwait(false);
    await conn.InsertWithChildrenAsync(story).ConfigureAwait(false);
    

    To retrieve,

    var conn = await GetDatabaseConnection<Story>().ConfigureAwait(false);
                return await AttemptAndRetry(async () => await conn.GetAllWithChildrenAsync<Story>(recursive: true).ConfigureAwait(false));
    

    When I store, I see tables Story, ForegroundStored and StoryForeground have appropriate values in SQLite database. However, when I retrieve Story list, my Foregrounds parameter in Story class is null.

    What am I doing wrong?

Sign In or Register to comment.