主攻ASP.NET.3.5.MVC架构之重生: LINQ(六)
LINQ to SQL
LINQ to SQL执行标准数据库命令
*查询并创建Id&Title匿名类型
LINQ to SQL Code
MydataDataContext data = new MydataDataContext();
var query = data.Article.Select(m => new { Id = m.ArticleID, Title = m.ArticleTitle });
SqlCode
SELECT [t0].[ArticleID] AS [Id], [t0].[ArticleTitle] AS [Title]
FROM [dbo].[Article] AS [t0]
*查询特殊数据行数
Where()方法之前调用Select()方法,先使用Where()过滤数据
LINQ to SQL Code
MydataDataContext data = new MydataDataContext();
var query = data.Article
.Where(m=>m.CategoryID==30&&m.ArticleID>20)
.Select(m => new { Id = m.ArticleID,Cid=m.CategoryID, Title = m.ArticleTitle });
SqlCode
SELECT [t0].[ArticleID] AS [Id], [t0].[CategoryID] AS [Cid], [t0].[ArticleTitle] AS [Title]
FROM [dbo].[Article] AS [t0]
WHERE ([t0].[CategoryID] = 30) AND ([t0].[ArticleID] > 20)
*查询特殊数据行数
Where()方法之前调用Select()方法,先使用Where()过滤数据
LINQ to SQL Code
MydataDataContext data = new MydataDataContext();
var query = data.Article
.Where(m=>m.CategoryID==30&&m.ArticleID>20)
.Select(m => new { Id = m.ArticleID,Cid=m.CategoryID, Title = m.ArticleTitle });
SqlCode
SELECT [t0].[ArticleID] AS [Id], [t0].[CategoryID] AS [Cid], [t0].[ArticleTitle] AS [Title]
FROM [dbo].[Article] AS [t0]
WHERE ([t0].[CategoryID] = 30) AND ([t0].[ArticleID] > 20)
*查询按创建发布时间排序，并按照[Sort]字段排序
LINQ to SQL Code
MydataDataContext data = new MydataDataContext();
var query = data.Article.OrderBy(m => m.CreateTime).ThenBy(m => m.Sort);
SqlCode
SELECT [t0].[ArticleID], [t0].[CategoryID], [t0].[ArticleTitle], [t0].[ArticleBody], [t0].[IsTop], [t0].[Sort], [t0].[CreateTime], [t0].[SysUserID], [t0].[State]
FROM [dbo].[Article] AS [t0]
ORDER BY [t0].[CreateTime], [t0].[Sort]
*查询按创建发布时间倒序，并按照[IsTop]字段排序
LINQ to SQL Code
MydataDataContext data = new MydataDataContext();
var query = data.Article.OrderBy(m => m.IsTop).ThenByDescending(m => m.CreateTime); SqlCode
SELECT [t0].[ArticleID], [t0].[CategoryID], [t0].[ArticleTitle], [t0].[ArticleBody], [t0].[IsTop], [t0].[Sort], [t0].[CreateTime], [t0].[SysUserID], [t0].[State]
FROM [dbo].[Article] AS [t0]
ORDER BY [t0].[IsTop], [t0].[CreateTime] DESC
*查询单独一行
LINQ to SQL Code
var query = data.Article.Single(m => m.ArticleID == 54);
返回单独一行实例
*查询返回字母a开头的数据。（LINK Select）
LINQ to SQL Code
MydataDataContext data = new MydataDataContext();
var query = data.Article.Where(m => m.ArticleTitle.StartsWith("a"));
using System.Data.Linq.SqlClient;
MydataDataContext data = new MydataDataContext();
var query = data.Article.Where(m => SqlMethods.Like(m.ArticleTitle,"a%"));
SqlCode
SELECT [t0].[ArticleID], [t0].[CategoryID], [t0].[ArticleTitle], [t0].[ArticleBody], [t0].[IsTop], [t0].[Sort], [t0].[CreateTime], [t0].[SysUserID], [t0].[State]
FROM [dbo].[Article] AS [t0]
WHERE [t0].[ArticleTitle] LIKE 'a%'
*连接不同表，内连接（INNER JOIN）
LINQ to SQL Code
MydataDataContext data = new MydataDataContext();
var query = data.AD
.Join(data.AdClass,c=>c.ClassID,m=>m.ClassID,(c,m)=>new {c.ID,c.ClassID,m.ClassName});
SqlCode
SELECT [t0].[ID], [t0].[ClassID], [t1].[ClassName]
FROM [dbo].[AD] AS [t0]
INNER JOIN [dbo].[AdClass] AS [t1] ON [t0].[ClassID] = ([t1].[ClassID])
*连接不同表，外连接（OUTER JOIN）
LINQ to SQL Code
var query = from c in data.AD
join m in data.AdClass
on c.ClassID equals m.ClassID into cm
from m in cm.DefaultIfEmpty()
select new { c.ID, c.ClassID, m.ClassName };
SqlCode
SELECT [t0].[ID], [t0].[ClassID], [t1].[ClassName] AS [ClassName]
FROM [dbo].[AD] AS [t0]
LEFT OUTER JOIN [dbo].[AdClass] AS [t1] ON [t0].[ClassID] = ([t1].[ClassID])
*LINQ tp SQL 插入数据（Insert Data）
1.使用InsertOnSubmit()方法将实体Add到存在表中
2.调用DataContext中SubmitChanges()
LINQ to SQL Code
MydataDataContext data = new MydataDataContext();
SysUser su = new SysUser();
su.Logname = "testLoginName";
su.Password = "123456";
su.State = 1;
su.CreateTime = DateTime.Now;
data.SysUser.InsertOnSubmit(su);
data.SubmitChanges();
int userid = su.SysUserID;
*LINQ tp SQL 更新修改数据(UpDate Data)
1.修改实体属性
2.调用DataContext中SubmitChanges()
3.对实体和底层数据更新修改
LINQ to SQL Code
MydataDataContext data = new MydataDataContext();
SysUser su = data.SysUser.Single(m => m.SysUserID == 13);
su.Logname = "updateUserName";
su.Password = "654321";
data.SubmitChanges();
*LINQ tp SQL 删除数据(Delete Data)
LINQ to SQL Code
MydataDataContext data = new MydataDataContext();
SysUser suDelete = data.SysUser.Single(m => m.SysUserID == 13);
data.SysUser.DeleteOnSubmit(suDelete);
data.SubmitChanges();
*动态查询
执行动态SQL语句
LINQ to SQL Code
MydataDataContext data = new MydataDataContext();
//data.ExecuteCommand("INSERT INTO [SysUser]([Logname],[Password],[State],[CreateTime])VALUES(@p0,@p1)");
var query=data.ExecuteQuery(typeof(SysUser),"Select * from sysuser WHERE sysuserid=@p0",new object[]{2});
*LINQ to SQL 查询日志
1<%@ Page Trace="true"%> => Trace="true"
2调用方法//context.Log = new TraceWrite();
TraceWrite.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.IO;
using System.Globalization;
namespace AllFor.Helpers
{
public class TraceWrite:TextWriter
{
public override void Write(string value)
{
HttpContext.Current.Trace.Warn(value);
}
public override void Write(char[] buffer,int index,int count)
{
HttpContext.Current.Trace.Warn("LINQ",new string(buffer,index,count));
}
public override Encoding Encoding
{
get { return Encoding.Unicode; }
}
public TraceWrite():base(CultureInfo.CurrentCulture)
{
}
}
}
.jpg)
