public class User
{
[Column(IsIdentity = true)]
public int id { get; set; }
public int mode { get; set; }
public string name { get; set; }
public List<Order> orders { get; set; }
}
public class Order
{
[Column(IsIdentity = true)]
public int orderId { get; set; }
public int userId { get; set; }
public string name { get; set; }
public int orderTypeGroup { get; set; }
public int orderTypeCode { get; set; }
public OrderTypeGroup OrderTypeGroup { get; set; }
public OrderType OrderType { get; set; }
}
public class OrderTypeGroup
{
[Column(IsIdentity = true)]
public int id { get; set; }
public int groupCode { get; set; }
public string groupName { get; set; }
public List<OrderType> Items { get; set; }
}
public class OrderType
{
[Column(IsIdentity = true)]
public int id { get; set; }
public int groupCode { get; set; }
public int mode { get; set; }
public int code { get; set; }
public string typeName { get; set; }
}
[Fact]
public void Include_OneToManyTTT()
{
g.sqlite.Delete<User>();
g.sqlite.Delete<OrderTypeGroup>();
g.sqlite.Delete<OrderType>();
g.sqlite.Delete<Order>();
User[] users = new[]
{
new User() {id=1,mode = 1, name = "User1"},
new User() {id=2,mode = 2, name = "User2"},
};
g.sqlite.Insert(users);
OrderTypeGroup[] orderTypeGroups = new[]
{
new OrderTypeGroup() {groupCode = 1, groupName = "Group1"},
new OrderTypeGroup() {groupCode = 2, groupName = "Group2"},
new OrderTypeGroup() {groupCode = 3, groupName = "Group3"},
};
g.sqlite.Insert(orderTypeGroups);
var types = new[]
{
new OrderType() {code = 1, groupCode = 1, mode = 1, typeName = "type_1_1_1"},
new OrderType() {code = 2, groupCode = 1, mode = 1, typeName = "type_1_1_2"},
new OrderType() {code = 3, groupCode = 1, mode = 1, typeName = "type_1_1_3"},
new OrderType() {code = 1, groupCode = 2, mode = 1, typeName = "type_2_1_1"},
new OrderType() {code = 2, groupCode = 2, mode = 1, typeName = "type_2_1_2"},
new OrderType() {code = 3, groupCode = 2, mode = 1, typeName = "type_2_1_3"},
new OrderType() {code = 1, groupCode = 3, mode = 1, typeName = "type_3_1_1"},
new OrderType() {code = 2, groupCode = 3, mode = 1, typeName = "type_3_1_2"},
new OrderType() {code = 3, groupCode = 3, mode = 1, typeName = "type_3_1_3"},
new OrderType() {code = 1, groupCode = 1, mode = 2, typeName = "type_1_2_1"},
new OrderType() {code = 2, groupCode = 1, mode = 2, typeName = "type_1_2_2"},
new OrderType() {code = 3, groupCode = 1, mode = 2, typeName = "type_1_2_3"},
new OrderType() {code = 1, groupCode = 2, mode = 2, typeName = "type_2_2_1"},
new OrderType() {code = 2, groupCode = 2, mode = 2, typeName = "type_2_2_2"},
new OrderType() {code = 3, groupCode = 2, mode = 2, typeName = "type_2_2_3"},
new OrderType() {code = 1, groupCode = 3, mode = 2, typeName = "type_3_2_1"},
new OrderType() {code = 2, groupCode = 3, mode = 2, typeName = "type_3_2_2"},
new OrderType() {code = 3, groupCode = 3, mode = 2, typeName = "type_3_2_3"},
};
g.sqlite.Insert(types);
var orders = new[]
{
new Order() {name = "order_1_1", userId = 1, orderTypeGroup = 1, orderTypeCode = 3},
new Order() {name = "order_1_2", userId = 1, orderTypeGroup = 2, orderTypeCode = 2},
new Order() {name = "order_1_3", userId = 1, orderTypeGroup = 3, orderTypeCode = 1},
new Order() {name = "order_2_1", userId = 2, orderTypeGroup = 1, orderTypeCode = 2},
new Order() {name = "order_2_2", userId = 2, orderTypeGroup = 3, orderTypeCode = 1},
new Order() {name = "order_2_3", userId = 2, orderTypeGroup = 2, orderTypeCode = 3},
};
g.sqlite.Insert(orders);
var t = g.sqlite.Select<User>()
.IncludeMany(a => a.orders.Where(o => o.userId == a.id),
then =>
then
.InnerJoin(o => o.OrderTypeGroup.groupCode == o.orderTypeGroup)
.InnerJoin(o => o.OrderType.groupCode == o.orderTypeGroup && o.OrderType.code == o.orderTypeCode && o.OrderType.mode == a.mode)
.IncludeMany(o => o.OrderTypeGroup.Items.Where(t => t.groupCode == o.OrderTypeGroup.groupCode && t.mode == a.mode))
).ToList();
var t2 = g.sqlite.Select<OrderTypeGroup>()
.IncludeMany(o => o.Items.Where(t => t.groupCode == o.groupCode))
).ToList();
}