blocks|key|774833|text|或者，您可以将一个查询与查找一起使用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|774834|var+lookup+=+new+Dictionary<int,+Course>();
conn.Query<Course,+Location,+Course>(@"
++++SELECT+c.*,+l.*
++++FROM+Course+c
++++INNER+JOIN+Location+l+ON+c.LocationId+=+l.Id++++++++++++++++++++
++++",+(c,+l)+=>+{
++++++++Course+course;
++++++++if+(!lookup.TryGetValue(c.Id,+out+course))
++++++++++++lookup.Add(c.Id,+course+=+c);
++++++++if+(course.Locations+==+null)+
++++++++++++course.Locations+=+new+List<Location>();
++++++++course.Locations.Add(l);+/*+Add+locations+to+course+*/
++++++++return+course;
+++++}).AsQueryable();
var+resultList+=+lookup.Values;|code-block|syntax|javascript|774835|查看此处https://www.tritac.com/blog/dappernet-by-example/|offset|length|774836|entityMap|0|LINK|mutability|MUTABLE|url|https://www.tritac.com/blog/dappernet-by-example/^0|0|0|4|1D|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

Alternatively, you can use one query with a lookup:

<pre><code>var lookup = new Dictionary&lt;int, Course&gt;();
conn.Query&lt;Course, Location, Course&gt;(@"
 SELECT c.*, l.*
 FROM Course c
 INNER JOIN Location l ON c.LocationId = l.Id 
 ", (c, l) =&gt; {
 Course course;
 if (!lookup.TryGetValue(c.Id, out course))
 lookup.Add(c.Id, course = c);
 if (course.Locations == null) 
 course.Locations = new List&lt;Location&gt;();
 course.Locations.Add(l); /* Add locations to course */
 return course;
 }).AsQueryable();
var resultList = lookup.Values;
</code></pre>

See here <a href="https://www.tritac.com/blog/dappernet-by-example/" rel="noreferrer">https://www.tritac.com/blog/dappernet-by-example/</a>

blocks|key|558402|text|少了点什么。如果没有在SQL查询中指定Locations中的每个字段，则无法填充对象Location。看一看：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|558403|var+lookup+=+new+Dictionary<int,+Course>()
conn.Query<Course,+Location,+Course>(@"
++++SELECT+c.*,+l.Name,+l.otherField,+l.secondField
++++FROM+Course+c
++++INNER+JOIN+Location+l+ON+c.LocationId+=+l.Id++++++++++++++++++++
++++",+(c,+l)+=>+{
++++++++Course+course;
++++++++if+(!lookup.TryGetValue(c.Id,+out+course))+{
++++++++++++lookup.Add(c.Id,+course+=+c);
++++++++}
++++++++if+(course.Locations+==+null)+
++++++++++++course.Locations+=+new+List<Location>();
++++++++course.Locations.Add(a);
++++++++return+course;
+++++},
+++++).AsQueryable();
var+resultList+=+lookup.Values;|code-block|syntax|javascript|558404|在查询中使用l.*，我得到了位置列表，但没有数据。|558405|entityMap^0|J|9|16|8|0|0|6|3|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|X|8|@]|D|@]|E|$]]]|N|$]]

Something is missing. If you do not specify each field from <code>Locations</code> in the SQL query, the object <code>Location</code> cannot be filled. Take a look:

<pre><code>var lookup = new Dictionary&lt;int, Course&gt;()
conn.Query&lt;Course, Location, Course&gt;(@"
 SELECT c.*, l.Name, l.otherField, l.secondField
 FROM Course c
 INNER JOIN Location l ON c.LocationId = l.Id 
 ", (c, l) =&gt; {
 Course course;
 if (!lookup.TryGetValue(c.Id, out course)) {
 lookup.Add(c.Id, course = c);
 }
 if (course.Locations == null) 
 course.Locations = new List&lt;Location&gt;();
 course.Locations.Add(a);
 return course;
 },
 ).AsQueryable();
var resultList = lookup.Values;
</code></pre>

Using <code>l.*</code> in the query, I had the list of locations but without data.

blocks|key|558422|text|我知道我来晚了，但还有另一个选择。你可以在这里使用QueryMultiple。如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|558423|var+results+=+cnn.QueryMultiple(@"
++++SELECT+*+
++++++FROM+Courses+
+++++WHERE+Category+=+1+
++ORDER+BY+CreationDate
++++++++++;+
++++SELECT+A.*
++++++++++,B.CourseId+
++++++FROM+Locations+A+
INNER+JOIN+CourseLocations+B+
++++++++ON+A.LocationId+=+B.LocationId+
INNER+JOIN+Course+C+
++++++++ON+B.CourseId+=+B.CourseId+
+++++++AND+C.Category+=+1
");

var+courses+=+results.Read<Course>();
var+locations+=+results.Read<Location>();+//(Location+will+have+that+extra+CourseId+on+it+for+the+next+part)
foreach+(var+course+in+courses)+{
+++course.Locations+=+locations.Where(a+=>+a.CourseId+==+course.CourseId).ToList();
}|code-block|syntax|javascript|558424|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I know I'm really late to this, but there is another option. You can use QueryMultiple here. Something like this:

<pre><code>var results = cnn.QueryMultiple(@"
 SELECT * 
 FROM Courses 
 WHERE Category = 1 
 ORDER BY CreationDate
 ; 
 SELECT A.*
 ,B.CourseId 
 FROM Locations A 
INNER JOIN CourseLocations B 
 ON A.LocationId = B.LocationId 
INNER JOIN Course C 
 ON B.CourseId = B.CourseId 
 AND C.Category = 1
");

var courses = results.Read&lt;Course&gt;();
var locations = results.Read&lt;Location&gt;(); //(Location will have that extra CourseId on it for the next part)
foreach (var course in courses) {
 course.Locations = locations.Where(a =&gt; a.CourseId == course.CourseId).ToList();
}
</code></pre>

blocks|key|558480|text|不确定是否有人需要它，但我有它的动态版本，没有模型的快速和灵活的编码。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|558481|var+lookup+=+new+Dictionary<int,+dynamic>();
conn.Query<dynamic,+dynamic,+dynamic>(@"
++++SELECT+A.*,+B.*
++++FROM+Client+A
++++INNER+JOIN+Instance+B+ON+A.ClientID+=+B.ClientID++++++++++++++++
++++",+(A,+B)+=>+{
++++++++//+If+dict+has+no+key,+allocate+new+obj
++++++++//+with+another+level+of+array
++++++++if+(!lookup.ContainsKey(A.ClientID))+{
++++++++++++lookup[A.ClientID]+=+new+{
++++++++++++++++ClientID+=+A.ClientID,
++++++++++++++++ClientName+=+A.Name,++++++++++++++++++++++++++++++++++++++++
++++++++++++++++Instances+=+new+List<dynamic>()
++++++++++++};
++++++++}

++++++++//+Add+each+instance++++++++++++++++++++++++++++++++
++++++++lookup[A.ClientID].Instances.Add(new+{
++++++++++++InstanceName+=+B.Name,
++++++++++++BaseURL+=+B.BaseURL,
++++++++++++WebAppPath+=+B.WebAppPath
++++++++});

++++++++return+lookup[A.ClientID];
++++},+splitOn:+"ClientID,InstanceID").AsQueryable();

var+resultList+=+lookup.Values;
return+resultList;|code-block|syntax|javascript|558482|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Not sure if anybody needs it, but I have dynamic version of it without Model for quick &amp; flexible coding.

<pre><code>var lookup = new Dictionary&lt;int, dynamic&gt;();
conn.Query&lt;dynamic, dynamic, dynamic&gt;(@"
 SELECT A.*, B.*
 FROM Client A
 INNER JOIN Instance B ON A.ClientID = B.ClientID 
 ", (A, B) =&gt; {
 // If dict has no key, allocate new obj
 // with another level of array
 if (!lookup.ContainsKey(A.ClientID)) {
 lookup[A.ClientID] = new {
 ClientID = A.ClientID,
 ClientName = A.Name, 
 Instances = new List&lt;dynamic&gt;()
 };
 }

 // Add each instance 
 lookup[A.ClientID].Instances.Add(new {
 InstanceName = B.Name,
 BaseURL = B.BaseURL,
 WebAppPath = B.WebAppPath
 });

 return lookup[A.ClientID];
 }, splitOn: "ClientID,InstanceID").AsQueryable();

var resultList = lookup.Values;
return resultList;
</code></pre>

blocks|key|775017|text|还有另一种使用JSON结果的方法。尽管公认的答案和其他答案都得到了很好的解释，但我只是想了另一种方法来获得结果。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|775018|创建一个存储过程或一个select+qry，以json格式返回结果。然后将结果对象反序列化为所需的类格式。请仔细阅读示例代码。|775019|using+(var+db+=+connection.OpenConnection())
{++++++++++++++++
++var+results+=+await+db.QueryAsync("your_sp_name",..);
++var+result+=+results.FirstOrDefault();++++
++++++++++++++++++++
++string+Json+=+result?.your_result_json_row;
+++++++++++++++++++
++if+(!string.IsNullOrEmpty(Json))
++{
+++++List<Course>+Courses=+JsonConvert.DeserializeObject<List<Course>>(Json);
++}
++++
++//map+to+your+custom+class+and+dto+then+return+the+result++++++++
}|code-block|syntax|javascript|775020|这是另一个思考的过程。请查看相同的内容。|775021|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

There is another approach using the JSON result. Even though the accepted answer and others are well explained, I just thought about an another approach to get the result.
Create a stored procedure or a select qry to return the result in json format. then Deserialize the the result object to required class format. please go through the sample code.
<pre><code>using (var db = connection.OpenConnection())
{ 
 var results = await db.QueryAsync(&quot;your_sp_name&quot;,..);
 var result = results.FirstOrDefault(); 
 
 string Json = result?.your_result_json_row;
 
 if (!string.IsNullOrEmpty(Json))
 {
 List&lt;Course&gt; Courses= JsonConvert.DeserializeObject&lt;List&lt;Course&gt;&gt;(Json);
 }
 
 //map to your custom class and dto then return the result 
}
</code></pre>
This is an another thought process. Please review the same.

I'm currently using Entity Framework for my db access but want to have a look at Dapper. I have classes like this:

<pre><code>public class Course{
 public string Title{get;set;}
 public IList&lt;Location&gt; Locations {get;set;}
 ...
}

public class Location{
 public string Name {get;set;}
 ...
}
</code></pre>

So one course can be taught at several locations. Entity Framework does the mapping for me so my Course object is populated with a list of locations. How would I go about this with Dapper, is it even possible or do I have to do it in several query steps?

How do I map lists of nested objects with Dapper

数据库

JSON

我目前正在使用实体框架来访问我的数据库，但我想看看Dapper。我有这样的类：public class Course{ public string Title{get;set;} public IList<Location> Locations {get;set;} ...}public class Loc...

问如何使用Dapper映射嵌套对象的列表
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用Dapper映射嵌套对象的列表EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用Dapper映射嵌套对象的列表
EN