SQL 2008 TVP (Table Valued Parameters) and LINQ - It is Possible! (Sorta)

I'm sure you saw all those blog posts about SQL Server's TVP (table valued parameters) so I'll be brief and I won't go into much details.

The Solution

Hi,

First of all, LINQ to SQL doesn't support the use of TVP natively, period. So I decided to try to make a workaround or something that emulates it and I think my solution is pretty transparent for the data context user.

Here's what I tried to do: I wanted to retrieve LINQ objects from my data context by passing a variable length argument to a method which in turn calls a stored procedure that uses TVP. Simple?

Here we go (don't mind about the usefulness of my example, focus on the "how")...

First of all, let's create the TVP type. I needed to pass a list of emails to a stored procedure, so my type is as follow:

CREATE TYPE [dbo].[Email] AS TABLE
(
    [Email] [nvarchar](150) NOT NULL,
    PRIMARY KEY CLUSTERED([Email] ASC) WITH (IGNORE_DUP_KEY = OFF)
)

Then, let's create the method in a partial class of the data context:

public partial class YourDataContext : System.Data.Linq.DataContext
{
    public IEnumerable<Member> GetMembersMatchingXyz(List<string> emails)
    {
        DataTable emailsTable = new DataTable();
        emailsTable.Columns.Add("Email", typeof(string));

        foreach (string e in emails)
            emailsTable.Rows.Add(e);

        using (SqlConnection conn = new SqlConnection(Connection.ConnectionString))
        {
            SqlCommand cmd = new SqlCommand("my_stored_proc", conn);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter p = cmd.Parameters.AddWithValue("@emails", emailsTable);
            p.SqlDbType = SqlDbType.Structured;
            p.TypeName = "Email";

            conn.Open();
            SqlDataReader reader = cmd.ExecuteReader();

            // Naturally, you already retrieved your objects so no deferred
            // loading needed nor posible, hence the ToList()
            return this.Translate<Member>(reader).ToList();
        }        
    }
}

So there you have it, LINQ objects retreived from a table valued parameter. The real deal is the last line of the method where Translate is called. It creates LINQ objects from your existing data reader and bind them to your data context.

I told you it would be brief because the code says it all and also because there's a lot of examples out there...

But none of them explained how to return full blown LINQ objects from your data context! At least, none that I'm aware of... So this is what I wanted to show you. I think it's a more transparent way to use TVPs with LINQ.

Hope you liked it.

I'm putting time and effort on this blog and having you here is my reward. Make me feel better and better everyday by spreading the love with the buttons below! Also, don't hesitate to leave a comment. Thanks for reading!

Related Articles

• Improve Performance of your LINQ to SQL Queries - Query Compilation
• Uncommitted Reads in LINQ: Avoiding Deadlocks and Timeouts!

See ya