OrmLite在联接的表中选择多列

本文关键字：选择 OrmLite | 更新日期: 2023-09-27 18:27:10

我在使用OrmLite填充POCO中的某些列时遇到了一些困难。我有三张桌子，分别叫Dog、Bowl和DogBowl。DogBowl是一张连接桌，上面有Dog和Bowl的id。

Dogs
    PK Id: int, not null
    Breed: varchar(20), not null
    Name: varchar(20), not null
Bowls
    PK Id: int, not null
    Type: varchar(20), not null
    Color: varchar(20), not null
Dogs_Bowls
    PK: DogId, not null
    PK: BowlId, not null

以下是我绘制的POCO

public class Dog : IHasId<int>
{
    [AutoIncrement]
    public int Id { get; set; }
    [Required]
    public string Breed { get; set; }
    [Required]
    public string Name { get; set; }
}

public class Bowl : IHasId<int>
{
    [AutoIncrement]
    public int Id { get; set; }
    [Required]
    public string Type { get; set; }
    [Required]
    public string Color { get; set; }
}

public class DogBowl
{
    [Required]
    public int DogId { get; set; }
    [Required]
    public int BowlId { get; set; }
    [Ignore]
    public string DogName { get;set; }
    [Ignore]
    public string BowlColor { get;set; }
}

这是我正在运行的c#代码。

var dogBowl = db.Select<DogBowl>(db
    .From<Dog>()
    .Join<Dog, DogBowl>((d, db) => d.Id == db.DogId)
    .Join<DogBowl, Bowl>((db, b) => db.BowlId == b.Id)
    .Where<Dog>(d => d.Id == 5))
    .ToList();

我想生成的SQL是这样的：

select
    db.DogId,
    db.BowlId,
    d.Name AS DogName,
    b.Color as BowlColor
from DogBowl db
join dog d on db.DogId = d.Id
join bowl b on db.BowlId = b.Id
where d.Id = 5

我的问题是DogBowl.DogName和DogBowl.BowlColor属性在代码执行后为null。我正在使用上提供的说明https://github.com/ServiceStack/ServiceStack.OrmLite从标题为"在联接的表中选择多个列"的部分，但它不起作用。如何填充DogBowl.DogName和DogBowl.BowlColor属性？

OrmLite在联接的表中选择多列

生成的SQL可能是正确的。您可以通过检查属性db来验证执行后生成的SQL。GetLastSql（）。

问题是通过将结果指定为

db.Select<DogBowl>

，您正在创建DogBowl对象的列表。DogBowl属性DogName和BowlColor将始终为null，因为SQL语句中没有与这些名称完全匹配的字段。OrmLite不会神奇地弄清楚那里发生了什么——你必须让它们按名称匹配。

如果您想将结果分配给一个包含Dog和Bowl字段的"平面"对象，您可以定义一个新的DTO并分配结果，如下所示：

public class FullDogBowl
{
    public int DogId { get; set; }
    public int BowlId { get; set; }
    public string Breed { get; set; }
    public string Name { get; set; }
    public string Type { get; set; }
    public string Color { get; set; }
}
var dogBowl = db.Select<FullDogBowl>(db
    .From<Dog>()
    .Join<Dog, DogBowl>((d, db) => d.Id == db.DogId)
    .Join<DogBowl, Bowl>((db, b) => db.BowlId == b.Id)
    .Where<Dog>(d => d.Id == 5))
    .ToList();

或者，如果您确切地知道要使用的SQL，只需使用它：

string sql = @"select
    db.DogId,
    db.BowlId,
    d.Name AS DogName,
    b.Color as BowlColor
from DogBowl db
join dog d on db.DogId = d.Id
join bowl b on db.BowlId = b.Id
where d.Id = @dog_id ";
var dogBowlList = db.SqlList<DogBowl>(sql, new { dog_id = 5, });

想在Raul的回答中添加[Ignore]告诉OrmLite完全忽略该属性，这样您将表重新用作合并的POCO"视图"的方法就不会起作用。我建议将结果集POCO拆分为一个单独的POCO，其中包含您想要返回的所有字段：

public class DogBowl
{
    [Required]
    public int DogId { get; set; }
    [Required]
    public int BowlId { get; set; }
}
public class DogBowlInfo
{
    public int DogId { get; set; }
    public int BowlId { get; set; }
    public string DogName { get; set; }
    public string BowlColor { get; set; }
}

它现在返回一个填充的结果集，其中包含：

using (var db = OpenDbConnection())
{
    db.DropAndCreateTable<Dog>();
    db.DropAndCreateTable<Bowl>();
    db.DropAndCreateTable<DogBowl>();
    var dog = new Dog { Breed = "Breed", Name = "Name" };
    var bowl = new Bowl { Color = "Color", Type = "Type" };
    db.Save(dog);
    db.Save(bowl);
    db.Insert(new DogBowl { DogId = dog.Id, BowlId = bowl.Id });
    var dogBowl = db.Select<DogBowlInfo>(
         db.From<Dog>()
          .Join<Dog, DogBowl>((d, b) => d.Id == b.DogId)
          .Join<DogBowl, Bowl>((d, b) => d.BowlId == b.Id)
          .Where<Dog>(d => d.Id == dog.Id));
    dogBowl.PrintDump();
}
相关文章：