ManyToOne and ManyToMany Object Creation and Retrieval

Sep 9, 2013 at 12:21 AM
Edited Sep 9, 2013 at 12:35 AM
Hi,

This is in two parts:

ManyToOne

I'm trying to create a SQLCE data store with a couple of the ManyToOne references and I'm not having much luck.

The structures I'm using are as follows:
    [Entity(KeyScheme.Identity)]
    public class BeefEQData
    {
        [Field(IsPrimaryKey = true)]
        public int? Id { get; set; }

        [Field]
        public string Barcode { get; set; }
        [Field]
        public int? WorkId { get; set; }

        [Reference(typeof(Hung), "HungId", "Id", ReferenceType = ReferenceType.ManyToOne)]
        public Hung Hung { get; set; }
        [Field]
        public int? HungId { get; set; }

        [Reference(typeof(Grade), "GradeId", "Id", ReferenceType = ReferenceType.ManyToOne)]
        public Grade Grade { get; set; }
        [Field]
        public int? GradeId { get; set; }

        //[Reference(typeof(Grade), "Id", ReferenceType = ReferenceType.ManyToMany)]
        //public Grade[] Grades { get; set; }

    }
with child objects of:
    [Entity(KeyScheme.Identity)]
    public class Hung
    {
        public Hung()
        {
            // this is required for cascading inserts to work
            Id = -1;
        }

        [Field(IsPrimaryKey = true)]
        public int? Id { get; set; }

        [Field(RequireUniqueValue = true)]
        public string Code { get; set; }
        [Field]
        public string Description { get; set; }
    }

    [Entity(KeyScheme.Identity)]
    public class Grade
    {
        public Grade()
        {
            // this is required for cascading inserts to work
            Id = -1;
        }

        [Field(IsPrimaryKey = true)]
        public int? Id { get; set; }

        [Field(RequireUniqueValue = true)]
        public string Code { get; set; }
        [Field]
        public string Description { get; set; }
    }
This will create the correct data structures in the SQLCE store and inserts the correct data values.

The rub comes when I then try to do the select<BeefEQData>().

The code I use is:
var retn = _store.Select<BeefEQData>(x => x.Barcode == "1234567890", true).FirstOrDefault();
This comes up with an exception in the FieldAttributeCollection, when trying to get the field for the child data (Hung or Grade) based on the name of the parent (BeefEQData) field name ("HungId" or "GradeId" respectively) - which obviuosly doesn't exist and should be "Id" (the PK of those tables) instead.

Note that I have had to change the base code to set up the reference PKs on the Insert operation and then try to fill the references in the Select:

File: SQLStoreBase.cs, line 983, Insert the following:
// set the item key
Entities[entityName].Fields[reference.ForeignReferenceField].PropertyInfo.SetValue(item, refPK, null);
File: DatStore.cs, add overload for IEnumerable<T> Select<T>(Func<T, bool> selector) to also allow fillReferences to be passed in:
        public IEnumerable<T> Select<T>(Func<T, bool> selector, bool fillReferences)
            where T : new()
        {
            return (from e in Select<T>(fillReferences)
                   where selector(e)
                   select e);
        }
What am I missing, or is this a bug in the library?

ManyToMany

As you can see from the main data structure, I would like to set up a ManyToMany relationship as well. I know how to do this 'manually' in SQL with an intermeiary table to do the ManyToOne and then OneToMany style linkages, but I'm not sure just quite how to achieve this with this library (or if it's even possible!)

Any suggestions on these two questions will be most gratefully received.

Many thanks,
Mike

p.s. I'm using build 103695 of the library
Sep 10, 2013 at 10:55 PM
I am also very interested in the ManyToMany implementation.

Mike, you are much further along in your development, could I ask you some questions? I have posted a discussion here as well, but wonder if you might have some insight.

Thanks! Wish I could help in your situation.

I'm trying to save what used to be serialized XML objects to a sqlite database on Windows CE.

Kind Regards,
Jarvis
Coordinator
Sep 10, 2013 at 11:00 PM
Yes, putting together a test and answer for this question is on my short list. I hoped to hit it today, but other things got in the way.

The short answer is that 1:1, 1:N and N:1 should work fine (and I'll get you a sample). n:n is not currently supported for a few reasons. First, adding in a join table is complex as it is with a relational DB. Second, many stores that I use are non-relational databases (several cloud implementations) and I've not even gotten my head around how I would do an N:N relationship between entities for that.

One might argue that I should at least support N:N in relational database stores by putting the support in the SQLBaseStore, and that's totally valid. I just haven't had the time or, honestly, a project requirement for it.