Create table in Xamarin throws exception because of Generic list.

phaniphani USMember

Hello, i have a class named Stack. It has 3 properties. Two of them are of string type and one is of List
My class definition for Stack class is as follows :

`

public class Stack
{
    public Stack()
    {
    }
    private string m_strLatLng;
    public string LatLng
    {
        get{return m_strLatLng;}
        set{m_strLatLng =value;}
    }
    private string m_strSpecialInstructions;
    public string SpecialInstructions
    {
        get{return m_strSpecialInstructions;}
        set{ m_strSpecialInstructions = value;}
    }

    private List<Employee> lstEmps;
    public List<Employee> Employees
    {
        get{return lstEmps;}
        set{lstEmps= value;}
    }
}

`
Employee is another class with its own 4 properties (2 int, 2 string). So i have CreateTable and it throws me this exception

System.NotSupportedException has been thrown.Don't know about system.Collections.Generic.List`1 [MyAppName.Employee]

So cant I create a table which has a list of another class? What is my alternative?Can someone explain with code. Thanks.

Posts

  • JohnMillerJohnMiller USForum Administrator, Xamarin Team Xamurai

    You should be able to do that.
    do you have a
    using System.Collections.Generics; at the top of your files?

    Where is your exception being throw? Which line of code?

  • phaniphani USMember

    @John Miller. I do have that line. The exception is currently thrown in SQLite.cs file

    `

    public static string SqlType (TableMapping.Column p, bool storeDateTimeAsTicks)
                {
                    var clrType = p.ColumnType;
                    if (clrType == typeof(Boolean) || clrType == typeof(Byte) || clrType == typeof(UInt16) || clrType == typeof(SByte) || clrType == typeof(Int16) || clrType == typeof(Int32)) {
                return "integer";
                } else if (clrType == typeof(UInt32) || clrType == typeof(Int64)) {
                return "bigint";
            } else if (clrType == typeof(Single) || clrType == typeof(Double) || clrType == typeof(Decimal)) {
                return "float";
            } else if (clrType == typeof(String)) {
                int len = p.MaxStringLength;
                return "varchar(" + len + ")";
            } else if (clrType == typeof(DateTime)) {
                return storeDateTimeAsTicks ? "bigint" : "datetime";
                #if !NETFX_CORE
            } else if (clrType.IsEnum) {
                #else
            } else if (clrType.GetTypeInfo().IsEnum) {
                #endif
                return "integer";
            } else if (clrType == typeof(byte[])) {
                return "blob";
            } else if (clrType == typeof(Guid)) {
                return "varchar(36)";
            } else {
                throw new NotSupportedException ("Don't know about " + clrType); //Here the exception is thrown
            }
        }
    

    `

  • JohnMillerJohnMiller USForum Administrator, Xamarin Team Xamurai
    edited March 2013

    List<Employee> is not a valid type for a SQLite database value.

    The valid types are shown in that post above. Your type of List<Employee> does not match any of the clrType == typeof(XXXX) statements, so you get that exception.

    You will need to rethink the class structure a little to be able to use SQLite-net like that. Instead of the Stack having a List<Employee> Employees you could have the Employee class contain a int StackId. This way each employee in the Employee table has a reference id (FK) to the Stack table.

  • phaniphani USMember

    @John Miller. So i will in future try to insert values into my Stack table. But first i create them. Like this

    namespace MyAppName { public class MyAppDataBase: SQLiteConnection { public string dbPath = String.Empty; public MyAppDataBase (string path) : base(path) { dbPath = path; CreateTable<Code> (); CreateTable<Employee> (); CreateTable<Stack>(); //Break point 1 CreateTable<Tent>(); //Break point 2 }
    ......

    I have two break points where i showed in the code. So as soon as i hit point 1 and go continue i get that exception. I havent really done anything yet like insert,update or anything. First time the app starts i go and create table like above and then i bring my data from web service and populate these tables. But i dont even get there.
    So that is all i have. Will this information help?If not, please do ask. Thanks.

  • JohnMillerJohnMiller USForum Administrator, Xamarin Team Xamurai
    edited March 2013

    @phani

    The problem is in the CreateTable<Stack>(). Your Stack class cannot contain the List<Employee> Employees property. If you don't want that to be part of the table anyway, you can add the Ignore attribute to it. But if you do want that in the table, you need to restructure your classes like I mentioned above. You cannot have a List in the table. Its not mapped to a valid SQLite data type. Basically all you can use are

    • string
    • int
    • double
    • byte[]
    • DateTime

    See this: http://www.sqlite.org/datatype3.html
    And this: https://github.com/praeclarum/sqlite-net#example-time

  • phaniphani USMember

    @John. Thanks. I know its very late answering to this question. I got busy elsewhere. Problem now is i have multiple stacks say 4(1,2,3,4) for example and 3 employees(A,B,C). For Stack 1 i have all 3 employees working. For Stack 2 i have 2 employees(A,B) working. For Stack 3 I have 2 employees(A,C) working. For Stack 4 i have 3 again. So if i use the FK concept, i can assign a stack id for an employee for only one stack.Employee_StackIDfk =1. But i have multiple stacks that can have multiple employees. So if i had collection type. For Stack 1 collection of (A,B,C).For stack 2 collection(A,B). In that manner. Does this explanation help you in understanding my problem

    (Stack 1 -> A B C)
    (Stack 2 -> A B)
    (Stack 3 -> A C)
    (Stack 4 -> A B C)

    Can you help me this regard?Any idea would be of great help. Thanks.

  • JonSJonS USMember

    @VictorArce.8951 said:
    I ran into the same problem, SQLite doesn't work with Complex Types like Lists.
    Just add [Ignore] to the property and save the list in another table like @Jmiller says, then use an ID to re-built your object.

    using SQLite;
    ...
    [Ignore]
    public List Products { get; set; }

    My problem was that I had referenced SQLite.Net.Annotations. Once I cut my using statements down to SQLite and System.Collections.Generic, I was fine. This plagued me for a good while though.

  • aquabayaquabay AUMember ✭✭

    @VictorArce.8951 said:
    I ran into the same problem, SQLite doesn't work with Complex Types like Lists.
    Just add [Ignore] to the property and save the list in another table like @Jmiller says, then use an ID to re-built your object.

    using SQLite;
    ...
    [Ignore]
    public List Products { get; set; }

    This seems to break the "separation of concern" design principal. By adding the [Ignore] attribute to the POCO model you are creating a dependency on a particular persistence technology. Probably better to change model design to reflect the database.

Sign In or Register to comment.