Skip to content

Multiple enumerations of IEnumerable<SqlDataRecord> passed as TVP #2064

Open

Description

@pvekshyn

Looks like Dapper twice enumerates IEnumerable passed as TVP

var numbers = GenerateNumbers(3);
BulkInsert(numbers);

static IEnumerable<int> GenerateNumbers(int count)
{
    for (int i = 0; i < count; i++)
    {
        Console.WriteLine(i);
        yield return i;
    }
}

void BulkInsert(IEnumerable<int> numbers)
{
    var records = numbers.Select(x => MapToSqlDataRecord(x));
    var sql = @"INSERT INTO [Test] (Id) SELECT Id FROM @tvp;";

    using (var connection = new SqlConnection(connectionString))
    {
        connection.Execute(sql, new { tvp = records.AsTableValuedParameter("dbo.TestType") });
    }
}

static SqlDataRecord MapToSqlDataRecord(int i)
{
    var record = new SqlDataRecord(
        new SqlMetaData("Id", SqlDbType.Int)
    );
    record.SetInt32(0, i);
    return record;
}

this code writes to console
0
0
1
2

and if I pass enumerable from database it throws exception because can not open second reader on same connection (same enumerable works fine using ADO.Net)

image
I believe this is the first enumeration

Activity

  1. pvekshyn commented on Apr 3, 2024

    @pvekshyn
    Author

    Ah, I got the idea, you are trying to avoid exception when the enumerable is empty.
    This is not correct, better catch exception in this case.

  2. mgravell commented on Apr 3, 2024

    @mgravell
    Member

    I'm inclined to agree; at best we should type-test for IReadOnlyCollection<T> and perform this test via .Count; anything else: just send it down

  3. pvekshyn commented on Apr 3, 2024

    @pvekshyn
    Author

    Let's say I have any source of enumerable: db reader, http returning IAsyncEnumerable, grpc streaming, whatever
    And I want to pass it to TVP without collecting the whole List in memory
    I can do it using ADO.Net, but it's not possible using Dapper because of second enumeration, and it will be definitely not possible with IReadOnlyCollection

  4. mgravell commented on Apr 3, 2024

    @mgravell
    Member

    yes, I understand and agree; the point is that what we should change here is basically:

    if (source is IReadOnlyList<T> { Count:= 0 })
    {
        // short-circuit
    }
    // main code
    

    In your case, the type-test will fail, so we'll just fall into the // main code, without doing any iteration.

  5. pvekshyn commented on Apr 3, 2024

    @pvekshyn
    Author

    Great, thank you

    P.S. on a second thought catching the exception is a bad idea, because sql will not be executed and caller should know about that

  6. VinceSeely commented on Dec 11, 2025

    @VinceSeely

    @mgravell I would love to try giving this problem a stab and see if I can resolve the issue.

  7. bedamien commented on Mar 24, 2026

    @bedamien

    Would using Enumerable.TryGetNonEnumeratedCount() be worthwhile, over using a 'hand-rolled' check?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions