one-to-many insert using dapper dot net

Question

I have seen here

How to insert an IEnumerable<T> collection with dapper-dot-net

how dapper is able to handle IEnumerable as an input param and dispatch multiple commands for each member of the collection.

In my case I have an IEnumerable<int> Categories and a int SurveyId and I want to insert this one-to-many relationship into a separate mapping table called SurveyCategories

Is there a LINQ extension that I could use to concatenate these Categories with the same SurveyId, similar to .Concat()?

Or should I loop through the collection and build up a new list of objects with SurveyId and CategoryId properties?

score 1 · Accepted Answer · answered Dec 30 '12 at 16:38

1

You could do one insert for the surveys, and then insert all the survey categories at once using the following linq query as the parameter:

var allSurveyCategories = surveys.SelectMany(s =>
     s.Categories.Select(c => new{SurveyId = s.SurveyId, CategoryId = c}));

answered Dec 30 '12 at 16:38

Eren Ersönmez

38,383
7
71
92

I am confusing matters a bit by trying to adjust my example. I have updated my answer to give an example of what I am actually doing. Your answer looks better but I am still trying to understand how to implement it. Thanks – Pricey Dec 30 '12 at 16:48
I have updated my answer based on your use of .SelectMany, its slightly less confusing. – Pricey Dec 30 '12 at 17:38

Pricey · Answer 2 · 2012-12-30T17:34:04.650

Here is what I have done so far, I changed the categories slightly to an int array called CategoryIds just due to how its being used in my system, but I could have done survey.Categories.Select(c => c.Id).Zip(...

// insert using source data from form post matching a ISurvey interface, where .Id will be 0
var surveyId = conn.Query<int>("INSERT ... " + 
                               "SELECT CAST(SCOPE_IDENTITY() AS INT)")
                               .First();

        if(source.CategoryIds != null && source.CategoryIds.Count() > 0) {
            var surveyCategories = source.CategoryIds
                    .Zip(
                        Enumerable.Repeat<int>(
                            surveyId,
                            source.CategoryIds.Count()
                        ),
                    (c, s) => new { SurveyID = s, CategoryID = c }
            );

            conn.Execute(@"INSERT INTO [SurveyCategories] " + 
                         "VALUES (@SurveyID, @CategoryID)", 
                         surveyCategories);
        }

UPDATE: Here is my new approach using SelectMany based on Eren's answer, the use of Enumerable.Repeat(..) is a bit of a hack but this is the only way I have been able to do the same thing so far.

    ...
        var surveyCategories = source.CategoryIds.SelectMany(
            s => Enumerable.Repeat(surveyId, 1),
            (c, s) => new { SurveyID = s, CategoryID = c });
    ...

You cannot avoid looping. If number of elements are same, Zip is OK. Otherwise you need joins — Tilak, Dec 30 '12 at 16:48

one-to-many insert using dapper dot net

2 Answers2