简单的内部联接结果与Dapper？ [英] Simple inner join result with Dapper?

问题描述

我似乎找不到有关我的问题的文档或示例（现在正在搜索一会儿）。我认为我的问题很简单，所以去吧。

I can't seem to find documentation or examples for my problem (been searching a while now). I think my problem is pretty straightforward, so here goes.

我有两个表。我的主表称为Persons，辅助表为PersonEntries。对于个人表中的每个人，我在个人条目表中可以有0个或多个条目。

I have two tables. My primary table is called Persons and the secondary table is PersonEntries. For each person in Person table, i can have 0 or more entries in the PersonEntries table. Like this.

Table: Person
Id
Name

Table: PersonEntry
PersonId
CheckinTime
CheckoutTime

我有两个对象此

public class Person {
  public string Name;
  public List<PersonEntry> PersonEntries;
}

public class PersonEntry {
  public DateTime CheckinTime;
  public DateTime CheckoutTime;
}

如果我要将其从数据库获取到我的c＃类中，我将如何会吗我可以将一个表映射到我的c＃类中，并为每个表做一个映射，但是接下来我要匹配哪些条目映射到哪个人。

If i was to get it from the database into my c# classes how would i do it? I can map a single table into my c# class and do it for each table, but then i'm left to match what entries maps to what person.

我已经看到几个将ONE PersonEntry映射到ONE Person的示例，这里的问题是我的关系是零对多。我的人有一个PersonEntry项目的列表。

I've seen several examples of mapping ONE PersonEntry to ONE Person, the problem here is that i have a zero-to-many relation. My Person have a LIST of PersonEntry items.

推荐答案

您可以执行以下操作（请参见< a href = https://www.tritac.com/blog/dappernet-by-example rel = noreferrer> https://www.tritac.com/blog/dappernet-by-example ） ：

You can do something like this (see https://www.tritac.com/blog/dappernet-by-example):

public class Shop {
  public int? Id {get;set;}
  public string Name {get;set;}
  public string Url {get;set;}
  public IList<Account> Accounts {get;set;}
}

public class Account {
  public int? Id {get;set;}
  public string Name {get;set;}
  public string Address {get;set;}
  public string Country {get;set;}
  public int ShopId {get;set;}
}

var lookup = new Dictionary<int, Shop>()
conn.Query<Shop, Account, Shop>(@"
                SELECT s.*, a.*
                FROM Shop s
                INNER JOIN Account a ON s.ShopId = a.ShopId                    
                ", (s, a) => {
                     Shop shop;
                     if (!lookup.TryGetValue(s.Id, out shop)) {
                         lookup.Add(s.Id, shop = s);
                     }
                     if (shop.Accounts == null) 
                         shop.Accounts = new List<Account>();
                     shop.Accounts.Add(a);
                     return shop;
                 }
                 ).AsQueryable();
var resultList = lookup.Values;

这篇关于简单的内部联接结果与Dapper？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！