linq设计器生成的属性没有默认值，如getDate()将失效；
可视化设计使用dbml文件, 保存后自动生成 [dbml名].design.cs后缀的代码文件, 生成的类都是部分类.同时允许我们手工添加 [dbml
名].cs文件,这个不会在dbml修改时被重写
1.如果想自动创建数据库中两个表的关系, 需要将这两个表同时拖出.
2.单击设计器空白处,查看属性面板, 有很多属性可以在这里修改:最有用的上下文命名空间 和 实体命名空间. 上下文命名空间是
DataContext类的命名空间, 我通常将其放置在DataAccess层中.实体命名空间是所有Model类所在的命名空间, 我将其放置在Model层中.
3.O/R 设计器允许我们创建一个不会重写的类来扩成自动生成的代码,创建方法是在O/R设计器的空白处点右键, 在弹出菜单中选择"查看代
码"会在.dbml文件中添加一个.cs文件
Linq转Sql语句：
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
context.SubmitChanges();
sw.ToString();//为转换后的sql语句
ref(按引用传值):
SYS_LOG log = new SYS_LOG();
SetLogUrlProperty(context, ref log);//log的URL信息，如Controller和Action的名称
SetLogSqlProperty(userId, sql, ref log);//log的用户、调用的SQL、日期信息
log.Note = note;
log.Result = isSuccess ? 1 : 0;
log.ErrorReason = errorReason;
Linq插入数据：
private static LangSinBlogLinqDataContext db = new LangSinBlogLinqDataContext();
public static void insertArticle(Article article) {
Tab_Article t = new Tab_Article();
t.Atitle = article.Atitle;
t.Aclass = 1;
t.Aauthor = "admin";
db.Tab_Article.InsertOnSubmit(t);
db.SubmitChanges();
}
Linq查询数据：
public static IQueryable getArticleList() {
return from article in db.Tab_Article orderby article.id descending select article;
}
Linq查询一条数据：var article = db.Tab_Article.Single(t => t.id == Convert.ToInt32(id));
Linq更新一条数据：
public static void updateArticle(Article a) {
var article = db.Tab_Article.Single(t => t.id == Convert.ToInt32(a.Id));
article.Atitle = a.Atitle;
db.SubmitChanges();
}
Linq更新一个字段的所有数据：
public string UpdateRole(int RoleID, string RoleName, string Description)
{
try
{
var obj = sysdb.SYS_ROLE.SingleOrDefault(x => x.RoleID == RoleID);
if (obj == null)
{
return "该角色已不存在，可能同时多个客户端在操作本项.";
}
obj.RoleName = RoleName;
obj.Description = Description;
sysdb.SubmitChanges();
}
catch (System.Exception ex)
{
return ex.Message;
}
return string.Empty;
}
Linq删除一条数据:
public static void deleteArticle(string id) {
var article = db.Tab_Article.Single(t => t.id == Convert.ToInt32(id));
db.Tab_Article.DeleteOnSubmit(article);
db.SubmitChanges();
}
Linq多表查询：
public static IQueryable getArticleListByClass(string _aclass) {
if (_aclass == null || _aclass == "")
{
var query = from t in db.Tab_Article
orderby t.id descending
from c in db.Tab_Article_Class
where t.Aclass == c.id
select new
{
id = t.id,
AIsOut = t.AIsOut,
Atitle = t.Atitle
};
return query;
}
else {
var query = from t in db.Tab_Article
orderby t.id descending
from c in db.Tab_Article_Class
where t.Aclass == c.id
&&
t.Aclass == Convert.ToInt32(_aclass)
select new
{
id = t.id,
Atitle = t.Atitle
};
return query;
}
}
1.别名
GridView1.DataSource = from c in Customers where c.CustomerID.StartsWith("A") select new {顾客ID=c.CustomerID, 顾客名=c.Name, 城市=c.City};
2.关键字orderby、descending
rpt_Message.DataSource = from gb in ctx.tbGuestBooks orderby gb.PostTime descending select gb;
3.Single()
tbGuestBook gb = ctx.tbGuestBooks.Single(b => b.ID == 1);
4.group by
var 一般分组 = from c in ctx.Customers
group c by c.Country into g
where g.Count() > 5
orderby g.Count() descending
select new
{
国家 = g.Key,
顾客数 = g.Count()
};
group c by new { c.City, c.Country } into g
group o by new { 条件 = o.Freight > 100 } into g
sublist.GroupBy(m => new { m.denom, m.country })
inventoryList.GroupBy(m => m.denom)
5.关键字distinct
var 过滤相同项 = (from c in ctx.Customers orderby c.Country selectc.Country).Distinct();
6.关键字Union
var 连接并且过滤相同项 = (from c in ctx.Customers where c.City.Contains("A") select c).Union
(from c in ctx.Customers where c.ContactName.StartsWith("A") select c).OrderBy(c => c.ContactName);
7.关键字Concat
var 连接并且不过滤相同项 = (from c in ctx.Customers where c.City.Contains("A") select c).Concat
(from c in ctx.Customers where c.ContactName.StartsWith("A") select c).OrderBy(c => c.ContactName);
8.关键字join
var innerjoin = from p in ctx.Products
join c in ctx.Categories
on p.CategoryID equals c.CategoryID
select p.ProductName;
9.外衔接，相当于leftjoin
var leftJoin = from student in db.Student
join book in db.Book on student.ID equals book.StudentID into temp
from tt in temp.DefaultIfEmpty()
select new
{
sname= student.Name,
bname = tt==null?"":tt.Name//这里主要第二个集合有可能为空。需要判断
};
10.
var 单结果集存储过程 =
from c in ctx.sp_singleresultset()
where c.CustomerID.StartsWith("A")
select c;
11.调用带参数的存储过程
create proc [dbo].[sp_withparameter]
@customerid nchar(5),
@rowcount int output
as
set nocount on
set @rowcount = (select count(*) from customers where customerid = @customerid)
调用：
ctx.sp_withparameter("ALFKI", ref rowcount);
Response.Write(rowcount);
12.调用带返回值的存储过程
create proc [dbo].[sp_withreturnvalue]
@customerid nchar(5)
as
set nocount on
if exists (select 1 from customers where customerid = @customerid)
return 101
else
return 100
调用：
Response.Write(ctx.sp_withreturnvalue("ALFKI"));
13.多结果集的存储过程
create proc [dbo].[sp_multiresultset]
as
set nocount on
select * from customers
select * from employees
找到生成的存储过程方法：
[Function(Name="dbo.sp_multiresultset")]
public ISingleResult<sp_multiresultsetResult> sp_multiresultset()
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
return ((ISingleResult<sp_multiresultsetResult>)(result.ReturnValue));
}
由于现在的VS2008会把多结果集存储过程识别为单结果集存储过程（只认识第一个结果集），我们只能对存储过程方法多小动手术，修改为：
[Function(Name="dbo.sp_multiresultset")]
[ResultType(typeof(Customer))]
[ResultType(typeof(Employee))]
public IMultipleResults sp_multiresultset()
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
return (IMultipleResults)(result.ReturnValue);
}
然后使用下面的代码调用：
var 多结果集存储过程 = ctx.sp_multiresultset();
var Customers = 多结果集存储过程.GetResult<Customer>();
var Employees = 多结果集存储过程.GetResult<Employee>();
GridView1.DataSource = from emp in Employees where emp.FirstName.Contains("A") select emp;
GridView1.DataBind();
GridView2.DataSource = from c in Customers where c.CustomerID.StartsWith("A") select c;
GridView2.DataBind()
var query =
from n in names
where n.Length > 3
let u = n.ToUpper()
where u.EndsWith ("Y")
select u;
var dynamicTextItems = from drow in LabelTranslationLines let dlineData = importHelper.GetTextFromRow(drow) where dlineData.StartsWith("_dynamicText") let dlineDataList = importHelper.GetNonBlankItemArray(drow) select new { Row = drow, LineData = dlineData, LindDataList = dlineDataList }; int documentHeaderLineNumber = 9; //find this out and replace as needed var dataTextItems = from drow in LabelTranslationLines.Skip(documentHeaderLineNumber) let dlineData = importHelper.GetTextFromRow(drow) where !dlineData.StartsWith(ImportHelper.IgnoreLineOfDataPrefix) let dlineDataList = importHelper.GetNonBlankItemArray(drow) select new { Row = drow, LineData = dlineData, LindDataList = dlineDataList };
int totalQuantity = (from loss in db.IncidentLosses where loss.ReportId == reportId select loss.Quantity).Sum(); double totalValue = (from loss in db.IncidentLosses where loss.ReportId == reportId select loss.Value).Sum(); string language = fnLib.userLanguage(); IQueryable<IncidentReportLossRow> rows = from loss in db.IncidentLosses where loss.ReportId == reportId && loss.vwt_IncidentLossCategory.Language == language && loss.vwt_IncidentLossItemType.Language == language orderby loss.Id descending let status = from statuses in db.vwt_IncidentLossStatus where statuses.Id == loss.StatusId && statuses.Language == language select statuses.Title //the dbml file wont associate for some reason! select new IncidentReportLossRow { Category = loss.vwt_IncidentLossCategory.Title, Type = loss.vwt_IncidentLossItemType.Title, Status = status.FirstOrDefault(), Quantity = loss.Quantity, Value = loss.Value };
16.关键字 into
from n in names
select new
{
Original = n,
Vowelless = n.Replace ("a", "").Replace ("e", "").Replace ("i", "").Replace ("o", "").Replace ("u", "")
}
into temp
where temp.Vowelless.Length > 2
select temp.Original
names.Select ((s,i) => i + "=" + s) //i是索引，s是值
18.linq将字符串返回数组集合：
IEnumerable<int> templateEntities = Enumerable.Empty<int>();
if (!string.IsNullOrEmpty(template.Entities))
{
templateEntities = from t in template.Entities.Split(',')
select SharedHelper.TryParseInt32(t) into x
where x.HasValue
select x.Value;
}
19. NOT IN
var controlList=from control in context.ProtectionControls
where !(from riskToControl in context.ProtectionRiskToProtectionControls
select riskToControl.ProtectionControlId)
.Contains(control.Id)
orderby control.Id select new { Id = control.Id, Name = control.DropDownDisplayName };
20.dbml文件属性设置：
