ServiceStack OrmLite如何实现外键/相关属性的自动设置？ [英] ServiceStack OrmLite How can I achieve automatic setting of foreign key/related properties?

问题描述

我创建了下面的例子来测试外键，到目前为止，它运行良好。我希望能够做的是使用这个框架，我建立了关系的属性，并保存父对象时保存子对象，并自动设置PrimaryKey和外键。
DataManager类公开了Connection

  public class DataManager 
 {
 
 DataManager（）
 {
 OrmLiteConfig.DialectProvider = SqliteDialect.Provider; 
 ConnectionString = SqliteFileDb; 
 updateTables（）; 
 
 $ b $ private void updateTables（）
 {
 using（var dbConn = OpenDbConnection（））
 {
 dbConn.DropAndCreateTable< Person> ;（）; 
 dbConn.DropAndCreateTable< PhoneNumber>（）; 
 
 
 public static string SqliteFileDb =〜/ App_Data / db.sqlite.MapAbsolutePath（）; 
私有静态DataManager管理器; 
 public static DataManager Manager {
 
 get 
 {
 if（manager == null）
 manager = new DataManager（）; 
 return manager; 
} 
} 
 public IDbConnection InMemoryDbConnection {get;组; } 
 public IDbConnection OpenDbConnection（string connString = null）
 {
 connString = ConnectionString; 
返回connString.OpenDbConnection（）; 
} 
受保护的虚拟字符串ConnectionString {get;组; } 
受保护的虚拟字符串GetFileConnectionString（）
 {
 var connectionString = SqliteFileDb; 
返回connectionString; 
 
 
 
 
 $ p $这些是我的POCO的BaseClass用于实现我的结果：
 $ b $ pre $  public class Person：LiteBase 
 {
 [AutoIncrement] 
 [PrimaryKey] 
 public int Id {get;组; } 
 
 public string Name {get;组; } 
 public string Surname {get;组; } 
私人列表< PhoneNumber>数; 
 public List< PhoneNumber> PhoneNumbers {
 get 
 {
 if（numbers == null）
 numbers = GetList< PhoneNumber>（p => p.Person == Id）; 
返回号码; 
 
 
 
 
 $ b public class PhoneNumber 
 {
 public string Number {get;组; } 
 public string描述{get;组; } 
 
 [AutoIncrement] 
 [PrimaryKey] 
 public int Id {get;组; } 
 $ b $ [引用（typeof（Person））] 
 public int Person {get;组; } 
 $ b $ public void AddPerson（Person person）
 {
 Person = person.Id; 
 
 
 
 public class LiteBase：INotifyPropertyChanged 
 {
 public List< T> GetList< T>（表达式< T>（表达式< Func< T，bool>> thefunction）其中T：new（）
 {
 var objects = new List< T>（）; 
 using（var conn = Data.DataManager.Manager.OpenDbConnection（））
 {
 objects = conn.Where< T>（thefunction）; 
} 
返回对象; 
 
 
 public T GetItem< T>（表达式< T>（Tunc< T，bool>> thefunction）其中T：new（）
 {
 T obj =新T（）; 
 using（var conn = Data.DataManager.Manager.OpenDbConnection（））
 {
 obj = conn.Where< T>（thefunction）.FirstOrDefault< T>（）; 
} 
 return obj; 
} 
 
公共事件PropertyChangedEventHandler PropertyChanged; 
 $ b $ NotifyPropertyChangedInvocator 
 protected void OnPropertyChanged（string propertyName）
 {
 PropertyChangedEventHandler handler = PropertyChanged; 
 if（handler！= null）
 handler（this，new PropertyChangedEventArgs（propertyName））; 
 
 
 
 
 $ p $简单的类创建Person和PhoneNumber对象
  public class PersonManager 
 {
 
 public void CreatePerson（string name，string surname，string number） 
 {
 using（var conn = DataManager.Manager.OpenDbConnection（））
 {
 var pnum = new PhoneNumber {Number = number}; 
 var person = new Person 
 {
 Name = name，
 Surname = surname，
 
}; 
 conn.Save< Person>（person）; 
 var id = conn.GetLastInsertId（）; 
 person.Id =（int）id; 
 pnum.AddPerson（person）; 
 conn.Save< PhoneNumber>（pnum）; 
 
} 
} 
 
 public List< Person> GetPeople（）
 {
列表< Person>人; 
 using（var conn = DataManager.Manager.OpenDbConnection（））
 {
 people = conn.Select< Person>（）; 
} 
返回人; 
} 
 
 public List< PhoneNumber> GetNumbers（）
 {
列表< PhoneNumber>数; 
 using（var conn = DataManager.Manager.OpenDbConnection（））
 {
 numbers = conn.Select< PhoneNumber>（）; 
} 
返回数字; 
 
 
 $ / code $ / pre 
 
 
这里是用法：
 
 
  var manager = new PersonManager（）; 
 manager.CreatePerson（John，Doe，12345679）; 
 manager.CreatePerson（Jack，Smith，12345679）; 
 manager.CreatePerson（Peter，Jones，12345679）; 
 manager.CreatePerson（Dan，Hardy，12345679）; 
 var people = manager.GetPeople（）; 
 var numbers = manager.GetNumbers（）; 
 for（int i = 0; i< people.Count; i ++）
 {
 Console.WriteLine（{0} {1} {2}，
 people [I] .name和人[I] .Surname，人[I] .ID）; 
 
（int n = 0; n  {
 Console.WriteLine（PN：{0} {1}，
数字[n]。数字，数字[n] .Person）; 
 
（int p = 0; p< people.Count; p ++）
 {
 var person = people [p]; 
 Console.WriteLine（{0}：{1} {2} {3}，
 person.Id，person.Name，person.Surname，person.GetItem< PhoneNumber>（x => ; x.Person == person.Id）。数）; 
 
 
 
 $ b $ p 
 $输出如我所料：
 
 
 
 
 John Doe 1 
 
 
 Jack Smith 2 
 
 
 
彼得琼斯3 
 
 
 Dan Hardy 4 
 
 
 
 PN：12345679 1 
 
 
 
 PN：12345679 2 
 
 
 
 PN：12345679 3 
 
 
 
 PN：12345679 4 
 
 $ b $ 1：John Doe 12345679 
 
 2：Jack Smith 12345679 
 
 
 3：Peter Jones 12345679 
 
 4：Dan Hardy 12345679  
 
 
 
 
  var john = new Person 
 {
 Name =John，
 Surname =Smith，
 PhoneNumber = new PhoneNumber {Number =123456789} 
}; 
 conn.Save< Person>（john）; 
 var number = john.PhoneNumber.Number 
  
这是否可能？
解决方案
默认情况下OrmLite v3在字符串字段中使用所有复杂类型的属性，并且需要显式设置所有引用。 
 $ b 
在下一个主要v4版本（ETA 2013年11月下旬）中，OrmLite 用 [Reference] 属性添加一些对外部引用的支持， OrmLite这些属性应该存储在外部表中，而不是blobbed，例如： 
 
 
  public class Customer 
 {
 [AutoIncrement] 
 public int Id {get;组; } 
 public string Name {get;组; } 
 
 [参考] 
 public CustomerAddress PrimaryAddress {get;组; } 
 
 [Reference] 
 public List< Order>订单{get;组; } 
 
 
 
 $ b $ p 
 $ b 
这将允许你调用 db.SaveReferences （）保存引用属性，例如： 
 
 
  var customer = new Customer 
 {
 Name =Customer 1，
 PrimaryAddress = new CustomerAddress {
 AddressLine1 =1 Humpty Street，
 City =Humpty Doo，
 State = Northern Territory，
 Country =Australia
}，
 Orders = new [] {
新订单{LineItem =Line 1，Qty = 1，Cost = 1.99m}，
新订单{LineItem =Line 2，Qty = 2，Cost = 2.99m}，
} .ToList（），
}; 
 
 Assert.That（customer.Id，Is.EqualTo（0））; // Id尚未保存
 
 //插入customer，填充自动递增的customer.Id 
 //指定`references：true`来填充ForeignKey ID和
 / /保存相关的行，例如：
 db.Save（customer，references：true）; 
 
 Assert.That（customer.Id，Is.GreaterThan（0））; 
 Assert.That（customer.PrimaryAddress.CustomerId，Is.EqualTo（customer.Id））; 
 Assert.That（customer.Orders.All（x => x.CustomerId == customer.Id））; 
  
 
 
 
手动保存引用
 
 
 
 -grained控制你也可以选择你要保存的引用，例如：
 $ b $ pre $ db $。 //不保存相关行
 
 // 1：1 PrimaryAddress引用尚未保存
 Assert.That（customer.PrimaryAddress.CustomerId，Is.EqualTo（0））; 
 
 // 1：1 PrimaryAddress引用已保存，ForeignKey id已填充
 db.SaveReferences（customer，customer.PrimaryAddress）; 
 $ b $ // 1：许多订单引用保存和外键ids填充
 db.SaveReferences（customer，customer.Orders）; 
 
 
 $ b 
加载所有与实体相关的行
 
 
 
然后你可以加载主行及其所有的引用： 
 
 
  var dbCustomer = db.LoadSingleById< Customer>（customer.Id）; 
 
 dbCustomer.PrintDump（）; 
 
 Assert.That（dbCustomer.PrimaryAddress，Is.Not.Null）; 
 Assert.That（dbCustomer.Orders.Count，Is.EqualTo（2））; 
  
 
I have created the following example to test Foreign Keys and up to this point, it works well. What I would like to be able to do, is use this framework that I built to set the property of the relationship and have it Save the child object when the Parent is saved and automatically set the PrimaryKey and Foreign Key.
The DataManager class exposes the Connection
 public class DataManager
{

    DataManager()
    {
        OrmLiteConfig.DialectProvider = SqliteDialect.Provider;
        ConnectionString = SqliteFileDb;
        updateTables();
    }

    private void updateTables()
    {
        using (var dbConn = OpenDbConnection())
        {
            dbConn.DropAndCreateTable<Person>();
            dbConn.DropAndCreateTable<PhoneNumber>();
        }
    }
    public static string SqliteFileDb = "~/App_Data/db.sqlite".MapAbsolutePath();
    private static DataManager manager;
    public static DataManager Manager {

        get
        {
            if (manager == null)
                manager = new DataManager();
            return manager;
        }
    }
    public IDbConnection InMemoryDbConnection { get; set; }
    public IDbConnection OpenDbConnection(string connString = null)
    {
        connString = ConnectionString;
        return connString.OpenDbConnection();
    }
    protected virtual string ConnectionString { get; set; }
    protected virtual string GetFileConnectionString()
    {
        var connectionString = SqliteFileDb;
        return connectionString;
    }
}
These are my POCO's with the BaseClass used to Achieve my results:
    public class Person : LiteBase
{
    [AutoIncrement]
    [PrimaryKey]
    public int Id { get; set; }

    public string Name { get; set; }
    public string Surname { get; set; }
    private List<PhoneNumber> numbers;
    public List<PhoneNumber> PhoneNumbers {
        get
        {
            if (numbers == null)
                numbers = GetList<PhoneNumber>(p => p.Person == Id);
            return numbers;
        }
    }

}

public class PhoneNumber
{
    public string Number { get; set; }
    public string Description { get; set; }

    [AutoIncrement]
    [PrimaryKey]
    public int Id { get; set; }

    [References(typeof (Person))]
    public int Person { get; set; }

    public void AddPerson(Person person)
    {
        Person = person.Id;
    }
}

public class LiteBase:INotifyPropertyChanged
{
    public List<T> GetList<T>(Expression< Func<T,bool>> thefunction) where T : new()
    {
        var objects = new List<T>();
        using (var conn = Data.DataManager.Manager.OpenDbConnection())
        {
            objects = conn.Where<T>(thefunction);
        }
        return objects;
    }

    public T GetItem<T>(Expression<Func<T, bool>> thefunction) where T : new()
    {
        T obj = new T();
        using (var conn = Data.DataManager.Manager.OpenDbConnection())
        {
            obj = conn.Where<T>(thefunction).FirstOrDefault<T>();
        }
        return obj;
    }

    public event PropertyChangedEventHandler PropertyChanged;

    [NotifyPropertyChangedInvocator]
    protected virtual void OnPropertyChanged(string propertyName)
    {
        PropertyChangedEventHandler handler = PropertyChanged;
        if (handler != null) 
            handler(this, new PropertyChangedEventArgs(propertyName));
    }
}
Simple Class to Create Person and PhoneNumber objects
 public class PersonManager
    {

        public void CreatePerson(string name, string surname, string number)
        {
            using (var conn = DataManager.Manager.OpenDbConnection())
            {
                var pnum = new PhoneNumber { Number = number };
                var person = new Person
                    {
                        Name=name,
                        Surname = surname,

                    };
                conn.Save<Person>(person);
                var id = conn.GetLastInsertId();
                person.Id = (int)id;
                pnum.AddPerson(person);
                conn.Save<PhoneNumber>(pnum);

            }
        }

        public List<Person> GetPeople()
        {
            List<Person> people;
            using (var conn = DataManager.Manager.OpenDbConnection())
            {
                people = conn.Select<Person>();
            }
            return people;
        }

        public List<PhoneNumber> GetNumbers()
        {
            List<PhoneNumber> numbers;
            using (var conn = DataManager.Manager.OpenDbConnection())
            {
                numbers = conn.Select<PhoneNumber>();
            }
            return numbers;
        }
    }
And here is the usage:
var manager = new PersonManager();
        manager.CreatePerson("John", "Doe", "12345679");
        manager.CreatePerson("Jack", "Smith", "12345679");
        manager.CreatePerson("Peter", "Jones", "12345679");
        manager.CreatePerson("Dan", "Hardy", "12345679");
        var people = manager.GetPeople();
        var numbers = manager.GetNumbers();
        for (int i = 0; i < people.Count; i++)
        {
            Console.WriteLine("{0} {1} {2}",
                people[i].Name,people[i].Surname,people[i].Id);
        }
        for (int n = 0; n < numbers.Count; n++)
        {
            Console.WriteLine("PN: {0} {1}",
                numbers[n].Number,numbers[n].Person);
        }
        for (int p = 0; p < people.Count; p++)
        {
            var person = people[p];
            Console.WriteLine("{0}: {1} {2} {3}",
                person.Id,person.Name,person.Surname,person.GetItem<PhoneNumber>(x=>x.Person==person.Id).Number);
        }
The output is as I expected :

  
John Doe 1
  
  
Jack Smith 2
  
  
Peter Jones 3
  
  
Dan Hardy 4
  
  
PN: 12345679 1
  
  
PN: 12345679 2
  
  
PN: 12345679 3
  
  
PN: 12345679 4
  
  
1: John Doe 12345679
  
  
2: Jack Smith 12345679
  
  
3: Peter Jones 12345679
  
  
4: Dan Hardy 12345679
What I really would like to be able to do is the following:
var john = new Person
                {
                    Name = "John",
                    Surname = "Smith",
                    PhoneNumber = new PhoneNumber { Number = "123456789" }
                };
                conn.Save<Person>(john);
                var number = john.PhoneNumber.Number
Is this at all possible?
解决方案
By default OrmLite v3 blobs all complex types properties in a string field and you need to explicitly set all references.

In the next major v4 release (ETA late Nov 2013), OrmLite adds some support for external references with the [Reference] attribute, which lets you tell OrmLite these properties should be stored in an external table and not blobbed, e.g:
public class Customer
{
    [AutoIncrement]
    public int Id { get; set; }
    public string Name { get; set; }

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

    [Reference]
    public List<Order> Orders { get; set; }
}
This will allow you to call db.SaveReferences() to save the reference properties, e.g:
var customer = new Customer
{
    Name = "Customer 1",
    PrimaryAddress = new CustomerAddress {
        AddressLine1 = "1 Humpty Street",
        City = "Humpty Doo",
        State = "Northern Territory",
        Country = "Australia"
    },
    Orders = new[] { 
        new Order { LineItem = "Line 1", Qty = 1, Cost = 1.99m },
        new Order { LineItem = "Line 2", Qty = 2, Cost = 2.99m },
    }.ToList(),
};

Assert.That(customer.Id, Is.EqualTo(0)); //Id is not saved yet

//Inserts customer, populates auto-incrementing customer.Id
//Specify `references:true` to populate the ForeignKey ids and 
//save the related rows as well, e.g:
db.Save(customer, references:true); 

Assert.That(customer.Id, Is.GreaterThan(0)); 
Assert.That(customer.PrimaryAddress.CustomerId, Is.EqualTo(customer.Id));
Assert.That(customer.Orders.All(x => x.CustomerId == customer.Id));


Saving References manually

For more fine-grained control you can also choose which references you want to save, e.g:
db.Save(customer);  //Doesn't save related rows

//1:1 PrimaryAddress Reference not saved yet
Assert.That(customer.PrimaryAddress.CustomerId, Is.EqualTo(0));

//1:1 PrimaryAddress Reference saved and ForeignKey id populated
db.SaveReferences(customer, customer.PrimaryAddress);

//1:Many Orders References saved and ForeignKey ids populated
db.SaveReferences(customer, customer.Orders);


Loading all related rows with the entity

You can then load the master row and all its references with db.LoadSingleById, e.g:
var dbCustomer = db.LoadSingleById<Customer>(customer.Id);

dbCustomer.PrintDump();

Assert.That(dbCustomer.PrimaryAddress, Is.Not.Null);
Assert.That(dbCustomer.Orders.Count, Is.EqualTo(2));


                        
这篇关于ServiceStack OrmLite如何实现外键/相关属性的自动设置？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！