Newbie returned data problem

ServiceStack.OrmLite
1

Hi, I’ve got this newbie “problem” with data returned from my table.
I have these two SQL table in my db:

Schermata 2015-10-11 alle 15.33.49.png1062×512 79.5 KB

that I have defined them with these models:

    [Route("/customer", "GET")]
    public class Customer
    {
        [PrimaryKey]
        [AutoIncrement]
        public int Id { get; set; }
        public string Name { get; set; }

        public int? CustomerAddressId { get; set; }

        [Reference]
        public CustomerAddress PrimaryAddress { get; set; }
    }

    public class CustomerAddress
    {
        [PrimaryKey]
        [AutoIncrement]
        public int Id { get; set; }
        public string AddressLine1 { get; set; }
        public string AddressLine2 { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string Country { get; set; }
    }

and this interface:

    public class CustomeServices : Service
    {
        public object Get(Customer request)
        {
            return Db.Select<Customer>();
        }
    }

I have this output:

[{"Id":1,"Name":"cust1","CustomerAddressId":1},
{"Id":2,"Name":"cust2","CustomerAddressId":2},
{"Id":3,"Name":"cust3","CustomerAddressId":3},
{"Id":4,"Name":"cust4","CustomerAddressId":4}]

but I was expecting something like that:

[{"Id":1,"Name":"cust1","PrimaryAddress":
{"Id":1,"AddressLine1":"add11","AddressLine2":"add12","City":"city1","State":"state1","Country":"country1"}
},
{"Id":2,"Name":"cust2","PrimaryAddress":
{"Id":2,"AddressLine1":"add21","AddressLine2":"add22","City":"city2","State":"state2","Country":"country2"}
},
{"Id":3,"Name":"cust3","PrimaryAddress":
{"Id":3,"AddressLine1":"add31","AddressLine2":"add32","City":"city3","State":"state3","Country":"country3"},
},
{"Id":4,"Name":"cust4","PrimaryAddress":
{"Id":4,"AddressLine1":"add41","AddressLine2":"add42","City":"city4","State":"state4","Country":"country4"}]
}]

What should I do? I’m expecting it wrong results?

Thank you.

2

You need to use Load* API’s to load references, e.g.

 return Db.LoadSelect<Customer>();
3

Thank you mythz, everything works as I wish!
I take my example a bit further to make it more similar to my actual db situation (aka all column names messed up… I’ve not designed the db :frowning:)

    [Route("/anaggen", "GET")]
    public class AN01_ANAGGEN
    {
        [Index(Unique = true)]
        [PrimaryKey]
        [AutoIncrement]
        public int AN01_IDANAGGEN { get; set; }

        public string AN01_NAME { get; set; }

        public int? AN01_IDAZIENDA_AN04 { get; set; }

        [Reference]
        public AN04_AZIENDE AN04_AZIENDE { get; set; }
    }

    [Route("/aziende", "GET")]
    public class AN04_AZIENDE
    {
        [Index(Unique = true)]
        [PrimaryKey]
        [AutoIncrement]
        public int AN04_IDAZIENDA { get; set; }

        public string AN04_ADDRESSLINE1 { get; set; }

        public string AN04_ADDRESSLINE2 { get; set; }

        public string AN04_CITY { get; set; }

        public string AN04_STATE { get; set; }

        public string AN04_COUNTRY { get; set; }
    }

I’ve got an OrmLite exception “Cant find ‘AN01_ANAGGENId’ Property on Type ‘AN04_AZIENDE’” here:

public object Get(AN01_ANAGGEN request)
{
    return Db.LoadSelect<AN01_ANAGGEN>();
}

because I think I have not defined the Index, I’ve tried different ways with the Alias decorator but I can’t figure it out… can you help me? (I know I have strange db column names…)

4

The PrimaryKey should only have a [PrimaryKey] or [AutoIncrement] attribute, not both and not an index. If the primary key uses the Id naming convention it doesn’t need any attribute.

I don’t see how the relationships between the 2 tables is inferred, please read the documentation on Reference Conventions. For mapping to legacy tables read about how you can use the [Alias] attribute.

Please also never re-use a Request DTO as a data model.

5

Sorry mythz, I read the documentation but I’m getting mad because I can’t understand how define the Alias on my cross-table Id… :frowning:
I cleaned my code as you advised me, and now I have:

    public class AN04_AZIENDE
    {
        [PrimaryKey]
        public int AN04_IDAZIENDA { get; set; }

        public string AN04_ADDRESSLINE1 { get; set; }

        public string AN04_ADDRESSLINE2 { get; set; }

        public string AN04_CITY { get; set; }

        public string AN04_STATE { get; set; }

        public string AN04_COUNTRY { get; set; }
    }

    public class AN01_ANAGGEN
    {
        [PrimaryKey]
        public int AN01_IDANAGGEN { get; set; }

        public string AN01_NAME { get; set; }

        public int? AN04_AZIENDEId { get; set; }

        [Reference]
        public AN04_AZIENDE PrimaryAddress { get; set; }
    }

but I need to rename

public int? AN04_AZIENDEId { get; set; }

to

public int? AN01_IDAZIENDA_AN04 { get; set; }

I’ve tried different Alias definitions but I really can’t understand how it should work… sorry… can you help me?

6

I’m assuming you’ve tried the obvious use of [Alias]?

[Alias("AN01_IDAZIENDA_AN04")]
public int? AN04_AZIENDEId { get; set; }

What was the error?

What other examples of specifying a reference convention have you tried? Did you use the [References] attribute?

[References(typeof(AN04_AZIENDE))]
public int? AN01_IDAZIENDA_AN04 { get; set; }

Please provide details of what examples from the docs you’ve tried and the behavior you’ve got (please also do this for future questions).

7

THANK YOU!
Yeah… it was the

[References(typeof(AN04_AZIENDE))]

I completely misunderstood its meaning… thank you very much again…

8

Hi mythz! I’ve got another ormlite-related problem…
If in the same table I have two different references to another (same) table (but with different indexes) like so:

    public class AN01_ANAGGEN
    {
        [PrimaryKey]
        public int AN01_IDANAGGEN { get; set; }

        public string AN01_NAME { get; set; }

        [References(typeof(AN04_AZIENDE))]
        public int? AN01_IDAZIENDA_AN04 { get; set; }

        [References(typeof(AN04_AZIENDE))]
        public int? AN01_IDAZIENDAALT_AN04 { get; set; }

        [Reference]
        public AN04_AZIENDE AZIENDA { get; set; }

        [Reference]
        public AN04_AZIENDE AZIENDAALT { get; set; }
    }

Now, the second reference load the same id of the first, is there a way to solve it?

9

OrmLite has multiple self referencing as documented in the GitHub repository.

The problem will be that the naming is convention to support this isn’t being used. You could try [Alias]ing the related columns so that you can match the naming convention showed in the documentation.

10

Hi layoric, thank you for your answer… I’ve tried with different solutions using the Alias decorator but I can’t figure out how it works.
As you can see I haven’t a clean or “standard” naming convention… and this confuse me a lot!
The things I can only change are the name of the references (AZIENDA and AZIENDAALT):

    [Reference]
    public AN04_AZIENDE AZIENDA { get; set; }

    [Reference]
    public AN04_AZIENDE AZIENDAALT { get; set; }

Can you please advise me something more specific?

11

Looking at the code you have to work with, I would try using the alias for the FK ids. Something like

public class AN01_ANAGGEN
{
    [PrimaryKey]
    public int AN01_IDANAGGEN { get; set; }

    public string AN01_NAME { get; set; }

    [References(typeof(AN04_AZIENDE))]
    [Alias("AN01_IDAZIENDA_AN04")]
    public int? AZIENDAId{ get; set; }

    [References(typeof(AN04_AZIENDE))]
    [Alias("AN01_IDAZIENDAALT_AN04 ")]
    public int? AZIENDAALTId{ get; set; }

    [Reference]
    public AN04_AZIENDE AZIENDA { get; set; }

    [Reference]
    public AN04_AZIENDE AZIENDAALT { get; set; }
}

Hope that helps.

12

Thank you very much!
I didn’t thought to set the Alias References with the current column name… I did the opposite!
Anyway… now it works!
Thank you again.