one-to-many insert using dapper dot net

c# dapper linq

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?

Accepted Answer

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}));

Popular Answer

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 });
    ...


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why