ManyToOne - Get filled objects

[Marcelo José Amador Filho]

Hi, I have two tables:

public class Inventory
    {
        [PrimaryKey, AutoIncrement]
        public int LocalId { get; set; }

        public int InventoryId { get; set; }
        public DateTime CloseDate { get; set; }
        public string InventoryType { get; set; }
        public DateTime LastUpdated { get; set; }
        public bool IsSuppressed { get; set; }
        public bool DeletionConfirmed { get; set; }

        [ForeignKey(typeof(Store))]
        public int StoreId { get; set; }
        [ManyToOne]
        public Store Store { get; set; }

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

And

    public class Store
    {
        [PrimaryKey, AutoIncrement]
        public int LocalId { get; set; }

        public int StoreID { get; set; }
        public string SID { get; set; }
        public string Name { get; set; }

        [OneToMany(CascadeOperations = CascadeOperation.All)]
        public List<Inventory> Inventories { get; set; }
        [OneToMany(CascadeOperations = CascadeOperation.All)]
        public List<Category> Categories { get; set; }
        [OneToMany(CascadeOperations = CascadeOperation.All)]
        public List<Item> Items { get; set; }
        [OneToMany(CascadeOperations = CascadeOperation.All)]
        public List<Location> Locations { get; set; }
        [OneToMany(CascadeOperations = CascadeOperation.All)]
        public List<InventoryGroup> InventoryGroups { get; set; }
    }

When I try to get a Inventory by id, I'm doing this:

public Inventory GetByIdAndStore(int inventoryId, int storeId)
        {
            Inventory inventory = _connection.Table<Inventory>().Where(c => c.InventoryId == inventoryId && c.StoreId == storeId).FirstOrDefault();
            if (inventory != null)
            {
                if (inventory.Countsheets == null) inventory.Countsheets = new List<Countsheet>();
            }
            return inventory;
        }

The Store object inside Inventory class is coming null. How can I fill this property without using a new getStoreById?

[ManyToOne]
public Store Store { get; set; }

[Martin Kuckert]

Countsheets is also always null. You have to use one of the extension methods like GetAllWithChildren to resolve the relationship attributes. The access using Table<> is SQLites method not respecting any relationships at all.

[Marcelo José Amador Filho]

I believe I need to use the Query like this example right?

var result = conn.Query<MeasurementInstanceModel>(
    "SELECT * " +
    "FROM MeasurementInstanceModel AS it " +
    "JOIN MeasurementSubjectModel AS sb " +
    "ON it.MeasurementSubjectId == sb.Id " +
    "WHERE sb.Name == ?", avariable);

[Marcelo José Amador Filho]

This is the way that worked for me.

public Inventory GetByIdAndStore(int inventoryId, int storeId)
        {
            var inventory = _connection.Query<Inventory>(
                "SELECT * FROM Inventory i"
                + " WHERE i.InventoryId = " + inventoryId.ToString()
                + " AND i.StoreId = " + storeId.ToString()
                ).FirstOrDefault();
            if (inventory != null)
            {
                inventory.Store = _connection.Query<Store>(
                    "SELECT * FROM Store s"
                    + " WHERE s.StoreID = " + inventory.StoreId.ToString()
                    ).FirstOrDefault();
                inventory.Countsheets = _connection.Query<Countsheet>(
                    "SELECT * FROM Countsheet c"
                    + " WHERE c.InventoryID = " + inventoryId.ToString()
                    + " AND c.StoreID = " + storeId.ToString()
                    ).ToList();
                if (inventory.Countsheets == null) inventory.Countsheets = new List<Countsheet>();
            }
            return inventory;
        }

I just don't wanted to go to database so many times. Does anyone else have another suggestion?

Thank you

[Guillermo Gutiérrez]

You're using plain sqlite-net methods. To automatically fetch relationships you have to use SQLite.Net Extensions read methods, defined in the ReadOperations file. You have to import the SQLiteNetExtensions.Extensions namespace and fetch them with any of the *WithChildren method.

Alternatively, you can use any sqlite-net method and then load relationships with GetChildren method (that is what most convenience methods do anyway):

var inventory = _connection.Query<Inventory>(
            "SELECT * FROM Inventory i"
            + " WHERE i.InventoryId = " + inventoryId.ToString()
            + " AND i.StoreId = " + storeId.ToString()
        ).FirstOrDefault();

_connection.GetChildren(inventory);

[Marcelo José Amador Filho]

And how would be if the var inventory received a List<Inventory>? Do I need to write a foreach and use the GetChildren for each one of them?

Thank you

[Guillermo Gutiérrez]

Yes.

Unless you use any *WIthChildren read method: GetAllWithChildren with a filter parameter for a list, or GetWithChildren or FindWithChildren for a single element. For example, your code can be translated to something like this:

var inventoryList = _connection.GetAllWithChildren<Inventory>(i => i.InventoryId == inventoryId && i.StoreId == storeId);

[Marcelo José Amador Filho]

If I need to use JOIN, I have to use the _connection.Query<object> right? I saw in other posts that SQLite does not support Linq.

[Guillermo Gutiérrez]

Correct. You can't JOIN using LINQ on sqlite-net

[Marcelo José Amador Filho]

When I use the GetChildren, there are some properties coming null. Because of that, I need to check and force another get if is null. Is that right? What should I do to avoid this? The Async methods can solve this?

Thank you

#region Ensure that CaseSizeCount.Item, CaseSizeCount.Item.InventoryGroup and CaseSizeCount.CaseSize are not null
                foreach (CaseSizeCount cs in cscList)
                {

                    _connection.GetChildren(cs);
                    if (cs.Item == null)
                    {
                        cs.Item = _connection.Table<Item>().Where(x => x.ItemId == cs.ItemID).FirstOrDefault();
                    }
                    _connection.GetChildren(cs.Item);
                }
                #endregion

[Guillermo Gutiérrez]

If the property is null is either because the relationship is not well defined, or because the object doesn't exist.

As a side note, this code:

if (cs.Item == null)
{
    cs.Item = _connection.Table<Item>().Where(x => x.ItemId == cs.ItemID).FirstOrDefault();
}
_connection.GetChildren(cs.Item);

Is exactly the same to this:

cs.Item = _connection.FindWithChildren<Item>(cs.ItemId);

Also, you can use the recursive parameter of the Get methods to load the complete relationship tree, if that's your case. Be careful because if you're using CascadeOperation.All you may end loading way more objects than you need.

[Marcelo José Amador Filho]

These are my entities. Did I do something wrong?

    public class CaseSizeCount
    {
        [PrimaryKey, AutoIncrement]
        public int LocalId { get; set; }

        public string _CompositeKey { get; set; }

        public int SortOrder { get; set; }
        public bool Counted { get; set; }
        public double PurchaseCount { get; set; }
        public double CaseCount { get; set; }
        public double pakCount { get; set; }

        [ForeignKey(typeof(Store))]
        public int StoreID { get; set; }
        [ManyToOne]
        public Store Store { get; set; }

        [ForeignKey(typeof(Countsheet))]
        public int CountsheetID { get; set; }
        [ManyToOne]
        public Countsheet Countsheet { get; set; }

        [ForeignKey(typeof(Item))]
        public int ItemID { get; set; }
        [ManyToOne]
        public Item Item { get; set; }

        [ForeignKey(typeof(CaseSize))]
        public int CaseSizeID { get; set; }
        [ManyToOne]
        public CaseSize CaseSize { get; set; }

        [ForeignKey(typeof(Location))]
        public int LocationID { get; set; }
        [ManyToOne]
        public Location Location { get; set; }
    }

    public class Item
    {
        [PrimaryKey, AutoIncrement]
        public int LocalId { get; set; }

        public int ItemId { get; set; }
        public string Description { get; set; }
        public string RecipeUom { get; set; }
        public bool KeyItem { get; set; }

        [ForeignKey(typeof(Store))]
        public int StoreID { get; set; }
        [ManyToOne]
        public Store Store { get; set; }

        [ForeignKey(typeof(InventoryGroup))]
        public int InventoryGroupId { get; set; }
        [ManyToOne]
        public InventoryGroup InventoryGroup { get; set; }

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

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

[Marcelo José Amador Filho]

And btw, thank you very much for the support. =D

[Guillermo Gutiérrez]

Your problem is most probably in the primary key of your Item class, that it's defined as LocalId rather than ItemId even when your sample code fetch items by ItemId.