So you can probably tell that I’ve been doing a lot of LINQ lately. 🙂 One thing I’ve found is that it’s easier for me to write complex queries in the database as stored procedures or views, and then use Linq to SQL to retrieve the results, sort, filter, do paging, etc.

The downside of that approach is the results normally come back as a <view or proc name>Result type, instead of the actual table type. That’s sometimes an issue, because I use partial classes to give my table classes some extra functionality, and I might want to allow updates, etc. against various tables.

So the goal is to be able to call a view or stored procedure, but coerce those results into a good ol’ MyTable object.

You may already know how you can drag a stored procedure or view from your database (in Server Explorer), and drop it on top of a Table entity in the O/R designer. That will cause the stored procedure to return results of that table’s type, instead of <procname>Result.

But Rick Strahl had a great post from a while back explaining how you can use views, procs, & dynamic SQL, and cast those results to a specific table type using ExecuteQuery. A snippet from Rick’s post is below:

What’s also interesting is that when you provide LINQ a query like this it still works with the DataContext’s change tracking. For example, the following code actually works as you’d expect:

IEnumerable<Customer> custList =  context11.ExecuteQuery<Customer>("select * from Customers where CustomerId={0}", "ALFKI");
Customer cust11 = custList.Single<Customer>();
Response.Write(cust11.CompanyName);
cust11.CompanyName = "Alfreds Futterkiste " + DateTime.Now.ToString();
context11.SubmitChanges();

custList = context11.ExecuteQuery<Customer>("select * from Customers where CustomerId={0}", "ALFKI");
cust11 = custList.Single<Customer>();
Response.Write(cust11.CompanyName);

The cool thing (as Rick mentions) is that you don’t have to do a “select * from Customers” — you could do a “select CompanyName, ContactName from Customers” and it will work, too. You don’t need to bring back all columns in order to successfully cast the result to a Customer object.

I’ll post my continuing forays into Linq as I continue. One thing that remains to be seen is whether Linq to SQL is robust enough to be helpful vs requiring me to extend it too much to do what I want/need.