Closed GoogleCodeExporter closed 9 years ago
hmmm not following at all, can you include the SQL? How are we to populate that
IEnumerable
Original comment by sam.saff...@gmail.com
on 3 Jun 2011 at 11:40
It might not be the way to go but I thought we could map:
declare @id nvarchar(20) = 'test-post-1'
SELECT BlogPosts.PostId AS Id, BlogPosts.Title, BlogPosts.Slug,
BlogPosts.Summary, BlogPosts.MetaDescription, BlogPosts.[Content],
BlogPosts.ContentFormat,
BlogPosts.PublishDate, Tags.TagId AS Id, Tags.Title AS Title, Tags.Slug AS Slug
FROM BlogPosts INNER JOIN
BlogPostTags ON BlogPosts.PostId = BlogPostTags.Post_id INNER JOIN
Tags ON BlogPostTags.Tag_id = Tags.TagId
WHERE (BlogPosts.Slug = @id)
It may be that its just better performance to issue separate queries for the
tags in which case I'll stick to that.
Original comment by b...@planetcloud.co.uk
on 3 Jun 2011 at 11:47
Can I clarify; your aim is to make the following easier (or automatic), yes?
public void ParentChildIdentityAssociations()
{
var lookup = new Dictionary<int, Parent>();
var parents = connection.Query<Parent, Child, Parent>(@"select 1 as [Id], 1 as [Id] union all select 1,2 union all select 2,3 union all select 1,4 union all select 3,5",
(parent, child) =>
{
Parent found;
if (!lookup.TryGetValue(parent.Id, out found))
{
lookup.Add(parent.Id, found = parent);
}
found.Children.Add(child);
return found;
}).Distinct().ToDictionary(p => p.Id);
parents.Count().IsEqualTo(3);
parents[1].Children.Select(c => c.Id).SequenceEqual(new[] { 1,2,4}).IsTrue();
parents[2].Children.Select(c => c.Id).SequenceEqual(new[] { 3 }).IsTrue();
parents[3].Children.Select(c => c.Id).SequenceEqual(new[] { 5 }).IsTrue();
}
Original comment by marc.gravell
on 3 Jun 2011 at 12:35
Oooh nice. I hadn't even got that far. In actual fact I'm quite happy with the
above (now I know how to do it :p).
In your experience does this tend to work faster than two separate queries? In
my case this would be a simple inner join returning a post record for each tag
vs a query for the post and a query for the post tags.
Original comment by b...@planetcloud.co.uk
on 3 Jun 2011 at 1:39
What would be nice is if there was an overload of GridReader.Read<T> that
accepted a multi mapping e.g. multi.Read<Parent, Child, Parent>
The only reason I bring this up is that whilst the above works fine, in some
cases I need to perform paging and am doing this using multiple results
(http://stackoverflow.com/questions/6214140/dapper-multi-mapping-with-a-single-r
eturn-value). Currently I'm just handling this by constructing a second In
query to load the associations, which I'm more than happy to do of course.
Original comment by b...@planetcloud.co.uk
on 3 Jun 2011 at 1:45
It always depends on the volume. For an excessively large query, the amount of
duplicated data in an artificially rectangular *single* query may cause
bandwidth issues, but... at that point you are already pulling lots of data.
There is, however, absolutely no reason that GridReader can't offer
multi-mapping
Original comment by marc.gravell
on 3 Jun 2011 at 3:40
I totally support the grid reader handling multi mapping, only reason it is not
there is that I did not get around to it... a patch is more than welcome
Original comment by sam.saff...@gmail.com
on 4 Jun 2011 at 11:38
I'll have a go :)
Original comment by b...@planetcloud.co.uk
on 4 Jun 2011 at 1:44
Just wanted to say +1. This comes up quite a bit.
Original comment by pe...@hardwareworld.com
on 10 Jun 2011 at 11:30
BTW I have found the code for the "artificially rectangular" approach of Marc's
June 3 post is simpler if an OrderBy is used to ensure all the parents are in
order. This eliminates the need for the lookup object since you deal with one
parent, then move to the next, etc.
Original comment by pe...@hardwareworld.com
on 10 Jun 2011 at 11:50
Care to post your code? By it's nature the delegate will pass duplicate parents
(if they have many children) so surely the lookup is necessary?
Original comment by b...@planetcloud.co.uk
on 11 Jun 2011 at 9:52
I tried adding multi mapping support to the GridReader but I think I'm in way
over my head :(
Original comment by b...@planetcloud.co.uk
on 11 Jun 2011 at 10:45
[deleted comment]
Sure - my code looks like this. By ensuring parents are grouped it eliminates
the need for the Dictionary and for the Distinct(). Apologies I'm using a
different unit test framework...
[TestClass]
public class UnitTest1
{
[TestMethod]
public void ParentChildIdentityAssociations()
{
var connection = new SqlConnection("data source=(local);trusted_connection=yes");
connection.Open();
var parents = new List<Parent>();
Parent current = null;
// SQL command should use order by to ensure all parent records are grouped by the parent id
const string commandText = "select 1 as [Id], 1 as [Id] union all select 1,2 union all select 1,4 union all select 2,3 union all select 3,5";
connection.Query<Parent, Child, Parent>(commandText,
(parent, child) =>
{
if (current == null || current.Id != parent.Id)
{
parents.Add(parent);
current = parent;
}
current.Children.Add(child);
return current;
});
Assert.AreEqual(3, parents.Count);
CollectionAssert.AreEqual(new[] { 1, 2, 4 }, parents[0].Children.Select(c => c.Id).ToArray());
CollectionAssert.AreEqual(new[] { 3 }, parents[1].Children.Select(c => c.Id).ToArray());
CollectionAssert.AreEqual(new[] { 5 }, parents[2].Children.Select(c => c.Id).ToArray());
}
}
public class Parent
{
public int Id;
public List<Child> Children = new List<Child>();
}
public class Child
{
public int Id;
}
Original comment by pe...@hardwareworld.com
on 11 Jun 2011 at 4:33
complete ... just checked in a fix ... not trivial by any means :)
Original comment by sam.saff...@gmail.com
on 14 Jun 2011 at 1:06
Nice!!
Original comment by pe...@hardwareworld.com
on 14 Jun 2011 at 4:05
Original issue reported on code.google.com by
b...@planetcloud.co.uk
on 2 Jun 2011 at 7:02