Tuesday, January 12, 2010

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



See ya

16 comments:

Unknown said...

Where is your Translate method? Can we see that?

Unknown said...
dripcode said...

nice!

Arkain said...

Awesome thanks :)

Anonymous Avatar Anonymous said...

Very nice!

I was able to get this working within the existing Linq function which returns ISingleResult, rather than IEnumerable. It just took a bit of trial to find the right cast.

return (ISingleResult)this.Translate(MySQLDataReader).GetResult();

Anonymous Avatar Anonymous said...

Apparently it has an issue with the > and < thinking it's markup.

return (ISingleResult < Member > )this.Translate(MySQLDataReader).GetResult < Member >();

Unknown said...

Thanks for the addition :) Very cool!

Pritesh Ostwal said...

Thanks this is a saver

Anonymous Avatar Anonymous said...

Excellent solution. Thank you.

Anonymous Avatar Anonymous said...

Excellent job, thank you...

LearningMan said...

Thank you very much, implemented and worked seamlessly. :)

Unknown said...
This comment has been removed by the author.
Unknown said...
This comment has been removed by the author.
Anonymous Avatar Anonymous said...

Where is your Member class?

siva said...

Using this we are reading table type parameters.

How do we pass table type parameters to a stored procedure

Unknown said...

Is it possible to pass table type to sp using dbml?

©2009-2011 Mike Gleason jr Couturier All Rights Reserved