Add a Stored Procedure
Open the Northwind.edmx designer.Right-click on an empty space of the designer surface and select Update Model from Database.expand the Stored Procedures node, check Ten Most Expensive Products (dbo), and then click on the Finish button.
On the designer surface, right-click on an empty space, and select Add from the context menu, then select Function Import.On the Add Function Import window, select Ten_Most_Expensive_Products as the stored procedure name them retype this name as the Function Import Name.Click on the Create New Complex Type button to create a new entity type.Type the function import name as "Ten_Most_Expensive_Products_Function".
Click OK.
we can call this stored procedure as follows
var myProduct = from p in objNORTHWINDEntities.Ten_Most_Expensive_Products_Function()
select p;
foreach (var p in myProduct)
{
Console.WriteLine("Product Name: {0}, Price: {1}",
p.TenMostExpensiveProducts, p.UnitPrice);
}
Console.ReadLine();
Similarly you can call create a stored proc with param and call from your application-
Create PROCEDURE [dbo].[GetProductFrmProductID]
(
@ProductID int
)
AS
SET NOCOUNT ON
Select * from Products where ProductID = @ProductID
Add a stored proc as the same way ,choose Entities instead of complex type as the stored proc returns the data as product entity type-
use the LINQ as follows-
NORTHWINDEntities objNORTHWINDEntities = new NORTHWINDEntities();
var myProductFromSP = from p in objNORTHWINDEntities.Product_Function(2)
select p;
foreach (var p in myProductFromSP)
{
Console.WriteLine("Product ID: {0}, Name: {1}",
p.ProductID, p.ProductName);
}
Console.ReadLine();
when you run it,the output will be as follows-
Entity Framework version 1.0 only has limited support for stored procedures.It can't handle multiple result sets from the same stored procedure, not to mention dynamic result sets (the stored procedure returns different result sets according to different input parameters).
Compiled Query
A compiled query is actually a function. The function contains a compiled LINQ query expression and can be called just like a regular function.It increase performance by compiling the query once and executing it several times in the application with different parameters.
The following code shows how to define a compiled query:
NORTHWINDEntities objNORTHWINDEntities = new NORTHWINDEntities();
Func<NORTHWINDEntities, strin>, IQueryable<Product>> fn
= CompiledQuery.Compile((NORTHWINDEntities NW, strin> cate>ory) =>
from p in NW.Products
where p.Cate>ory.Cate>oryName == cate>ory
select p);
var objCompiledQueryOutput = fn(objNORTHWINDEntities, "Bevera>es");
Console.WriteLine("Total products in Bevera>es cate>ory : {0}",
objCompiledQueryOutput.Count());
Console.ReadLine();
Direct SQL
LINQ to entity supports direct SQL with the following methods-
The ExecuteStoreQuery() method lets you execute a raw SQL query and converts the result of your query directly into objects.
The ExecuteStoreCommand() method lets you directly execute SQL commands against the database.
Example-
NORTHWINDEntities objNORTHWINDEntities = new NORTHWINDEntities();
var directSQL = objNORTHWINDEntities.ExecuteStoreQuery<Product>(
"SELECT * " +
"FROM Products " +
"WHERE UnitPrice >28" +
"ORDER BY ProductName;"
);
Console.WriteLine("Total products :{0}",
directSQL.Count());
int rowCount = objNORTHWINDEntities.ExecuteStoreCommand(
" update products "
+ "set UnitPrice=UnitPrice+1 "
+ "where productID=35");
if (rowCount < 1)
Console.WriteLine("No product is updated");
else
Console.WriteLine("Product price is updated");
Dynamic query with parameters
We can also build a dynamic query string with parameters and then pass different parameters to query the database at runtime.
string queryString =
@"SELECT VALUE Product
FROM NorthwindEntities.Products
AS Product
WHERE Product.ProductID = @id";
ObjectQuery<product> productQuery =
new ObjectQuery<Product>(queryString, objNORTHWINDEntities);
productQuery.Parameters.Add(new ObjectParameter("id", 1));
foreach (var p in productQuery)
{
Console.WriteLine("Product name: {0}", p.ProductName);
}
Console.ReadLine();
Inheritance
LINQ to Entities supports three types of inheritance: Table Per Hierarchy (TPH) inheritance, Table Per Type (TPT) inheritance, and Table Per Concrete (TPC) inheritance.
In Table per Hierarchy inheritance there is a single database table that contains fields for both parent information and child information. With relational data a discriminator column contains the value that determines which class any given record belongs to.
Suppose we have a person table as follows
CREATE TABLE [dbo].[person](
[PersonID] [int] IDENTITY(1,1) NOT NULL,
[LastName] [nvarchar](20) NOT NULL,
[FirstName] [nvarchar](10) NOT NULL,
[EnrollmentDate] [datetime] NULL,
[HireDate] [datetime] NULL,
[PersonType] [varchar](1) NULL,
CONSTRAINT [PK_person] PRIMARY KEY CLUSTERED
(
[PersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
As you can see, the table holds two different fields from two different types: HireDate (which belong to a professor type) and EnrollmentDate (which belongs to student type). The table also includes an Text discriminator field which is called PersonType. When PersonType equals P, the person type is a professor and when it is S, the type is student.
The following steps will help you to understand how you can create a TPH inheritance mapping.
Update your model from database so that you can add the new entity person
Change the entity class name from person to BasePerson.
Right-click on an empty space on the designer surface, then choose
Add | Entity… from the context menu.
Create two Entity "Professor" and "Student" and make "BasePerson" as their base type.
Cut HireDate from BasePerson and paste it to Professor class and Cut Enrollment date from BasePerson to Student Class.
Right-click on the PersonType property of the BaseCustomer entity, and select Delete from the context menu. We need to delete this property because we will use this property as our discriminator.
Now select the Professor entity on the model designer, and go to the Mapping Details window (it should be next to your Output window or you can open it from menu View | Other Windows | Entity Data Model Mapping Details).
On the Mapping Details window, click on
Again on the Mapping Details window, click on
Repeat the above process for Student type so that it looks as follows-
No comments:
Post a Comment