gistfile1.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;

namespace SimpleDataC
{
    class Program
    {
        static void Main(string[] args)
        {
            CreateDatabase();
            CreateTables();
            var db = Simple.Data.Database.OpenConnection(ConnectionString());
            db.Address.Insert(new {Street = "street1", HouseNumber = "1"});
            db.Person.Insert(new {LastName = "lastname1", FirstName = "firstname1", AddressId = 1});
            db.Person.Insert(new {LastName = "lastname1", FirstName = "firstname2", AddressId = 1});
            db.BadHabit.Insert(new {BadHabit = "Drinks"});
            db.BadHabit.Insert(new {BadHabit = "Smokes"});
            db.BadHabit.Insert(new {BadHabit = "Eats to much"});
            db.BadHabitPerson.Insert(new {BadHabitId = 1, PersonId = 1});
            db.BadHabitPerson.Insert(new {BadHabitId = 2, PersonId = 1});
            db.BadHabitPerson.Insert(new {BadHabitId = 3, PersonId = 2});
            var result = db.Person.FindAllByLastName("lastname1").WithAddress().With(db.Person.BadHabitPerson.BadHabit);
            foreach(var person in result)
            {
                Console.WriteLine("{0} {1} {2} {3} {4}", person.Id, person.LastName, person.FirstName, person.Address.Street, person.Address.HouseNumber);
                foreach(var badHabit in person.BadHabitPerson.BadHabit)
                {
                    Console.WriteLine(badHabit.BadHabit);
                }
            }
            var count = db.Persons.FindAllByLastName("lastname1").Count();
            Console.WriteLine("Count: " + count);
            Console.ReadLine();
        }

        public static String ConnectionString()
        {
            return "DataSource=\"test.sdf\"; Password=\"mypassword\"";
        }

        private static SqlCeEngine CreateDatabase() 
        {
            if(System.IO.File.Exists("test.sdf")) System.IO.File.Delete("test.sdf");
            var en = new SqlCeEngine(ConnectionString());
            en.CreateDatabase();
            return en;
        }

        private static void CreateTables()
        {
            using(var cn = new SqlCeConnection(ConnectionString()))
            {
                cn.Open();
                foreach(var sqlScript in CreateTableScripts())
                {
                    using(var cmd = new SqlCeCommand(sqlScript, cn))
                    {
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }

        private static IList<String> CreateTableScripts()
        {
            var s = new List<String>();
            s.Add("CREATE TABLE Address (Id int IDENTITY(1,1) PRIMARY KEY, Street nvarchar(40) NOT NULL, HouseNumber   nvarchar(10))");
            s.Add("CREATE TABLE Person (Id int IDENTITY(1,1) PRIMARY KEY, LastName nvarchar(40) NOT NULL, FirstName nvarchar(40), AddressId int NOT NULL)");
            s.Add("ALTER TABLE Person ADD CONSTRAINT FK_Person_Address FOREIGN KEY (AddressId) REFERENCES Address(Id)");
            s.Add("CREATE TABLE BadHabit (Id int IDENTITY(1,1) PRIMARY KEY, BadHabit nvarchar(40) NOT NULL)");
            s.Add("CREATE TABLE BadHabitPerson (BadHabitId int NOT NULL, PersonId int NOT NULL)");
            s.Add("ALTER TABLE BadHabitPerson ADD PRIMARY KEY(BadHabitId, PersonId)");
            s.Add("ALTER TABLE BadHabitPerson ADD CONSTRAINT FK_BadHabitPerson_Person FOREIGN KEY (PersonId) REFERENCES Person(Id)");
            s.Add("ALTER TABLE BadHabitPerson ADD CONSTRAINT FK_BadHabitPerson_BadHabit FOREIGN KEY (BadHabitId) REFERENCES BadHabit(Id)");
        return s;
        }
            
    }
}