Table Valued Parameters with Entity Framework

I recently worked on a project where I was calling a WCF service that returned a collection of values that had a Name and a Type. I needed to use that list to select values from another database using Entity Framework. A typical result set from the WCF service looked like this:

var list = new List<NameAndType>
{
    new NameAndType { Name = "ABC", Type = "123" },
    new NameAndType { Name = "ABC", Type = "456" },
    new NameAndType { Name = "DEF", Type = "123" },
    new NameAndType { Name = "DEF", Type = "123" }
};

In a local database I had a table that had a composite primary key made up of Name and Type. I needed to write a query that took this list of name/type values and found matching rows in my database. This would be a simple thing to do if I had a single value as my key. If that were the case I could have written a query like this:

var list = List<string> { "ABC", "DEF" };
var items =
    from i in context.Items
    where list.Contains(i.Name)
    select i;

The above query will translate into a statement similar to SELECT * FROM Items WHERE Name IN ('ABC', 'DEF'). The problem is that I had two properties that I needed to predicate on. I wasn't sure how to write the query in Entity Framework but my first thought was something like the following:

var list = new List
{
    new NameAndType { Name = "ABC", Type = "123" },
    new NameAndType { Name = "ABC", Type = "456" },
    new NameAndType { Name = "DEF", Type = "123" },
    new NameAndType { Name = "DEF", Type = "123" }
};

var result =
    from a in context.Items
    join b in list on new { a.Name, a.Type }
        equals new { b.Name, b.Type }
    select a;

This query threw an error complaining that only primitive types could be used. I tried a few more queries but I could not figure out a way to do this. I started to think about how I would actually accomplish this in SQL using a stored procedure and settled on using a Table-Valued Parameter. At first I thought I was going to need to write a stored procedure and call that from Entity Framework, but after doing a little testing I was able to come up with a solution that allowed me to do this using an Entity Framework query:

DataTable dt = new DataTable();
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Type", typeof(string));

list.ForEach(l => dt.Rows.Add(l.Name, l.Type));
SqlParameter tableParameter = new SqlParameter("@Ids", SqlDbType.Structured);
tableParameter.TypeName = "dbo.NameAndType";
tableParameter.Value = dt;

var items = context.Database.SqlQuery<Item>(
    "SELECT * FROM dbo.Items items "
    + "INNER JOIN @Ids ids ON items.Name = ids.Name "
    + "AND items.Type = ids.Type",
    tableParameter);

The code creates a DataTable and adds a row for each item in my list of NameAndType values. It then creates a SqlParameter with SqlDbType of Structured and gives it a TypeName of dbo.NameAndType and a value of the table. I then created an Entity Framework query using the Database.SqlQuery<T> method which allows you to execute some SQL code. I select from my table and join to my table valued parameter and was able to get the items from my database.

One thing to note with this approach is that all of the items returned from the query will not be tracked by Entity Framework. If you need Entity Framework to track the entities you would have to do something similar to this:

foreach ( var item in items )
{
    db.Items.Attach(item);
}

In Summary

This concludes my post on writing an Entity Framework query that uses a table-valued parameter. If you have any questions, feel free to contact me with any questions.