Entity Framework Tutorial Basics(34):Table-Valued Function
Table-Valued Function in Entity Framework 5.0
Entity Framework 5.0 supports Table-valued functions of SQL Server.
Table-valued functions are similar to stored procedure with one key difference: the result of TVF is composable which means that it can be used in a LINQ query.
We have created a TVF GetCourseListByStudentID in the database that will return all the courses of a particular student. For example:
USE [SchoolDB]
GO
/****** Object: UserDefinedFunction [dbo].[GetCourseListByStudentID] */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetCourseListByStudentID]
(
-- Add the parameters for the function here
@studentID int
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
select c.courseid, c.coursename,c.Location, c.TeacherId
from student s left outer join studentcourse sc on sc.studentid = s.studentid left outer join course c on c.courseid = sc.courseid
where s.studentid = @studentID
)
Now, update your EDM and add this TVF into your EDM. Right click on the designer → select Update Model from the Database..
Expand Stored Procedures and Functions node → expand schema node (dbo schema in our case) → select 'GetCourseListByStudentID' and click Finish. Make sure that the checkbox for 'Import selected procedures and functions into the entity model' is checked (this will import the function automatically).
After you imported the function, you can verify it: Open Model Browser → expand Function Imports → right click on imported function 'GetCourseListByStudentID' → click Edit:
You can see that EDM has automatically created the complex type GetCourseListByStudentID_Result as a return collection type.
You can also select an existing entity as a return type if TVF returns the same columns as entity:
Now, you can use TVF with DBContext. For example:
using (var ctx = new SchoolDBEntities())
{
//Execute TVF and filter result
var courseList = ctx.GetCourseListByStudentID().Where(c => c.Location.SpatialEquals(DbGeography.FromText("POINT(-122.360 47.656)"))))
.ToList<GetCourseListByStudentID_Result>(); foreach (GetCourseListByStudentID_Result cs in courseList)
Console.WriteLine("Course Name: {0}, Course Location: {1}",
cs.CourseName, cs.Location);
}
最新文章
- [HTML/HTML5]6 使用图像
- Datatable的Select()
- 在Eclipse中使用JSHint检查JavaScript
- VS2010在非IE浏览器下调试Silverlight程序
- iptables实战系列:通过NAT转发实现私网对外发布信息
- [OM]Dropship SO(直发/直运订单)的流程
- [每日一题] OCP1z0-047 :2013-08-12 view视图的描述哪些是正确的?
- Comparable与compareTo
- JvisualVM、JMC监控远程服务器
- JavaScript基础学习(二)&mdash;JavaScript基本概念
- 最近完成的AndroidStudio项目实现思路及应用技术
- Unix:关于一个file在file system和disk中占用空间
- git命令 高级
- 获取redis主从复制链SHELL脚本
- JLOI2015 DAY1 简要题解
- [luogu P2054] [AHOI2005]洗牌
- POJ - 1474 :Video Surveillance (半平面交-求核)
- Intellij IDEA 2017 debug断点调试技巧与总结详解篇
- 使用Pypi镜像源加速第三方库的安装
- NGINX防御CC攻击教程