Stored Procedures and UDFs

Linq to Sql supports mapping the results of Stored Procedures and UDFs to objects. This also works when using FluentLinqToSql, but support is not as complete as it is for regular table mappings.

For example, if you have a stored procedure that called CountCustomerOrders that returns rows containing a customer name and the number of orders for that customer, then you could create a class like this:

public class CustomerOrderCount {
	public string Name { get; set; }
	public int NumberOfOrders { get; set; }
}

...and a corresponding method on your DataContext that calls datacontext.ExecuteMethodCall:

public class MyDataContext : DataContext {
	public static string ConnectionString = "Data Source=(local);Initial Catalog=FluentLinqToSql;Persist Security Info=True;Integrated Security=SSPI";
	public MyDataContext(MappingSource mappingSource) : base(ConnectionString, mappingSource) {
	}

	public IEnumerable<CustomerOrderCount> CountCustomerOrders() {
		return (IEnumerable<CustomerOrderCount>)ExecuteMethodCall(this, (MethodInfo)MethodInfo.GetCurrentMethod()).ReturnValue;
	}
}

...then your mapping would look like this:

public class MyFunctionMappings : FunctionMapping<MyDataContext> {
   public MyFunctionMappings() {
      Map(x => x.CountCustomerOrders())
           .ElementType<CustomerOrderCount>(type => {
              type => type.Map(x => x.Name);
              type => type.Map(x => x.NumberOfOrders);
           }); 
   }
} 

Note that rather than inheriting from Mapping<T> you have to inherit from FunctionMapping<YourDataContextType>. You only need one FunctionMapping class per datacontext - all stored procedure / UDFs can be mapped in the same class.

Last edited Sep 6, 2009 at 3:18 PM by JeremyS, version 1

Comments

No comments yet.