How to write Linq2Db version of TVP Query

[DDSGooch]

I've tried all kinds of ways of doing this but just can't get it to work.

How do I convert the following SQL into Linq2Db.

CREATE TABLE Contacts (
    Name varchar(255),
    City varchar(255),
	Modified datetime NULL
);

INSERT Contacts (Name, City)
	VALUES ('John', 'London'),
			('Alex', 'NewYork'),
			('Fred', 'Sydney')

SELECT * FROM Contacts
WHERE Name IN (SELECT Name FROM (VALUES('John')) AS Source(Name))

From the reading I've done I think I need to use the FromSql function but I can't get it work. The query doesn't have to be based on an 'IN' but could use an INNER JOIN on the TVP if that's simpler. I've tried code like the following but get various errors, such as
System.Data.SqlClient.SqlException : Column, parameter, or variable @table. : Cannot find data type dbo.TestTableType. which I'm not surprised by as TestTableType isn't defined. However, this is the sort of thing the TVP unit tests seems to do:

linq2db/Tests/Linq/DataProvider/SqlServerTypesTests.TVP.cs

Line 191 in 314e6e0

public void TableValuedParameterInQueryUsingFromSqlTest(

        public class TVPRecord
        {
            public int? Id { get; set; }
            public string Name { get; set; }
        }

        public static DataTable GetDataTable(IEnumerable<TVPRecord> data)
        {
            var table = new DataTable();

            table.Columns.Add("Id", typeof(int));
            table.Columns.Add("Name", typeof(string));

            foreach (var record in data)
            {
                table.Rows.Add(record.Id, record.Name);
            }

            return table;
        }


      [Test]
      public async Task TVPQuery()
      {
              var table = dbConnection.CreateTable<Contact>();

              table.Insert(() => new Contact
              {
                  Name = "John",
                  City = "London",
              });

              var names = new List<string> { "John", "Fred" };

              var recordList = GetDataTable(names.Select((n, id) => new TVPRecord
              {
                  Id = id,
                  Name = n,
              }));

              var parameter = new DataParameter("table", recordList,  "[dbo].[TestTableType]");

              var result = await dbConnection.FromSql<TVPRecord>($"{parameter}")
                  .InnerJoin(table,
                  (t, s) => t.Name == s.Name,
                  (t, s) => t).ToListAsync();

              Console.WriteLine(result.FirstOrDefault());
}

Any help would be much appreciated.

[DDSGooch]

I think I've used the wrong terminology in this discussion. I think I meant a Table Value Constructor. Anyway, while doing some more searching I discovered this comment: #2145 (comment) which has pointed me at the AsQuery method and I think this will do what I require.

I think I can use it something like this:

        [Test]
        public async Task LargeSelectUsingTableValueConstructor()
        {
            DataConnection.TurnTraceSwitchOn();
            DataConnection.WriteTraceLine = (message, s1, s2) => NUnit.Serilog.Log.ForCurrentTest().Information("{SQL}", message);
            using (var dbConnection = DbConnectionFactory())
            {
                var contactTable = dbConnection.Value.CreateTable<Contact>();

                contactTable.Insert(() => new Contact
                {
                    Name = "John",
                    City = "London",
                });

                var names = new List<string> { "John", "Fred" }
                    .Concat(Enumerable.Range(1, 50000).Select(n => n.ToString()))
                    .AsQueryable(dbConnection.Value);

                var result = await names
                    .InnerJoin(contactTable,
                        (name, contact) => name == contact.Name,
                        (name, contact) => contact)
                    .Where(t => t.Name != null)
                    .ToListAsync();

                Console.WriteLine(result.FirstOrDefault().Name);
            }
        }

In case you're interested, the reason I want to use this rather than the IN approach is that there is no limit on the number of values that can be supplied, whereas if you use the IN approach it's limited to about 40,000 with SQL Server.

[viceroypenguin]

If you ahve 40k records to do a join with, I'd recommend you create a temporary table. Bulk loading the data into the table will most likely be faster than creating an insanely long SQL query and forcing the server to parse it. You can do that as such:

private record ContactName(string Name);

[Test]
public async Task BulkLoadJoin()
{
  DataConnection.TurnTraceSwitchOn();
  DataConnection.WriteTraceLine = (message, s1, s2) => NUnit.Serilog.Log.ForCurrentTest().Information("{SQL}", message);
  using var dbConnection = DbConnectionFactory();

  var contactTable = dbConnection.Value.CreateTable<Contact>();

  contactTable.Insert(() => new Contact
  {
    Name = "John",
    City = "London",
  });

  await using var names = await dbConnection.Value.CreateTempTable(
    Enumerable.Range(1, 50000)
      .Select(n => new ContactName(n.ToString()))
      .Append(new ContactName("John"))
      .Append(new ContactName("Fred")));

  var result = await names
    .InnerJoin(contactTable,
      (name, contact) => name == contact.Name,
      (name, contact) => contact)
    .Where(t => t.Name != null)
    .ToListAsync();

  Console.WriteLine(result.FirstOrDefault().Name);
}

[DDSGooch]

Thanks, that's great.

Yes, you're right when we have this scenario (i.e. a large number of values to find) using a temporary table almost certainly be better but the technique with the AsQueryable function will be useful in other places.