ServiceStack with EF6 Code First SQL Database

Question

I'm trying to implement Service Stack beside or over an existing WebApi project with an EF6 Code-First SQL Database.

I'm running into trouble with setting the foreign key references.

My classes look like this:

public class Place
{
   public Guid Id { get; set; }
   public virtual ICollection Images {get; set;}
}

My Database foreign keys look like this:

dbo.Images
- ID
- Image
- FK-People_Id
- FK-Places_Id
- FK-Things_Id

I've tried the annotaion on the class and the reference

[Alias("Places")]
public class Place
{
    public Place()
    {
        Images = new List<Images>();
    }

    public Guid Id { get; set; }
    [Reference]
    public virtual ICollection<Images> Countries { get; set; }
}

The Image class is annotated the same, and I've also tried the [References(typeof(Image)] annotation but either way I get:

'Invalid column name'

I was a bit worried about this Db structure anyway, because I have to turn off proxy creation to prevent circular reference errors. Will I be able to use this SQL database with ServiceStack, or would I have to rebuild it?

Answer 1

OrmLite is a POCO ORM which uses your POCO classes so you shouldn't be using interfaces on your Data models and your properties don't need to be virtual as they don't need to mocked or have proxy classes generated.

So I would create your classes so they're like:

[Alias("Places")]
public class Place
{
    public Guid Id { get; set; }

    [Reference]
    public List<Image> Countries { get; set; }
}

[Alias("Images")]
public class Image
{
    public Guid Id { get; set; }

    //[Alias("ColumnAliasIfNotPlaceId")]
    public Guid PlaceId { get; set; }

    public string Url { get; set; } //Example field on Image
}

Which you can then populate with references by using Save:

var placeId = Guid.NewGuid();
var place = new Place
{
    Id = placeId,
    Countries = new List<Image> {
        new Image { Id = Guid.NewGuid(), PlaceId = placeId, Url = "http://url.to/country1.png" },
        new Image { Id = Guid.NewGuid(), PlaceId = placeId, Url = "http://url.to/country2.png" },
    }
};

db.Save(place, references: true);

Which you can retrieve with references using the db.Load* APIs, e.g:

var fromDb = db.LoadSingleById(placeId); fromDb.PrintDump();

Which prints out:

{
    Id: e97f0834fa404411a32e87c91d7d5b32,
    Countries: 
    [
        {
            Id: af1df09bd57d42219d363b79d0f9420e,
            PlaceId: e97f0834fa404411a32e87c91d7d5b32,
            Url: "http://url.to/country1.png"
        },
        {
            Id: 687855a7231d409787cdbdefc2ffbb22,
            PlaceId: e97f0834fa404411a32e87c91d7d5b32,
            Url: "http://url.to/country2.png"
        }
    ]
}

Of course these tables can be accessed individually using the normal Query APIs, e.g:

"Images Only:\n{0}".Print(db.Select(x => x.PlaceId == placeId).Dump());

Which prints out:

Images Only:
[
    {
        Id: af1df09bd57d42219d363b79d0f9420e,
        PlaceId: e97f0834fa404411a32e87c91d7d5b32,
        Url: "http://url.to/country1.png"
    },
    {
        Id: 687855a7231d409787cdbdefc2ffbb22,
        PlaceId: e97f0834fa404411a32e87c91d7d5b32,
        Url: "http://url.to/country2.png"
    }
]

I've created a live demo of this on Gistlyn if you want to play around with it more.