First, we will try to call a simple stored procedure. In the sample database, there is a stored procedure called Ten Most Expensive Products. We will call this stored procedure to get the top ten most expensive products.

Querying a stored procedure

Now, from Program.cs, we can call this stored procedure as follows:

var tenProducts = from p in 
    NWEntities.Ten_Most_Expensive_Products()
    select p;
foreach (var p in tenProducts)
{
    Console.WriteLine("Product Name: {0}, Price: {1}",
    p.TenMostExpensiveProducts, p.UnitPrice);
}

As we know the return result of the stored procedure, we can also replace the var datatype with the specific return type, as shown in the following code snippet:

IEnumerable<Ten_Most_Expensive_Products_Result> tenProducts = 
    from p 
    in NWEntities.Ten_Most_Expensive_Products()
    select p;
foreach (Ten_Most_Expensive_Products_Result p in tenProducts)
{
    Console.WriteLine("Product Name: {0}, Price; {1}",
    p.TenMostExpensiveProducts, p.UnitPrice);
}

In this way, we can clearly see the datatype of the tenProducts variable, so later on we can compare this query with the one that maps to an existing entity class.

The SQL statement is pretty straightforward, as follows:

exec [dbo].[Ten Most Expensive Products]

The output will look like the one shown in the following screenshot:

In the above example, LINQ to Entities created a new type for the return result of the stored procedure. It actually just added the word Result after the stored procedure name to create the name of the return datatype. If we know that the return result is a kind of entity, we can tell LINQ to Entities to use that specific entity as the return type instead of creating a new type.

For example, let's create a stored procedure as follows:

Create PROCEDURE [dbo].[GetProduct]
   (
   @ProductID int
   )
AS
   SET NOCOUNT ON
   Select * from Products where ProductID = @ProductID

You can create this stored procedure in Microsoft SQL Server Management Studio or by right-clicking on the Stored Procedures node in Server Explorer of Visual Studio and selecting Data Connections | Northwind.dbo | Add New Stored Procedure from the context menu.

After the stored procedure has been created, follow these steps to add it to the entity data model and add a function import:

To call this method you can write a statement as follows:

Product getProduct = NWEntities.GetProduct(1).FirstOrDefault();

The complete method for the stored procedure should be as follows:

static void TestStoredProcedure()
{
using(NorthwindEntities NWEntities = new NorthwindEntities())
  {
    IEnumerable<Ten_Most_Expensive_Products_Result> tenProducts =
              from p
              in NWEntities.Ten_Most_Expensive_Products()
              select p;
    Console.WriteLine("Ten Most Expensive Products:");
    foreach (Ten_Most_Expensive_Products_Result p in tenProducts)
    {
      Console.WriteLine("Product Name: {0}, Price; {1}",
                 p.TenMostExpensiveProducts, p.UnitPrice);
    }

    // map a stored procedure to an entity class
  Product getProduct = NWEntities.GetProduct(1).FirstOrDefault();
  Console.WriteLine("
Product name for product 1:{0}", 
    getProduct.ProductName);
  }
}

And if you run the program, you should have an output as shown in the following screenshot:

Interestingly, you can add another function for the same stored procedure but with a different function name (GetProduct1), and for the new function, you can check the Complex radio button to create a new type (GetProduct1_Result) for the result of the stored procedure instead of using the Product class. LINQ to Entities will automatically create a new class for the return type.

The generated return type class GetProduct1_Result is almost identical to the Product class.

A big difference between the GetProduct and GetProduct1 methods is that the product you retrieved using GetProduct is managed by the DbContext entity. Any changes you made to it will be committed back to the database if you call SaveChanges() later. However, the product you retrieved using GetProduct1 is not managed by the DbContext entity, and thus won't be committed back to the database if you call SaveChanges() later.