使用ServiceStack.OrmLite填充POCO [英] Populating POCO's with ServiceStack.OrmLite

问题描述

请考虑以下域模型：

class Customer
{
  int id {get;set}
  string Name {get;set}
  List<Contact> Contacts {get;set}
}

class Contact
{
  int id {get;set}
  string FullName {get;set}
  List<PhoneNumber> {get;set}
}

class PhoneNumber
{
  int id {get;set}
  PhoneType Type {get;set}
  string Number {get;set}
}

客户，联系与联系我们；电话号码是我们数据库中的独立实体。

Customer, Contact & PhoneNumbers are separate entities in our DB.

关于如何使用ORMLite以最有效的方式为其所有链接的联系人和联系人电话号码填充完整客户的任何建议和/或Dapper，考虑到对db和cpu周期的调用以映射到POCO？

Any Suggestions as to how to populate a full customer with all its linked contacts and the contacts phone numbers in the most efficient way using ORMLite and/or Dapper, with consideration of calls to the db and cpu cycles to map to the POCOs?

推荐答案

我已更新我的示例是提取固定的客户列表并将其转换为层次结构。需要注意的几件事：

I have updated my example to pull the flat customer list and transform it into a hierarchy. A couple of things to note:

• 我在联接生成器中明确指定列，因为Id列在联接中具有相同的名称


• 性能应该不错，因为我正在为客户使用哈希并添加电话号码，因此唯一真正的循环是搜索匹配的联系人

型号：

class CustomerComplete
{
    [BelongTo(typeof(Customer))]
    public string CustomerName { get; set; }

    [BelongTo(typeof(Customer))]
    public int CustomerId { get; set; }

    [BelongTo(typeof(Contact))]
    public int ContactId { get; set; }

    [BelongTo(typeof(Contact))]
    public string ContactFullName { get; set; }

    [BelongTo(typeof(PhoneNumber))]
    public int PhoneNumberId { get; set; }

    [BelongTo(typeof(PhoneNumber))]
    public PhoneType Type { get; set; }

    [BelongTo(typeof(PhoneNumber))]
    public string Number { get; set; }
}

class Customer
{
    public Customer()
    {
        this.Contacts = new List<Contact>();
    }

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

    public string Name { get; set; }

    [Ignore]
    public List<Contact> Contacts { get; set; }
}

class Contact
{
    public Contact()
    {
        this.PhoneNumbers = new List<PhoneNumber>();
    }

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

    public string FullName { get; set; }

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

    [Ignore]
    public List<PhoneNumber> PhoneNumbers { get; set; }
}

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

    public PhoneType Type { get; set; }
    public string Number { get; set; }

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

enum PhoneType { None = 0 }

用法：

db.CreateTableIfNotExists<Customer>();
db.CreateTableIfNotExists<Contact>();
db.CreateTableIfNotExists<PhoneNumber>();

db.Insert<Customer>(new Customer { Name = "Widget Co Inc" });
var customerId = (int) db.GetLastInsertId();

db.Insert<Contact>(new Contact { FullName = "John Smith", CustomerId = customerId });
var contactId = (int)db.GetLastInsertId();

db.Insert<PhoneNumber>(new PhoneNumber { ContactId = contactId, Number = "555.555.5555", Type = PhoneType.None });
db.Insert<PhoneNumber>(new PhoneNumber { ContactId = contactId, Number = "444.444.4444", Type = PhoneType.None });

db.Insert<Contact>(new Contact { FullName = "Jack Smith", CustomerId = customerId });
contactId = (int)db.GetLastInsertId();

db.Insert<PhoneNumber>(new PhoneNumber { ContactId = contactId, Number = "111.111.1111", Type = PhoneType.None });

// contact without phone number test
db.Insert<Contact>(new Contact { FullName = "Jill Smith", CustomerId = customerId });

// build join
var jn = new JoinSqlBuilder<Customer, Customer>()
    .LeftJoin<Customer, Contact>(
        customer => customer.Id, 
        contact => contact.CustomerId,
        customer => new { CustomerId = customer.Id, CustomerName = customer.Name },
        contact => new { ContactId = contact.Id, ContactFullName = contact.FullName })
    .LeftJoin<Contact, PhoneNumber>(
        contact => contact.Id, 
        phone => phone.ContactId,
        null,
        phone => new { PhoneNumberId = phone.Id, phone.Number, phone.Type });

var all = db.Select<CustomerComplete>(jn.ToSql());

var customerHash = new Dictionary<int, Customer>();

foreach (var cc in all) 
{
    if (!customerHash.ContainsKey(customerId))
        customerHash[customerId] = new Customer { Id = cc.CustomerId, Name = cc.CustomerName };

    if (cc.ContactId == 0) continue; // no contacts for this customer

    var contact = customerHash[customerId].Contacts.Where(x => x.Id == cc.ContactId).FirstOrDefault();
    if (null == contact)
    {
        contact = new Contact
        {
            CustomerId = customerId,
            Id = cc.ContactId,
            FullName = cc.ContactFullName
        };

        // add contact
        customerHash[customerId].Contacts.Add(contact);
    }

    if (cc.PhoneNumberId == 0) continue; // no phone numbers for this contact

    contact.PhoneNumbers.Add(new PhoneNumber
    {
        ContactId = cc.ContactId,
        Id = cc.PhoneNumberId,
        Number = cc.Number,
        Type = cc.Type
    });
}

// our hierarchical customer list
var customers = customerHash.ToList();

还请注意，v4似乎支持使用Reference属性更简单的方法可能会多次调用数据库。 在此处查看单元测试例如。

Also note that v4 seems to support a much easier way of doing this using the Reference attribute, though it may make multiple calls to the DB. See the unit tests here for an example.

这篇关于使用ServiceStack.OrmLite填充POCO的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！